Programming WinCE Database

By Kevin Cao 08/09/99

Introduction:

A WinCE database is like a big spreadsheet table. In a regular spreadsheet, you have multiple columns and rows. In the WinCE database, Row is called record and columns are called properties. Each record can contain 0 or more properties. Each propertie is defined by a standard datatype, like integer or string.

Figure 1. A sample WinCE database

*You probably know something about SQL if you are using database functions. Microsoft just announced the SQL support in their future releases. Right now, you have to write functions to manipulate the data in your database.

*Memory on your WinCE machine is very limited. Before you start working on your database, take a moment to design the database layout to minimize the storage requirement.

Section One -- Show you how to create, open and close a database

Section Two -- Browse all database on your WindowsCE, Even the hidden ones!

Section Three -- Read database contents

Section Four -- Write database contents

Section Five -- Other useful database functions

Appendix A -- PocketC Database API

Section One:

PocketC Database APIs are suppose to be simple and easy to use.   If you have the time or luxary, go search on http://msdn.microsoft.com for WindowsCE database functions.  You will thank us for the simple database interface in PocketC.  In this section, we are going to learn how to create, open and close a database. 

Create a database

Once you have a clear idea how the database or "spreadsheet" is going to look like, it is time to create the database with the dbcreate function. It takes two arguments:

Database name: The name can be up to 32 characters long. If you pass a longer name, the first 32 characters will be treated as the database name. All databases will be placed in the \Database folder.

Database type: an integer that can be used later on for search database of certain type. You can decide the number for your own database. Don't assume the number you pick is used only for your database. Other database might use the same number.

Return value: if dbcreate is successful, it returns an integer.  This integer is the object identifier for your database.   It can be used to open the database you just created.  see dbopen for detail.

When can dbcreate fail?

• The WinCE database storage is running out of space.
• Another database is already using the name

When it fails, the return value is 0.  You can call getlasterror to obtain the error information.

<Note to Kevin: Need to explain GetLastError later and the error handling system>

Open a database

After a database is created successfully, you can open it now.   A good programming practice is to check the return value of dbcreate before call dbopen.  If you don't check, your program might run on one user's machine, and fail on the other user's machine.   For example: User B has a database called "TodoList", and your program tried to create a database with the same name. 

*Each WinCE database can be distinguished from each other by using its object id or its name.

dbopen is the function you need to open a database.  It takes two parameters:

• Database Object ID:   An integer which identify the database.  It can be obtained by using dbcreate or dbenum.  
• Database name: The name can be up to 32 characters long. If you pass a longer name, the first 32 characters will be treated as the database name. All databases will be placed in the \Database folder.

You can specify either object id or name to open the database.   If you know the function name only, just use 0 for the object ID.  If you know the function object ID only, just use an empty string "" for the database name parameter.

Return value: If the database is opened, the return value is the object id of this database.  Otherwise, the return value is 0. 

When can dbopen fail?

• the operating system doesnot have enough memory to open the database
• the database doesnot exist with the OID or name you provided.

Note: PocketC Database API only supports opening one database at a time.  If you want to open a second database, you must close the current database first.

Close Database

Since we can only open one database at one time, close a database is pretty simple.  Just call dbclose().

Return value can be 1 or 0.  1 means the database is closed.   0 means the function failed to close the database. 

Section One Summary

In Section One, we learned how to create, open and close a database on WindowsCE.

• dbcreate(string name, int dbtype) -- create a new database.  it needs a name and a type.
• dbopen(int oid, string name) -- open a database by its object id or name
• dbclose() -- close current opening database.

It is time to write a few lines of PocketC code to review what you have learned.  After you create a new database, the big question is that how can you tell if your database is actually created?  Please read Section Two to find out how!

Section Two

In this section, we are going to find out all the database on your WindowsCE device!  You will probably be surprised by the number of databases.

Enumerate Database

dbenum is the function can be used to enumerate all the databases.  It takes two parameters:

• int bFirst:  you must use 1 for parameter bFirst if you are using dbenum to get the first database.  After that, you just keep using 0 until dbenum returns 0.
• int databasetype: Do you remember dbcreate takes a database type parameter? You can use it here to specify what type of database you want to enumerate through.  If you want to see all the database regardless of their type, just use 0 for database type.

Return value:

dbenum returns one database object id each time it is called.   Once all of the databases are enumerated, dbenum returns 0.  You will need the object id to find out more about the database.

