Tuesday, January 23, 2007

Working with .cdb files created from .mdb files

When you convert a .mdb file to a .cdb file (when you copy it to the device with ActiveSync), there are several additional tables that get created:

MSysTables
MSysFields
MSysIndexes
MSysProcs

You can use the two of these to get Metadata from the .cdb file:

MSysTables
TableName
TableId
TableFlags (0 for user, 3 for system)

MSysFields
TableId
FieldName
FieldId
Len
Type (2=INT16, 18=UINT16, 3=INT32, 19=UINT32, 64=FileTime, 1=String, 65=Blob, 11=BOOL, 5=DOUBLE)


Here are two sample functions to get the meta data from the tables:

Function GetTableListFromCDB(cdbFile As String, _
tableId() As Long, _
tableName() As String, _
tableFlags() As Long, _
tableCount As Integer) As Boolean

tableCount = 0

' Mount a Database Volume
Dim filename As String
Dim volumeId As String

volumeId = _


SregoCETPx1.MountDatabaseVolume(cdbFile, OPEN_EXISTING)

If volumeId = "" Then
MsgBox "Unable to mount database volume"
GetTableListFromCDB = False
Exit Function
End If

' Open Database in mounted Volume
Dim dbHandle As Long
dbHandle = SregoCETPx1.OpenDatabaseByName(volumeId, _
"MSysTables", 0, CEDB_AUTOINCREMENT)

If dbHandle <> 0 Then ' make sure database was opened

' read each record
While SregoCETPx1.ReadDatabaseRecord(dbHandle)


tableName(tableCount) = _

SregoCETPx1.GetCurrentRecPropValue(0)

tableId(tableCount) = _

SregoCETPx1.GetCurrentRecPropValue(1)


tableFlags(tableCount) = _

SregoCETPx1.GetCurrentRecPropValue(2)

tableCount = tableCount + 1

Wend

SregoCETPx1.CloseDatabase dbHandle

End If

SregoCETPx1.UnmountDatabaseVolume volumeId

GetTableListFromCDB = True

End Function


Function GetFieldListFromCDB(cdbFile As String, _

tableId As Long, fieldName() As String, _

fieldId() As Long, fieldLen() As Long, _

fieldType() As Long, _

fieldCount As Integer) As Boolean

fieldCount = 0

' Mount a Database Volume
Dim filename As String
Dim volumeId As String

volumeId = _

SregoCETPx1.MountDatabaseVolume(cdbFile, _

OPEN_EXISTING)

If volumeId = "" Then
MsgBox "Unable to mount database volume"
GetFieldListFromCDB = False
Exit Function
End If

Dim recTableId As Long

' Open Database in mounted Volume
Dim dbHandle As Long
Dim id As Long
dbHandle = SregoCETPx1.OpenDatabaseByName(volumeId, _
"MSysFields", 0, CEDB_AUTOINCREMENT)

If dbHandle <> 0 Then ' Success


' read each record
While SregoCETPx1.ReadDatabaseRecord(dbHandle)

recTableId = SregoCETPx1.GetCurrentRecPropValue(0)

If recTableId = tableId Then

fieldName(fieldCount) = _

SregoCETPx1.GetCurrentRecPropValue(1)

id = SregoCETPx1.GetCurrentRecPropValue(2)
fieldId(fieldCount) = ShiftRight(id, 24)

fieldLen(fieldCount) = _

SregoCETPx1.GetCurrentRecPropValue(3)
fieldType(fieldCount) = _


SregoCETPx1.GetCurrentRecPropValue(4)

fieldCount = fieldCount + 1

End If

Wend

SregoCETPx1.CloseDatabase dbHandle

End If


SregoCETPx1.UnmountDatabaseVolume volumeId

GetFieldListFromCDB = True

End Function


' Power2 from http://search.devx.com/
Function Power2(ByVal exponent As Long) As Long
Static res(0 To 31) As Long
Dim i As Long

' rule out errors
If exponent <> 31 Then Err.Raise 5

' initialize the array at the first call
If res(0) = 0 Then
res(0) = 1
For i = 1 To 30
res(i) = res(i - 1) * 2
Next
' this is a special case
res(31) = &H80000000
End If

' return the result
Power2 = res(exponent)

End Function

' ShiftRight from http://search.devx.com/
Function ShiftRight(ByVal value As Long, ByVal times As Long) As Long
' we need to create a mask of 1's corresponding to the
' digits in VALUE that will be retained in the result
Dim mask As Long, signBit As Long

' return zero if too many times
If times >= 32 Then Exit Function
' return the value if zero times
If times = 0 Then ShiftRight = value: Exit Function

' evaluate the sign bit in advance
signBit = (value < times="31" mask =" Not" value =" (value" shiftright =" (value">

To use these function, you do something like this:

List1.Clear

Dim tableName(100) As String
Dim tableId(100) As Long
Dim tableFlags(100) As Long ' 0-user, 3-system
Dim tableCount As Integer

Dim fieldId(100) As Long
Dim fieldName(100) As String
Dim fieldLen(100) As Long
Dim fieldType(100) As Long
Dim fieldCount As Integer

Dim i As Integer
Dim j As Integer

tableCount = 0

Dim cdbFile As String

cdbFile = "\my documents\gps.cdb"

If GetTableListFromCDB(cdbFile, tableId, tableName, _
tableFlags, tableCount) Then

For i = 0 To tableCount - 1
' only show user tables
If tableFlags(i) = 0 Then


List1.AddItem "Table: " + tableName(i) + _
" ID: " + Str(tableId(i))

If GetFieldListFromCDB(cdbFile, tableId(i),

fieldName, fieldId, _

fieldLen, fieldType, _

fieldCount) Then

For j = 0 To fieldCount - 1

List1.AddItem (" " + Str(fieldId(j)) + _
" - " + fieldName(j) + " Type: " + _
Str(fieldType(j)) + _
" Len: " + Str(fieldLen(j))) + _
" ID: " + Str(fieldId(j))

Next

List1.AddItem ""
End If
End If
Next
End If

The output looks like this:


This code sample gets a list of all user tables in the .cdb and display them in a listbox along with each field in that table. The key things here are the TableId and the FieldId. These number are what tie the metadata to the real records in the table.

If you are really going to read a real table, you would do something like the following. My sample .cdb file is gps.cdb and it has a table in it called note.

' Mount a Database Volume
Dim filename As String
Dim volumeId As String

volumeId = SregoCETPx1.MountDatabaseVolume(cdbFile, OPEN_EXISTING)

If volumeId = "" Then
List1.AddItem "Unable to mount Volume: " + cdbFile
Exit Sub
End If

' Open Database in mounted Volume
Dim dbHandle As Long
dbHandle = SregoCETPx1.OpenDatabaseByName(volumeId, _
"note", 0, CEDB_AUTOINCREMENT)

If dbHandle <> 0 Then ' make sure database was opened successfully

Dim recCount As Integer
recCount = 0

While SregoCETPx1.ReadDatabaseRecord(dbHandle) ' read each record

Dim pCount As Long
Dim recId As Long

recId = SregoCETPx1.GetCurrentRecId
pCount = SregoCETPx1.GetCurrentRecPropCount

List1.AddItem Str(recCount) + " - RecId: " + _
Str(recId) + " PropCnt: " + Str(pCount)


For i = 0 To pCount - 1 ' display each property in record

Dim pId As Long
Dim pVal As Variant
Dim pLen As Long
Dim pFlags As Long
Dim pType As Long

pId = SregoCETPx1.GetCurrentRecPropId(i)
pVal = SregoCETPx1.GetCurrentRecPropValue(i)
pLen = SregoCETPx1.GetCurrentRecPropLen(i)
pFlags = SregoCETPx1.GetCurrentRecPropFlags(i)
pType = SregoCETPx1.GetCurrentRecPropType(i)

Dim tempstr As String


Select Case pType

Case CEVT_I4, CEVT_I2, CEVT_UI4, CEVT_UI2, CEVT_R8

tempstr = Val(pVal)

Case CEVT_LPWSTR

tempstr = pVal

Case CEVT_BOOL

If pVal Then tempstr = "True" Else tempstr = "False"

Case CEVT_BLOB

tempstr = SregoCETPx1.GetCurrentRecPropBlobStr(i)


Case CEVT_FILETIME

tempstr = CStr(pVal)

Case Else

tempstr = "***" ' unhandled type

End Select

List1.AddItem (" " + Str(pId) + " = " + tempstr)

Next



recCount = recCount + 1

Wend

SregoCETPx1.CloseDatabase dbHandle

End If

SregoCETPx1.UnmountDatabaseVolume volumeId


The output looks like this:


The property id of each field in the record can be looked up in the FieldId list populated by the previous calls.

No comments: