Tech Note 34: Accessing Palm OS Databases

January 9, 2008
© NSB Corporation. All rights reserved.

Contents:

    Introduction

    Objects
    Index and Quick Reference
    Objects and Collections
    Functions
    Methods
    Properties

Introduction

The PalmDB Automation Control (PalmDB.dll) is a COM component that allows full access to Palm OS Database files as created by Palm organizers, Treo phones, or similar handeld devices. These are .pdb files containing a binary representation of a database as stored on a desktop PC after a backup HotSync.

PalmDB was created by Maxime Labelle several years ago. While it is no longer be actively maintained by the author, it works reasonably well and the source code is freely available. We've made some enhancements to it. If you have made improvements to the control itself, please let us know.

You can download the original binaries, source and documentation here:
http://www.palmgear.com/index.cfm?fuseaction=software.showsoftware&PartnerREF=&siteid=1&prodID=7371

Installation and Use

In order to use the PalmDB Automation Control, PalmDB.dll must be copied to the computer running an NSBasic program that uses it. As it is an ActiveX control, it is not contained within an NS Basic/Desktop program and must be installed and registered separately. It is, however, included in the NS Basic/Desktop installer so if you are developing using NS Basic/Desktop, there is nothing you need to do to install on your own machine.

To use the control in an NSBasic program, include the statement

AddObject "PalmDB.Database", "PalmDB"

This must be executed before the control can be used. "PalmDB" is the name that you will use to refer to the control. Any name can be used, but this document assumed "PalmDB". Each instance of the control can only open a single database, so if you need multiple databases opened, such as for copying, you will need additional AddObject statements.

There is a sample called PalmDatabase included with the standard set of samples that come with NS Basic/Desktop. It shares a data file called keydbtest.pdb with the sample called KeyedDBDemo, included with NS Basic/Palm.

To use this control, you need to use NS Basic/Desktop 1.2 or later.

Objects

The PalmDB Automation Control exposes the following objects and collection:

Object Description
Database The Database object is the top-level object and can be directly created by a client application. It represents a .pdb file on the desktop. Use PalmDB to refer to this object.
ApplicationInfo Each PalmOSc database can contain user-defined application-specific data. This usually contains the database categories. Use PalmDB.ApplicationInfo to refer to this object.
Records The Records collection allows both random and sequential access to the list of records in the database. Adding and removing records is also supported. Use PalmDB.Record to refer to this collection.
Record This object represents an actual record in the database. It exposes methods and functions to manage the individual fields. Use PalmDB.Records(whichRecord) refer to an individual record. Records are numbered starting at 0 and running through PalmDB.Records.Count - 1.

 

Index and Quick Reference:

Objects and Collections
        ApplicationInfo (object) ApplicationInfo
        Database (object) Database
        Record (object) Record
        Records (collection) Records
Functions
        Count (function) object.Count
        GetDateField (function) object.GetDateField(Offset)
        GetDoubleField (function) object.GetDoubleField(Offset)
        GetNSBDateField (function) object.GetNSBDateField(Offset)
        GetNSBTimeField (function) object.GetNSBTimeField(Offset)
        GetSingleField (function) object.GetSingleField(Offset)
        GetStringField (funtion) object.GetStringField(Offset, length)
        GetValueField (function) object.GetValueField(Offset, Length)
        Item (function) object[.Item](Index)
Methods
        Add (method) object.Add(Index)
        CategoryInitialize (method) object.CategoryInitialize
        Load (method) object.Load(Filename)
        PutDateField (method) object.PutDateField(Value, Offset)
        PutDoubleField (method) object.PutDoubleField(Value, Offset)
        PutNSBDateField (method) object.PutNSBDateField(Value, Offset)
        PutNSBTimeField (method) object.PutNSBTimeField(Value, Offset)
        PutSingleField (method) object.PutSingleField(Value, Offset)
        PutStringField (method) object.PutStringField(Value, [Offset])
        PutValueField (method) object.PutValueField(Value, Offset, Length)
        Remove (method) object.Remove(Index)
        Save (method) object.Save(Filename)
