Contents

    Overview
    Reading and Writing to non-Keyed Databases
    Reading and Writing to Keyed Databases
    A few words about the "database" variable
    PDB and PRC files Working Together
    Access to Large Databases
    Database Programming Frequently Asked Questions
    Database Programming Techniques
    Resources and Suggested Reading
    Database Conversion Tools (Conduits)

Overview of Palm Databases in NSBasic/Palm

All databases on a Palm device, including the ones you create using NS Basic/Palm, have the same format. Databases you create using NS Basic/Palm have the backup bit set by default, so they are copied to your "x:\palm\{username}\backup" on a Hotsync ('x' is the directory where you have your Hotsync software installed). Once there, it is possible to manipulate them. Palm Programs are saved in a special format of .pdb file, called .prc files.

NS Basic/Palm only runs on the Palm device itself. Palm devices only know about one kind of database: .pdb. NS Basic/Palm can access all .pdb files. Databases created by NS Basic/Palm are automatically copied to the desktop on a Hotsync. There is a wide variety of utilities that are available to convert between PDB, Access, CSV, Excel, and other formats. We have listed the ones we are aware of later in this document. Be sure to check out the NSBasic/Palm Web Board and ask around. You'll find lots of useful information on this topic there.

The Palm OS doesn't much care what you put in a database record. As far as the OS is concerned, a database record is just a series of bytes. Your app has to interpret those bytes in whatever way you want it to.

You can download the samples for this technote here.

Reading and Writing to non-Keyed Databases

This method of database access is probably the most simple in concept, but can often cause the most confusion. Think of a database in NSBasic/Palm as a sort of 'grid'. Each 'cell' in the holds one byte of data. The description of the DIM statement in the NSBasic/Palm handbook has a list of the NSBasic/Palm data types and their storage requirements. Strings are an exception. They require one byte per character plus an additional byte for their terminator. So writing to a database the word 'Cat' from the begining of a record in the database would look something like this (the '\0' symbol denotes the end of the string):

Writing to a non-keyed database is simple. You just need to be mindful of where you plcace your data. Dates and Numbers, for example, are easy enough, as they'll translate to 8-byte values (for dates and times) and 4-byte values (for Integers and single-precision floating point values). When you put Strings in the mix you need to force them to the length that you want your data to be in, otherwise you'll fire off DmWriteCheck exceptions.  So if your field that contains the word Cat could be up to 15 characters in length what you store would look something like this in the Palm memory:

To write to a non-keyed database you need to use two different functions: dbPosition() and dbPut(). The dbPosition function will set your record pointer to the specific record number and 'offset' (or 'cell' as discussed above). When data is written using dbPut() the pointer into the record will be set to the end of the last piece of data written. In this example a name exists in a field object called fldName. This code will write the contents of fldName to the beginning of the first record of the database.

'assume name is DIMed as String and db as Database
name = rightpad(fldName.text,15) 'set the string to be 15 characters long.

res = dbposition(db, 1, 0)
res = dbput (db,name)
if res not = 0 then
    msgbox "Write error: "+str (res)
end if

To write more data to the same record is simple. Just use more dbPut statements.

'assume that dDate is DIMed as a Date type
res = dbput (db,dDate)
if res not = 0 then
    msgbox "Write error: "+str (res)
end if

'assume fmyFloat is DIMed as a Float
res = dbput (db,fmyFloat)
if res not = 0 then
    msgbox "Write error: "+str (res)
end if

To read these back is just about as simple. Just use dbPosition() along with dbGet(). Each call to dbGet will move the pointer in the record, so there's no need to use repeated calls to dbPosition (although you can if you only want particular fields from a record in the database).

res = dbposition(db, 1, 0)
res = dbget (db,name)
if res not = 0 then
    msgbox "Read error: "+str (res)
end if
fldName.text = rtrim(name) ' removes the spaces padded on by the rightpad() function.

dDate=-1
res = dbget (db,dDate)
if res not = 0 then
    msgbox "Read error: "+str (res)
end if 

fldDate.text = datemmddyy(dDate) 'convert the date into a displayable format

res = dbget (db,fmyFloat)
if res not = 0 then
    msgbox "Read error: "+str (res)
