The idea of putting a database on a device that lacks a disk drive seems like a totally alien concept. After all, just how useful can a platform that fits into a shirt pocket be? But Microsoft states that ADO (ActiveX Data Objects) is a basic framework for general-purpose data access in all Microsoft Windows operating systems. So I decided to see just what ADOCE (ActiveX Data Objects Compact Edition) could and couldn't do.
Say adios to…
ADOCE is by no means a full implementation of ADO, hence the words “compact edition” in the name. A full implementation of ADO would be a waste of limited storage and contain too many unnecessary features. This means you'll occasionally have to find a way around some missing features you're used to in the full ADO, but it also means there's plenty of memory left for applications and databases.
For me, the three most notable omissions in ADOCE are the command object, the recordset.save method, and the total lack of an update command when using direct SQL. While none of these issues are showstoppers, their absence does sometimesrequire a few changes in the way I usually code my applications. Take for example this snippet of SQL to update a table:
SET manufacturer = "Just Born",
product = "Hot Tamales"
WHERE upc = "7097040027"
With the lack of the SQL update command in ADOCE, what was a one-step process under Windows 2000 is now a multistep process under Windows CE:
Call objRS.Find("upc = '7097040027'")
objRS("upc") = "7097040027"
objRS("manufacturer") = "Just Born"
objRS("product") = "Hot Tamales"
While not a major change in coding style, it clearly shows that ADOCE is a subset of ADO for the Windows CE platform. This isn't necessarily a bad thing; it just means that code may not be 100 percent compatible with other Windows platforms.
Data manipulation language limitations
The remaining objects, methods, and properties cover just about everything you may need to accomplish. Listing A spells out all the ADOCE-supported objects and their associated methods and properties. These work in much the same manner as they do under traditional ADO, with some limitations.
In addition to the lack of the SQL update command, the remaining commands have been scaled back somewhat. What's left is a combination of small size and functionality, because there's usually a way to accomplish the majority of tasks. Nowhere is the trade-off between size and function more apparent than with the select statement.
Under ADOCE, there are five variations of the select statement: simple, join, order by, projection, and restricted. The simple SQL select is the base upon which all queries are written:
SELECT * FROM order_tbl
Upon this foundation, you have the "restricted", "projection" and "order by" forms of the SQL select statement:
Unfortunately, there are several surprises buried in ADOCE's select syntax, as detailed in Table A.
Under ADOCE, implicit joins are the only supported type of join. In addition, the number of tables that can be joined is limited to a maximum of four. For example, here is an ADOCE inner join:
INNER JOIN product_tbl
ON order_tbl.upc = product_tbl.upc
ORDER BY order_tbl.upc ASC
Other SQL commands
With the exception of the aforementioned update command, the remainder of the data manipulation statements faired well. The SQL insert and delete commands are remarkably unchanged from their desktop counterparts:
INSERT INTO product_tbl
VALUES ("Just Born",
Creating databases and tables
You have several options available when creating databases and tables under ADOCE. The first is SQL's data definition language statements. Though not necessarily the easiest way for the non-DBA to create databases and tables, these statements (Listing B) offer the most control.
The easiest method, by far, is to create a database using Microsoft Access and then convert it to the Windows CE format. This has the advantages of not scaring the non-DBA types, such as myself.
The ADOCE application programming interface
ADOCE provides two functions that, more than anything else, serves to justify the existence of ADOCE. These functions are DesktopToDevice and DeviceToDesktop. As their names suggest, they provide a programmatic way to transfer databases both to and from a Windows CE-based device. Table B outlines the parameters for these two functions.