Home arrow ASP arrow Page 5 - 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 - Querying our MySQL database
(Page 5 of 6 )

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.
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