end if
fldCost.text = str(fmyFloat)

Most desktop utilities that produce .pdb files create them without keys. Be sure to use the "Operations by Position" functions to access them, not "Position by Key." See Section 10.D in the NSBasic/Palm Handbook.

Reading and Writing to Keyed Databases

Writing to a keyed database is a matter of having a unique ID for each record (called a 'key'). The NSBasic database header maintains the key information along with the offsets into the database where the information is stored. The two biggest advantages to this are ease of access and ease of storage. To find a record in a keyed database you need only search by the key value (in a non-keyed database you need to search one record at a time). With keyed databases you can also store your information using a User Defined Type or UDT (created with the NSBasic/Palm TYPE statement). This way instead of writing say, 50 elements, into a database one element at time (as with dbPut) you have a single dbInsert() statement with 3 arguments:

dbInsert (database,keyvalue,UDT)

Here's an example. Like the non-keyed database example above, this code will write one string, one date and one floating point number into a Palm PDB file. Assume the following UDT:

type myData
    name as string
    dDate as Date
    fmyFloat as float
End type

Next, assume the following variables are defined in your Project Startup code:

Global db as Database
Global msgdata as string
Global recordData as myData

Writing to the database with the dbInsert command is as simple as this (this assumes the database has been created and is currently open):

recordData.name="John Doe"
recordData.dDate = mmddyytodate("09/29/99")
recordData.fmyFloat = val("123.45")

Dim res as Integer
Dim keyval as Integer

keyval=1
res=dbinsert (db,keyval,recordData)

This writes a new record to the database with a key of 1 and now holds the data shown above. Reading the data back out is almost exactly the same, just in reverse:

keyval=1 ' must be set to the key of the record you want to retrieve

res=dbread(db,keyval,recordData)
if (res = 0) then
    msgbox recorddata.name
    msgbox datemmddyy(recorddata.dDate)
    msgbox str(recorddata.fmyfloat)
else
    alert ("Oops!","An error occured : "+str(res),3,"OK")
end if

Please note that if you get a return value of 2 in 'res', this means that the record you tried to read wasn't found and the next closest record to it was returned instead.

For more information on using keyed databases read section 10.D.b - "Operations by Record Key" in the NSBasic/Palm Handbook. There is also an example of creating a sequential key value that is described in Section 10.D.c - "Using a sequentially numbered key" in the NSBasic/Palm Handbook.

A few words about the "database" variable

When a database is created or opened by a Palm OS application, it is assigned a unique number ("handle") by the operating system to refer to that database, and this unique handle is assigned to the NSBasic database variable:

Dim db1 as database ' our variable to hold the Palm OS database handle
Dim res as integer ' our result variable
res=dbCreate(db1,"MyDB",0,"Test")
res=dbOpen(db1,"MyDB",0)

Every subsequent database operation refers to the database by this handle rather than by the database name:

'examples...
res=dbFind(db1,"Doe, John")
res=dbInsert(db1,dbKey,dbData)

res=dbOpen(db1,"MyDB",0)
res=dbOpen(db1,"NextDB",0) ' db1 no longer refers to "MyDB"
res=dbOpen(db1,"NextDB",0) ' db1 now refers to the "NextDB", but this time opened in read-only mode

The appropriate way to refer to multiple databases is to use a separate "database" variables for the different handles:

