Home arrow ASP arrow Page 4 - 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 - Using a connection string
(Page 4 of 6 )

An alternative to using a system DSN to connect to a MySQL database is the connection string. Connection strings allow us to connect to a database using an ADO connection object and its open method in the same way that we use a system DSN, however, instead of making the connection object retrieve our connection details from a DSN, we explicitly supply them, like this:

adoConn.Open "Driver={mySQL}; Server=localhost; Port=3306; Option=0; Socket=; Stmt=; Database=address; Uid=admin; Pwd=password;"

The connection string to connect to a MySQL database is specified in exactly the same way as that for an SQL Server or Access database. Each parameter is a name/value pair separated by a semi-colon. The details of each of these parameters are shown below:



Parameter

Default Value

Comments

Driver

 

Tells the connection object to use the MySQL driver to attempt the database connection

Server

localhost

The hostname/I.P. address of our MySQL server

Port

3306

The TCP/IP port to use when communicating with our MySQL server

Option

0

An integer for which we can assign a value to tell MyODBC how it should attempt certain tasks

Socket

 

The socket or windows pipe to connect to

Stmt

 

A valid SQL query that will be executed when a connection to the MySQL server is established

Database

 

The name of the default database that MySQL should run our queries against

Uid

 

The username that the MyODBC driver should supply to the MySQL server during authentication

Password

 

The password that the MyODBC driver should supply to the MySQL server during authentication



The option parameter allows us to specify such things as the type of columns our MyODBC driver can handle, how to connect to the MySQL server, which version of the ODBC driver to use, etc. Generally though, we donít need to specify anything for the option parameter, so it can be left as zero. You can read more about the option parameter here

Create a new ASP file named cs_test.asp. Enter the following code into cs_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 "Driver={mySQL}; Server=localhost; Port=3306; Option=0; Socket=; Stmt=; Database=address; Uid=admin; Pwd=password;"

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

%>


If you take a look at the dsn_test.asp file that we created earlier, you'll notice that the only difference is in the parameter passed to the open method of our ADO connection object.

To use a DSN to connect to our MySQL database, we used the following parameter for the open method of our ADO connection object:

"DSN=mysql_dsn"

To connect with a connection string, we used the following code:

"Driver={mySQL}; Server=localhost; Port=3306; Option=0; Socket=; Stmt=; Database=address; Uid=admin; Pwd=password;"

Personally, I prefer to use connection strings instead of DSN connections. Some say that under heavy loads, a DSN connection can slow the server down because of the time involved in accessing the registry to get the DSNís connection properties. I like to define the parameters for my connection string in an external .inc file and use ASP's "include" function to make then available in my code.

For example, create a new file named "database.inc" and add the following code ASP to it:

<%

dim dbDriver

dim dbDatabase

dim dbUser

dim dbPass

dbDriver = "{mysql}"

dbDatabase = "address"

dbUser = "admin"

dbPassword = "password"

%>

Next, create a file named ďparam_test.aspĒ and enter the following code into it:

<!-- #INCLUDE file="database.inc" -->

<%

dim adoConn

set adoConn = Server.CreateObject("ADODB.Connection")

adoConn.Open "Driver=" & dbDriver & "; Database=" & dbDatabase & "; Uid=" & dbUser & ";Pwd=" &

dbPassword & ";"

if adoConn.errors.count = 0 then

response.write "Connected Successfully!"

end if

%>


Our param_test.asp page uses ASP's "<!-- #INCLUDE -->" directive to make the contents of the database.inc file available to itself. Then it's just a simple matter of substituting those values into the connection string. Notice how we haven't explicitly specified every parameter in the connection string? When we do this, the MyODBC driver assumes that the MySQL server is the local machine and attempts the connection using its default values for the parameters we donít specify.
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