Using ADOCE from a C++ Application

  • Article

 

Pocket PC devices come preloaded with ADOCE (Active Data Objects for the Windows CE operating system). This enables applications developers targeting the Pocket PC to take advantage of a powerful database language that is well established, well documented, and very robust. This article will show you how to access ADOCE from a C++ application.

Applies to:
   Microsoft Windows Powered Pocket PC 2000
   Microsoft Active X Data Objects for Microsoft Windows CE

One of the (numerous) advantages the PocketPC developer has over their Palm counterparts is the ability to use SQL (structured query language) to access storage data. Developers have been using SQL for over a decade for desktop and workstation applications, and there are countless resources available both on SQL syntax and integrating SQL into various languages.

With Active Data Objects (ADO), Microsoft has provided Windows developers with a common tool they can use from both C++ and Visual Basic applications (and any other language that uses COM objects) in a consistent manner. Unfortunately, using COM objects from C++ is not as simple as using them from Visual Basic, so Virtual Office Systems has created a free C++ class library encompassing the COM interface to the ADO objects.

ADOCE architecture.

What You Need

Gotchas

ADOCE 3.0 (the version that currently ships with the Pocket PC) has problems connecting to CDB files through the ADO Connection object. If a developer needs to access a CDB file, he or she needs to connect to the CDB file directly from the Recordset object, rather than reusing a Connection object for multiple Recordsets. Unfortunately, this means that each Recordset needs to establish its own connection, which is slower than using a single Connection object. This is supposed to be fixed in ADOCE 3.1.

Languages Supported

English

Querying Results from the Database with ADO

To retrieve the rows (also known as "records") from a database table, ADO uses the SELECT command from SQL. The SELECT command provides a criteria of what to retrieve, known as the "WHERE clause" and an optional sort order. Once the SELECT command has been issued, the application can do something with each row returned. To issue a SELECT command and process the results:

  1. Construct a CVOConnection object in your code without specifying a provider. This creates a connection to the main Windows CE database (cedb).
  2. Construct a CVORecordset object, passing the CVOConnection object you created in step 1 as the connection.
  3. Call the CVORecordset::Open() method, passing a SQL string (for example, "SELECT Name, Address, City, State, Zip FROM Customers ORDER BY Zip, Name"). Pass adOpenForwardOnly as the CursorType parameter, and adLockReadOnly as the LockType parameter.
  4. If the CVORecordSet::IsOpen() method returns FALSE, an error occurred while executing the command and the SQL syntax passed to the Open() method in step 3 should be verified. If not, proceed to step 5.
  5. Call the CVORecordset::MoveFirst() method to move to the first row returned by the query.
  6. As soon as the CVORecordSet::IsEOF() method returns TRUE, you have reached the last row of the query results. Loop until IsEOF() returns TRUE.
  7. Using the CVORecordset object's GetFieldCount(), GetFieldName() and GetFieldValueString() methods, you can easily parse the results of each row.
  8. Invoke the CVORecordset::MoveNext() method until IsEOF (see step 6) returns FALSE.
  9. Invoke the CVORecordset::Close() method to close the Recordset. This happens automatically when the CVORecordset object goes out of scope.

Inserting Rows into the Database Through ADO

To add rows to a SQL database, each row must be "inserted" into the database. As long as the row doesn't violate the constraints of the table being added to (i.e. not providing a required column value), the new row will be available to future queries.

  1. Construct a CVOConnection object in your code without specifying a provider. This creates a connection to the main Windows CE database (cedb).
  2. Construct a CVORecordset object, passing the CVOConnection object you created in step 1 as the connection.
  3. Call the CVORecordset::Open() method, passing a SQL string (for example, "INSERT INTO Customers (Name, Address, City, State, Zip) VALUES ('Bob Smith', '1 Main Street', 'Dallas', 'TX', '75244')"). As before, pass adOpenForwardOnly as the CursorType parameter, and adLockReadOnly as the LockType parameter.
  4. If the CVORecordSet::Open() method returns FALSE, an error occurred while executing the command and the syntax of the SQL command should be verified. If not, the row has been successfully inserted.

Deleting Rows from the Database through ADO

In SQL, rows are deleted from a table using the DELETE command. Which rows are deleted are determined by the WHERE clause, using the same syntax as the SELECT command. To delete rows from a table:

  1. Construct a CVOConnection object in your code without specifying a provider. This creates a connection to the main Windows CE database (cedb).
  2. Construct a CVORecordset object, passing the CVOConnection object you created in step 1 as the connection.
  3. Call the CVORecordset::Open() method, passing a SQL string (for example, "DELETE FROM Customers WHERE Name = 'Bob Smith'"). As before, pass adOpenForwardOnly as the CursorType parameter, and adLockReadOnly as the LockType parameter. If the CVORecordSet::Open() method returns FALSE, an error occurred while executing the command and the SQL syntax of the DELETE command should be verified. If not, the row(s) are deleted.

Conclusion

Beginning with SQL Server 2000,ADOCE now has the ability to remotely access a database through SQL queries. This capability is only just now becoming widely useful in the marketplace, since wireless handheld networking is a relatively new market. It won't be long before sales reps are routinely plugging into their corporate contacts database, delivery people are instantly updating delivery logs, doctors and nurses are accessing patient histories, and any number of other remote client/server database applications are commonplace on the Pocket PC platform.

With ADO, Microsoft has provided developers of Windows-based applications with a common SQL query tool they can use from both C++ and Visual Basic applications (and any other language that uses COM objects) in a consistent manner.