Dim dbInventory as database
Dim dbPrices as database
res=dbOpen(dbInventory,"MyDB",0)
res=dbOpen(dbPrices,"NextDB,0)
'now both databases are opened, and each handle is saved in a separate "database" variable
'...database operations and other code...
res=dbClose(dbPrices)
res=dbClose(dbInventory)
'now both databases are closed, and can be safely opened again later if needed

Some programmers open all databases at the beginning of an application, then close them all at the end, while some programmers open databases only when they're needed, closing them immediately as soon as possible after any data manipulation. Which method you use is up to you, but be sure to evaluate the return code of every database operation, to catch any errors that may creep in.

PDB and PRC files Working Together

Databases (PDB) are not stored "within" the application (PRC). They are still separate. There are three attributes to a database: the Creator ID, the type, and the name. The Creator ID and type are both four-byte values, such as 'appl', 'REAd', ''Font', 'Data', 'zrCT' ... basically anything. Note that in code these characters are surrounded by single quotes as if they were char constants and not the double quotes typically used for strings. An ID or type could also be represented as a long integer (generally in hex) for instance 'appl' could also be represented as 0x6170706C, although this is less common.

There can only be one db with the type of 'appl' with any given ID on the device at any time. This typically would be the PRC file that represents the executable code of your program. This uniqueness attribute does not apply to other types. For instance, if you use a Palm "Doc" reader and have several Doc files on your Palm there will be many databases with a creator/type pair of 'REAd'/'TEXt' on your device. The only truly unique aspect for any database is the name -- which must be unique with respect to all other databases on the device regardless of their type/creater. For instance if you have a Doc w/ the name "videos", you can not also have a NSBasic database w/ the same name.

The Palm launcher treats multiple databases sharing the same ID in a special manner. When you delete the database with type 'appl' of any given ID ('fBAR') then all other databases with the same ID will also be removed from the device. This prevents unusable data from cluttering up your valuable memory when you ditch an old app.

Since the palm launcher groups databases together by ID, you can make use of a special tool (such as ZarfCatalog, which you can find on PalmGear) to assure yourself that they are indeed still separate databases. Each will appear in the backup directory individually ... assuming of course that their backup bit has been set. No "unknown operation" to extract them would be required.

The only reason to -not- use the same ID for the app and its data is if for some reason you want the data to remain on the PDA when the application that uses it is deleted.

Access to Large Databases

It used to be the case that it was impossible to have databases larger than about 8000 records. This is because of a limitation of an area of memory known as the Heap. The Heap holds information such as the database header. This header is basically a big array that holds the handles of each record. In Palm OS versions up to 3.3 the Heap is 64k. The rule of thumb is that each record takes up 8 bytes of the heap. This means that databases can't be larger than 8000 records. Anything more would be impossible.

That changed in OS 3.5.  Here's a document from the PalmOS website on an overview of PalmOS 3.5 and what's new:

http://www.palmos.com/dev/tech/docs/palmos35/

Here's the part that's really interesting as far as this discussion is concerned:

http://www.palmos.com/dev/tech/docs/palmos35/#The Dynamic Heap

Specifically what it says is:

The Dynamic heap is now sized based on the amount of memory available to the system. The size which will be used is as follows:

Device RAM size	Heap size
< 2 mb of ram	64 k
>= 2 mb	128 k
>= 4 mb	256 k

What this translates to is that you can now have a PalmOS API supported database of as many as 24000 records.  Access to databases of this size will be slow, but it can be done.

It bears to keep in mind that many of the database products currently for the palm have been implementing their *own* memory management functions to allow for large data structures, etc. This means that while it might say it's a PDB, it's really a custom structure internally. This could then allow for say - a 24000 record database to take only 10k-40k of heap instead of 230k.

Also remember that databases of this size are going have a profound impact on how long a Hotsync will take to run.

Database Programming Frequently Asked Questions

Q: What's the difference between using dbInsert (for keyed databases) and dbPut (for non-keyed databases)?

A: Michael Verive put the description best:

The dbInsert function will write a record to a keyed database, and requires the use of a unique key. The record information is then stored along with the key. When read, the records are read in key sequence alphabetically, saving the hassle of sorting the records. The dbPut command will write the record in a specific location, designated by dbPosition. No unique key is needed, since the records are read either sequentially or by positioning the file pointer to the specific record number and offset.

Each form of database access has its own advantages and disadvantages. Keyed databases require the use of a unique key for each record, and only use a single key. They are very useful when information needs to be searched for by a unique key, and when having the data "sorted" is needed. Non-keyed databases neither use nor require a unique key, but are either accessed by direct record/offset access, or sequentially. Since there is no key, searching for a specific record may be more time consuming, although the data can be sorted, and a fast binary search algorithm used. When all the records are the same length, direct access by record number is very fast with non-keyed databases. If you need the fastest access, and space isn't a limitation (storing strings of different lengths in non-keyed databases by padding with spaces is very inefficient), the non-keyed database may be your answer. You may need to develop your own sorting and binary search routines, however. I have a demo of sort routines for arrays, but the same concept can easily be applied to non-keyed databases.

Q:What format do you store Palm dates in?

A: In NS Basic/Palm, dates are stored internally as (year-1900)*10000+month*100+day.

Q: What format do you store Palm timestamps in?

A: It's h*10000+m*100+s.

Q: Why would someone get a 'Bus Error' running an NSBasic/Palm program?

A: A "Bus Error" in POSE may be caused by attempting to dbread using a key field of a type other than the one used to dbinsert the record.

Q: How do I access NSBasic Databases with Borland Delphi?

A: If you're looking to do it via a conduit, you can use Tabdee Ltd's TurboSync components. They have been updating their product to use the date and time formats that NSBasic dates are stored in. You can find out more at http://www.tabdee.ltd.uk .

Q: How many records can I store in my Database?

A: See the section of the Database Technote titled "Access to Large Databases"

Q: Using a keyed database, can we know the position when we use dbreadnext or dbreadprev?

A: (Thanks to Mike Verive [mverive© peoplepc.com])

It all depends on how you get to your record. If you are using dbReadNext after a dbReset, you can keep a pointer active that coincides with the record number (ditto with dbReadPrev, since you can increment or decrement the pointer). This technique is used (along with getting the number of records) in the Keyed Phone DB.

However, if you delete or insert records, you will have to reinitialize your pointer.

If you want the power of using keyed databases, and need to know where you are, there are a few other ways. One way is to use an array to keep track of the keys and their position (this can also be done with a second database). Use dbreset to start at the beginning of the database you want to track the keys of, then build the array of keys (or database of keys) one at a time. The array index (or new database position) can then be used to determine the record number. Again, the delete/insert problem ith pointers remains, but that can be solved in code as well.

Q: How can I delete records from a non-keyed database?

A: There is no NSBasic/Palm call to manage record deletion in non-keyed databases. Your best approach would be to have a delete 'flag' field in your record of your database. Then write a 'compress database' database management routine that would re-write your database file, minus the records that have a the delete flag set on them.

Q: How can I delete records from a keyed database?

A: Use the dbDelete() function call. See the dbOpen() function call for a list of possible error codes.

Q: When I call dbDelete() to delete a record and then call dbGetNoRecs(), NSBasic still says I have the same number of records as before. Why?

A: The call to dbDelete() only flags the record as being 'ready to delete'. When the database is closed with dbClose() these records are automatically compressed out.

Q: How do memory cards work? (Springboard, Compact Flash, SD, Memory Stick)

A: Depending on the device, you can sometimes use these from NS Basic. By compiling your program with the Fat App switch on, you can avoid issues that arise from your program being on a different storage location than the Runtime. It's important to understand that current memory cards use the Virtual File System (VFS), which does not allow the use (directly) of the db statements. You can use them using NSBasic's VFS shared library, described in Tech Note 23.

Here are some excellent articles by Larry Garfield of InfoSync that explain what VFS cards can and cannot be used for:

• Part 1: http://www.infosync.no/show.php?id=1768
• Part 2: http://www.infosync.no/show.php?id=1772
• Part 3: http://www.infosync.no/show.php?id=1787

Database Programming Techniques

How to write to the Memo Pad Safely

Jeff Debrosse has contributed some code that shows how to "safely" write to the memopad in a keyed manner as well as non-keyed. It have added some date/time formatting code as well.

Check it out here: http://www.nsbasic.com/pub/palm/samples/Take-A-Memo.zip

Resources and Suggested Reading

Here are some documents that explain the format of .pdb and .prc files:

• Unofficial Documentation: http://www.mactech.com/articles/mactech/Vol.21/21.08/PDBFile/index.html
• Format of .prc files: http://web.mit.edu/tytso/www/pilot/prc-format.html
• Mizuno-ami's great explanation (for VB users): TechNote 02a

Database Conversion Tools (Conduits)

More?

If anyone hears of more useful resources on this topic or has useful experiences, please let us know at support© nsbasic.com so we can improve this document. Code samples will be greatly appreciated by all!

Note that Palm reserves the right to change the format.