Приглашаем посетить
Хлебников (hlebnikov.lit-info.ru)

17.5 Accessing databases with ADO and ASP

Table of Contents

Previous Next

17.5 Accessing databases with ADO and ASP

17.5.1 What is ADO?

ActiveX Data Objects, or ADO for short, is a Microsoft product providing database access at the programming level. It is a series of objects closely related to ASP discussed in the last chapter. ADO can be used to simplify the tasks for adding database functionalities to your Web pages. Any scripting language supported by ASP can be used to call ADO functions. In this section, we will show you how to use JScript (a version of ECMAScript) to program ADO databases. Then the same program will be translated into PerlScript in ASP format. Note that PerlScript is a scripting language that can be used in ASP and is different from the general programming language Perl.

ADO is part of a larger structure called Universal Data Access (UDA) constructed by Microsoft. A structural diagram of UDA is shown in Fig. 17.21.

Figure 17.21. The structure of Universal Data Access (UDA)

graphics/17fig21.gif


Basically, you don't need to install ADO explicitly. As part of the Microsoft Data Access Component, ADO will be installed automatically with Microsoft server products. This means that if you have installed the IIS or PWS, you already have ADO ready for action.

In fact, ADO is a DLL and the current version is msado15.dll. It is usually located inside the "Program Files" directory. You can use the Find feature of your Windows system or issue the command dir msado*.dll /s in a DOS window to locate the library.

ADO works by connecting to any relational databases through Open Database Connectivity (ODBC) or the Object Linking and Embedding Database (OLE DB).

Once you have a database on ODBC, you can concentrate on ADO programming. ADO provides seven objects for us to access and manipulate databases at the programming level. We will introduce two of them, namely, the "Connection Object" and the "Recordset Object," in this section.

17.5.2 Accessing databases with Connection and Recordset objects

The Connection object of ADO represents a unique session with a data source. You can use it to establish and manage connections between your applications and ODBC databases. The Connection object contains a number of methods and properties. We are particularly interested in the following methods:

Open()

Opens a connection to a data source.

Close()

Closes a connection to a data source.

Execute()

Executes the specified database query and SQL statement.


For example, the following ADO statements open the ODBC database personnel:



Conn = new ActiveXObject("ADODB.Connection")
Conn.Open("personnel")

To establish a database connection with ADO, you first create an instance of the Connection object. In this case, the instance is called Conn. With this Conn, you can activate the Open() method to open the ODBC database personnel.

This database contains one table called people and the SQL statement below is often used to extract all the data inside the table people:



SELECT * FROM people

To execute this SQL statement, you can use



RS = Conn.Execute("SELECT * FROM people")

Although the Connection object simplifies the task of connecting to an ODBC database and execution of a SQL query, it has limitations. In particular, you cannot (or at least it is not convenient to) create scripts with the Connection object to retrieve, display, and manipulate data and related information. This is the job of another ADO object called Recordset. In fact, the execute command above from the Connection object returns a Recordset object (RS) about all data inside the table people.

The Recordset object, as its name implies, contains properties and features that you can use to display all database fields and a set of database rows, or records. For example, you can use the count property of the Recordset below to count and display all fields about the table people:



count = RS.Fields.Count;
for ( ii = 0; ii < count; ii++ )
{
 Response.Write( RS.Fields(ii).Name )
 Response.Write("<br />");
}

The name property associated with RS.Fields(ii).Name returns the name of the field of the table. The value property of the fields will return the data value associated with the field.

For a real example, let's consider the database example ex15-15.htm and convert it to ASP. First, we use the same XHTML interface by copying ex15-15.htm to ex17-13.htm and changing the following two lines:



12: Access An ODBC DataBase <br />with ASP<br /><br />
28: <form action = "ex1713.asp" method="post">

Line 28 simply changes the form action to call the ASP page ex17-13.asp. The listing of this ASP page is follows:



Example: ex17-13.asp - Using ADO

 1: <%@Language=JScript%>
 2: <?xml version="1.0" encoding="iso-88591"?>
 3: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
 4:     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 5: <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
 6: <head><title>Example ex1713.asp</title></head>
 7: <style>
 8:  .txtSt{font-family:arial;font-weight:bold;font-size:13pt;color:#ffff00}
 9: </style>
10: <% querySt = Request.Form("querySt") %>
11: <body class="txtSt" style="background:#000088;font-size:18pt;
12:       text-align:center" >
13:   Based On Your SQL Query String:<br />
14:   <div style="color:#ffffff"><%= querySt %></div><br />
15:   The Search Results Are As Follows:<br /><br />
16:  <img src="line1.gif" width="600" height="6" alt="pic" /><br />
17:  <table cellspacing="5" class="txtSt" width="570">
18:
19: <%
20:  Conn = new ActiveXObject("ADODB.Connection")
21:  Conn.Open("personnel" )
22:  RS = Conn.Execute(querySt)
23:
24:  count = RS.Fields.Count
25:  Response.Write("<tr>")
26:  for ( ii = 0; ii < count; ii++ )
27:  {
28:  Response.Write("<td style='color:#00ff00'>"+RS.Fields(ii).Name+"</td>" )
29:  }
30:  Response.Write("</tr>")
31:
32:  rowCount = 0
33:  while ( ! RS.EOF )
34:  {
35:   Response.Write("<tr>")
36:   for ( ii = 0; ii < count; ii++ )
37:   {
38:   Response.Write("<td style='color:#ffff00'>"+RS.Fields(ii).Value+"</td>")
39:   }
40:   Response.Write("</tr>")
41:   RS.MoveNext()
42:   rowCount++
43:  }
44:
45:  RS.Close()
46:  Conn.Close()
47: %>
48:
49: </table>
50: <img src="line1.gif" width="600" height="6" alt="pic" /><br />
51: Total Entries = <%= rowCount%> <br /><br />
52: </body>
53: </html>

First, the query string submitted from the XHTML page is captured by the Request.Form object (line 10). The ADO statements in lines 2022 open the database personnel and execute the SQL statement querySt. The for-loop in lines 2629 uses the count property of the Recordset object to count and display all field names. All the coding here is in line with the discussion above.

In order to display all the rows (or database data), you can use the following while-loop against the end of file of the Recordset (lines 3343):



while ( ! RS.EOF )
{
 Response.Write("<tr>")
 for ( ii = 0; ii < count; ii++ )
 {
   Response.Write( "<td>"+RS.Fields(ii).Value+"</td>" )
 }
 Response.Write("</tr>")
 RS.MoveNext()
 rowCount++
}

RS.EOF is a property of the Recordset object to represent the end of the record. This while-loop will loop through all rows of the table. Inside the while-loop, there is a simple for-loop to construct and display all field data as a single table row in XHTML format. The statement



RS.MoveNext()

will move to the next row of data so that the while-loop can continue the row iteration. Since the Recordset object is considered as a file, you need to close it in line 45 along with closure of the Connection object. Some screen shots of this example are shown in Figs 17.22 and 17.23.

Figure 17.22. ex17-13.htm

graphics/17fig22.jpg


Figure 17.23. ODBC with ASP

graphics/17fig23.jpg


17.5.3 Using ASP PerlScript to call ADO functions

ASP is a language-independent package where multiple scripting languages are accepted. We have shown in Chapter 16 that VBScript, JScript, and PerlScript can co-exist on the same ASP page. In this section, we will show you how to use PerlScript to call ADO functions to perform database functionalities. PerlScript is an ASP scripting language engine running on a PC with Microsoft windows. PerlScript is free and available from Active Perl (www.active.com). When you install the Active Perl language for a Microsoft system, you will have already installed the PerlScript engine for ASP.

Again, only the Connection and Recordset objects associated with ADO are considered. To open the database and execute an SQL query statement, you can use the following PerlScript:



<%
$Conn = $Server->CreateObject("ADODB.Connection");
$Conn->Open( "personnel" );
$RS = $Conn->Execute("SELECT * FROM people");
%>

You may already have noticed that these statements are very similar to the JScript version in previous example ex17-13.asp. PerlScript is designed to work with ASP and contains all the advantage of being a Perl-compliant language. That's why all the variables above have a dollar sign in front of them. Unlike JScript, PerlScript uses reference to call ADO functions. For example, the arrow symbol



$Conn->Open( "personnel" );

indicates that the object variable $Conn calls the Open() function using a reference method. When the SQL query string SELECT * FROM people is executed, the query results are returned to the variable $RS. This variable is actually an object of Recordset type. All the properties of the Recordset object can be applied to format the result. For example, the following code can be used to display all the names of the fields as an XHTML table row:



$count = $RS->Fields->{Count};
$Response->Write("<tr>");
for ( $i = 0; $i < $count; $i++ )
{
  $Response->Write("<td>");
  $Response->Write($RS->Fields($i)->Name);
  $Response->Write("</td>");
};
$Response->Write("</tr>");

Now you can use ASP and PerlScript to program databases. As a quick example, you can use the same XHTML interface by copying ex17-13.htm to ex17-14.htm and changing the following two lines:



12: Access An ODBC DataBase <br />Using PerlScript with ADO<br /><br />
28: <form action ="http://www.pwt-ex.com/chap17a/ex1714.asp" method="post">

The statement in line 28 changes the form action to call the ASP page ex17-14.asp. The listing of this ASP page is as follows:



Example: ex17-14.asp - Using ASP PerlScript To Call ADO Functions

 1: <%@Language=PerlScript%>
 2: <?xml version="1.0" encoding="iso-88591"?>
 3: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
 4:     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 5: <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
 6: <head><title>Example ex1714.asp</title></head>
 7: <style>
 8:   .txtSt{font-family:arial;font-weight:bold;font-size:13pt;color:#ffff00}
 9: </style>
10: <% $querySt = $Request->Form("querySt"); %>
11: <body class="txtSt" style="background:#000088;font-size:18pt;
12:       text-align:center" >
13:   Based On Your SQL Query String:<br />
14:   <div style="color:#ffffff"><%= $querySt%></div><br />
15:   The Search Results Are As Follows:<br /><br />
16:  <img src="line1.gif" width="600" height="6" alt="pic" /><br />
17:  <table cellspacing="5" class="txtSt" width="570">
18:
19: <%
20: $Conn = $Server->CreateObject("ADODB.Connection");
21: $Conn->Open( "personnel" );
22: $RS = $Conn->Execute( $querySt);
23:
24: $count = $RS->Fields->{Count};
25:
26: $Response->Write("<tr>");
27: for ( $i = 0; $i < $count; $i++ )
28: {
29:   $Response->Write("<td style='color:#00ff00'>");
30:   $Response->Write($RS->Fields($i)->Name);
31:   $Response->Write("</td>");
32: };
33: $Response->Write("</tr>");
34:
35: $rowCount =0;
36: while ( ! $RS->{EOF} )
37: {
38:   $Response->Write("<tr>");
39:   for ( $i = 0; $i < $count; $i++ )
40:   {
41:     $Response->Write("<td style='color:#ffff00'>");
42:     $Response->Write($RS->Fields($i)->{Value});
43:     $Response->Write("</td>");
44:   };
45:  $Response->Write("</tr>");
46:  $RS->MoveNext();
47:  $rowCount++;
48: };
49:
50: $RS->Close();
51: $Conn->Close();
52: %>
53:
54: </table><br />
55: <img src="line1.gif" width="600" height="6" alt="pic" /><br />
56: Total Entries = <%= $rowCount %> <br /><br />
57: </body>
58: </html>

Basically, this ASP page is a direct translation of example ex17-13.asp into the PerlScript language. All structure and function names are the same. Lines 2022 open the ODBC database called personnel and execute the query statement SELECT * FROM people. The for-loop in lines 2732 uses the count property of the Recordset object to display all the names of the database fields. Finally, the while-loop in lines 3648 iterates over all the rows returned by the query. For each row, a for-loop is used to format the database data



$RS->Fields($i)->{Value}

as a table row in XHTML format.

    Table of Contents

    Previous Next