Friday, April 29, 2005

Pocket Access - IV

Finishing off where I left the previous post, the MSysProcs has only two columns:

  • ProcName - Procedure name

  • SQLText - SQL command


This table is generally empty (I've never seen an instance where it wasn't).

Now, let's look at column types. We cannot confuse CEDB column types with Pocket Access column types. CEDB types are limited to (C/C++):

#define CEVT_I2 2 // short
#define CEVT_UI2 18 // unsigned short
#define CEVT_I4 3 // int
#define CEVT_UI4 19 // unsigned int
#define CEVT_FILETIME 64 // FILETIME
#define CEVT_LPWSTR 31 // LPWSTR
#define CEVT_BLOB 65 // BYTE*
#define CEVT_BOOL 11 // BOOL
#define CEVT_R8 5 // double


I defined these in C# for my previous article as:

public enum CeDbType : ushort
{
Int16 = 2,
UInt16 = 18,
Int32 = 3,
UInt32 = 19,
FileTime = 64,
String = 31,
Blob = 65,
Bool = 11,
Double = 5
}


Pocket Access types resemble SQL types and are more varied. From the online documentation found on the Windows CE SDKs, we can derive the following C# enum:

public enum SqlDataType : short
{
Unknown = 0,
Char = 1,
Numeric = 2,
Decimal = 3,
Integer = 4,
Smallint = 5,
Float = 6,
Real = 7,
Double = 8,
DateTime = 9,
Time = 10,
TimeStamp = 11,
VarChar = 12,
LongVarChar = -1,
Binary = -2,
VarBinary = -3,
LongVarBinary = -4,
BigInt = -5,
TinyInt = -6,
Bit = -7
}

Monday, April 25, 2005

Pocket Access - III

Pocket Access files are essentially mounted CEDB volumes with a very specific structure. They all contain four system tables:

  • MSysFields - Contains all the columns of all tables in the database, including the columns of the system tables.

  • MSysTables - Contains all the tables in the database, including the system tables themselves.

  • MSysIndexes - User indexes are all declared here.

  • MSysProcs - Stored procedures?


An empty Pocket Access database has all four tables created and the first two have some information: the system metadata. System tables are all created as type 3 (see below) and none contains a sort order with the exception of MSysFileds which has a sort order (type 0 - undocumented) on column zero.

To create a Pocket Access database, we must create all system tables an fill them with their default values. This process must be done in a very specific order: 1 - Mount a new CEDB volume; 2 - Create the databases and store their OIDs (the preferred order of creation is MSysFields, MSysTables, MSysIndexes, MSysProcs); 3 - Fill the MSysFields database; 4 - Fill the MSysTables database.

Each record on the MSysFields database has 5 columns (names taken from MSysFields itself):

  • TableID - (int) Table OID

  • FieldName - (string) Field name

  • FieldID - (int) Field property id

  • Len - (short) Field length

  • ODBCType - (short) Data type



The MSysTables table contains only 3 columns:

  • TableName - (string) Table name

  • TableID - (int) Table OID

  • TableFlags - (int) Table flags (system tables = 3, user = 0)



MSysIndexes has the following columns:

  • TableID - (int) Table OID

  • IndexName - (string) Index name

  • FieldID - (int) Field property id

  • IndexFlags - (int) CEDB sort order flags

Sunday, April 24, 2005

CEDB .NET

Finnaly, I got to finish the article. I always look back and think I should have written it in a different way, but I'll get over it... Now I have to start writing about the Pocket Access stuff.

CEDB .NET

Friday, April 22, 2005

CEDB .NET under construction

This one is taking me longer to finish than I had expected. For an early look and your early comments, check it here: CEDB .NET

Pocket Access - II

While researching for my managed CEDB wrapper article and code I found two very interesting posts on Sue Loh's blog:
Common CE database questions
More CE database Q&A
My own experience, from C++ of course, tells me that is is possible not only to access but even to create a Pocket Access database without using ADOCE at all. As a matter of fact, as as Sue Loh explains, Pocket Access cdb databases are CEDB databases with a special format. They have metadata tables with a predefined format and support more types than CEDB does natively. Once you know this format and how to manipulate it there is no difference from approaching the database from ADOCE or from CEDB, with the exception of SQL. The SQL processor exists only on the ADOCE stack, so accessing these databases from the CEDB API means you don't have SQL to play with...

Wednesday, April 20, 2005

Square Games

So you want to write the next blockbuster game for the HP Communicator? Cool, here's how to handle GAPI and a square screen.

Pocket Access - I

Here is an article of fellow MVP Christian Forsberg (he speaks portuguese, did you know?) on migrating applications from Pocket Access to SQL CE. This is an interesting thing because I'm preparing an article on almost the reverse subject: how to access CEDB and Pocket Access databases from .NET CF without using the ADOCE COM interfaces. It's an interesting trip into native code and running around the CF marshaller. I've been learning a lot...

Friday, April 15, 2005

So, what is a tinyint?

A tinyint is a byte. For SQL CE 2.0, this means a DBTYPE_UI1, not a DBTYPE_I1. Where was my head when I decided otherwise? Duh...

Ok, I fixed the bug in SQL CE Console...

Wednesday, April 13, 2005

SQL CE Console 1.0.14 Released

The new minor version release of SQL CE Console is now out. I corrected a few bugs and added the ability of copying the contents of the query data grid to the clipboard. The new setup can be found here. New feature suggestions and bug reports are welcome.

Tuesday, April 12, 2005

FastSqlCe

I finally decided to go ahead with FastSqlCe, an alternative data access layer for SQL CE 2.0. The first go at it was when I decided to compare table insertion performance between a prepared SQL statement and a raw table insert. Comparing a native C++ implementation with a managed one is just not fair, so I decided to write a very simple layer and P/Invoke it. The results were stunning: a raw table insert can be up to five times faster than a prepared SQL INSERT statement. Five times. Of course, the INSERT statement had parameters.

Then I tried filling in a DataTable. The early results were also stunning: managed code was faster. Can't be, can it? After revising and tweaking the code I noticed that the less you P/Invoke, the better performance you get. After a full morning of tweaks I got the better of it and now I can (grimly) smile after squeezing a 15% improvement. Hey, native is faster.

But then I noticed how some SQL CE features are missing altogether, like schema management. How do you rename a table? How do you rename a column? How do you check if a column is marked as IDENTITY? Well, there may be some room for another data access layer after all. Enter FastSqlCe. Enter? Hum. It's not ready yet, sorry. But you can take an early look at it here: FastSqlCe online documentation. Comments and suggestions are welcome. I will post an early sample as soon as it is ready.

Friday, April 01, 2005

Me Blog

I finally had to do it: create my own blog! I will talk mostly about what I do: write software for mobile devices. I've been doing this since 2002 and I am totally addicted. So beware...