Accessing the Database from ASP Pages

In general, the CML application does not access the FmLib database directly from ASP files. The following code shows this type of access, in an example from an early version of the file AddItems.asp:

<% Dim rs,cn

   '--- Open a connection to use for filling in author and subject lists
   Set cn = Server.CreateObject("ADODB.Connection")
   cn.ConnectionTimeout = Application("FmLib_ConnectionTimeout")
   cn.Open Application("FmLib_ConnectionString")

   '--- Populate author list from table
   Set rs = Server.CreateObject("ADODB.Recordset")
   rs.Open "SELECT item#,barcode FROM Item WHERE bib#=" & nBibNo, cn
   Do Until rs.EOF
      Response.Write "<OPTION VALUE=" & rs("item#") & ">" & rs("barcode") & "</OPTION>" & vbCrLf
      rs.MoveNext
   Loop
   rs.Close
%>

In this code, an ActiveX Data Objects (ADO) connection object is instantiated using the CreateObject method of the ASP Server object. Next, connection parameters (including the ODBC DSN) are retrieved from the Application object, and a recordset object is created.

This recordset object is now opened with a command that passes two parameters. The first is an SQL SELECT statement that returns an item# and a barcode from the item table, for a certain bib#. The second parameter is "cn", the connection to the database, which was established earlier in the file:

   Set cn = Server.CreateObject("ADODB.Connection")

The results of this query are written to the client, record-by-record in a loop, with a Response.Write statement.

Support of ODBC

ODBC is a common protocol for communicating with databases. The CML application's connection to the FmLib database uses ADO (usually through RDS ActiveX objects), which communicates through OLE DB. OLE DB in turn uses the ODBC layer to communicate with the SQL Server database.

ODBC support is a BackOffice logo requirement. The goal of this logo requirement is the use of a more standard and flexible means of database connectivity. Instead of using a database-specific language, applications should use an ODBC-compliant language, of which ADO is an example.