Database Attributes

Now we have the database's object id, we can find out the database's name, physical storage size in bytes and number of records.

string dbname(int oid) -- returns database name in a string. If failed, return an empty string ""

int dbsize(int oid) -- returns database physical storage size in bytes. If failed, returns 0

in dbnrecs(int oid) -- returns total records in the database. If failed, return 0

For above three functions, if parameter oid is 0, the function will return current open database's attributes.

Show me the CODE!

Here is a short segment of PocketC source code to list all the database.

listdb()
{
    int ret;    int oid;
    ret =    dbenum(1,0);
    if (ret==0) return;
    putsl("List all the databases");
    putsl("Name----------Size----------------NumRecords------");
    while ((oid=dbenum(0,0))!=0)
    {
        putsl(dbname(oid) + "\t\t" + dbsize(oid) + "\t" + dbnrecs(oid));
     }
}

Output:

List all the databases
Name----------Size----------------NumRecords------
testdb        456    2
\RecycleInfo        480    1
\Categories Database        1744    20
Contacts Database        380    0
Tasks Database        380    0
Appointments Database        380    0
\DesktopPositions        1444    11
\EventNotifications        600    5
\UserNotifications        380    0

The first database in the list is called testdb, which is created using PocketC.  We will show you how to write actual data to the database in the next section.

*Note: Did you notice that some database with a backslash afront of their names?  These databases are the Windows CE system databases.  Don't modified them unless you know what you are doing.  Btw, Don't forget to add a backslash, otherwise, \D will be converted into some character, and dbopen will fail.   You need to call dbopen(0,"\\DesktopPositions").  

Section Three Read database contents

Don't you want to find out what is in those databases listed by our code?  I know I want to.  In this section, we are going to learn how to dump the entire database out to the console screen.   

The concept is fairly simple.  We are going to go through each record in the database, and display the record properties one by one.  The number of the record can be found by using dbnrecs(int oid).  The tricky part is to find out the record properties.

int dbrecpropcnt() -- returns number of properties in the current record.  If failed, return -1.

int dbrecproptype(int index) -- returns the property type by using its index.  Windows CE database supports following data types.

#define CEVT_I2 2
#define CEVT_UI2 18
#define CEVT_I4 3
#define CEVT_UI4 19
/*Not Supported */ #define CEVT_FILETIME 64
#define CEVT_LPWSTR 31
/*Not Supported */ #define CEVT_BLOB 65

CEVT_I2

A 16-bit signed integer.

CEVT_I4

A 32-bit signed integer.

CEVT_LPWSTR

A null-terminated string.

CEVT_UI2

A 16-bit unsigned integer.

CEVT_UI4

A 32-bit unsigned integer.

We currently don't support CEVT_FILETIME and CEVT_BLOB.   PocketC users will not be able to access CEVT_FILETIME and CEVT_BLOB properties.   I am working on a solution, and open for suggestions as well.  It is possible to reuse the existing time function to obtain the CEVT_FILETIME.

dbrecpropval(int index) -- returns the property value by using its index.  Possible return type is int and string. 

It seems we almost have everything we needed.  We can find out the property count of the current record,  the property type and value.  When we call dbopen to open a database, it automatically set the current record to the first record in the database.  We need a function that allow us to move current record position.

dbseek(int seektype, int dwValue) -- seek the record depends on the seek type and seek value.  Returns record object id.  If failed, return 0.

#define CEDB_SEEK_CEOID 0x00000001
#define CEDB_SEEK_BEGINNING 0x00000002
#define CEDB_SEEK_END 0x00000004
#define CEDB_SEEK_CURRENT 0x00000008

Again, here is the list of seek type we support at this moment.  

Show me the CODE!

This function dumps the contents of a database.

checkdb(int oid)
{
    int ret;    int cnt; int i; int reccnt; int j;
    putsl("----------------------------------------");
    ret =    dbopen(oid,"");
    if (ret==0)
    {
        putsl(dbname(0) + " open failed");
        return;
    }
    reccnt = dbnrecs(0);
    for (j = 0; j < reccnt; j++)
    {
        cnt = dbrecpropcnt();
        for (i=0; i < cnt; i++)
        {
            puts("("+ dbrecproptype(i) + " " + dbrecpropval(i)+ ")");
        }
        dbseek(8,1);
        putsl("");
    }
    putsl("----------------------------------------");
    ret = dbclose();
}

 

Database Output for a database with name "\Categories Database"

 

