Using MyODBC To Access Your MySQL Database Via ASP - Setting up a system DSN
(Page 3 of 6 )
The first way that we can connect to a MySQL database is via a System Data Source Name (DSN). Using a system DSN to setup a connection details to a MySQL database is easy, and can be done through the "Data Sources (ODBC)" option in the Administrative folder of the control panel. Let's create one now (I will assume that you have MySQL installed on the same machine as your Windows NT/2000 web server).
- Click on the start menu -> Settings -> Control Panel -> Administrative Tools -> Data Sources (ODBC) menu option. This will load the ODBC data source administrator tab.
- Because we want to create a system DSN that is accessible to any user logged into our web server, click on the "System DSN" tab, and then on the "Add..." button.
- Select the MySQL driver from the bottom of the list and click the finish button.
- The MySQL driver configuration dialog appears. It contains seven text boxes and a variety of check boxes.
Into the Windows DSN name field, enter "mysql_dsn". This is simply the name that we will use to refer to our DSN from within ASP. Into the MySQL host field, enter either the Net BIOS name or I.P. address of your MySQL server. If you have MySQL installed locally (on the same machine that you are creating the system DSN on), then enter 127.0.0.1.
In the database name field, enter the name of our address book database, "address". This tells MySQL the name of the database we want to issue our SQL queries to when we are connected.
Enter your MySQL username and password details into the user and password fields. If you aren't sure what they are and you're running MySQL on Windows, then run c:\mysql\bin\winmysql.exe and click on the "my.ini Setup" tab. Under the "[WinMySQLAdmin]" section, you'll find your default MySQL username and password. Leave both the port and sql command on connect fields empty. - Lastly, make sure that the "Return matching rows" checkbox is ticked. Click on the OK button to create your new MySQL system DSN.
You'll notice the new MySQL DSN is displayed in the system data sources list of the system DSN tab. The details of our new system DSN are stored in the registry under the HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\mysql_dsn tab. You can use the edit -> find menu option of regedit (start -> run -> "regedit") to search for "mysql_dsn" to see how its details are stored.
Now that we have our system DSN all ready to go, let's create an ASP page to actually test it out. Create a new ASP file named dsn_test.asp. Enter the following code into dsn_test.asp:
<%
on error resume next
dim adoConn
dim adoRS
dim counter
set adoConn = Server.CreateObject("ADODB.Connection")
set adoRS = Server.CreateObject("ADODB.Recordset")
adoConn.Open "DSN=mysql_dsn"
adoRS.ActiveConnection = adoConn
if adoConn.errors.count = 0 then
response.write "<h2>Fields In The 'Contacts' Table:</h2>"
adoRS.Open "describe contacts"
while not adoRS.EOF
response.write adoRS.fields(0).value & "<br>"
adoRS.MoveNext
wend
else
response.write "ERROR: Couldn't connect to database"
end if
%>As you can see, we're using an ADO connection object to connect to our MySQL database. We pass the name of our system DSN to its open method, prefixed with "DSN=", telling the connection object to extract the details of our connection from the "mysql_dsn" system DSN.
We check the "errors.count" property of our ADO connection to make sure that the connection attempt completed successfully and that no errors were generated. Once connected, we simply use a recordset object and the MySQL command "describe [table name]" to list the fields contained in our "contacts" table. We will talk more about the describe command later.
Next: Using a connection string >>
More ASP Articles
More By Annette Tennison
