Home arrow ASP arrow Page 3 - Using MyODBC To Access Your MySQL Database Via ASP
ASP

Using MyODBC To Access Your MySQL Database Via ASP


MySQL is the most popular open source database system available today, and is currently installed on over two million servers worldwide. In this article Annette takes a look at using the free MyODBC driver to talk to a MySQL database from an ASP script.

Author Info:
By: Annette Tennison
Rating: 5 stars5 stars5 stars5 stars5 stars / 260
January 18, 2002
TABLE OF CONTENTS:
  1. · Using MyODBC To Access Your MySQL Database Via ASP
  2. · Installing MyODBC
  3. · Setting up a system DSN
  4. · Using a connection string
  5. · Querying our MySQL database
  6. · Conclusion

print this article
SEARCH DEVARTICLES

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).
  1. Click on the start menu -> Settings -> Control Panel -> Administrative Tools -> Data Sources (ODBC) menu option. This will load the ODBC data source administrator tab.
  2. 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.
  3. Select the MySQL driver from the bottom of the list and click the finish button.
  4. 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.
  5. 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.
blog comments powered by Disqus
ASP ARTICLES

- Central Scoreboard with Flash and ASP
- Calorie Counter Using WAP and ASP
- Creating PGP-Encrypted E-Mails Using ASP
- Be My Guest in ASP
- Session Replacement in ASP
- Securing ASP Data Access Credentials Using t...
- The Not So Ordinary Address Book
- Adding and Displaying Data Easily via ASP an...
- Sending Email From a Form in ASP
- Adding Member Services in ASP
- Removing Unconfirmed Members
- Trapping HTTP 500.100 - Internal Server Error
- So Many Rows, So Little Time! - Case Study
- XDO: An XML Engine Class for Classic ASP
- Credit Card Fraud Prevention Using ASP and C...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials