CSCI 4900/6900 -- Web programming

Maymester 2001

Database access from ASP pages

Although we won't be programming ASP pages, we will be running database-access server scripts using PHP. The same general principles apply, only the syntax and object models differ somewhat.

Database components and languages

The databases with which we'll be working are called relational databases. This means that the data is kept in a collection of tables. Each row of the table is called a record and each column is called a field. A record describes a specific entity, and each field describes a particular attribute which belongs to each entity.. In this example each record describes a student.
A relational database with only one table is called a flat-file database.
On Windows platforms, database access is easy if the data source comes with drivers for the Open Database Connectivity (ODBC) interface. Most databases which run on Windows work with ODBC. You can even use Microsoft Excel if you only need a flat-file database!
We'll be using the open-source database MySQL. Here is a useful MySQL reference .
Structured Query Language (SQL) is the standard language for all database access. If you haven't used SQL before, you may want to look at this SQL tutorial . MySQL syntax for each of the SQL statements can be found in part 7 of the MySQL reference listed above.
The data access model allows you to make a query against a database and extract the results under program control. This model is called ActiveX Data Objects (ADO) in the Windows world.

The Microsoft Data Access Model

The MS data model involves these key technologies and standards:

The Universal Data Access model is a high-level generic spec
ODBC and OLEDB (Object Linking and Embedding Database) are actual database interfaces (i.e. there are drivers you can get) that provide a consistent interface across relational database products
To connect to a Windows database from a Microsoft product you must define a Data Source Name (DSN). This is done through the Windows ODBC control panel. Screen shots of this operation are provided on pp. 464-465 of Halata.
The ActiveX Data Objects (ADO) model allows you to access ODBC and OLEDB data sources from a server-side script.

There are also non-Microsoft products which allow you to access ODBC data sources by providing an ODBC driver. In general, the data source is assumed to be on the same computer as the Web server.

Using the ADO data model

The key objects in the ADOP data model are:

The connection object -- you create this and then work through it to access the data source
The recordset object -- this contains the records which are returned from a database query
The command object -- this allows you to set up an SQL command which you want to execute.

Establishing a connection

Here is the server-side code to establish a database connection:

dim oConn
set oConn = server.createObject("ADOBD.connection")
oConn.open "dsnStudentRecords"

This creates a connection object and binds it to the DSN "dsnStudentRecords". Halata's code doesn't check for success, which I recommend doing.

In Halata you will see that the connection.open() method can be called with any one of several different parameter sets. Here is the executive overview:

If you have a dataset name (DSN), that means the data set is registered with ODBC and you can open it by name.
You can also use a connection string of the form CS = DRV & DRQ, where DRV is the driver name and DRQ is the name of a local disk file.

Retrieving records

You can create a recordset which contains all of the records in a table:

dim oRS
set oRS = server.createObject("ADODB.recordset")
oRS.open("products")

This will retrieve all the records in the table "products. Alternatively you can use an SQL SELECT statement to retrieve only a selected subset of records:

dim oRS
set oRS = server.createObject("ADODB.recordset")
oRS.open("SELECT * FROM products WHERE productType='software'")

This will retrieve only the records whose type field has the value "software".

Once you have retrieved the records into a recordset you can then traverse the recordset to access the individual records:

response.write("<table><th>Product<th>Type<th>Price")
do while not orS.EOF
response.write("<tr>")
response.write("<td>" & oRS.productName)
response.write("<td>" & oRS.productType)
response.write("<td>" & CurrencyFormat(orS.price))
orS.movenext
loop
response.write("</table>")

This will create a table with one row for each product record in the recordset oRS.

Closing the recordset

It is recommended to close the recordset and recycle its memory after use:

orS.close
set orS = nothing

I suspect that recordset resources are automatically garbage-collected when the script ends. But it is always good housekeeping to close anything you open!

Learning PHP

We'll use the online references, headquartered at www.php.net:

Very short tutorial

PHP info script

PHP manual