\Categories Database        1744     20
----------------------------------------
(2 0)(2 1)(31 Business)(2 0)(2 0)(2 0)
(2 1)(2 1)(31 Competition)(2 0)(2 0)(2 0)
(2 2)(2 1)(31 Favorites)(2 0)(2 0)(2 0)
(2 3)(2 1)(31 Gifts)(2 0)(2 0)(2 0)
(2 4)(2 1)(31 Goals/Objectives)(2 0)(2 0)(2 0)
(2 5)(2 1)(31 Holiday)(2 0)(2 0)(2 0)
(2 6)(2 1)(31 Holiday Cards)(2 0)(2 0)(2 0)
(2 7)(2 1)(31 Hot Contacts)(2 0)(2 0)(2 0)
(2 8)(2 1)(31 Ideas)(2 0)(2 0)(2 0)
(2 9)(2 1)(31 International)(2 0)(2 0)(2 0)
(2 10)(2 1)(31 Key Customer)(2 0)(2 0)(2 0)
(2 11)(2 1)(31 Miscellaneous)(2 0)(2 0)(2 0)
(2 12)(2 1)(31 Personal)(2 0)(2 0)(2 0)
(2 13)(2 1)(31 Phone Calls)(2 0)(2 0)(2 0)
(2 14)(2 1)(31 Status)(2 0)(2 0)(2 0)
(2 15)(2 1)(31 Strategies)(2 0)(2 0)(2 0)
(2 16)(2 1)(31 Suppliers)(2 0)(2 0)(2 0)
(2 17)(2 1)(31 Time & Expenses)(2 0)(2 0)(2 0)
(2 18)(2 1)(31 VIP)(2 0)(2 0)(2 0)
(2 19)(2 1)(31 Waiting)(2 0)(2 0)(2 0)
----------------------------------------

Section Four -- Write, and Modify database contents

After going through the previous three sections, you should have enough knowledge to browse through any WindowsCE databases. It is time to learn how to write to your own database, or modify the existing databases.

To make learning interesting, we are going to create a stock portfolio database.

Figure 2. A sample stock portfolio. The first column is the index number for each company stock. The second column is the company name, the third is the company's stock symbol and the last column is how many shares you have.

The name of our sample stock is called "sample stock", and the type for our database is 20000. You can use a different name and different value for the database type. Just remember not to use any existing database names.

Review Section One, you should be able to write following source code which will create and open the database.

Sample source code:

int dboid;

dboid = dbcreate("sample stock",20000);

if (dboid == 0)

{ alert("failed to open database"); return; }

dbopen(dboid,"");

Now we are almost ready to write data to the database.

First of all, we need to learn a few tricks with dbrecwrite function.

dbrecwriteprop(int rec_oid, int prop_type, int prop_id, void prop_value)

int rec_oid : each record in the database has its own object identifier. You can obtain the rec_oid by using dbrecnow(). If rec_oid is 0, a new record will be written.

int prop_type : each property has a property type. The property type can be

CEVT_I2

A 16-bit signed integer.

CEVT_I4

A 32-bit signed integer.

CEVT_LPWSTR

A null-terminated string.

CEVT_UI2

A 16-bit unsigned integer.

CEVT_UI4

A 32-bit unsigned integer.

int prop_id : each property can have its identification. The identification is simply an positive integer value which can be decided by the developer.

void prop_value: depends on the type you supplied in prop_type. If prop_type is CEVT_I4 a 32-bit signed integer, you better have a integer for prop_value.

For example, I want to create a new record with one integer property. The property's ID is 1, and Value is 1000.

Here is the code: int recoid; recoid = dbrecwriteprop(0, CEVT_I4, 1,(int) 1000);

*Remember to put typecast around the property value parameter.

What about one more property for this record? The good thing we saved the record object id for the new record we just created. Now we can use recoid again in the next call to dbrecwriteprop.

The second property is a string, its ID is 2, and Value is "Intel Corp".

recoid = dbrecwriteprop(recoid,CEVT_LPWSTR,2,(string)"Intel Corp");

*One more time, Remember to put typecast around the property value parameter. WinCE database doesnot support Float or Char Property Type. So if you have float or character values, just type cast them into string or int.

Now, Let's say I want to change first property's value to 1001. Sounds difficult? Nah.

recoid = dbrecwriteprop(recoid, CEVT_I4, 1, (int) 1001);

Now the record should look like