Properties
        ApplicationInfo (property) object.ApplicationInfo
        Attributes (property) object.Attributes [= Value]
        Category (property - Record object) object.Category [= Value]
        Category (property - ApplicationInfo object) object.Category(Index) [= Value]
        CreationDate (property) object.CreationDate
        Creator (property) object.Creator [= Value]
        ModificationDate object.ModificationDate
        Name (property) object.Name [= Value]
        Records (property) object.Records
        Size (property) object.Size [= Value]
        Type (property) object.Type [= Value]

Objects and Collections

 

ApplicationInfo (object)


The ApplicationInfo object can contain user-defined application-specific data.
This usually contains the database categories.

Syntax 

ApplicationInfo

Methods

Propertities

Remarks

This implementation doesn't support putting arbitrary data in the application-info area. To this end, methods and functions will be added, much like the implementation of the Record object.

This object can not be created directly. Instead, use the ApplicationInfo property of the Database object.


 

Database (object)


The Database object is the top-level object and can be directly created by a client application. It represent a .pdb file on the desktop.

Syntax 

Database

Methods

Propertities

Remarks

Use the AddObject statement to create a database object as described in Installation and Use

 
 

Record (object)


The Record object represents an actual record in the database. It exposes methods and functions to manage the individual fields.

Syntax 

Record

Methods

Propertities

Remarks

This object can not be created directly. Instead, use the methods and functions of the Records collection. 

 
 

Records (collection)


The Records collection allows for both random and sequential access to the list of records in the database. Adding and removing records is also supported.

Syntax 

Records

Methods

Remarks

The Records object support Visual Basic's For Each construct to allow for enumerating the records in the Database.

This object can not be created directly. Instead, use the Records property of the Database object. 

 
 

Functions

Count (function)

Applies To

Syntax 

object.Count
Item Meaning
object An expression that evaluates to an object in the Applies To list.

Remarks

 

 

GetDateField (function)

Applies To

Syntax 

object.GetDateField(Offset)
The GetDateField function syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Offset Offset in bytes from the beginning of the record data where the date starts, starting from 0.

Remarks

A Date value stored in a field is a 4-byte long value.

 

 

GetDoubleField (function)

Applies To

Syntax 

object.GetDoubleField(Offset)
The GetDoubleField function syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Offset Offset in bytes from the beginning of the record data where the double-precision floating point number starts, starting from 0.

Remarks

GetDoubleField can be used to retrieve NSBasic Palm dates and times. See the NSBasic Palm documentation for more information.

 

 

GetNSBDateField (function)

Applies To

Syntax 

object.GetNSBDateField(Offset)
The GetNSBDateField function syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Offset Offset in bytes from the beginning of the record data where the date starts, starting from 0.

Remarks

The NSBasic Desktop Date type includes both the date and time, while the NSBasic Palm Date type only includes the date. This method will return a value that represents midnight on the given date. It can be combined with the GetNSBTimeField method to combine an NSBasic Palm date and time into a single value.

 

 

GetNSBTimeField (function)

Applies To

Syntax 

object.GetNSBTimeField(Offset, BaseDate)
The GetNSBTimeField function syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Offset Offset in bytes from the beginning of the record data where the time starts, starting from 0.
BaseDate The date which is combined with the time to form an NSBasic Desktop date.

Remarks

The NSBasic Desktop Date type includes both the date and time, while the NSBasic Palm Time type only includes the time. You must include a date in which the time is given.

The following code fragment shows how to reas an NSBasic Desktop date from a database containing a date and a time:

   With PalmDB.Records(0)
        Dim dt
        Dim d
        d = .GetNSBDateField(0)
        dt = .GetNSBTimeField(8, d)
        MsgBox t
    End With

If you are only interested in comparing times within a single arbitrary day, you can use the Now function to get today's date and use that as the base date.

 

 

GetSingleField (function)

Applies To

Syntax 

object.GetSingleField(Offset)
The GetSingleField function syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Offset Offset in bytes from the beginning of the record data where the single-precision floating point number starts, starting from 0.

