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 = _
tableName(tableCount) = _
tableFlags(tableCount) = _
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
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.
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
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)
While SregoCETPx1.ReadDatabaseRecord(dbHandle)
tableName(tableCount) = _
SregoCETPx1.GetCurrentRecPropValue(0)
tableId(tableCount) = _
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, _
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 = _
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
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
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) = _
id = SregoCETPx1.GetCurrentRecPropValue(2)
fieldId(fieldCount) = ShiftRight(id, 24)
fieldLen(fieldCount) = _
SregoCETPx1.GetCurrentRecPropValue(3)
fieldType(fieldCount) = _
fieldType(fieldCount) = _
SregoCETPx1.GetCurrentRecPropValue(4)
fieldCount = fieldCount + 1
End If
Wend
SregoCETPx1.CloseDatabase dbHandle
End If
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),
" 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:
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:
Post a Comment