Review dbrecwrite(...)

Function dbrecwrite is a pretty useful function.   Let us review what it just did for us. 

• Create a new record with one property
• Update property in a record
• Add property to a record

Since this function can do a many things at once, please be careful when you are using it.  I am going to show you a sample code which write our stock data into one database.

Show me the CODE!

storedb()
{
    int recoid;
    /* line 1 */
    recoid =    dbrecwrite(0,CEVT_I4,1,(int)1001);
    recoid =     dbrecwrite(recoid,CEVT_LPWSTR,2,(string)"Intel Corp");
    recoid =     dbrecwrite(recoid,CEVT_LPWSTR,3,(string)"INTC");
    recoid =    dbrecwrite(recoid,CEVT_I4,4,(int)60);

    /* line 2 */
    recoid =    dbrecwrite(0,CEVT_I4,1,(int)1002);
    recoid =     dbrecwrite(recoid,CEVT_LPWSTR,2,(string)"Net Bank");
    recoid =     dbrecwrite(recoid,CEVT_LPWSTR,3,(string)"NTBK");
    recoid =    dbrecwrite(recoid,CEVT_I4,4,(int)300);

    /* line 3 */
    recoid =    dbrecwrite(0,CEVT_I4,1,(int)1003);
    recoid =     dbrecwrite(recoid,CEVT_LPWSTR,2,(string)"QWest");
    recoid =     dbrecwrite(recoid,CEVT_LPWSTR,3,(string)"QWST");
    recoid =    dbrecwrite(recoid,CEVT_I4,4,(int)50);
}

Console Output :

----------------------------------------
(3 1003)(31 QWest)(31 QWST)(3 50)
(3 1002)(31 Net Bank)(31 NTBK)(3 300)
(3 1001)(31 Intel Corp)(31 INTC)(3 60)
----------------------------------------

*Note: We created Intel Record first, and it is pushed one lower every time when a new record is written to the database.

Section Five -- Other useful database functions

Section Five... I hope you are not bored to death right now.   If you made this far, you are capable of writting a simple database browser and you should know following basic concepts:

• Database is made up by many records
• Record is made up by many properties
• Property can be an integer or a string
• Each item in the database has its own indentification number.   Database has object id, Record has object id and Property has its own id
• PocketC can only open one database at a time

Before we go any furthor, I strongly suggest you write a small database browser, and create a database with your own data. 

.... < a few hours later > ....

Now, here is a list of additional functions for you do play with.

dbrecdelprop(int recoid, int proptype, int propid); 

parameters:

• int recoid -- record object id
• int proptype -- the property you want to delete's type
• int propid -- the property you want to delete's ID

Description:

• Delete one property from the record

Return:

• returns record id if successful.  If function failed, return 0.

dbrecdel(int recoid)

parameters:

• int recoid -- record object id

Description:

• Delete one record

Return:

• 1 indicates success. 0 indicates failure.  The function could fail when you pass into a wrong or invalid record object id

dbrecnow()

Description:

• Get current record object id.  When you are iterating through the database, you can obtain the current record object id by call dbrecnow.

Return:

• 0 indicates failure.  The function should return the record object id for the current reading/writing record

dbdelete

dbdelete(int dboid)

parameters:

• int dboid -- database object id

Description:

• Delete one database

Return:

• 1 indicates success. 0 indicates failure.  The function could fail when you pass into a wrong or invalid database object id or another program is using the database at this moment.

Appendix A -- PocketC Database API

dbcreate(string dbname, int dbtype)
-- create database with name and type

dbopen(int dboid, string dbname)
-- open database by using database object id or name

dbclose()
-- close database

dbdelete(int dboid)
-- delete database

dbseek(int seektype, int seekvalue)
-- seek database in various ways

dbrecnow()
-- get current record object id

dbrecdelprop(int recoid, int proptype, int propid); 
-- delete property from a record

dbrecdel(int recoid)
-- delete a record

dbrecwrite(int recoid, int proptype, int propid, void propvalue)
-- write one property to the database

dbrecread(int proptype, int propid)
-- read one specific property from the database

dbsize(int dboid)
-- return database size

dbname(int dboid)
-- return database name

dbnrecs(int dboid)
-- return database record numbers

dbenum(int first, int dbtype)
-- enumerate through databases with certain type

dbrecproptype(int index)
-- find out property type

dbrecpropcnt()
-- find out property count

dbrecpropval()
-- find out property value