Hello. I have shared hosting with mysql database. I have successfully connected to the db, but can’t get data from it. I get an error at the stage of using the reader. Please, help me extract query result correctly.
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using System;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;
public class test : MonoBehaviour
{
private string source;
private MySqlConnection connection;
void Start () {
SetupSQLConnection();
TestDB();
CloseSQLConnection();
}
private void SetupSQLConnection() {
if (connection == null) {
string connectionString = "server=mysql76.hostland.ru;user=host1817122;database=host1817122;password=************";
try {
connection = new MySqlConnection(connectionString);
connection.Open();
Debug.Log("Successfull connect! =)");
} catch (MySqlException ex) {
Debug.LogError("MySQL Error: " + ex.ToString());
}
}
}
private void CloseSQLConnection() {
if (connection != null) {
connection.Close();
}
}
public void TestDB() {
string sqlQuery = "SELECT id, name, value FROM rewards_list";
using (MySqlCommand command = new MySqlCommand(sqlQuery, connection)) {
try {
using (MySqlDataReader reader = command.ExecuteReader()) {
}
} catch(MySqlException e) {
Debug.Log("error message: " + e.Message);
Debug.Log("sql error number: " + e.Number);
}
}
}
Among other things, it turns out to display the total number of records in the table using ExecuteScalar ():
string sqlQuery = "SELECT COUNT(*) from rewards_list";
using (MySqlCommand command = new MySqlCommand(sqlQuery, connection)) {
object result = command.ExecuteScalar();
{
int r = Convert.ToInt32(result);
Debug.Log(r);
}
}
But the output of several records at once leads to a new error:
string sqlQuery = "SELECT * from rewards_list";
using (MySqlCommand command = new MySqlCommand(sqlQuery, connection)) {
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Debug.Log(reader[0].ToString());
}
}
That indicates that your original SELECT was the problem. Most likely, you asked for a field that doesn’t exist in the “rewards_list” table (or you mistyped the field name, which SQL thinks is the same thing as asking for a field that doesn’t exist).
No idea what the problem is now, but there’s a lot more code in test.cs if it has a Line 101. Can you post the whole thing?
There’s also no WHERE clause - though I don’t know it’s required, I’ve always been under the assumption it was.
Try: SELECT * FROM `rewards_list` WHERE 1
WHERE clauses are optional. When you leave them out, you simply get the entire table (or the cartesian product of all tables named in your query, which can be enormous).
There have been various kinds of connection attempts. Now I’ve deleted everything, leaving only 2 output methods - scalar number and strings. Scalar output works, strings don’t. Code at the moment:
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using System;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;
public class test : MonoBehaviour
{
private string source;
private MySqlConnection connection;
void Start () {
SetupSQLConnection();
TestDB();
CloseSQLConnection();
}
private void SetupSQLConnection() {
if (connection == null) {
string connectionString = "server=mysql76.hostland.ru;user=host1817122;database=host1817122;password=*****";
try {
connection = new MySqlConnection(connectionString);
connection.Open();
Debug.Log("Successfull connect! =)");
} catch (MySqlException ex) {
Debug.LogError("MySQL Error: " + ex.ToString());
}
}
}
private void CloseSQLConnection() {
if (connection != null) {
connection.Close();
}
}
public void TestDB() {
string sqlQuery = "SELECT * from rewards_list WHERE id = 1"; // FOR ROWS
//string sqlQuery = "SELECT COUNT(*) from rewards_list"; // FOR SCALAR
using (MySqlCommand command = new MySqlCommand(sqlQuery, connection)) {
command.CommandTimeout = 1000;
/// Rows
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Debug.Log(reader[0].ToString());
}
///
/* /// Scalar
object result = command.ExecuteScalar();
if (result != null)
{
int r = Convert.ToInt32(result);
Debug.Log(r + " rows in table");
}
/// */
}
}
void Update () {
}
}
Can you confirm there is a field called “id” in the rewards_list table? Is your database set to use case sensitivity, so that maybe the field should be “ID” instead of “id?”
Not really related to your issue, but I just want to make something clear:
Hopefully you don’t create a game here ^^. You never ever want a client to have access to your database. Since you have to ship your database credentials with your client, everyone could read them out and directly mess with your database. That’s why in almost all cases you handle the actual database stuff on your server and provide a simple, robust and secure interface (usually https via php) to get or update anything database related.
Apart from that I just quickly dropped the mysql connector into my plugins folder and tried to query my local mysql server as well as the mysql server running on my raspberry pi using your code as a foundation. I noticed that at least my mysql servers (mysql 5.5.21 on my win10 and mysql 5.5.62 on RPI) I had to use “username” instead of “user” in the connection string.
Apart from that this runs fine in Unity:
string connectionString = "server=<raspberrypiIP>;username=<username>;database=<dbname>;password=<password>";
using (var connection = new MySqlConnection(connectionString))
{
connection.Open();
using (MySqlCommand command = new MySqlCommand("select * from user", connection))
{
MySqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
Debug.Log("has rows");
while (reader.Read())
{
int fieldCount = reader.FieldCount;
for (int i = 0; i < fieldCount; i++)
{
Debug.Log("#"+i+" "+reader.GetName(i)+" = " + reader.GetValue(i).ToString());
}
}
}
connection.Close();
}
My “user” table has 4 columns “id”, “name”, “password” and “salt”. I get all those fields just like expected. Are you sure your user has the required permissions on the table / columns? Since it’s an externally hosted server, are you sure it actually allows connections from outside? If this server is part of a webspace they usually limit connection from localhost or at least to the actual webserver.
Can you try if you can connect to your database using a client like HeidiSQL or just the mysql command line client? If that fails or don’t give you access to your desired databases / tables it’s most likely a server side restriction / issue.
That’s a very good question. I wouldn’t be shocked if his account won’t let him query the primary key field, which any field named “id” is likely to be. He does say he can execute a “SELECT COUNT(*),” which says his connection succeeds. I’m thinking he needs to query something he is sure will return a recordset, assuming everything actually is working.
