helpinghand
search
needassistance
 
Database APIs
ADO
Category: SQL
Introduction

ActiveX Data Objects (ADO) is a language-neutral object that is the basis of Microsoft’s Universal Data Access strategy, which means that it is consistent across any programming environment that supports ActiveX/COM objects. This includes Visual Basic, Visual C++ and Java, and scripting languages such as JavaScript, Jscript and VBScript.

Online applications can be built by using ADO on the server to deliver dynamic content through the World Wide Web. Through this mechanism, any client platform supporting a modern Web browser can access these applications, allowing users of Windows PCs, Macs, UNIX workstations and Web-devices to simply connect to your application.

This is not intended to be a complete guide to the functionality provided by ADO but it does provide a good introduction to accessing Mimer SQL through the ADO object model. Where examples are provided, Microsoft Visual Basic is used. It should be possible to use the examples as a basis for translation into other languages.

The examples are based on the sample schema that is provided as part of the Mimer SQL distribution. They assume that an ODBC data source named HOTELDB has been created, which connects to the Mimer SQL database that contains the example schema.

ADO is distributed with a number of products, including Visual Studio and Visual Basic. ADO is also included in with the Microsoft Data Access Components (MDAC) software development kit (SDK), which can be downloaded from www.microsoft.com/data. The MDAC SDK is supported on the following operating systems: Microsoft Windows 95, Windows 98, Windows Millennium (Windows Me), Windows NT 4.0, and Windows 2000.
Function

OLE DB is Microsoft’s next generation following on from Open Database Connectivity (ODBC). Microsoft’s plans for OLE DB go way beyond just providing access to relational databases; OLE DB providers will include non-relational database systems, spreadsheets, documents, graphics, e-mail and CAD/CAM data stores.
ADO is a high-level interface to OLE DB. Because there is an OLE DB data provider for ODBC, ADO is capable of accessing any Mimer SQL ODBC data source in a very simple and straightforward manner.



There are three main objects in ADO: the Connection, the Command, and the Recordset. The following representation of the object model implies a hierarchical relationship between the Connect object and the Command and Recordset objects, in reality these objects can exist independently of a Connection. However, the Error, Parameter and Field objects are part of a hierarchy.



The Connection, Command, Recordset and Field objects all support a Properties collection, which contains provider-specific information about the object. These properties can be read-only or read/write.



Example

Opening a Connection
The Connection object provides the link between the application and the database server. While it is not absolutely necessary to use an explicit Connection object (a connect string can be supplied to a Command or Recordset object) it does avoid the overheads of establishing a new connection for every request.

There are a number of different ways of opening a Connection object. The following are some examples:

'Open a connection using the Data Source name, User ID and Password
Dim dbConn As New ADODB.Connection
dbConn.Open "HOTELDB", "HOTELADM", "HOTELADM"

'Open a connection using OLE DB tags
Dim dbConn As New ADODB.Connection
dbConn.Open "Data Source=HOTELDB;User ID=HOTELADM;Password=HOTELADM;"

'Open a connection using ODBC tags
Dim dbConn As New ADODB.Connection
dbConn.ConnectionString = "DSN=HOTELDB;UID=HOTELADM;PWD=HOTELADM;"
dbConn.ConnectionTimeout = 10 'Number of seconds before timing out
dbConn.Open

'Open a connection using the ODBC connection dialog
Dim dbConn As New ADODB.Connection
dbConn.Properties("Prompt") = adPromptComplete
dbConn.Open "Driver=MIMER;"


Another way of opening a Connection object is to create a Microsoft Data Link from Windows Explorer, simply right-click and choose New/Microsoft Data Link (alternatively create an empty file with a UDL extension and double-click on it). The Data Link Properties dialog box will appear and allow you to build a connection string. While it is possible to save the password this would make the system insecure. To open a Connection object using a Data Link:

'Open a connection using a Data Link
Dim dbConn As New ADODB.Connection
dbConn.Open "File Name=C:\App\connect.udl"


The state of the Connection and individual properties can be referenced. The State property is a bitmask and the AND operator should be used when testing:

If (dbConn.State And adStateOpen) = adStateOpen Then
MsgBox dbConn.Properties("User Name") & " connected"
End If


The properties for the Connection object can be listed:

