N342 Database/ADO

• Basics
• Exercise 1 - Access Database
• ASP Database Use
  • Exercise 2/3 - ADO/SQL
• User Data
• Exercise 4 - ADO/User Data
• JavaScript Exceptions
• SQL Update, Insert, and Delete

Basics

Databases are one key component of most Web systems and can easy be generically accessed from ASP using the ADO (ActiveX Data Objects) technology. We will examine the combination of ASP, Access databases, ADO, and SQL to produce database driven pages.

Databases

Relational databases from nearly any vendor can be manipulated using the SQL language, that includes Access and Microsoft SQL Server. On an IIS Web server (and many others) the ADO (ActiveX Data Objects) provide a simple means of connecting to an SQL compliant database, issuing SQL commands to create, delete, retrieve or modify parts of the database. Essentially anything that can be expressed in SQL can by performed by a server-side application (e.g. ASP).

ADO can provide a simple, generic means of database use but the nuances are very complex. The examples given here will generally work for our purposes but are not intended for every application.

Microsoft Access

Relational databases such as Access, Oracle, SQL Server are the primary means used of storing, maintaining, and retrieving data. Microsoft developed the technology called ADO (ActiveX Data Objects) that provides generic access to data, including Access, Lotus Notes, Excel, Word, etc. A sample Access database will be downloaded for use with the following examples.

Web Servers/Databases/ADO

Web applications that store persistent data implement a three-tier model consisting of:

1. User interface on the browser.
2. Logic on the Web server.
3. Data storage on data server.

Tier 2 relates to ASP and server scripts, gathering and presenting data to the user, and storing or retrieving the data. To simplify database use, Microsoft has developed ActiveX Data Objects (ADO) for any application that supports ActiveX. 

The following JScript ASP program uses ADO to open the Comp-2000.mdb database, send the SQL statement to the database engine to return all records from the Employee table, and print the LNAME field of the first record, which happens to be Smith.

<%@ Language=JScript %> <% conn = Server.CreateObject("ADODB.Connection"); conn.Open ("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" + Server.MapPath("Comp-2000.mdb")); rs = conn.Execute("SELECT Fname, Lname FROM Employee"); Response.Write( "<TABLE BORDER=1>"+ " <TR>"+ " <TD>First</TD>"+ " <TD>Last</TD>"+ " </TR>"+ " <TR>"+ " <TD>" + rs("Fname") + "</TD>"+ " <TD>" + rs("Lname") + "</TD>"+ " </TR>"+ "</TABLE>" ); %>

Browser Output First Last John Smith

The key points of ADO illustrated are:

• conn = Server.CreateObject("ADODB.Connection") - Create a connection object.
• conn.Open ("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" + Server.MapPath("Comp-2000.mdb")) - Open the database using the connection object.
• rs = conn.Execute("SELECT Fname, Lname FROM Employee") - Execute the SQL. The recordset object, rs, is populated with the records resulting from the Select. In this example, the recordset contains only the Fname and Lname fields for each record in the table.
• rs("Fname")  - Returns the value of the FNAME field.

Iteration

Database tables generally consist of multiple records and iteration over the records allows each record to be accessed. The following example iterates over all employee records, generating a table of first and last names.
 

<%@ Language=JScript%> <% conn = Server.CreateObject("ADODB.Connection"); conn.Open ("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" + Server.MapPath("Comp-2000.mdb")); rs = conn.Execute("SELECT Fname, Lname FROM Employee"); Response.Write( "<TABLE BORDER=1>"+ " <TR>"+ " <TD>First</TD>"+ " <TD>Last</TD>"+ " </TR>"); while (!rs.EOF ) { Response.Write( " <TR>"+ " <TD>" + rs("Fname") + "</TD>"+ " <TD>" + rs("Lname") + "</TD>"+ " </TR>"); rs.MoveNext(); } rs.Close(); Response.Write( "</TABLE>" ); %>

Partial HTML listing <TABLE BORDER=1>   <TR>      <TD>First</TD>      <TD>Last</TD>   </TR>       <TR>          <TD>John</TD>          <TD>Smith</TD>       </TR>       <TR>           <TD>Franklin</TD>           <TD>Wong</TD>       :       :

SQL Select

SELECT Fname, Lname FROM Employee

selects all Employee records but only the Fname and Lname fields.

SELECT Fname, Lname FROM Employee WHERE Lname > 'English'

selects only Employee records where the Lname field is greater than 'English'.

'English' is alphabetic so must be enclosed in quotes.

 

User Form Data

ASP scripts can interact with users using the same GET and POST methods previously examined. The following uses a form to collect user input, the last name of an employee, that is retrieved from the Employee table of our database source.

GET - With use of parameters to Request, the Web server parses the HTTP GET data and returns the values of the specified parameters. If your application requires unparsed data from the form, you can access it by calling Request without any parameters.

The GET form with Last data is returned to the script and its value can be accessed by: Request("Last") which has the value Smith given the input to the form in the example below. Note in the ASP that when Last is undefined, all records of the table are retrieved and displayed. For Last=Smith the SQL statement generated will be:

SELECT * FROM Employee WHERE LNAME = 'Smith'

<H1>Employee Search</H1>
<form method = "GET"
      action="Search.Asp">
   Employee Last Name 
  <INPUT TYPE = "text" 
              name="Last" size="20" />
</form>

<%@ Language=JavaScript%> 
<% conn = Server.CreateObject("ADODB.Connection"); 
      conn.Open ("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" + 
                        Server.MapPath("Comp-2000.mdb"));

      if (Request("Last") == "") 
          rs = conn.Execute("SELECT * FROM Employee");
      else
          rs = conn.Execute("SELECT * FROM Employee WHERE Lname = '" + 
                                     Request("Last") + "'"); 

  Response.Write(
	"<TABLE BORDER=1>"+
	"   <TR>"+
	"      <TD BGCOLOR='silver'>First</TD>"+
	"      <TD BGCOLOR='silver'>Last</TD>"+
	"   </TR>");
  
   while (!rs.EOF ) {  
	Response.Write(
	"   <TR>"+
	"            <TD>" + rs("Fname") + "</TD>"+
	"            <TD>" + rs("Lname") + "</TD>"+
	"   </TR>");  
         rs.MoveNext();
   }

   rs.Close();
   Response.Write( "</TABLE>" );
%>

JavaScript and Exceptions

In the following note the use of try and catch which perform exception handling in Java and JavaScript. When ADO methods encounter an error, such as attempting to read more records than there are in the record set, the method throws an exception. When the method call is enclosed by the try operation, the exception is caught by the nearest catch. 
 

SQL Update, Insert and Delete

SQL Select reads a database table, producing a set of records matching the selection criteria; Update, Insert, Delete and some other operations write or otherwise modify the table.

• In ASP, set access mode = 3 for write access to database. See examples below.

To modify databases or other files through ASP scripts, IIS must have write access to the files.

IIS - If you are administering a Web server

A common error message indicating security problems when using IIS on XP is: 

Operation must use an updateable query

The correction is to give the Internet Guest account IUSR_MACHINE Write permissions on the directory containing the .mdb file.

Do the following to correct while logged in as an administrator:

1. Start | Settings | Control Panel | Administrative Tools | Computer Management | Local Users and Groups |
   Users | IUSR_MACHINE (i.e. the Internet Guest Account) | Right Click | Properties | Member Of | Administrators
2. Sometimes you may have to delete the .mdb database file from the directory and copy back to the directory.
3. Reboot machine.

Delete

The following example deletes an existing record from the Employee table only if a record with the SSN='666666666' is already in the table. 

<%@ Language=JScript%>
<%	
  conn = Server.CreateObject("ADODB.Connection");
  conn.Mode = 3;
  conn.Open ("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" + 
                     Server.MapPath("Comp-2000.mdb"));

  conn.Execute("DELETE from Employee where SSN='666666666'");

  conn.Close();
%>

Note the ADO is slightly more complicated, requiring to set the connection mode = 3 for write access.  The following example on insert provides a more complete perspective.

Update

The following example updates the salary of a record from the Employee table only if a record with the SSN='123456789' is already in the table. The lucky employee is given a 10% raise each time the script executes.

<%@ Language=JScript%>
<%	
  conn = Server.CreateObject("ADODB.Connection");
  conn.Mode = 3;
  conn.Open ("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" + 
                     Server.MapPath("Comp-2000.mdb"));

  conn.Execute("UPDATE Employee SET Salary = Salary * 1.10 where SSN= '123456789'  ");

  conn.Close();
%>

Insert

The following example inserts a new record into the Employee table only if a record with the same SSN is not already in the table. Only four of the record fields are inserted: SSN, FNAME, LNAME, DNO.

Note that running the script once inserts the record and displays "Record Inserted", running a second time displays "Name already in table". The SSN is the primary table key, attempting to insert a record with the same primary key as one already in the table violates the integrity of the database and is an error. Testing if(rs.EOF) ensures that when true the record can be safely inserted.

Note that

• rs = conn.Execute("SELECT * FROM Employee where SSN='666666666'");

returns the record set resulting from the SELECT.

<%@ Language=JScript%>
<%	
  conn = Server.CreateObject("ADODB.Connection");
  conn.Mode = 3;
  conn.Open ("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" + 
                     Server.MapPath("Comp-2000.mdb"));
  rs = conn.Execute("SELECT * FROM Employee where SSN='666666666'");

  if (rs.EOF) {

    conn.Execute("Insert into Employee (SSN, FNAME, LNAME, DNO) values ('666666666', 'Frank', 'Smith', '5')");

    Response.Write("Record Inserted");
  }
  else {
    Response.Write("Name already in table");
  }
  conn.Close();
%>

Common ADO methods

Each ASP language (JavaScript, VBScript, PerlScript) varies somewhat on the syntax but each implements the same ADO methods.

ADO object creation. JavaScript - rs = Server.CreateObject("ADODB.Recordset"); Perl - $rs = Win32::OLE->new('ADODB.Recordset'); VB - rs = Server.CreateObject("ADODB.Recordset") Open database and execute SQL. JavaScript - rs.Open ("SELECT * FROM Employee;", "Data Source=N342db;"); Perl -  rs->Open("SELECT * from Employee;","Data Source=N342db;") VB - rs.Open "SELECT * FROM Employee" , "Data Source=N342db;"  Move to the first record. JavaScript - rs.MoveFirst(); Perl - rs->MoveFirst() VB - rs.MoveFirst() Move to next record. JavaScript - rs.MoveNext(); Perl - rs->MoveNext() VB - rs.MoveNext() Test for end of file. JavaScript - rs.EOF Perl - rs->EOF VB - rs.EOF Test for beginning of file JavaScript - rs.BOF Perl - rs->BOF VB - rs.BOF Access current record fields JavaScript - rs("Lname") Perl - rs->Fields('LNAME')->value VB - rs("Lname") Close the recordset. JavaScript - rs.Close(); Perl - rs->Close() VB - rs.Close