MyODBC - Connect to MySQL from ASP/ASPX

MyODBC is a system driver which is required if you want to connect to a MySQL database from an Active Serve Page (ASP) document, or any other type of server pages such as PHP or JSP.

Firstly run the setup program. After the installation finishes, open Control Panel/Administrative Tools/Data Sources. Click Add Data and select MySQL x.xx add the end of the list.

Enter the data source name (to some extent, this can be any name you like; as the name will not affect the ASP script) and type the username and password.

Click OK to close the dialog box.

A sample code to open a MySQL Database from an ASP file is shown below

<%
Dim sConnection, objConn , objRS

sConnection = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=database_name; UID=username;PASSWORD=password; OPTION=3"

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

objConn.Open(sConnection)

Set objRS = objConn.Execute("SELECT username, realname FROM accounts")

While Not objRS.EOF
Response.Write objRS.Fields("username") & ", " & objRS.Fields("realname") & "<br>"
Response.Write " "
objRS.MoveNext
Wend

objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

An explanation of the OPTION parameter is found below

Configuration - Option Value

Microsoft Access - 3

Microsoft Visual Basic - 3

Large tables with too many rows - 2049

Driver trace generation (Debug mode) - 4

Query log generation (Debug mode) - 524288

Generate driver trace as well as query log (Debug mode) - 524292

Large tables with no-cache results - 3145731

For more information, visit

http://dev.mysql.com/doc/mysql/en/connection-parameters.html