Remarks

 

 

GetStringField (function)

Applies To

Syntax 

object.GetStringField(offset[, length])
The GetStringField function syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Offset Offset in bytes from the beginning of the record data where the string starts, starting from 0.
Length Number of bytes to read

Remarks

This function will retrieve all of the characters from the offset to the end of the record. This is useful for storing binary data in a database. When the string contains text, use code similar to the following to trim the string:

s = PalmDB.Record(0).GetStringField(37)
s = Left(s, Instr(s, chr(0)) - 1)

 

 

GetValueField (function)

Applies To

Syntax 

object.GetValueField(Offset, Length)
The GetValueField function syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Offset Offset in bytes from the beginning of the record data where the value starts, starting from 0.
Length Length in bytes of the value to retrieve. Can either be 1 (Byte), 2 (Integer) or 4 (Long).

Remarks

 

 

Item (function)

Applies To

Syntax 

object[.Item](Index)
The Item function syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Index A valid index of the record to be retrieved, in the range [0 - Count].

Remarks

 

 

 

Methods

 

Add (method)

Applies To

Syntax 

object.Add(Index)
The Add method syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Index A numeric value specifying the index of the new record in the database, starting from 0. The record will be inserted at position Index or appended to the list of records if Index is equal the records Count.

Remarks

 

 

 

CategoryInitialize (method)

Applies To

Syntax 

object.CategoryInitialize
The CategoryInitialize method syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.

Remarks

The CategoryInitialize method creates initial categories of Unfiled, Business and Personal.

 

Load (method)

Applies To

Syntax 

object.Load(Filename)
The Load method syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Filename The full path name of the .pdb file in which the Database is stored. If FileName does not exist, an error will be raised. Use On Error Resume Next to trap this. If you want to create a new database, do not do a Load.

Remarks

 

 

PutDateField (method)

Applies To

Syntax 

object.PutDateField(Value, Offset)
The PutDateField method syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Value A Date value to be stored in the field.
Offset Offset in bytes from the beginning of the record data where the value starts, starting from 0.

Remarks

This method works on Palm dates, which are 4-byte integers representing a total number of seconds since a specified date. These are different from from NSBasic dates.

 

 

PutDoubleField (method)

Applies To

Syntax 

object.PutDoubleField(Value, Offset)
The PutDoubleField method syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Value A double-precision floating point value to be stored in the field.
Offset Offset in bytes from the beginning of the record data where the value starts, starting from 0.

Remarks

This method can be used to store NSBasic Palm dates and times. See the NSBasic Palm documentation for more information.

 

 

PutNSBDateField (method)

Applies To

Syntax 

object.PutNSBDateField(Value, Offset)
The PutNSBDateField method syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Value A date value to be stored in the field.
Offset Offset in bytes from the beginning of the record data where the value starts, starting from 0.

Remarks

The Date data type in NSBasic Desktop stores both the date and time of day, while the Date data type in NSBasic Palm only stores the date. This method extracts the date portion of its argument and stores it as an NSBasic Palm date.

 

 

PutNSBDateField (method)

Applies To

Syntax 

object.PutNSBDateField(Value, Offset)
The PutNSBDateField method syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Value A time value to be stored in the field.
Offset Offset in bytes from the beginning of the record data where the value starts, starting from 0.

Remarks

The Date data type in NSBasic Desktop stores both the date and time of day, while the Time data type in NSBasic Palm only stores the time. This method extracts the time portion of its argument and stores it as an NSBasic Palm time. This time is truncated to the second.

 

 

PutSingleField (method)

Applies To

Syntax 

object.PutSingleField(Value, Offset)
The PutSingleField method syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Value A single-precision floating point value to be stored in the field.
Offset Offset in bytes from the beginning of the record data where the value starts, starting from 0.

Remarks

 

 

PutStringField (method)

Applies To

Syntax 

object.PutStringField(Value, [Offset])
The PutStringField method syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Value A valid String to be stored in the field.
Offset Offset in bytes from the beginning of the record data where the value starts, starting from 0.

Remarks

 

 

PutValueField (method)

Applies To

Syntax 

object.PutValueField(Value, Offset, Length)
The PutValueField method syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Value A numeric value to be stored in the field. Make sure to use an appropriate datatype to hold the value, depending upon the Length.
Offset Offset in bytes from the beginning of the record data where the value starts, starting from 0.
Length Length in bytes of the value to retrieve. Can either be 1 (Byte), 2 (Integer) or 4 (Long).

Remarks

 

 

Remove (method)

Applies To

Syntax 

object.Remove(Index)
The Remove method syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Index A valid index of the record to remove from the database, in the range 0 to Count - 1.

Remarks

 

 

Save (method)

Applies To

Syntax 

object.Save(Filename)
The Save method syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Filename The full path name of the .pdb file in which to store the Database. If the file does not exist, it will be created.

Remarks

 

 

 

 

 

Properties

 

ApplicationInfo (property)

Applies To

Syntax 

object.ApplicationInfo
The ApplicationInfo property syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.

Remarks

 

 

 

Attributes (property)

Applies To: Database, Record

Syntax 

object.Attributes [= Value]
The Attributes property syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Value A combination of one or more numeric values that represents the attributes to set. It is recommended to use the symbolic Database Attributes constants or Record Attributes constants to specify the mask.

Database Attributes

Name Meaning
dmHdrAttrResDB  
dmHdrAttrReadOnly  
dmHdrAttrAppInfoDirty  
dmHdrAttrBackup  
dmHdrAttrOKToInstallNewer  
dmHdrAttrResetAfterInstall  
dmHdrAttrCopyPrevention  
dmHdrAttrStream  
dmHdrAttrHidden  
dmHdrAttrLaunchableData  
dmHdrAttrOpen  
dmAllHdrAttrs  

Record Attributes

Name Meaning
dmRecAttrSecret  
dmRecAttrBusy  
dmRecAttrDirty  
dmRecAttrDelete  
dmAllRecAttrs  

Category Attributes

Name Meaning
dmRecAttrCategoryMask  

Remarks

 

 

 

Category (property - Record object)

Applies To

Syntax 

object.Category [= Value]
The Category property syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Value A numeric value in the range [0 - 15].

Remarks

 

 

Category (property - ApplicationInfo object)

Applies To

Syntax 

object.Category(Index) [= Value]
The Category property syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Index A numeric value in the range [0 - 15].
Value A valid String to be stored in the specified category slot.

Remarks

 

 

CreationDate (property)

Applies To

Syntax 

object.CreationDate
The CreationDate property syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.

Remarks

 

 

Creator (property)

Applies To

Syntax 

object.Creator [= Value]
The Creator property syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Value A valid 4-letter String to hold the Database creator. A valid creator consist of a String containing upper-case only or mixed upper and lower case letters. Lower-case only creators are reserved for use by Palm.

Remarks

 

 

ModificationDate (property)

Applies To

Syntax 

object.ModificationDate
The ModificationDate property syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.

Remarks

 

 

Name (property)

Applies To

Syntax 

object.Name [= Value]
The Name property syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Value A valid String to hold the name of the database.

Remarks

 

 

Records (property)

Applies To

Syntax 

object.Records
The Records property syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.

Remarks

 

 

Size (property)

Applies To: ApplicationInfo, Record

Syntax 

object.Size [= Value]
The Size property syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Value The new size of the object. The original contents of the object will be kept if at all possible. If the new size if greater than the current size, the object will be truncated.

Remarks

 

 

Type (property)

Applies To

Syntax 

object.Type [= Value]
The Type property syntax contains the following items:
Item Meaning
object An expression that evaluates to an object in the Applies To list.
Value A valid 4-letter String to hold the Database type. A valid type consist of a String containing lower-case only or mixed upper and lower case letters. Upper-case only types are reserved for use by Palm.

Remarks

This implementation assumes Data databases. Therefore, it is recommended to always create new databases using a Type of "data".