Connecting to mySQL database, or rather not connecting!

Topic

New Reply

I’m trying to connect to a remote mySQL database.

I created a passthrough query and set the ODBC connection string as follows (using generic names here):

ODBC;Driver={MySQL ODBC 5.1 Driver};Server=mySQLserver.com;Database=mydatabase;User=myusername;Password=myPassword;Option=3

I get an “ODBC error – call failed” message. If I put in what I know is an incorrect password, I get the mySQL connection screen. I can put in the correct password and hit “Test” and it says “Connected”. There is even a dropdown with all the databases on the server. So I know I’m connecting, but my query still fails. I don’t get it!

Reply | Quote

Replies

What if you don’t specify any option at all (instead of option 3)?

Reply | Quote

That doesn’t help. Option=3 supposedly specifies the connection is from Access or VB.

Reply | Quote

Option 3 has an totally different meaning, IMO, it’s specific to MySQL.

Can you run the query directly in MySQL?

Reply | Quote

Well, I found out today that tablenames in mySQL are case sensitive! I had a simple SQL statement I was using to test, “SELECT * FROM tblCustomer”, that worked directly in the mySQL database. We eventually figured out the problem (it was a connection issue related to an inadvertent change in security), but in the meantime I had changed the SQL statement in the passthrough to “SELECT tblcustomer.* FROM tblCustomer”. This statement works in Access and in SQL Server, but not in mySQL! Interestingly, we did some further testing and found that fieldnames are NOT case sensitive. Go figure!

Reply | Quote

Table names are case sensitive if the database server is hosted on Linux. I think it is because of the file system, as each table is stored in a separate file, using the file’s systems characteristics . If the database is hosted on a Windows server, they are not case sensitive. Weird, I know.

Reply | Quote

P.S.: That’s why I always use SQLYog as a first test tool. Good to connect direct to the server and test your stuff. The community edition is free.

Reply | Quote