Link to home
Start Free TrialLog in
Avatar of mjpeet
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("connected 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].ToString() + ", " + dr[1].ToString());
                        }

                        dr.Close();
                  }
                  catch (Exception e) {
                        Console.WriteLine(e.Message);
                  }
                  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
Avatar of mjpeet
mjpeet

ASKER

Doubled the points.  Anyone?
Avatar of mjpeet

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

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
Avatar of mjpeet

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
Did you specify the databaseName while configuring the MyODBC object?

Hope this helps
.K.
ASKER CERTIFIED SOLUTION
Avatar of modulo
modulo

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial