How to use MySQL with ASP

When I decided to use MySQL in my sample web site, I had some problems getting it to work as expected, because there are very scattered resources about combining these two tools. Looks like Microsoft guys don't want to accept MySQL as a database tool and that's why you can't find a word about it on Microsoft's web site. On the other hand, Unix guys believe that Microsoft things are all bugs!!! and they try to avoid it.
I'm a Microsoft fan. I've been working with MS-SQL server for years now. But as you can see, free things (like MySQL) push you to change your course. (I wonder who would change from Microsoft, if Linux and all related stuff were not free!!!)
In this case (MySQL), Microsoft is in big trouble. MySQL has many good capabilities, even FullText search, and they are going to provide Stored Procedures support in version 4.2, which is a must for such a database server. So, Microsoft must do something really cool to attract more customers.


Step 0: Installing MySQL server and MyODBC

I assume that you know how to download and install and run MySQL on your development system. You can download the latest version from www.mysql.com. Do not use versions which are not stable yet.
If you are using a Windows environment, you will need MyODBC drivers too. Run the setup program and go with default settings. Then run mysql.exe which in in your mysql\bin folder.
I suggest you download and use dbtools specially if you are familiar with Microsoft SQL server manager.

Step 1: Connection String

First of all you must define your connection string. Best practice is to define it in your global.asa file. Use an Application variable and initialize it in your Application_onstart subroutine:

Sub Application_OnStart
    Application("DBCONN")="Driver={MySQL};SERVER=localhost;DATABASE=xxx; UID=xxx;PWD=xxx;Extended Properties='OPTION=16387'"
End Sub

Change the SERVER, DATABASE, UID, and PWD as you need. If you don't use "OPTION=16387", it will give you the message "Multiple-step operation generated errors" when you try to change values of table fields. This option means "Don't Optimize Column Width".


Step 2: Connection Handling

As I saw in other's codes, the best way to handle connections to the MySQL database is to open a connection in each ASP page, when you want to access the database, and then close that connection when you are done with database.
So, the best practice is to create two INCLUDE files, one to open connection and the other to close it.

openconn.inc

<%
    Set myConn = Server.CreateObject("ADODB.Connection")
    myConn.open(Application("DBCONN"))
%>

closeconn.inc

<%
    myConn.close
    Set myConn=Nothing
%>

And I think you know how to use these two INCLUDE files:

<!-- #include file="openconn.inc" -->
   <%
   ...
   ... Step 3 code
   ...
   %>
<!-- #include file="closeconn.inc" -->

Step 3: Accessing data

If you just want to read a set of data from a MySQL table, simply use the following syntax:

Set MyResultSet = MyConn.Execute("SELECT * FROM ... ")

But if you want to be able to use the RecordCount property, you have to set the CursorLocation property of the RecordSet to value adUseClient (3).

Set MyResultSet = Server.CreateObject("ADODB.recordset")
MyResultSet.CursorLocation = adUseClient
MyResultSet.Open "SELECT * FROM ...", myconn

This will work in most of the situations. But some times, using a Client Cursor causes ADO to generate E_FAIL error 80004005 for "Microsoft Cursor Engine". I think this is because of incompatibility between MyODBC driver and ADO. In this case, forget about using adUseClient. To determine the number of records, you can issue another query with "SELECT COUNT(*)" like the following example:

set MainQuery = MyConn.Execute("SELECT * FROM ... WHERE ...")
set CountQuery = MyConn.execute("SELECT COUNT(*) FROM ... WHERE ...")
Count = CountQuery(0)
set CountQuery = nothing

If you are going to change the data (AddNew or Update), you should set the LockType property of the RecordSet to a value that lets you change the data, like adLockOptimistic (3).

Set CheckCateg = Server.CreateObject("ADODB.recordset")
CheckCateg.Open "SELECT * FROM ...", myconn, , adLockOptimistic

I have learned a lot of these from a page created by Dominic Winsor. But in Step 1 and 3, I have added some tricks that I learned the hard way!!!