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.

Next: Querying our MySQL database >>
 

More ASP Articles
More By Annette Tennison

Comments On: Using MyODBC To Access Your MySQL Database Via ASP

· I had a few problems with getting the "describe" to work, as the...   >>> Post your comment now!