Dim dbProp As ADODB.Property
For Each dbProp In dbConn.Properties
Debug.Print dbProp.Name & " = " & dbProp.Value
Next dbProp


The Connection object supports the Execute method to process an SQL statement or stored procedure. In the following example adCmdText indicates that it is a SQL statement and adExecuteNoRecords that a result set is not returned (options are OR'ed together):

dbConn.Execute "update ROOM_PRICES set PRICE = PRICE * 1.05" & _
" where HOTELCODE = 'LAP'", _
lngRowsAffected, adCmdText Or adExecuteNoRecords
MsgBox lngRowsAffected & " rows have been updated"


The Close method tells ADO to disconnect from the Mimer SQL server:

dbConn.Close


Depending on how ODBC has been configured the connection may remain in a pool of unused connections for a period of time in the expectation of being used again. Under ADO 2.0 the connection was removed from the pool when the Connection object variable was set to Nothing, this does not happen with ADO 2.1 onwards.

Error Handling
An Error object contains details of a single error or warning returned from the data provider. Details include a textual description of the error, an error number and the source object that raised the error. The Errors collection holds all the errors as there may be more than one error reported for a failure.

Dim dbErr As ADODB.Error
Dim strErrMsg As String

'Loop through the Errors collection for the Connection
For Each dbErr In dbConn.Errors
strErrMsg = strErrMsg & _
"Source: " & dbErr.Source & vbNewLine & _
"Description: " & dbErr.Description & vbNewLine & _
"SQL State: " & dbErr.SQLState & vbNewLine & _
"NativeError: " & dbErr.NativeError & vbNewLine & _
"Number: " & dbErr.Number & vbNewLine & vbNewLine
Next

MsgBox strErrMsg, , "Database error"


The number of Errors and individual properties can be referenced:

If dbConn.Errors.Count > 0 Then  
If dbConn.Errors(0).SQLState = "40001" Then
'Handle transaction commit failure
. . .
End If
End If


A warning is indicated by an error number of zero.

Transaction Processing
A transaction is an essential part of database programming. It defines the beginning and end of a series of database operations that are regarded as a single unit. For example, to transfer money between two bank accounts, an amount is subtracted from one account and the same amount is added to the other account. It is essential that either both of these operations succeed or neither does.

Mimer SQL uses a method for transaction management called Optimistic Concurrency Control (OCC). OCC does not involve any locking of rows as such, and therefore cannot cause a deadlock. Most other DBMSs offer pessimistic concurrency control. Pessimistic concurrency control protects a user's reads and updates by acquiring locks on rows (or possibly database pages, depending on the implementation). These locks may force other users to wait if they try to access the locked items. The user that 'owns' the locks will usually complete their work, committing the transaction and thereby freeing the locks so that the waiting users can compete to attempt to acquire the locks. By completely eliminating the complicated locking overheads required by other DBMSs, Mimer SQL is able to avoid problems such as data being left inaccessible as a result of locks being held over user interactions or as a result of client processes failing.

Within ADO transactions are controlled through the Connection object. The BeginTrans method starts a transaction within the Mimer SQL server. Calling the CommitTrans method ends the transaction; at that stage Mimer SQL checks whether the transaction is valid and returns an error if a conflict is identified. If a conflict is encountered the application determines how to continue, for example whether to automatically retry the transaction or inform the user of the failure.

Calling the RollbackTrans method discards any changes made since the start of the transaction and ends the transaction.

On Error GoTo HANDLE_ERROR
Dim dbConn As ADODB.Connection

Set dbConn = New ADODB.Connection

'Open a connection
dbConn.Open "HOTELDB", "HOTELADM", "HOTELADM"

RETRY:
dbConn.BeginTrans 'Start the transaction

dbConn.Execute "update ROOM_PRICES set PRICE = PRICE * 1.05" & _
" where HOTELCODE = 'LAP'", , _
adCmdText Or adExecuteNoRecords

. . .

If MsgBox("Commit transaction?", vbYesNo) = vbYes Then
dbConn.CommitTrans 'Commit the transaction
Else
dbConn.RollbackTrans 'Abort the transaction
End If

. . .

Exit Sub

HANDLE_ERROR:
Dim dbErr As ADODB.Error
Dim strErrMsg As String

If dbConn.Errors.Count > 0 Then
If dbConn.Errors(0).SQLState = "40001" Then
'Handle transaction commit failure
MsgBox "Commit failure - retrying"
dbConn.RollbackTrans
Resume RETRY 'Automatically retry in this example
End If

'Loop through the Errors collection for the Connection
For Each dbErr In dbConn.Errors
strErrMsg = strErrMsg & _
"Source: " & dbErr.Source & vbNewLine & _
"Description: " & dbErr.Description & vbNewLine & _
"SQL State: " & dbErr.SQLState & vbNewLine & _
"NativeError: " & dbErr.NativeError & vbNewLine & _
"Number: " & dbErr.Number & vbNewLine & vbNewLine
Next

MsgBox strErrMsg, , "Database error"
Else
strErrMsg = "Description: " & Err.Description & vbNewLine & _
"Error: " & Err.Number & vbNewLine & vbNewLine

MsgBox strErrMsg, , "Visual Basic error"
End If


The IsolationLevel property sets or returns the transaction isolation level. The default isolation level for Mimer SQL is adXactRepeatableRead.

Execute a Command
While the Connection object allows the execution of an SQL statement or stored procedure, it is more usual to use a Command object to perform the execution of commands as it offers greater functionality and flexibility. In this case the values for the parameters in the SQL statement (indicated by '?') are supplied with the Execute method (note that this mechanism only works for input parameters; output parameters will not be handled correctly):

Dim dbCmd As ADODB.Command
Dim lngRowsAffected As Long

Set dbCmd = New ADODB.Command

Set dbCmd.ActiveConnection = dbConn 'Existing connection object
dbCmd.CommandText = & _
"update ROOM_PRICES" & _
" set PRICE = cast((cast(PRICE as float) * ?) as integer)" & _
" where HOTELCODE = ?"

dbCmd.Execute lngRowsAffected, Array(1.05, "LAP"), _
adCmdText Or adExecuteNoRecords
MsgBox lngRowsAffected & " rows have been updated"


A Connection does not need to have been opened before a Command can be used, a connection string can be supplied:

Set dbCmd = New ADODB.Command
dbCmd.ActiveConnection = _
"Data Source=HOTELDB;User ID=HOTELADM;Password=HOTELADM;"
. . .


An alternative to using an array of parameters as the second argument to the Execute method, is to use the Parameters collection:

Dim dbCmd As ADODB.Command
Dim dbPrm As ADODB.Parameter
Dim lngRowsAffected As Long

Set dbCmd = New ADODB.Command
Set dbCmd.ActiveConnection = dbConn
dbCmd.CommandText = & _
"update ROOM_PRICES" & _
" set PRICE = cast((cast(PRICE as float) * ?) as integer)" & _
" where HOTELCODE = ?"

Set dbPrm = dbCmd.CreateParameter(, adSingle, adParamInput, , 1.05)
dbCmd.Parameters.Append dbPrm
Set dbPrm = dbCmd.CreateParameter(, adChar, adParamInput, 4, "LAP")
dbCmd.Parameters.Append dbPrm

dbCmd.Execute lngRowsAffected, , adCmdText Or adExecuteNoRecords
MsgBox lngRowsAffected & " rows have been updated"


Parameter objects allow the use of a prepared statement to dynamically construct the query with a different set of parameters at execution time. A prepared statement can provide performance gains where the statement is repeatedly executed, although Mimer SQL version 8.2 reduces this performance difference:

Dim dbCmd As ADODB.Command
Dim dbPrm As ADODB.Parameter
Dim lngRowsAffected As Long

Set dbCmd = New ADODB.Command
Set dbCmd.ActiveConnection = dbConn
dbCmd.CommandText = & _
"update ROOM_PRICES" & _
" set PRICE = cast((cast(PRICE as float) * ?) as integer)" & _
" where HOTELCODE = ?"
dbCmd.CommandType = adCmdText
dbCmd.Prepared = True 'Prepare the statement

'Name the parameters: first = "price", second = "hotel"
Set dbPrm = dbCmd.CreateParameter("price", adSingle, _
adParamInput, , 1.05)
dbCmd.Parameters.Append dbPrm
Set dbPrm = dbCmd.CreateParameter("hotel", adChar, _
adParamInput, 4, "LAP")
dbCmd.Parameters.Append dbPrm

dbCmd.Execute lngRowsAffected
MsgBox lngRowsAffected & " rows have been updated"

'Reference the parameters by name
dbCmd("price") = 1.08
dbCmd("hotel") = "WIND"
dbCmd.Execute lngRowsAffected
MsgBox lngRowsAffected & " rows have been updated"

'Reference the parameters by position
dbCmd(0) = 1.06
dbCmd(1) = "WINS"
dbCmd.Execute lngRowsAffected
MsgBox lngRowsAffected & " rows have been updated"


Similarly, when using stored procedures the simple case allows input parameter values to be supplied to the Execute method:

Set dbCmd.ActiveConnection = dbConn
dbCmd.CommandText = "charge_room" 'Name of stored procedure
dbCmd.CommandType = adCmdStoredProc 'Stored procedure indicator
dbCmd.Execute , Array("SKY101", "900")


A more complicated example illustrates how to handle an output parameter:

Set dbCmd.ActiveConnection = dbConn
dbCmd.CommandText = "FREEQ"
dbCmd.CommandType = adCmdStoredProc
dbCmd.CommandTimeout = 10 'Number of seconds before timing out
dbCmd.Prepared = True

dbCmd.Parameters.Append _
dbCmd.CreateParameter("HotelCode", adChar, adParamInput, 3)
dbCmd.Parameters.Append _
dbCmd.CreateParameter("RoomType", adChar, adParamInput, 6)
dbCmd.Parameters.Append _
dbCmd.CreateParameter("ArrivalDate", adDBDate, adParamInput)
dbCmd.Parameters.Append _
dbCmd.CreateParameter("DepartureDate", adDBDate, adParamInput)
dbCmd.Parameters.Append _
dbCmd.CreateParameter("Rooms", adInteger, adParamOutput)

Debug.Print "Number of parameters: " & dbCmd.Parameters.Count

dbCmd("HotelCode") = "STG"
dbCmd("RoomType") = "SSGLS"
dbCmd("ArrivalDate") = "24/12/2002" 'Regional specific date format
dbCmd("DepartureDate") = "8-1-2003"
dbCmd.Execute

MsgBox dbCmd("Rooms") & " rooms are available"


The properties for the Command object can be listed:

Dim dbProp As ADODB.Property
For Each dbProp In dbCmd.Properties
Debug.Print dbProp.Name & " = " & dbProp.Value
Next dbProp


Using the Close method to close a Connection object causes any Command objects associated with the Connection object to have their ActiveConnection property to be set to Nothing, but the Command objects do persist.

Result-set Processing
There are a number of ways of returning a result-set. Perhaps the simplest is as the result of executing a command (note that the result-set from an Execute method is read-only):

Dim dbCmd As ADODB.Command
Dim rstHotels As ADODB.Recordset

Set dbCmd = New ADODB.Command
Set dbCmd.ActiveConnection = dbConn
dbCmd.CommandText = "select * from HOTEL"
dbCmd.CommandType = adCmdText

Set rstHotels = dbCmd.Execute

Do While Not rstHotels.EOF 'Loop until End Of File
Debug.Print rstHotels("NAME")
rstHotels.MoveNext 'Fetch next row
Loop


A Recordset can be thought of as an array of rows. The 'current row' is the row being examined and manipulated at any given time, and the location in the Recordset is the 'current row position'.

The Open method of the Recordset object can be used to execute a query and return a result-set; the equivalent of the previous example can be written using the Open method:

Dim rstHotels As ADODB.Recordset

Set rstHotels = New ADODB.Recordset
rstHotels.Open "HOTEL", dbConn, adOpenForwardOnly, , adCmdTable

Do While Not rstHotels.EOF
Debug.Print rstHotels!Name
rstHotels.MoveNext
Loop


The Open method has a number of arguments: Source, ActiveConnection, CursorType, LockType and Options. In the previousexample the option adCmdTable indicated thatthe source was a table name (i.e. HOTEL); the ActiveConnection was an existing Connection object; and a cursor-type of adOpenForwardOnly (the default) that rows can only be scrolled through in a forwards direction.

The default value for lock-type is adLockReadOnly, which means that the data cannot be altered. As Mimer SQL only supports optimistic locking all other lock-types are converted to use the optimistic mechanism.

A Connection does not need to have been opened before a Recordset can be used, a connection string can be supplied:

Dim rstHotels As ADODB.Recordset

Set rstHotels = New ADODB.Recordset
rstHotels.ActiveConnection = _
"Data Source=HOTELDB;User ID=HOTELADM;Password=HOTELADM;"
rstHotels.Open "HOTEL", , adOpenForwardOnly, , adCmdTable


An important property of a Recordset object is the cache size; this determines how many records from the result-set are fetched from the server in a single transfer and held locally. This can have a dramatic effect on performance (and memory). The default cache size is 1 but a larger size is generally more efficient:

rstHotels.CacheSize = 24


After a Recordset has been opened, the Fields collection contains a Field object for each column in the recordset. Although Field objects can exist independently they are normally used in conjunction with a Recordset object. Both the Fields and Properties collections for a Recordset can be referenced:

Dim rstHotels As ADODB.Recordset
Dim fldHotel As ADODB.Field
Dim dbProp As ADODB.Property
Dim intIndex As Integer

Set rstHotels = New ADODB.Recordset
rstHotels.ActiveConnection = dbConn
rstHotels.Open "HOTEL", , adOpenForwardOnly, , adCmdTable

'Display Fields collection information
Debug.Print rstHotels.Fields.Count & " Fields in Recordset"
For intIndex = 0 To rstHotels.Fields.Count - 1
Debug.Print " " & rstHotels.Fields(intIndex).Name,
Next intIndex
Debug.Print

'Display column names and their nullable definition
Debug.Print "Fields null attribute"
For Each fldHotel In rstHotels.Fields
If (fldHotel.Attributes And adFldIsNullable) = adFldIsNullable Then
Debug.Print " " & fldHotel.Name & " is nullable"
Else
Debug.Print " " & fldHotel.Name & " is not nullable"
End If
Next fldHotel

'Display Properties collection information
Debug.Print "Recordset properties"
For Each dbProp In rstHotels.Properties
Debug.Print " " & dbProp.Name & " = " & dbProp.Value
Next dbProp


Scrollable cursors allow you to move forward and back to any row within the result-set. A cursor type of adOpenStatic specifies that the cursor be opened in scroll mode. There are a number of methods for navigating through a recordset - Move, MoveFirst, MoveLast, MoveNext and MovePrevious:

rstHotels.Open "HOTEL", dbConn, adOpenStatic, , adCmdTable

Debug.Print "Original sort order"
Do While Not rstHotels.EOF
Debug.Print rstHotels!Name
rstHotels.Move 1 'Specifies how many records to move
Loop

If rstHotels.Supports(adMovePrevious) Then
Debug.Print "Names in reverse order"
rstHotels.Move -1 'Equivalent of MovePrevious
Do While Not rstHotels.BOF
Debug.Print rstHotels!Name
rstHotels.MovePrevious
Loop
End If


The following example illustrates two uses of the AddNew method to insert a new row. The second uses arguments to supply the field names and values; with this technique there is no requirement to perform an Update. The select statement in the Open method causes the Fields collection to be populated without a result-set being returned:

rstHotels.Open "select * from HOTEL where HOTELCODE is null", _
dbConn, , adLockOptimistic

'Check that the recordset permits inserts
If rstHotels.Supports(adAddNew) Then
With rstHotels
.AddNew
!HotelCode = "HILT"
!Name = "HILTON"
!City = "CHICAGO"
.Update
End With

rstHotels.AddNew Array("HOTELCODE", "NAME", "CITY"), _
Array("TRVL", "TRAVEL LODGE", "CHICAGO")
End If


The Delete and Update methods can be used to affect a record in the Recordset. It should also be remembered that the actual SQL statements (e.g. DELETE FROM …) can be used and that these are sometimes easier to code, and therefore maintain, than trying to use Recordsets for INSERT, DELETE and UPDATE operations.

Closing a Connection object while there are open Recordset objects on the connection rolls back any pending changes in the Recordset objects. This is not the same as a transaction rollback; in fact if a transaction is active when a Connection object is closed then an error is generated.
Benefits

It should be remembered that Microsoft has stated that ADO will replace the company’s other data-access models, including DAO and RDO. This means that ADO is not just for Internet/intranet/extranet data access; ActiveX Data Objects is the data access model for the future on Windows.

Links

www.microsoft.com/data
Last updated: 2002-05-18

 

Powered by Mimer SQL

Powered by Mimer SQL