|
|
|
|
 |
 |
|
|
|
|
|
Using MyODBC To Access Your MySQL Database Via ASP
http://www.webdevelopersnotes.com/tutorials/sql/installing_mysql_on_windows.php3
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.Active Server Pages are a Microsoft technology that allows developers proficient with either Visual Basic or VBScript to easily adapt their skills and knowledge to the web with very little effort at all. ASP uses ActiveX Data Object (ADO) to connect to several popular database management systems including Microsoft Access and SQL Server, Oracle, dBase and Visual Fox Pro.
Did you know that you can also talk to MySQL databases using ODBC through ASP? The MySQL database can either be on the same server, or on the other side of the world; running Windows, Linux, Solaris, FreeBSD, MacOS X, or any other one of the several operating systems that MySQL supports.
If you've never worked with an open source database management system such as MySQL before, then you¡¯re truly missing out. MySQL is the most popular open source database system available today, and is currently installed on over two million servers worldwide. Huge companies such as Yahoo! Finance, MP3.com and Motorola all use MySQL in their mission-critical application and they swear by it. Best of all, you can use MySQL for free in most circumstances!
In this article we're going to take a look at using the MyODBC driver to talk to a MySQL database in ASP. We will look at two different ways to connect to a MySQL database using the MyODBC driver: via a DSN, and via a connection string. We will use some standard SQL (ANSI) queries to work with tables by adding, updating and deleting records for an address book database. Lastly, MySQL comes with some very handy SQL commands that allow us to view information about databases and tables; we'll take a look at those too.
To get the full benefits of this article, you should have either a Windows NT/2000 machine running IIS and an intermediate knowledge of ASP, ADO and the standard ANSI-compliant SQL syntax.
Installing MyODBC
If you've ever done any work with databases through ASP using ADO, then you've probably used an ODBC driver to connect to that database. Several ODBC drivers come pre-installed with Windows, and they allow us to connect-to and manipulate several different databases using one a consistent set of objects.
MyODBC is the Open Database Connectivity (ODBC) compliant driver that we can use to connect to a MySQL database. Here's how MySQL.com describes the MyODBC driver:
"MySQL provides support for ODBC by means of [the] MyODBC program. MyODBC is a 32-bit ODBC (2.50) level 0 (with level 1 and level 2 features) driver for connecting an ODBC-aware application to MySQL.
MyODBC works on Windows95, Windows98, NT, 2000, XP and on most Unix platforms. Very many people use MyODBC daily with Access, VBA, Excel, Word, Delphi, ASP, Cold Fusion and more."
MyODBC is a free download from MySQL.com and is a snap to stall. Let's take a look at how to install the MyODBC driver right now.
[Note] I'm assuming that you either already have MySQL installed either on your PC, or have the login details of a remote PC where MySQL is installed. Either way, make sure you have access to a MySQL server before continuing. If you’ve never used MySQL, checkout Mitchell's article entitled "MySQL: Open-Source Power" here [End Note]
- Start by firing up your web browser and heading over to http://www.mysql.com. Take a look under the "Versions" tab on the right hand side of the page and click on MyODBC. At the time of writing, the current version on MyODBC was 2.50.39.
- Under the "MyODBC for Windows" section, click on the link that matches your operating system. In this article I'm focusing on Windows NT/2000, so click the NT/2000/XP link. This will take you to the mirrors page. Click on the link for the mirror that's closest to you and save the download to a folder on your hard drive (The download is a 1.45MB .zip file, so visit WinZip.com to download WinZip if you haven’t got it already).
- Once complete, extract the zip file to a temporary directory and run the included setup.exe file. This will install the MyODBC driver. When the installation is completed, simply click on the close button to bypass the data sources configuration dialog. Click the OK button and you're done!
If the setup program reports any errors or behaves in a weird way, check out the MySQL documentation.
Now that we've got the MyODBC driver installed, let's setup a MySQL database so that we can take a look at the connect to a MySQL database.
Setting up our MySQL database
Before I discuss using DSN's and the connection string to connect to a MySQL database, let's create a new MySQL database.
Using the MySQL console application available on both Windows (c:\mysql\bin\mysql.exe) and Linux/Unix (/usr/local/mysql), enter the following code to create a new database and one new table, which we will use later to play around with some MySQL commands:
create database address;
use address;
create table contacts
(
contactId int auto_increment not null,
firstName varchar(50),
lastName varchar(50),
address1 varchar(100),
address2 varchar(100),
phone varchar(20),
primary key(contactId),
unique id(contactId)
);
I won't go into too much detail about the SQL above, but we've just created a new database named address, which contains one table named contacts.
Let's now look at how we can setup a system DSN to access our MySQL database through ASP.
Setting up a system DSN
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.
Using a connection string
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.
Querying our MySQL database
As mentioned earlier, MySQL conforms to standard (ANSI) structured query language. As with SQL Server 2000 and Oracle, MySQL also has its own set of custom SQL functions that are available for us to use. We will talk more about these soon.
Using MySQL, we can add, update and delete records, just like we can for any other database management system that supports an OLEDB driver. Firstly, let's take a look at how we would add a record to our contacts table using the ADO recordset object:
<%
on error resume next
dim adoConn
dim adoRS
dim strQuery
set adoConn = Server.CreateObject("ADODB.Connection")
adoConn.Open "DSN=mysql_dsn"
strQuery = "INSERT INTO contacts VALUES(0, 'Jill', 'Smith', '20 Santamonica Parade', 'Beverly Hills', '555 0303')"
adoConn.Execute strQuery
%>
To update a record, we simply modify our query:
strQuery = "UPDATE contacts SET firstName = 'Jack' WHERE firstName = 'Jill'"
Lastly, to delete a record, we can use the ANSI SQL command, delete:
strQuery = "DELETE FROM contacts WHERE firstName = 'Jack'"
The LIMIT keyword
MySQL also has some implementation specific keywords that can save us an extraordinary amount of time. The first of these is "LIMIT". Using the limit keyword within a select statement, we can tell MySQL how many rows of data to return, and which row index to start fetching these rows from. For anyone working with Access/MS SQL looking to start using MySQL, setting up recordset paging will be extremely easier!
The limit keyword should be appended to the end of a MySQL query after the where and group by keywords. It accepts two arguments, with only the first required:
LIMIT [offset,] rows
If we use the limit keyword with just one argument, then MySQL will start the results offset at zero and fetch however many rows are specified as that parameter. On the other hand, if we specify two arguments, then MySQL will use the first argument as the row offset, and the second as the number of rows to return.
So, if we wanted to start to retrieve rows 6 to 15 in our contacts table, we would use the following query:
SELECT * FROM contacts
WHERE 1
LIMIT 5,10
The SHOW TABLES keyword
MySQL gives us easy access to the complete list of tables in any database that we have access to. Using the show tables keyword, we can retrieve a list of table names in the current database.
To use the show tables command, just pass it as the query for the open method of a recordset object:
adoRS.Open "SHOW TABLES"
This would return a complete list of all tables names in the current database. Each table name would occupy a new row in the results set.
The DESCRIBE keyword
PhpMyAdmin is a PHP script that allows you to modify your MySQL databases over the web. If you’ve ever worked with phpMyAdmin, then you’ll be familiar with its interface: It displays the details of each table in a database including field names, lengths and extra options.
Using the describe table keyword, we can accomplish this for our contacts table with very little effort:
adoRS.Open "DESCRIBE contacts"
while not adoRS.EOF
for counter = 0 to adoRS.Fields.Count - 1
response.write adoRS.Fields(counter).value & " "
next
response.write "<br>"
adoRS.MoveNext
wend
The output of this ASP code would look like this:
contactId int(11) PRI auto_increment
firstName varchar(50) YES
lastName varchar(50) YES
address1 varchar(100) YES
address2 varchar(100) YES
phone varchar(20) YES
In the example above, I have only passed one argument with the describe keyword. If we wanted to, we could pass two. The definition of the describe command looks like this:
DESCRIBE | DESC} tbl_name {col_name | wild}
The second (and optional) argument can either be a specific column name (such as "firstName") or a string containing wild card characters such as "%" and "_". Using the second argument is handy if you need to retrieve the field details from a table based on a specific set of specific search criteria.
Conclusion
Talking to a MySQL database from within an ASP script is easy. MySQL is an extremely powerful, scalable, free and flexible database management system and it runs the entire devArticles.com site!
If you've had much experience with ADO, then you'll realise that they are just plain old COM objects and you can use these COM objects in other languages including Visual Basic and Delphi to talk to MySQL server.
The MySQL.com web site also has a tonne of API's available for free download from their site. These API's allow you to talk to MySQL database from a huge number of different programming languages including C, C++ , Java, Perl and TCL.
|
|
 |
| back to top |
| |
|
