Accessing MySQL with ASP
Tuesday, December 30th, 2003 | Programming, Tech
Being an active server page coder by nature, all my sites are in ASP. So eventually I hit a problem – two sites want to use the same Access database but they are on different hosts. Therefore I decided it would be best to use a MySQL database that they could share by making remote connections.
Why use MySQL?
The main advantage is as I have already mentioned, having a central MySQL database allows you to connect all your sites directly to it and access the information. There are ways to do this in Microsoft Access but they are patchy at best. The other option would be to use MS SQL server although this doesn’t come cheap from any host.
Changing the code
Let’s say for example I have a MySQL database on a linux hosting account at www.examplelinux.com. And I have a website at www.examplewindows.com that I want to connect with. Luckily connecting to MySQL is similar to any other connection except we just need to make a few changes.
The first is obvious; the connection script needs to be changed as we are accessing MySQL rather than an Access database. The second are certain changes in the scripts to make sure we don’t end up with any MySQL errors.
A typical DNS less connection would be something like:
Driver={Microsoft Access Driver (*.mdb)}; DBQ=" + Server.MapPath("/Conndb/springer1.mdb")
Whereas a connection to MySQL would look more like:
Driver={MySQL ODBC 3.51 Driver}; Server=examplelinux.com; Database=dbname; UID=username; PASS=password
To do this your server needs to have the MySQL driver on it. You can download it from http://www.mysql.com/downloads/index.html. You can also use a DNS connection if you have one which makes things considerably simpler:
DSN=linuxexampledns
All you need to do is replace the connection line you currently use with the new one modifying the username, password and name of the database you are going to use on your target server.
Further modifications
To make the ASP code work with MySQL there are a few other modifications you need to make with your code. The problem arises from the use of the ‘ character. Where Access is ok with it, MySQL will bring up a nice big error message.
Luckily a bit of PHP style code can come to the rescue. If you are familiar with PHP, you will know you can remove the significance of a character using \ before it. Therefore a quick replace function does the trick
Replace(string, "'", "\'")
So for instance a select command would go from:
SELECT * FROM hobbies WHERE firstname = '" + (users.Fields.Item("firstname").Value) + "' ORDER BY name ASC
And changed to:
SELECT * FROM hobbies WHERE firstname = '" + Replace((users.Fields.Item("firstname").Value) ,"'","\'") + "' ORDER BY name ASC
Setting up the server
Once the modifications to the code have been made you can get on with setting up the server to accept the requests. First of all you need to allow the scripts to access the database.
In CPanel goto MySQL Database and scroll down to access hosts: then add in the IP address of www.windowsexample.com. If you can’t find it out, simply run the scripts and it will bring you up an access denied message containing the IP address you are trying to access the database from.
In Direct Admin goto MySQL Databases, click on the database and then you can enter the IP address in to the access hosts form near the bottom. If you can access the privileges section in phpMyAdmin directly you can also do it from there as you can set for each user what access host the user can use.
The only other problem is getting the contents of your Access database to your MySQL server. The easiest way I found to do this way to set up a DNS connection to the MySQL server on my local machine, though you may be able to do this directly to the server providing you grant yourself access, and then going into Access and Exporting it.
File > Export then open the drop down menu at the bottom and and scroll to the bottom of the list until you see ODBC Databases (). This will close the export window and prompt you to enter the name of what you want to call it when you export it. Most likely it will be best to leave the default here.
Then it will come up with a box asking you to select a data source. Select the machine data source tab and find your DNS connection on the list then click ok and your done.
Conclusion
Switching your database from Access to MySQL is a hassle – you need a Linux server, code changes and connection scripts. However if your sites need to share a database its a far cheaper option that using MS SQL.
Being an active server page coder by nature, all my sites are in ASP. So eventually I hit a problem – two sites want to use the same Access database but they are on different hosts. Therefore I decided it would be best to use a MySQL database that they could share by making remote connections.
Why use MySQL?
The main advantage is as I have already mentioned, having a central MySQL database allows you to connect all your sites directly to it and access the information. There are ways to do this in Microsoft Access but they are patchy at best. The other option would be to use MS SQL server although this doesn’t come cheap from any host.
Changing the code
Let’s say for example I have a MySQL database on a linux hosting account at www.examplelinux.com. And I have a website at www.examplewindows.com that I want to connect with. Luckily connecting to MySQL is similar to any other connection except we just need to make a few changes.
The first is obvious; the connection script needs to be changed as we are accessing MySQL rather than an Access database. The second are certain changes in the scripts to make sure we don’t end up with any MySQL errors.
A typical DNS less connection would be something like:
Driver={Microsoft Access Driver (*.mdb)}; DBQ=" + Server.MapPath("/Conndb/springer1.mdb")
Whereas a connection to MySQL would look more like:
Driver={MySQL ODBC 3.51 Driver}; Server=examplelinux.com; Database=dbname; UID=username; PASS=password
To do this your server needs to have the MySQL driver on it. You can download it from http://www.mysql.com/downloads/index.html. You can also use a DNS connection if you have one which makes things considerably simpler:
DSN=linuxexampledns
All you need to do is replace the connection line you currently use with the new one modifying the username, password and name of the database you are going to use on your target server.
Further modifications
To make the ASP code work with MySQL there are a few other modifications you need to make with your code. The problem arises from the use of the ‘ character. Where Access is ok with it, MySQL will bring up a nice big error message.
Luckily a bit of PHP style code can come to the rescue. If you are familiar with PHP, you will know you can remove the significance of a character using \ before it. Therefore a quick replace function does the trick
Replace(string, "'", "\'")
So for instance a select command would go from:
SELECT * FROM hobbies WHERE firstname = '" + (users.Fields.Item("firstname").Value) + "' ORDER BY name ASC
And changed to:
SELECT * FROM hobbies WHERE firstname = '" + Replace((users.Fields.Item("firstname").Value) ,"'","\'") + "' ORDER BY name ASC
Setting up the server
Once the modifications to the code have been made you can get on with setting up the server to accept the requests. First of all you need to allow the scripts to access the database.
In CPanel goto MySQL Database and scroll down to access hosts: then add in the IP address of www.windowsexample.com. If you can’t find it out, simply run the scripts and it will bring you up an access denied message containing the IP address you are trying to access the database from.
In Direct Admin goto MySQL Databases, click on the database and then you can enter the IP address in to the access hosts form near the bottom. If you can access the privileges section in phpMyAdmin directly you can also do it from there as you can set for each user what access host the user can use.
The only other problem is getting the contents of your Access database to your MySQL server. The easiest way I found to do this way to set up a DNS connection to the MySQL server on my local machine, though you may be able to do this directly to the server providing you grant yourself access, and then going into Access and Exporting it.
File > Export then open the drop down menu at the bottom and and scroll to the bottom of the list until you see ODBC Databases (). This will close the export window and prompt you to enter the name of what you want to call it when you export it. Most likely it will be best to leave the default here.
Then it will come up with a box asking you to select a data source. Select the machine data source tab and find your DNS connection on the list then click ok and your done.
Conclusion
Switching your database from Access to MySQL is a hassle – you need a Linux server, code changes and connection scripts. However if your sites need to share a database its a far cheaper option that using MS SQL.