Oracle9i Lite Developers Guide for Windows CE
Part No. A95913-01
This document describes Oracle Lite database ActiveX Data Objects for Windows CE. Each section of this document presents a different topic. These sections include:
ActiveX Data Objects is a programming interface which enables Visual Basic applications to access Oracle Lite database functionality. It is an object oriented programming interface that is based on the COM interface standard. It provides functions to access the underlying database engine. ActiveX Data Objects for Windows CE contains a subset of the features of ActiveX Data Objects, designed to run on the Windows CE environment.
To support the ActiveX Data Objects for Windows CE interface, Oracle Lite database has implemented a module that provides exactly the same interface, so that Visual Basic programmers can develop applications using the same Microsoft ActiveX Data Objects for Windows CE interface against Oracle Lite databases.
Oracle Lite database ActiveX Data Objects for Windows CE follows the guideline of Microsoft Windows CE ActiveX Data Objects v2.0 SDK, which is based on the desktop version of ActiveX Data Objects 1.0 with some differences.
Oracle Lite database ActiveX Data Objects for Windows CE can be accessed using almost any programming languages, however, the Oracle Lite database implementation is tailored for the Visual Basic environment.
Oracle Lite database ActiveX Data Objects for Windows CE is an interface that exists on top of Oracle Lite database's ODBC interface, providing a COM interface to application programs.
The ActiveX Data Objects for Windows CE interface is an object-oriented interface that provides its functions through the access of various COM interfaces (classes).
The following table lists the objects, methods, and properties of the ActiveX Data Objects for Windows CE control.
Table 3-1 ADOCE Object Types
|Field Object||[no methods]||ActualSize|
|Field Collection||[no methods]||Count|
*** Indicates not implemented.
Each ActiveX Data Objects for Windows CE object contains a set of methods and a set of properties. Methods are functions that can be called by the application. Properties are data members that can be set or retrieved to change a particular aspect of a feature.
The ActiveX Data Objects for Windows CE control has three objects: Active Connection, Recordset, and Field. Use Create Object to create an Active Connection object and activate a connection based on the DSN entry which is contained in ODBC.txt. Otherwise, if the user only creates a new Recordset object, the default "polite" DSN will be called and connect the entry's database. Use CreateObject to create a new Recordset object. Field objects should not be directly created because they exist only in the context of an existing recordset. Use Set to refer to a specific Field object. For example:
Dim rstCustomers, fldName Set rc = CreateObject("oladoce.activeconnection") rc.connect("polite") Set rstCustomers = CreateObject("oladoce.recordset") rstCustomers.Open "customers" Set fldName = rstCustomers.Fields("Name") MsgBox fldName.Value rstCustomers.Close rs.Disconnect Set fldName = Nothing Set rstCustomers = Nothing
The following table lists the methods the Active Connection Object supports.
Table 3-2 Active Connection Object Methods
|Connect||Open a connection|
|Disconnect||Close a connection|
This method creates a connection for an Active Connection Object.
Table 3-3 Connect Parameters
|DSNName||A string identifying a single DSN.|
The following table lists the methods the Recordset object supports.
Table 3-4 Recordset Object Methods
|AddNew||Inserts a new row into the recordset.|
|CancelUpdate||Cancels changes held in memory.|
|Clone***||Duplicates a recordset.|
|Close||Closes a recordset.|
|Delete||Deletes a row from the recordset.|
|GetRows***||Returns data stored in the recordset.|
|Move||Changes the pointer to the active row in the recordset.|
|MoveFirst||Makes the first row active.|
|MoveLast||Makes the last row active.|
|MoveNext||Moves the active row pointer to the next row.|
|MovePrevious||Moves the active row pointer to the previous row.|
|Open||Defines and opens recordsets; runs SQL commands.|
|Update||Commits changes held in memory and updates the actual table.|
|Supports||Determines if the recordset supports certain features.|
Because ActiveX Data Objects for Windows CE databases are intended to be accessed by a single user, there is no batch update mode. ActiveX Data Objects for Windows CE does not support the following methods:
This method creates a new record for an updatable Recordset object.
recordset.AddNew Fields, Values
Table 3-5 AddNew Parameters
|Fields||Optional. A single name or an array representing names or ordinal positions of the field(s) in the new record.|
|Values||Optional. A single value or an array representing values for the field(s) in the new record.|
Use the AddNew method to create and initialize a new record. Use the Supports method (Const adAddNew = &H01000400) to verify whether you can add records to the current Recordset object. You cannot add records if you open the recordset with the default LockType (Const adLockReadOnly = 1).
After you call the AddNew method, the new record becomes the current record and remains current after you call the Update method. The new record is added to the end of the Recordset.
If you call AddNew while editing the current record or while adding a new record, ActiveX Data Objects for Windows CE calls the Update method to save any changes and then creates the new record.
If Fields is an array, Values must also be an array with the same number of members, otherwise an error occurs. The order of field names must match the order of field values in each array.
Calling the AddNew method without arguments sets the EditMode property to 2 (adEditAdd) and causes ActiveX Data Objects for Windows CE to cache any field value changes locally. Calling the Update method posts the new record to the database and resets the EditMode property to 0 (adEditNone). If you pass the Fields and Values arguments, ActiveX Data Objects for Windows CE immediately posts the new record to the database. No Update call is necessary;the EditMode property value does not change from 0 (adEditNone).
ActiveX Data Objects for Windows CE always updates in immediate mode. Batch mode is not supported.
Dim rs, i, L1 Set rs = CreateObject("oladoce.recordset") rs.open "create table newtable (f1 varchar)" rs.open "newtable", "", 1, 3 rs.addnew "f1", "a" rs.addnew rs.fields("f1") = "b" rs.Update rs.Close rs.open "newtable" Set rs = Nothing
This method cancels any changes made to the current record or to a new record prior to calling the Update method.
Use the CancelUpdate method to cancel any changes made to the current record or to discard a newly added record. You cannot undo changes to the current record or to a new record after you call the Update method.
If you are adding a new record when you call the CancelUpdate method, the record that was current prior to the AddNew call becomes the current record again. The EditMode property is reset to 0.
If you have not changed the current record or added a new record, calling the CancelUpdate method generates an error. If changes were made, the EditMode property of the recordset is nonzero.
Dim rs Set rs = CreateObject("oladoce.recordset") rs.Open "Table1", "", 1, 3 rs.AddNew 'Changes the EditMode to 2 If rs.EditMode <> 0 Then rs.CancelUpdate MsgBox rs.EditMode rs.Close Set rs = Nothing
This method creates a duplicate Recordset object from an existing Recordset object.
Use the Clone method to duplicate Recordset objects, particularly if you want to be able to maintain more than one current record in a specified set of records. Using the Clone method is more efficient than creating and opening a new Recordset object with the same definition as the original.
The current record of a newly created clone is set to the first record.
Changes you make to one Recordset object are visible in all of its clones regardless of cursor type.
Closing the original Recordset does not close its copies;closing a copy does not close the original or any of the other copies.
You can only clone a Recordset object that supports bookmarks (Const adBookmark=8192). Bookmark values are interchangeable;that is, a bookmark reference from one Recordset object refers to the same record in any of its clones.
Dim rs1, rs2, f Set rs1 = CreateObject("adoce.recordset") rs1.open "MSysTables" Set f = rs1.Fields(0) Msgbox f.Value, ,f.Name Set rs2 = rs1.Clone Set f = rs2.Fields(0) Msgbox f.Value, ,f.Name
This method closes an open object and any dependent objects.
Use the Close method to close a Recordset object and free any associated system resources. However, closing an object does not remove it from memory; you can change its property settings and open it again later. To completely eliminate an object from memory, set the Recordset object variable to Nothing.
Closing a Recordset object releases the associated data you may have obtained through this particular Recordset object, but does not affect the underlying database. You can later call the Open method to reopen the recordset with the same or modified attributes. You must close a recordset before changing the structure of the underlying database.
While the Recordset object is closed, calling any methods that require a current row generates an error. Also, trying to close a closed recordset generates an error.
If an edit is in progress, calling the Close method generates an error; call the Update or CancelUpdate method first.
If you use the Clone method to create copies of an open Recordset object, closing the original or a clone does not affect any of the other copies.
This method deletes the current record in an open Recordset object.
Table 3-6 Delete Parameters
|AffectRecords||Optional. The only valid value for AffectRecords is 1 (adAffectCurrent). Any other value generates a run-time error. This feature is present for compatibility with ActiveX Data Objects for the desktop computer.|
Using the Delete method deletes the current record from the database. If the Recordset object does not allow record deletion, an error occurs.
Retrieving field values from the deleted record generates an error.
A deleted record remains current until you move to a different record, after which the deleted record is no longer accessible.If the attempt to delete records fails, a run-time error occurs.
This method retrieves multiple records of a Recordset into an array.
array = recordset.GetRows(Rows, Start, Fields)
Table 3-7 GetRows Parameters
|Array||A Variant variable in which the returned data is stored.|
|Rows||Optional. A Long expression indicating the number of records to retrieve. The default value is adGetRowsRest, or -1.|
|Start||Optional. A String or Variant that evaluates to the bookmark for the record from which the GetRows operation should begin.|
|Fields||Optional. A Variant representing a single field name or ordinal position, or an array of field names or ordinal position numbers. ADOCE returns only the data in these fields.|
Use the GetRows method to copy records from a Recordset into a two-dimensional array. The first subscript identifies the field and the second identifies the record number. The array variable is automatically dimensioned to the correct size when the GetRows method returns the data.
If you do not specify a value for the Rows argument, the GetRows method automatically retrieves all the records in the Recordset object. If you request more records than are available, GetRows returns only the number of available records.
If the Recordset object supports bookmarks, you can specify at which record the GetRows method should begin retrieving data by passing the value of that record's Bookmark property.
If you want to restrict the fields the GetRows call returns, you can pass either a single field name or number, or an array of field names or numbers, in the Fields argument.
After you call GetRows, the next unread record becomes the current record, or the EOF property is set to True if there are no more records.
Dim rstEmployees, strMessage Dim intRows, avarRecords Dim intRecord, intField Dim intRecCount, intFieldCount Set rstEmployees = CreateObject("adoce.recordset") rstEmployees.Open "mytable" strMessage = "Enter number of rows to retrieve." intRows = CInt(InputBox(strMessage)) If intRows <= 0 Then intRows = 1 ' If GetRowsOK is successful, print the results, ' noting if the end of the file was reached. If GetRowsOK(rstEmployees, intRows, avarRecords) Then If intRows > UBound(avarRecords, 2) + 1 Then MsgBox "Less than " & intRows & " rows in recordset." End If intRecCount = UBound(avarRecords, 2) + 1 intFieldCount = UBound(avarRecords, 1) + 1 MsgBox intRecCount & " records found." MsgBox intFieldCount & " fields found." For intRecord = 0 To intRecCount - 1 strMessage = "" For intField = 0 To intFieldCount - 1 strMessage = strMessage & _ avarRecords(intField, intRecord) & vbCrLf Next MsgBox strMessage Next Else MsgBox "GetRows failed!" End If rstEmployees.Close Set rstEmployees = Nothing Public Function GetRowsOK(rstTemp, intNumber, avarData) ' Store results of GetRows method in array. avarData = rstTemp.GetRows(intNumber) ' Return False only if fewer than the desired ' number of rows were returned, but not because the ' end of the Recordset was reached. If intNumber > UBound(avarData, 2) + 1 And Not rstTemp.EOF Then GetRowsOK = False Else GetRowsOK = True End If End Function
This method moves the position of the current record in a Recordset object.
recordset.Move NumRecords, Start
Table 3-8 Move Parameters
|NumRecords||A signed Long expression specifying the number of records the current record position moves.|
|Start||Optional. A String or Variant that evaluates to a bookmark. It is one of the values described in the following:
If the NumRecords argument is greater than zero, the current record position moves forward, toward the end of the recordset. If NumRecords is less than zero, the current record position moves backward, toward the beginning of the recordset.
If the Move call would move the current record position to a point before the first record, ActiveX Data Objects for Windows CE sets the current record to the position before the first record in the recordset, BOF is True. An attempt to move backward when the BOF property is already True generates an error.
If the Move call would move the current record position to a point after the last record, ActiveX Data Objects for Windows CE sets the current record to the position after the last record in the recordset, EOF is True. An attempt to move forward when the EOF property is already True generates an error.
Calling the Move method from an empty Recordset object generates an error.
If you pass the Start argument, the move is relative to the record with this bookmark assuming the Recordset object supports bookmarks. If not specified, the move is relative to the current record.
The following code example sets the constants for the Move method:
Const adBookmarkCurrent = 0 Const adBookmarkFirst = 1 Const adBookmarkLast = 2
These methods respectively move to the first, last, next, or previous record in a specified Recordset object and make that record the current record.
recordset.MoveFirst recordset.MoveLast recordset.MoveNext recordset.MovePrevious
Use the MoveFirst method to move the current record position to the first record in the recordset.
Use the MoveLast method to move the current record position to the last record in the recordset.
Use the MoveNext method to move the current record position one record forward, toward the bottom of the Recordset. If the last record is the current record and you call the MoveNext method, ActiveX Data Objects for Windows CE sets the current record to the position after the last record in the Recordset, EOF is True. An attempt to move forward when the EOF property is already True generates an error.
Use the MovePrevious method to move the current record position one record backward, toward the top of the Recordset. If the first record is the current record and you call the MovePrevious method, ActiveX Data Objects for Windows CE sets the current record to the position before the first record in the Recordset, BOF is True. An attempt to move backward when the BOF property is already True generates an error.
This method opens a cursor, and is read-only by default.
recordset.Open Source, ActiveConnection, CursorType, LockType, Options
Table 3-9 Open Parameters
|Source||Required. A Variant that evaluates to a table name or an SQL statement.|
|ActiveConnection||Optional. The zero-length string (ÒÒ) signifies the H/PC object store and is the only available option in ActiveX Data Objects for Windows CE 1.0.|
|CursorType||Optional. Determines what movement through the recordset is allowed and how updates to the underlying database are reflected in the Recordset. Types include:
Using other values results in a LockType of 1 (adOpenKeyset). Dynamic-type and static-type cursors are not available in ActiveX Data Objects for Windows CE.
|LockType||Optional. Determines what type of locking (concurrency) the provider should use when opening the Recordset. Values are:
Using other values result in a LockType of 3 (adLockOptimistic), unless the table itself is read-only. In that case, the LockType is 1 (adLockReadOnly). For compatibility with ActiveX Data Objects for the desktop computer, the default LockType is 1 (adLocklReadOnly). Pessimistic and optimistic batch locking are not available in ActiveX Data Objects for Windows CE.
There is no Command object in ActiveX Data Objects for Windows CE, so the Source argument must be a string.
There is no Connection object in ActiveX Data Objects for Windows CE, and the object store is the only possible connection. The ActiveConnection argument, if specified, must be a zero-length string (ÒÒ).
If you know what type of command you are using, setting the Options argument instructs ActiveX Data Objects for Windows CE to go directly to the relevant code. If the Options argument does not match the type of command in the Source argument, an error occurs when you call the Open method.
When the Open method is called with a non-row returning SQL command such as CREATE TABLE, no recordset is returned and the state of the recordset remains closed.
While ActiveX Data Objects for Windows CE databases are primarily single-user, other applications can open a recordset on the same database. For each recordset it opens, ActiveX Data Objects for Windows CE generates a keyset that holds pointers to all the database rows referred to by that recordset. In a multiuser scenario, because this keyset is not dynamically updated with changes to the underlying database, the keyset may hold pointers to records that have been deleted by other applications after the recordset was initially opened. Attempting to access those deleted records generates an error. When rows are added to the database by means of the current open recordset, the keyset for that recordset is also updated.
Also in a multiuser scenario, when rows are added to the database by other programs, the keysets for other recordsets referring to that database are not dynamically updated and the rows added by the other program are not visible. Changes by other applications to the rows the keyset knows about are still visible because the pointer to the row remains valid. To generate a new keyset and view additions and deletions by other programs, close the recordset and reopen it. Cloned recordsets share a common keyset.
The following code example sets CursorType and LockType constants for the Open method.
Const adOpenKeyset = 1 Const adLockOptimistic = 3 Dim rstLocked, rstUpdateable Set rstLocked = CreateObject("oladoce.recordset") Set rstUpdateable = CreateObject("oladoce.recordset") 'The default is to open a read-only, forward-only recordset rstLocked.Open "table1" 'You must specify other parameters to make a recordset 'updateable rstUpdateable.Open "table2","", adOpenKeyset, adLockOptimistic
This method determines whether a specified Recordset object supports a particular type of functionality.
Boolean = recordset.Supports(CursorOptions)
Table 3-10 Supports Parameter
|CursorOptions||A Long expression. It is one or a combination of the following:
Use the Supports method to determine what types of functionality a Recordset object supports. If the Recordset object supports the features whose corresponding constants are in CursorOptions, the Supports method returns True. Otherwise, it returns False.
ActiveX Data Objects for Windows CE always returns False for any of the following constants: adHoldRecords, adResync and adUpdateBatch. All other constants can return True.
The following code example sets the constants for the Supports method.
Const adHoldRecords = &H00000100 Const adMovePrevious = &H00000200 Const adAddNew = &H01000400 Const adDelete = &H01000800 Const adUpdate = &H01008000 Const adBookmark = &H00002000 Const adApproxPosition = &H00004000 Const adUpdateBatch = &H00010000 Const adResync = &H00020000 Const adNotify = &H00040000
This method saves any changes you make to the current record of a Recordset object.
recordset.Update Fields, Values
Table 3-11 Update Parameters
|Fields||Optional. A single name or an array representing names or ordinal positions of the field(s) you want to modify.|
|Values||Optional. A single value or an array representing values for the field(s) in the new record.|
Use the Update method to save any changes you make to the current record of a Recordset object since calling the AddNew method or since changing any field values in an existing record. The Recordset object must support updates. To set field values, do one of the following:
Assign values to a Field object's Value property and call the Update method.
Pass a field name and a value as arguments with the Update call.
Pass an array of field names and an array of values with the Update call.
When you use arrays of fields and values, there must be an equal number of elements in both arrays. Also, the order of field names must match the order of field values. If the number and order of fields and values do not match, an error occurs.
If you move from the record you are adding or editing before calling the Update method, ActiveX Data Objects for Windows CE automatically calls Update to save the changes. You must call the CancelUpdate method if you want to cancel any changes made to the current record or to discard a newly added record.
The current record remains current after you call the Update method.
The following table lists the properties the Recordset object supports.
Table 3-12 Recordset Object Properties
|AbsolutePage***||Specifies which page to move for a new current record.|
|AbsolutePosition||Specifies the ordinal position of a Recordset object's current record.|
|ActiveConnection||Sets the current database connection. Always a zero-length string (ÒÒ) in ActiveX Data Objects for Windows CE 1.0.|
|BOF||Indicates whether the current record position is before the first record in a Recordset object.|
|EOF||Indicates that the current record position is after the last record in a Recordset object.|
|Bookmark***||Specifies a bookmark that uniquely identifies a record in a Recordset object.|
|CacheSize***||Specifies the number of records from a Recordset object that are cached locally in memory.|
|Count||Indicates the number of fields in a recordset.|
|CursorType||Indicates the type of cursor used in a Recordset object.|
|EditMode||Indicates the editing status of the current record.|
|LockType||Indicates the type of locks placed on records during editing.|
|PageCount***||Indicates how many pages of data the Recordset object contains.|
|PageSize***||Indicates how many records constitute one page in the Recordset.|
|RecordCount||Returns a Long value that indicates the current number of records in a Recordset object.|
|Source||Indicates the source for the data in a Recordset object, SQL statement or table name.|
|ActualSize||Indicates the actual length of a field's value, in bytes.|
|Attributes||Indicates one or more characteristics of a Field object.|
|DefinedSize||Used to determine the data capacity of a Field object.|
|Name||Returns the name of a Field Object|
|Type||Indicates the data type of a Field object.|
|Underlying Value||Indicates a Field object's current value.|
|Value||Indicates the value of a Field object.|
A recordset is a virtual database table whose fields and rows correspond to a subset of the fields and rows in an actual database table on the H/PC. When you make additions, deletions, or changes to the information in a recordset row, you can pass those changes to the corresponding parts of the table. When you change data in the recordset, the recordset stores the changes in memory, allowing you to cancel them before the underlying database is updated. ActiveX Data Objects for Windows CE does not support batch updates. Only one row at a time can have data that is changed but not committed to the underlying database.
When you change the structure of the recordset table, the changes to the underlying database table are immediate.
This property specifies which page the new current record resides.
var = recordset.AbsolutePage
Returns a Long value from 1 to the number of pages in the Recordset object (PageCount).
Use the AbsolutePage property to identify the page number on which the current record is located. Use the PageSize property to logically divide the Recordset object into a series of pages, each of which has the number of records equal to PageSize, except for the last page, which may have fewer records.
Like the AbsolutePosition property, AbsolutePage is 1-based and equals 1 when the current record is the first record in the Recordset. Set this property to move to the first record of a particular page. Obtain the total number of pages from the PageCount property.
AbsolutePage supports three special values -1 (adPosUnknown), -2 (adPosBOF) and -3 (adPosEOF).
The following code example sets the constants for the AbsolutePage property.
Const adPosUnknown = -1 Const adPosBOF = -2 Const adPosEOF = -3
This property specifies the ordinal position of a Recordset object's current record.
var = recordset.AbsolutePosition
Returns a Long value from 1 to the number of records in the Recordset object (RecordCount).
Use the AbsolutePosition property to move to a record based on its ordinal position in the Recordset object.
Like the AbsolutePage property, AbsolutePosition is 1-based and equals 1 when the current record is the first record in the Recordset. You can obtain the total number of records in the Recordset object from the RecordCount property.
Do not use the AbsolutePosition property as a surrogate record number. The position of a specified record changes when you delete a preceding record. There is also no assurance that a specified record will have the same AbsolutePosition if the Recordset object is reopened. Bookmarks are the recommended way of retaining and returning to a specified position and are the only way of positioning across all types of Recordset objects.
AbsolutePosition supports three special values -1 (adPosUnknown), -2 (adPosBOF) and -3 (adPosEOF).
Attempting to set AbsolutePosition to any zero or negative value results in an error.
This property sets the current database connection.
var = recordset.ActiveConnection
The ActiveConnection property is write-only for the following reasons:
Although this property returns a Connection object in ActiveX Data Objects for the desktop computer, there is no Connection object in ActiveX Data Objects for Windows CE.
ActiveX Data Objects for Windows CE 1.0 can only work with the object store.
This property is also always a zero-length string (ÒÒ).
In a Recordset object, BOF indicates that the current record position is before the first record. EOF indicates that the current record position is after the last record.
The return values for the BOF and EOF properties are Boolean values.
The BOF property returns True if the current record position is before the first record, and False if the current record position is on or after the first record.
The EOF property returns True if the current record position is after the last record, and False if the current record position is on or before the last record.
If either the BOF or EOF property is True, there is no current record.
Use the BOF and EOF properties to determine whether a Recordset object contains records or whether you have gone beyond the limits of a Recordset object when you move from record to record.
If you open a Recordset object containing no records, the BOF and EOF properties are set to True, and the Recordset object's RecordCount property setting is 0. When you open a Recordset object that contains at least one record, the first record is the current record and the BOF and EOF properties are False.
Calling the Delete method, even if it removes the only remaining record from a Recordset, does not change the setting of the BOF or EOF property.
The following table shows what happens to the BOF and EOF property settings when you call various Move methods, but are unable to successfully relocate a record.
Table 3-13 BOF and EOF Parameters
|Move=0||No change||No change|
|MovePrevious, Move<0||True||No change|
|MoveNext, Move>0||No change||True|
This property returns a bookmark that uniquely identifies the current record in a Recordset object, or sets the current record in a Recordset object to the record identified by a valid bookmark.
object.Bookmark [= value]
Table 3-14 Bookmark Parameter
|object||An object expression that evaluates to an object.|
|value||A variant expression that evaluates to a bookmark.|
Returns a Variant expression that evaluates to a valid bookmark.
Use the Bookmark property to save the position of the current record and return to that record at any time.
When you open a Recordset object, each of its records has a unique bookmark. To save the bookmark for the current record, assign the value of the Bookmark property to a variable. To quickly return to that record at any time after moving to a different record, set the Recordset object's Bookmark property to the value of that variable.
If you use the Clone method to create a copy of a Recordset object, the Bookmark property settings for the original and the duplicate Recordset objects are identical and you can use them interchangeably. However, you cannot use bookmarks from different Recordset objects interchangeably, even if they were created from the same source or command.
This property indicates the number of records from a Recordset object that are cached locally in memory.
This property is not used by ActiveX Data Objects for Windows CE. It always returns a value of 1 and ignores any attempts to set it.
This property indicates the number of fields in a recordset.
Table 3-15 Count Parameter
|fields||Specifies the Fields collection of an open Recordset.|
Dim rs Set rs = CreateObject("adoce.recordset") rs.Open "MSysIndexes" MsgBox rs.Fields.Count rs.Close Set rs = Nothing
This property indicates the type of cursor used in a Recordset object.
Indicates what movement through the recordset is allowed and how updates to the underlying database are reflected in the recordset. Is either 0 (adOpenForwardOnly) or 1 (adOpenKeyset).
Use the CursorType property to specify the type of cursor that should be used when opening the Recordset object. The CursorType property is read/write when the recordset is closed and read-only when it is open.
The CursorType property may change to match the actual cursor type in use when the Recordset object is open. To verify specific functionality of the returned cursor, use the Supports method.
Because ActiveX Data Objects for Windows CE only implements adOpenForwardOnly and adOpenKeyset, all other values are mapped to adOpenKeyset.
This property indicates the editing status of the current record.
One of the values described in the following table is returned.
Table 3-16 Edit Mode Return Values
|adEditNone||0||No editing operation is in progress.|
|AdEditInProgress||1||Data in the current record has been modified but not yet saved.|
|AdEditAdd||2||The AddNew method has been invoked, and the current record in the copy buffer is a new record that has not been saved in the database.|
Use the EditMode property to determine the editing status of the current record. You can test for pending changes if an editing process has been interrupted and determine whether you need to use the Update or CancelUpdate method.
For more information about the EditMode property under different editing conditions, see the AddNew method.
The following code example sets the constants for the EditMode property.
Const adEditNone = 0 Const adEditInProgress = 1 Const adEditAdd = 2
This property indicates the type of locks placed on records during editing.
Returns one of the values described in the following table.
Table 3-17 Lock Mode Return Values
|adLockReadOnly||1||Default. You cannot add, delete, or change records.|
|AdLockOptimistic||3||You can add, delete, and change records.|
Use the LockType property to determine what type of locking the provider should use when opening a Recordset object or to return the type of locking in use on an open Recordset object. The LockType property is read/write when the Recordset is closed and read-only when it is open.
ActiveX Data Objects for Windows CE does not support adLockPessimistic or adLockBatchOptimistic and silently substitutes adLockOptimistic.
This property indicates how many pages of data the Recordset object contains.
Use the PageCount property to determine how many pages of data are in the Recordset object. Pages are groups of records whose size equals the PageSize property setting. Even if the last page is incomplete, because there are fewer records than the PageSize value, it counts as an additional page in the PageCount value. Set to -1 (adUnknown) when the size of the Recordset has not or cannot be determined.
For more information about page functionality, see the PageSize and AbsolutePage properties.
This property indicates how many records constitute one page in the Recordset.
var = recordset.PageSize
Returns a Long value, indicating how many records are on a page. The default is 10.
Use the PageSize property to determine how many records make up a logical page of data. Establishing a page size allows you to use the AbsolutePage property to move to the first record of a particular page. This is useful when you want to allow the user to page through data, viewing a certain number of records at a time.
This property can be set at any time, and its value will be used for calculating where the first record of a particular page is.
This property indicates the current number of records in a Recordset object.
var = object.RecordCount
Returns a Long value.
Use the RecordCount property to find out how many records are in a Recordset object. The property returns -1 (adUnknown) when ActiveX Data Objects for Windows CE cannot determine the number of records.
Reading the RecordCount property on a closed Recordset causes an error.
This property indicates the source for the data in a Recordset object, SQL statement or table name.
var = recordset.Source
Returns a String value.
Use the Source property to specify a data source for a Recordset object.
Setting this property has no effect, because a Source argument is required by the Open statement and there is no Execute method or Command object in ActiveX Data Objects for Windows CE.
The following table lists the properties the Field object supports.
Table 3-18 Field Object Properties
|ActualSize||Indicates, in bytes, the actual length of a field's value.|
|Attributes||Returns a value that indicates one or more characteristics of a Field object. This property is read-only.|
|DefinedSize||Used to determine the data capacity of a Field object. It returns, in characters, the defined size of the field. Compare with ActualSize, which returns the size in bytes.|
|Name||Returns the name of a field. This property is read-only.|
|Type||Indicates the data type of a Field object. The Type property is read-only.|
|UnderlyingValue||Indicates a Field object's current value in the database.|
|Value(default)||Indicates a Field object's current value in the recordset.|
The Field object has no methods or events. With the exception of Value, all the properties are read-only. The following code example shows how to use a Field object.
Dim rs, f Set rs = CreateObject("oladoce.recordset") rs.open "MyTables" Set f = rs.Fields(0) Msgbox f.Value, f.Name
This property indicates, in bytes, the actual length of a field's value.
var = field.ActualSize
Returns a Long value.
Use the ActualSize property to return the actual length of a Field object's value. For all fields, the ActualSize property is read-only. If ActiveX Data Objects for Windows CE cannot determine the length of the Field object's value, the ActualSize property returns -1 (adUnknown).
For character data types, it may be useful to use DefinedSize to determine the maximum size permitted.
This property indicates one or more characteristics of a Field object. This property is read-only.
var = field.Attribute
For a Field object, the value specifies characteristics of the field and can be a sum of one or more of the values described in the following table.
Table 3-19 Attributes Return Values
|adFldMayDefer ##||2||Indicates that the field is deferred, the field values are not retrieved from the data source with the whole record, but only when you explicitly access them.|
|AdFldUpdatable||4||Indicates that you can write to the field.|
|AdFldUnknownUpdatable ##||8||Indicates that the provider cannot determine if you can write to the field.|
|AdFldFixed||16||Indicates that the field contains fixed-length data. This is set for all data types except adVarWChar, adLongVarWChar, adVarBinary, and adLongVarbinary.|
|AdFldIsNullable||32||Indicates that the field accepts NULL values.|
|AdFldMayBeNull||64||Indicates that you can read NULL values from the field.|
|adFldLong ##||128||Indicates that the field is a long binary field, or that you can use the AppendChunk and GetChunk methods.|
|adFldRowID ##||256||Indicates that the field contains a record identifier, such as record number, unique identifier, and so forth.|
ActiveX Data Objects for Windows CE never returns the values adFldRowVersion and adFldCacheDeferred
The following code example sets the constants for the Attributes property.
Const adFldMayDefer = 2 Const adFldUpdatable = 4 Const adFldUnknownUpdatable = 8 Const adFldFixed = 16 Const adFldIsNullable = 32 Const adFldMayBeNull = 64 Const adFldLong = 128 Const adFldRowID = 256 Dim rs, n Set rs = CreateObject ("oladoce.recordset") rs.Open "table1" For n = 0 to rs.Fields.Count -1 MsgBox rs.Fields(n).Attributes Next
This property is used to determine the data capacity of a Field object. It returns the defined size of the field in characters. It can be compared with ActualSize, which returns the size in bytes.
var = field.DefinedSize
Returns a number that specifies the maximum length of the field.
ActiveX Data Objects for Windows CE supports two string types, strings with less than 256 characters and memo strings of up to 32,733 Unicode characters. These lengths are set when the table is created and are enforced when setting data. For strings less than 256 characters, DefinedSize returns the length specified when the table was created. For text fields that have undefined length, DefinedSize returns the maximum number of characters the field can hold, 32,733 Unicode characters.
The DefinedSize and ActualSize properties are different. For example, a Field object with a declared type of 202 (adVarWChar) and a maximum length of 50 characters returns a DefinedSize property value of 50, but the ActualSize property value it returns is the number of bytes of data stored in the field for the current record. This may be longer than the defined size because adVarWChar characters take two bytes per character.
This property returns name of a field. This property is read-only.
Returns a String value that specifies a name.
Field names can not be longer than 64 characters.
Dim rs, n Set rs = CreateObject ("oladoce.recordset") rs.Open "table1" For n = 0 to rs.Fields.Count -1 MsgBox rs.Fields(n).Name Next
This property indicates the datatype of a Field object. This property is read-only.
var = field.Type
One of the values described in the following table is returned.
Table 3-20 Type Return Values
|adVarWChar||202||Null-terminated Unicode character string of <256 characters|
|adLongVarWChar||203||Null-terminated Unicode character string|
|adVarBinary||204||Binary value of <256 bytes|
|adLongVarBinary||205||Binary value of <=65533 (4096*16 - 3) bytes|
|adInteger||3||4-byte signed integer|
|adSmallInt||2||2-byte signed integer|
|adDouble||5||Double-precision floating point value|
|adUnsignedSmallInt||18||2-byte unsigned integer|
|adUnsignedInt||19||4-byte unsigned integer|
|adBoolean||11||Boolean True/False value|
|adDouble||5||Double precision floating point number|
ActiveX Data Objects for the desktop computer allows other values for Type, but these are neither supported nor ever returned by ActiveX Data Objects for Windows CE.
The following code example sets the constants for the Type property.
Const adVarWChar = 202 Const adLongVarWChar = 203 Const adVarBinary = 204 Const adLongVarBinary = 205 Const adInteger = 3 Const adSmallInt = 2 Const adDouble = 5 Const adDate = 7 Const adUnsignedSmallInt = 18 Const adUnsignedInt = 19 Const adBoolean = 11 Const adDouble = 5 Dim rs, n Set rs = CreateObject ("oladoce.recordset") rs.Open "table1" For n = 0 to rs.Fields.Count -1 MsgBox rs.Fields(n).Type Next
This property indicates a Field object's current value in the database.
var = field.UnderlyingValue
Returns a Variant value.
Use the UnderlyingValue property to return the current field value from the database.
The OriginalValue property is not supported in ActiveX Data Objects for Windows CE.
This property indicates the value assigned to a Field object.
var = field.Value
Returns a Variant value. The default value depends on the Type property.
Use the Value property to set or return data from Field objects.
ActiveX Data Objects for Windows CE allows setting and returning long binary data with the Value property.
The Recordset object has one collection, Fields. There is no corresponding collection for indexes.
The Fields collection contains a Field object for each column in the Recordset. You can refer to a particular field by name or by index. The Fields collection supports the Count property.
The following code example shows how to use the Fields collection to get all the field names from the MyTable table.
Dim rs, n Set rs = CreateObject("oladoce.recordset") rs.open "MyTable" For n = 0 to rs.Fields.Count -1 Msgbox rs.Fields(n).Name Next
ActiveX Data Objects for Windows CE supports all Oracle Lite database for Windows CE SQL syntax except for:
a semi-column at the end of a statement
a single word statement, such as commit or rollback
The following table lists the ActiveX Data Objects for Windows CE system tables that are not supported by Oracle Lite database ActiveX Data Objects for Windows CE.
Table 3-21 ADOCE System Tables
|MsysTables||All the tables known to ActiveX Data Objects for Windows CE and their characteristics.|
|MsysIndexes||All the indexes for all the tables known to ActiveX Data Objects for Windows CE.|
|MsysFields||All the fields for all the tables known to ActiveX Data Objects for Windows CE.|
|MsysProcs||Stored SQL statements that can be executed by name.|
Oracle Lite database ActiveX Data Objects for Windows CE maps possible ActiveX Data Objects for Windows CE errors for its own use.
ActiveX Data Objects for Windows CE can generate ActiveX Data Objects errors and SQL errors. ActiveX Data Objects errors occur when the methods and properties of the control are used improperly. SQL errors are generated when there is a problem with the SQL statements used in the Open method.
Errors have a number and associated error text. The text is retrieved using the Description property of the Err object. There is no Helpfile or HelpContext information.
Errors are caught by using the On Error Resume Next statement along with inline error trapping. To trap errors inline, check the Err object after performing an operation to see if Err.Number is nonzero. Using the error code returned in Err.Number, you can handle each error appropriately, whether it is an SQL error or an ActiveX Data Objects error.
The following table lists the ActiveX Data Objects error values.
Table 3-22 ADOCE Errors
|adErrInvalidArgument||3001||The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another.|
|AdErrNoCurrentRecord||3021||Either BOF or EOF is True, or the current record has been deleted. The operation requested by the application requires a current record.|
|AdErrIllegalOperation||3219||The operation requested by the application is not allowed in this context.|
|AdErrFeatureNotAvailable||3251||The operation requested by the application is not supported by the provider.|
|AdErrItemNotFound||3265||ActiveX Data Objects could not find the object in the collection corresponding to the name or ordinal reference requested by the application.|
|adErrObjectNotSet||3420||The object referenced by the application no longer points to a valid object.|
|adErrDataConversion||3421||The application is using a value of the wrong type for the current operation.|
|adErrObjectClosed||3704||The operation requested by the application is not allowed if the object is closed.|
|adErrObjectOpen||3705||The operation requested by the application is not allowed if the object is open.|
|AdErrProviderNotFound||3706||ActiveX Data Objects could not find the specified provider.|
|AdErrInvalidConnection||3709||The connection string you specified is invalid.|
The following table lists the types of SQL errors you may encounter. The error values in the table are hexadecimal and can be shown in your code by using Hex(Err.Number).
Table 3-23 SQL Errors
|DB_E_CANTCONVERTVALUE||80040E07||Cannot convert type, ÒconstantÓ.||A literal value in the command text could not be converted to the type of the associated column for reasons other than data overflow. The error string should contain the offending constant.|
|DB_E_DATAOVERFLOW||80040E57||Constant value ÒconstantÓ overflowed.||A literal value in the command text overflowed the type specified by the associated column. The error string should contain the offending constant.|
|DB_E_ERRORSINCOMMAND||80040E14||Incorrect syntax near Òtoken.Ó||The command text contained one or more errors, typically a syntax error or unexpected keyword. The error string should contain the unexpected token.|
|E_OUTOFMEMORY||8007000E||Out of memory.||Out of memory.|
|DB_E_NOTABLE||80040E37||Table ÒtableÓ does not exist.||The specified table does not exist. The error string should contain the erroneous table name.|
|DB_E_BADCOLUMNID||80040E11||Field ÒfieldÓ does not exist.||A specified column did not exist. The error string should contain the erroneous field name.|
|DB_E_DUPLICATETABLEID||80040E3F||Table ÒtableÓ already exists.||The specified table already exists in the current data source.|
|DB_E_DUPLICATEINDEXID||80040E34||Index ÒindexÓ already exists.||The specified index already exists in the current data source object.|
|DB_E_NOINDEX||80040E35||Index ÒindexÓ does not exist.||The specified index does not exist in the current data source or did not apply to the specified table.|
|DB_E_DUPLICATECOLUMNID||80040E3E||Field ÒfieldÓ already exists.||A field name was the same in two or more elements.|
|DB_E_NOCOMMAND||80040E0C||N/A||No command has been set.|
|DB_E_DELETEDROW||80040E23||N/A||Row is deleted.|
|DB_E_CANTFETCHBACKWARDS||80040E24||N/A||A forward only cursor cannot read backwards.|
|DB_E_FIELDDIFFERENT||80040E41||Invalid field comparison near Ò%1!s.Ó||Two fields of different type were being compared.|
|DB_E_FIELDMAXEXCEED||80040E42||Exceeded maximum columns in table near Ò%1!s.Ó||An attempt was made to use more than the maximum number of columns per table.|