mjpeet
asked on
Connection Succeeds but "No Database Selected" on SELECT
Hi All,
I'm using MyOdbc 3.51 and ADO.NET to connect to a MySql 4.0 database. The connection works, but when I try to execute a SELECT I get:
{"ERROR [3D000] [MySQL][ODBC 3.51 Driver][mysqld-4.0.16-max- debug]No Database Selected" }
Here's the code:
string connStr = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=test; uid=testuser; PASSWORD=testuser; OPTION=3;";
OdbcConnection conn = new OdbcConnection(connStr);
conn.Open();
Console.WriteLine("connect ed to: " + conn.Database);
try {
string sql = "select * from my_table";
OdbcCommand cmd = new OdbcCommand(sql, conn);
OdbcDataReader dr = cmd.ExecuteReader();
while (dr.Read()) {
Console.WriteLine(dr[0].To String() + ", " + dr[1].ToString());
}
dr.Close();
}
catch (Exception e) {
Console.WriteLine(e.Messag e);
}
finally {
conn.Close();
}
You can see that "conn.Database" is blank. I can get it to work if I do this immediately after connecting:
sql = "use test";
cmd = new OdbcCommand(sql, conn);
cmd.ExecuteNonQuery();
But I don't think I should have to do that every time; I haven't seen that in any examples and I am specifying the database in the connection string.
Thanks in advance!
Mike
I'm using MyOdbc 3.51 and ADO.NET to connect to a MySql 4.0 database. The connection works, but when I try to execute a SELECT I get:
{"ERROR [3D000] [MySQL][ODBC 3.51 Driver][mysqld-4.0.16-max-
Here's the code:
string connStr = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=test; uid=testuser; PASSWORD=testuser; OPTION=3;";
OdbcConnection conn = new OdbcConnection(connStr);
conn.Open();
Console.WriteLine("connect
try {
string sql = "select * from my_table";
OdbcCommand cmd = new OdbcCommand(sql, conn);
OdbcDataReader dr = cmd.ExecuteReader();
while (dr.Read()) {
Console.WriteLine(dr[0].To
}
dr.Close();
}
catch (Exception e) {
Console.WriteLine(e.Messag
}
finally {
conn.Close();
}
You can see that "conn.Database" is blank. I can get it to work if I do this immediately after connecting:
sql = "use test";
cmd = new OdbcCommand(sql, conn);
cmd.ExecuteNonQuery();
But I don't think I should have to do that every time; I haven't seen that in any examples and I am specifying the database in the connection string.
Thanks in advance!
Mike
ASKER
Update:
Works fine using a DSN with these settings, but I don't want to use a DSN for deployment reasons.
Also, this is very strange behavior: If I add this code just after connecting:
conn.ChangeDatabase("test" );
I get the error:
Unknown database 'test1' <--- Note the '1' here! Nowhere do I specify 'test1' as the database!
Out of curiousity, I created a database named 'test1' and created my table in it. Then the above line works and everything is fine. Note that I still need the above line; specifying the database I want to use in the connection string doesn't seem to do anything.
Mike
Works fine using a DSN with these settings, but I don't want to use a DSN for deployment reasons.
Also, this is very strange behavior: If I add this code just after connecting:
conn.ChangeDatabase("test"
I get the error:
Unknown database 'test1' <--- Note the '1' here! Nowhere do I specify 'test1' as the database!
Out of curiousity, I created a database named 'test1' and created my table in it. Then the above line works and everything is fine. Note that I still need the above line; specifying the database I want to use in the connection string doesn't seem to do anything.
Mike
its looking like a bug man ..:))
What happens when you make a database called something other than test or test1 and specify that in the connect string? If vijji's right it could be some hairball bug that only occurs with a DB named test. Probably not, I know, but worth a shot. If it tries to connect to "somedatabase1" when you do it, then it might be useful to try putting some other sort of delimiter around the db name... like single quotes or backticks, just to let it know that you really mean just test and not test1. I really have no idea.. haven't used myodbc very much, but it might be worth a shot as a debugging step.
-Matt
-Matt
ASKER
Well, I discovered that I could prefix the table name with the database name. So, instead of:
string sql = "select * from test_table";
I can do:
string sql = "select * from test.test_table";
Or:
string sql = "select * from test1.test_table";
Provided both databases have the table "test_table".
Mike
string sql = "select * from test_table";
I can do:
string sql = "select * from test.test_table";
Or:
string sql = "select * from test1.test_table";
Provided both databases have the table "test_table".
Mike
Did you specify the databaseName while configuring the MyODBC object?
Hope this helps
.K.
Hope this helps
.K.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER