ADO Overview

The implementation of ADO used with Sun Chili!Soft ASP is called ADODB. ADO enables client applications to access and manipulate data in a database server from a variety of different vendors in the same manner. With ADO, data is updated and retrieved using a variety of existing methods (including SQL). In the context of ASP, using ADO typically involves writing script procedures that interact with a database and use HTML to display information from data sources.

In ADO, the Recordset object is the main interface to data. An example of the minimal VBScript code to generate a recordset from an ODBC data source is as follows:

set rstMain = CreateObject("ADODB.Recordset")

rstMain.Open "SELECT * FROM authors", _

"DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"

This generates a forward-only, read-only Recordset object useful for scanning data. A slightly more functional recordset can be generated as follows:

set rstMain = CreateObject("ADODB.Recordset")

rstMain.Open "SELECT * FROM authors", _

"DATABASE=pubs;UID=sa;PWD=;DSN=Publishers",

adOpenKeyset, adLockOptimistic

This creates a fully scrollable and updateable recordset.

Note

Adovbs.inc & Adojavas.inc: For applications that use VBScript (for example, Active Server Pages), you must include the Adovbs.inc file in your code in order to call ADO constants by name (use Adojavas.inc for JScript). Always refer to constants by name rather than by value since the values may change from one version to the next.

Note

Updatable Cursor support: Microsoft and Sun Chili!Soft use the Positioned Update and Positioned SQL features of ODBC to implement the AddNew, Update, and Delete methods of the ADO Recordset Object . For some of the supplied ODBC drivers these features are not implemented at all (MySQL, PostgreSQL). For others the support is incomplete (DataDirect SequeLink driver). For these drivers, Sun Chili!Soft uses the implementation of updatable cursors in the ODBC Driver Manager to supply the missing functionality. This works well for recordsets whose fields contain string or numeric data as well as a primary key, auto-increment, or timestamp fields. However, in recordsets containing binary fields or recordsets with duplicate rows, updates, inserts and deletes should be done using the Execute method of the Connection object. Connection.Execute will execute any SQL statement recognized by the database regardless of the capabilities of the ODBC driver.

Note

Linux and multiple SELECT statements: On Linux, ADO does not support stored procedures with multiple SELECT statements.

In ADO, the object hierarchy is de-emphasized. Unlike Data Access Objects (DAO) or Remote Data Objects (RDO), you do not have to navigate through a hierarchy to create objects, because most ADO objects can be independently created. This allows you to create and track only the objects you need. This model also results in fewer ADO objects, and thus a smaller working set.

ADO supports the following key features for building client/server and Web-based applications:

· Independently created objects.

· Support for stored procedures with in/out parameters and return values.

· Different cursor types, including the potential for support of back-end-specific cursors.

· Advanced recordsetcache management.

· Support for limits on number of returned rows and other query goals.