ASP Lecture Series (16) Accessing the Database
Author：Eve Cole
Update Time：2009-05-30 19:58:46
ActiveX Data Objects (ADO) is an easy-to-use and extensible technology for adding database access to Web pages. You can use ADO to write compact and concise scripts to connect to Open Database Connectivity (ODBC)-compliant databases and OLE DB-compliant data sources. If you are a scripter with some knowledge of database connections, you will find that ADO command statements are not complicated and easy to master. Likewise, if you are an experienced database programmer, you will appreciate ADO's advanced language-independence and query processing capabilities.
Creating the ODBC DSN File Before creating the database script, you must provide a way for ADO to locate, identify, and communicate with the database. The database driver uses a Data Source Name (DSN) to locate and identify a specific ODBC-compliant database to pass information from the Web application to the database. Typically, a DSN contains database configuration, user security, and location information, and can be obtained as a table in a Windows NT registry key or as a text file.
With ODBC, you can choose the type of DSN you want to create: user, system, or file. User and system DSNs are stored in the Windows NT registry. The system DSN allows all users logged on to a specific server to access the database, while the user DSN restricts database connections to specific users using appropriate security credentials. File DSN is used to obtain tables from text files, provides access to multiple users, and can be easily transferred from one server to another by copying the DSN file. For these reasons, the examples in this topic use file DSNs.
You can create DSN-based files by opening Control Panel from the Windows Start menu. Double-click the ODBC icon, select the File DSN property page, click Add, select the database driver, and click Next. Follow the instructions below to configure the DSN for your database software.
Configuring File DSN for Microsoft Access Database
In the Create New Data Source dialog box, select Microsoft Access Driver from the list box, and then click Next.
Type your DSN file name and click Next.
Click Finish to create the data source.
In the ODBC Microsoft Access 97 Setup dialog box, click Select. Select the Microsoft Access database file (*.mdb) and click OK.
NOTE For performance and reliability reasons, we strongly recommend that you use a Client-Server Database Engine to configure data driven by Web applications that must be accessible to more than 10 users simultaneously. Although ASP can use any ODBC-compliant database, it is designed and rigorously tested for use with client-server databases, including Microsoft ® SQL Server, Oracle, and others.
ASP supports shared file databases (such as Microsoft Access or FoxPro) as valid data sources. Although some examples in the ASP documentation use shared file databases, we recommend using such database engines only for development or limited deployment scenarios. Shared file databases may not be a good fit for client-server databases that cater to high-demand, high-quality Web applications.
Configure SQL Server database file DSN
Note If the database resides on a remote server, contact the server administrator for additional configuration information; the following procedure uses SQL Server's ODBC default settings, which may not apply to your hardware configuration.
In the Create New Data Source dialog box, select SQL Server from the list box, and then click Next.
Type a name for the DSN file and click Next.
Click Finish to create the data source.
Type the name, login ID, and password of the server running the SQL service program.
In the Create New Data Source for SQL Server dialog box, type the name of the server that contains the SQL Server database in the Server list box, and then click Next.
Select how to verify your login ID.
If you choose SQL server authentication, enter a login ID and password, and click Next.
In the Create New Data Source for SQL Server dialog box, set the default database, driver for stored procedure settings, and ANSI identification, and then click Next. (For more information, click Help.)
In the dialog box (also named Create a new data source for SQL Server), select a character conversion method, and then click Next. (For more information, click Help.)
In the next dialog box (also named "Create a new data source for SQL Server"), select login settings.
Note Typically, you can only use logs to debug database access problems.
In the ODBC Microsoft SQL Server Setup dialog box, click Test Data Source. If the DSN was created correctly, the Test Results dialog box will indicate that the test completed successfully.
SQL server connection and security information If you are developing an ASP database application that connects to a remote SQL Server database, you should consider the following issues:
Connection options - You can choose between TCP/IP sockets and named pipes to access the remote SQL Server database. When using named pipes, because the database user must be authenticated to Windows NT before a connection can be established, users with appropriate SQL Server access identities but without a Windows NT user account on the computer may be denied access to the named pipe. As an alternative, a connection using TCP/IP sockets can be connected directly to the database server without going through an intermediary computer using named pipes. Because you can connect directly to the database server using a TCP/IP socket connection, users can gain access through SQL Server authentication without having to authenticate through Windows NT.
Note Using TCP/IP sockets can improve performance when connecting to remote databases.
Security - If you are using SQL Server's integrated or mixed security features, and the SQL Server database is on a remote server, you cannot use Windows NT request/response acknowledgment. That is, the Windows NT request/response ID cannot be forwarded to the remote computer, but only Basic Authentication can be used, which is based on the user's username and password information.
For more information on this topic, see http://www.microsoft.com/sqlsupport/
Microsoft SQL Server Technical Support home page.
Configure Oracle database file DSN
First make sure that the Oracle user software is properly installed on the computer on which the DSN is to be created. For more information, contact your server administrator or consult your database software documentation.
In the Create New Data Source dialog box, select Microsoft ODBC for Oracle from the list box, and then click Next.
Type a name for the DSN file and click Next.
Click Finish to create the data source.
Enter your username, password, and server name, and click OK.
Note DSN files have a .dsn extension and are located in the ProgramsCommon FilesODBCData Sources directory.
For more information about creating DSN files, visit the Microsoft ODBC Web site: http://microsoft.com/odbc/.
Connecting to the Database The first step in accessing database information is to establish a connection to the database source. ADO provides a Connection object that can be used to establish and manage connections between applications and ODBC databases. The Connection object has various properties and methods that you can use to open and close database connections and make query requests to update information.
To establish a database connection, you first create an instance of the Connection object. For example, the following script creates a Connection object and then opens a database connection:
<%
'Create a connection object
Set cn = Server.CreateObject("ADODB.Connection")
'Open a connection; the string refers to the DSN
cn.Open "FILEDSN=MyDatabase.dsn"
%>
Note The DSN string cannot contain spaces, either before or after the equal sign (=).
In this case, the Connection object's Open method references a DSN-based file that contains location and configuration information about the database. You can also explicitly reference the provider, data source, user ID, and password without referencing the DSN.
Executing a Query with a Connection Object Using the Connection object's Execute method, you can issue a Structured Query Language (SQL) query to a database source and retrieve the results. SQL is the industry standard language for communicating with databases and has many commands for retrieving and updating information.
The following script uses the Connection object's Execute method to issue a query in a table using a SQL INSERT command, which inserts data into a specific database table. In the following example, the script inserts the name Jose Lugo into a database table named Customers.
<%
'Define file based DSN
strDSN = "FILEDSN=MyDatabase.dsn"
'Instantiate the Connection object and open a database connection
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open strDSN
'Define SQL SELECT statement
strSQL = "INSERT INTO Customers (FirstName, LastName) VALUES ('Jose','Lugo')"
'Use the Execute method to issue a SQL query to database
cn.Execute(strSQL)
%>
Note that files based on DSN path strings should not contain spaces before and after the equal sign (=).
In addition to the SQL INSERT command, you can also use the SQL UPDATE and DELETE commands to change and delete database information.
Using the SQL UPDATE command, you can change the value of each item in the database table. The following script uses the UPDATE command to change the FirstName field of each record in the Customers table whose LastName field contains the last name Smith to Jeff.
<%
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "FILEDSN=MyDatabase.dsn"
cn.Execute "UPDATE Customers SET FirstName = 'Jeff' WHERE LastName = 'Smith' "
%>
To delete specific records from a database table, use the SQL DELETE command. The following script deletes all rows with the last name Smith from the Customers table:
<%
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "FILEDSN=MyDatabase.dsn"
cn.Execute "DELETE FROM Customers WHERE LastName = 'Smith'"
%>
Note You must exercise caution when using the SQL DELETE command. When you use the DELETE command without a WHERE clause, it deletes all rows in the table. Be sure to include the SQL WHERE clause to specify the exact rows to delete.
Using Recordset Objects to Process Results Although the Connection object simplifies the task of connecting to a database and querying, the Connection object still has many shortcomings. Specifically, the Connection object that retrieves and displays database information cannot be used to create scripts; you must know exactly what changes you want to make to the database before you can use queries to implement the changes.
For retrieving data, checking results, and changing the database, ADO provides Recordset objects. As its name implies, the Recordset object has a number of features that you can use to retrieve and display a set of database rows, or "records," based on the constraints of your query. A Recordset object holds the location of records returned by a query, allowing you to step through the results one at a time.
Depending on the PointerType property setting of the Recordset object, you can scroll and update records. Database pointers allow you to locate a specific item within a set of records. Pointers are also used to retrieve and examine records and then perform operations based on those records. Recordset objects have properties that allow you to precisely control the behavior of pointers, improving your ability to inspect and update results. For example, you can use the CursorType and CursorLocation properties to set the type of a pointer, return the results to the client application (the results are typically persisted on the database server), and display the last changes made to the database by other users.
Retrieving Records A successful database application uses the Connection object to establish the link and the Recordset object to process the returned data. By "coordinating" the specific functionality of two objects, you can develop a database application that can perform almost any data processing task. For example, the following server-side script uses a Recordset object to execute a SQL SELECT command. The SELECT command retrieves a set of information based on query restrictions. The query also contains a SQL WHERE clause, which is used to narrow the scope of the query. In this example, the WHERE clause limits the query to all records containing the last name Smith in the Customers database table.
<%
'Establish a connection with data source
strDSN = "FILEDSN=MyDatabase.dsn"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open strDSN
'Instantiate a Recordset object
Set rsCustomers = Server.CreateObject("ADODB.Recordset")
'Open a recordset using the Open method
' and use the connection established by the Connection object
strSQL = "SELECT FirstName, LastName FROM Customers WHERE LastName = 'Smith' "
rsCustomers.Open strSQL, cn
'Cycle through record set and display the results
' and increment record position with MoveNext method
Set objFirstName = rsCustomers("FirstName")
Set objLastName = rsCustomers("LastName")
Do Until rsCustomers.EOF
Response.Write objFirstName & " " & objLastName & "<BR>"
rsCustomers.MoveNext
Loop
%>
Note that in the previous example, the Connection object was used to establish the database connection and the Recordset object used the connection to retrieve results from the database. This method is useful when you need to set up exactly how to establish a link to the database. For example, if you need to specify how long to wait before a connection attempt fails, you need to use the Connection object to set properties. However, if you only want to establish a connection using ADO's default connection properties, you should use the Open method of the Recordset object to establish the link:
<%
strDSN = "FILEDSN=MyDatabase.dsn"
strSQL = "SELECT FirstName, LastName FROM Customers WHERE LastName = 'Smith' "
Set rsCustomers = Server.CreateObject("ADODB.Recordset")
'Open a connection using the Open method
'and use the connection established by the Connection object
rsCustomers.Open strSQL, strDSN
'Cycle through the record set, display the results,
' and increment record position with MoveNext method
Set objFirstName = rsCustomers("FirstName")
Set objLastName = rsCustomers("LastName")
Do Until rsCustomers.EOF
Response.Write objFirstName & " " & objLastName & "<BR>"
rsCustomers.MoveNext
Loop
%>
When using the Open method of the Recordset object to establish a connection, the Connection object must be used to ensure the security of the connection.
Using the Command Object to Improve Queries With the ADO Command object, you can execute queries just like you would with the Connection and Recordset objects. The only difference is that with the Command object you can prepare, compile, and iterate your query on the database source using a different set of values. Issue an inquiry. The advantage of compiling queries this way is that you can minimize the time required to repeatedly issue modified requests to existing queries. Alternatively, you can leave a SQL query partially undefined before execution through options on the variable portion of your query.
The Command object's parameter collection saves you the trouble of re-building the query each time it is reissued. For example, if you need to regularly update supply and price information in an inventory-based Web system, you can predefine the query as follows:
<%
'Open a connection using Connection object Command object
'does not have an Open method for establishing a connection
strDSN = "FILEDSN=MyDatabase.dsn"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open strDSN
'Instantiate Command object; use ActiveConnection property to attach
'connection to Command object
Set cm= Server.CreateObject("ADODB.Command")
Set cm.ActiveConnection = cn
'Define SQL query
cm.CommandText = "INSERT INTO Inventory (Material, Quantity) VALUES (?, ?)"
'Save a prepared (or pre-compiled) version of the query specified in CommandText
'property before a Command object's first execution.
cm.Prepared = True
'Define query parameter configuration information
cm.Parameters.Append cm.CreateParameter("material_type",200, ,255 )
cm.Parameters.Append cm.CreateParameter("quantity",200, ,255 )
'Define and execute first insert
cm("material_type") = "light bulbs"
cm("quantity") = "40"
cm.Execute
'Define and execute second insert
cm("material_type") = "fuses"
cm("quantity") = "600"
cm.Execute
%>
Check the example above and you will notice that the script repeatedly builds and issues an SQL query with different values without redefining and resending the query to the database source. Compiling queries with Command objects also avoids problems with merging string and table variables caused by SQL queries. In particular, problems associated with defining the types of string, date, and time variables can be avoided by using the Parameter collection of the Command object. For example, an SQL query value containing "'" may cause the query to fail:
strSQL = "INSERT INTO Customers (FirstName, LastName) VALUES ('Robert','O'Hara')"
Note that the last name O'Hara contains a "'", which conflicts with the "'" used to represent data in the SQL VALUES keyword. This type of problem can be avoided by binding the query value as a Command object parameter.
Combining HTML tables and databases Accessing a Web page containing an HTML table enables users to remotely query a database and retrieve specific information. Using ADO you can create very simple scripts to collect user table information, create custom database queries, and return information to the user. Using the ASP Request object, you can retrieve information entered into an HTML table and incorporate this information into a SQL statement. For example, the following script module inserts information provided by an HTML table into a table. This script uses the Form collection of Request objects to collect user information.
<%
'Open a connection using Connection object. The Command object
'does not have an Open method for establishing a connection
strDSN = "FILEDSN=MyDatabase.dsn"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open strDSN
'Instantiate Command object
'and use ActiveConnection property to attach
'connection to Command object
Set cm= Server.CreateObject("ADODB.Command")
Set cm.ActiveConnection = cn
'Define SQL query
cm.CommandText = "INSERT INTO MySeedsTable (Type) VALUES (?)"
'Define query parameter configuration information
cm.Parameters.Append cm.CreateParameter("type",200, ,255 )
'Define and execute insert
cm("type") = Request("SeedType")
cm.Execute
%>
Managing Database Connections The biggest challenge in designing a robust Web database application, such as an online shopping application that serves thousands of customers, is managing database connections appropriately. Opening and maintaining a database connection, even when no information is being transferred, can severely consume database server resources and may cause connectivity issues. A well-designed Web database application will recycle database connections and be able to compensate for delays caused by network congestion.
Causing Connection Timeouts A sudden increase in activity can make the database server very unwieldy, significantly increasing the time it takes to establish a database connection. As a result, excessive connection latency will degrade database performance.
Using the Connection object's ConnectionTimeout, you can limit the time an application waits before giving up the connection attempt and issuing an error message. For example, the following script sets the ConnectionTimeout property to wait 20 seconds before canceling a connection attempt:
Set cn = Server.CreateObject("ADODB.Connection")
cn.ConnectionTimeout = 20
cn.Open "FILEDSN=MyDatabase.dsn"
The default ConnectionTimeout property is 30 seconds.
Note Before incorporating the ConnectionTimeout property into a database application, be sure that the connection provider and data source support the property.
Shared Connections Web database applications that frequently make and break database connections may degrade database server performance. ASP supports efficient management of connections using the sharing features of ODBC 3.5. Connection sharing maintains open database connections and manages the sharing of that connection among different users to maintain its performance and reduce the number of idle connections. For each connection request, the connection pool first determines whether there is an idle connection in the pool. If present, the connection pool returns the connection rather than establishing a new connection to the database.
If you want your ODBC driver to participate in connection sharing, you must configure the database driver and set the driver's CPTimeout property in the Windows NT registry. When ODBC disconnects, the connection is pooled rather than disconnected. The CPTimeout property determines how long a connection is retained in the connection pool. If a connection remains in the pool for longer than the CPTimeout setting, the connection will be closed and removed from the pool. The default value for CPTimeout is 60 seconds.
You can enable connection pooling for a specific ODBC database driver by selectively setting the CPTimeout property by creating a registry key set as follows:
HKEY_LOCAL_MACHINESOFTWAREODBCODBCINST.INIdriver-nameCPTimeout = timeout
(REG_SZ, units are in seconds)
For example, the following key sets the SQL Server driver's connection pool timeout setting to 180 seconds (3 minutes).
HKEY_LOCAL_MACHINESOFTWAREODBCODBCINST.INISQL ServerCPTimeout = 180
Note By default, the Web server activates SQL Server's connection pool by setting the CPTimeout to 60 seconds.
Using cross-page connections Although you can reuse cross-page connections by storing connections in ASP's Application object, keeping the connection open at all times is unnecessary and does not take full advantage of the advantages of connection pooling. If many users need to connect to the same ASP database application, a good approach is to reuse the database connection by placing the cross-page connection string in the ASP Application object. For example, you can specify the connection string in the Application_OnStart event procedure of the Global.asa file, as shown in the following script:
Application.lock
Application("ConnectionString") = "FILEDSN=MyDatabase.dsn"
Application.unlock
Then, in each ASP file that accesses the database, write:
<OBJECT RUNAT=Server ID=cn PROGID="ADODB.Connection"> </OBJECT>
To create an instance of the connection object, use the following script:
cn.Open Application("ConnectionString")
For open connections, you can write the following script at the end of the page to close the connection:
cn.Close
In situations where a single user needs to reuse a cross-page connection, it is better to use the Session object connection rather than the Application object.
Close the connection If you want to make better use of the connection pool, you should close the database connection as soon as possible. By default, the connection will be terminated after the script has finished executing. Closing a connection when it is no longer needed reduces the demands on the database server and makes the connection available to other users.
You can use the Connection object's Close method to terminate the connection between the Connection object and the database. The following script opens the connection and then closes it:
<% strDSN = "FILEDSN=MyDatabase.dsn"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open
cn.Close
%>
