Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Connection Succeeds but "No Database Selected" on SELECT

Posted on 2003-11-11
8
1,155 Views
Last Modified: 2008-03-17
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
0
Comment
Question by:mjpeet
8 Comments
 
LVL 1

Author Comment

by:mjpeet
ID: 9730899
Doubled the points.  Anyone?
0
 
LVL 1

Author Comment

by:mjpeet
ID: 9731003
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
0
 

Expert Comment

by:vijji
ID: 9731749

its looking like a bug man ..:))
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Expert Comment

by:spike3382
ID: 9737261
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
0
 
LVL 1

Author Comment

by:mjpeet
ID: 9739106
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
0
 
LVL 2

Expert Comment

by:xssass
ID: 9963127
Did you specify the databaseName while configuring the MyODBC object?

Hope this helps
.K.
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12689688
PAQed with points refunded (250)

modulo
Community Support Moderator
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question