Solved

Connection Succeeds but "No Database Selected" on SELECT

Posted on 2003-11-11
8
1,193 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
[X]
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
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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

729 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