Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Connecting to mySQL database, or rather not connecting!

    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!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    What if you don't specify any option at all (instead of option 3)?

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    That doesn't help. Option=3 supposedly specifies the connection is from Access or VB.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Option 3 has an totally different meaning, IMO, it's specific to MySQL.

    Can you run the query directly in MySQL?

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    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!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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.

  7. #7
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •