0

I'm trying to port a website that uses a Microsoft Access database to MySQL. As a starting point, I'm trying to open the SQL database:

The (old) Access code was:

Set cn = Server.CreateObject ("ADODB.Connection")
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open "PathToThe/database.db"

The (new) MySQL code is:

Dim connection_string, cn

connection_string = "Driver={MySQL ODBC 5.1 Driver};" &_
"server=url;" &_
"port=3306;" &_
"option=131072;" &_
"stmt=;" &_
"database=databasename;" &_
"uid=username;" &_
"pwd=thepassword"

Set cn = Server.CreateObject("ADODB.Connection")

cn.Open connection_string

When I use the above code, I get a "500 - Internal server error." If I comment out the "cn.Open connection_string" statement, I don't get the error (of course, the database isn't opened).

This is on a hosted Windows 2012 server.

I can connect to the SQL database using the MySQL Workbench program and the above url, database, username, and password, and I can see the tables and data. I can query the database using my hosts "Test MySQL" command with a simply "Select * from tablename"

I've check my connection string over and over. I've copied and pasted the url, password, etc from my source code into the Workbench to make sure I don't have a typo. I've tried without the "option=" setting (and setting it to 3). I've tried using a string literal rather than my connection_string variable. I am surprised that I get a 500 error rather than a database error.

Stumped.

UPDATE: It turned out that the hosting company didn't have the ODBC drivers installed.

Steve A
  • 1,798
  • 4
  • 16
  • 34
  • Since the DB is being opened on the server, I'd suspect the issue is the URL. Have you tried using `localhost`? – Ken White Jun 13 '16 at 23:58
  • Your connection string looks different from [the one at ConnectionStrings.Com](https://www.connectionstrings.com/mysql-connector-odbc-5-1/) – Flakes Jun 14 '16 at 05:48
  • 1
    There are loads of possible causes. Is the MyODBC driver installed and registered? Is the version referred to in your code the same as the one installed? Does your website's IUSR account have read/execute permission. The first thing i recommend you do is to enable friendly error messages so that you get a more detailed description of what's wrong - http://www.chestysoft.com/asp-error-messages.asp – John Jun 14 '16 at 12:17
  • Thanks Ken White, but this is on a hosted server and (I think but don't know) localhost is for my local machine, no? – Steve A Jun 14 '16 at 13:46
  • Thanks John. This is on a shared server, so I don't have much access to IIS admin commands. I did add "On Error Resume Next" to my asp file and then display Err.Description, which states, "Data source name not found and no default driver specified." I'm suspecting that the driver isn't loaded on the server, but don't know how to check it remotely (and my host isn't being very cooperative). – Steve A Jun 14 '16 at 13:49
  • 1
    If you don't have RDP access then you can use web.config to enable friendly error messages - see this question. http://stackoverflow.com/questions/2640526/detailed-500-error-message-asp-iis-7-5. A lot of shared hosts use the older version (3.51) of the driver - so this connection string is worth trying - http://www.connectionstrings.com/mysql-connector-odbc-3-51/ - however if it isn't explicitly stated that that your hosting package includes MySQL then it probably isn't supported. The JET driver ships with Windows, you need to install MyODBC yourself – John Jun 14 '16 at 14:21
  • 1
    @John is right it's likely an issue with the ODBC driver installation, is it 32-bit only? Is the ASP Web application running in 32 bit mode? – user692942 Jun 14 '16 at 14:22
  • Thanks John and Lankymart. The "On Error Resume ..." and "Err.Description" code did get me the asp messages, but I'm unsure how (on a shared, hosted, server) I can find out which ODBC are loaded (and if they are). I've written to my host... – Steve A Jun 14 '16 at 14:39
  • 2
    One more thought - if it's shared hosting and it does support MySQL then the it's most likely that it would mean using a database on the server itself. If you're trying to connect to a remote third party database you're likely to find that the firewall won't let you. @Lankymart makes a good point about 32 bit mode. As you have been using an Access database to date you almost certainly are in a 32 bit -environment - as the JET OLEDB driver is 32 bit only. – John Jun 15 '16 at 10:34
  • 1
    It turned out that my host did NOT have the ODBC drivers installed. They installed both the 32 and 64 bit drivers and voila! Thanks for your responses. They did lead me to feel the problem was on the host's end (and keep the pressure one until they fixed it. (John: The database is mine, ported from the Access db to a new SQL database. I'm doing this on an new server that doesn't have the JET drivers... hence the need for MySQL. Thanks again.) – Steve A Jun 15 '16 at 22:50

1 Answers1

0

It turned out that the hosting company didn't have the ODBC drivers installed (despite their assurances to the contrary). Groan.

Steve A
  • 1,798
  • 4
  • 16
  • 34