EPOC32, DBMS SQL hints and tips

From this page I just wanted to share some tips I've picked up when programming with the new EPOC32 DBMS and SQL methods. If you don't know what either are it's best you hit the back button now !

When porting my Reviser app from the Series 3x to the Series 5, it became apparent that the DBMS/SQL documentation provided by Psion PLC was ... lacking somewhat. While the documentation of the SQL interface was syntactically correct, it didn't provide adequate examples, nor did the documentation for the index creation methods in dbase.oxh.

A lot of trial and error was called for, along with a smattering of help from a few other programmers on comp.sys.psion.programmer.

The first thing to remember ?
always : 

INCLUDE "Dbase.oxh"

Using Bookmarks

Bookmarks are straightforward enough, but be warned they do consume memory ! If you use bookmarks be sure to relinquish them after use with KILLMARK.
The documentation from Psion doesn't mention the maximum number of bookmarks you can have at any one time - I have created over 500 at a time, with no problems. The documentation doesn't say what BOOKMARK returns either - they appear to be non zero positive integers

Multiple Tables in one database

Each database file can have one or more tables within it. Choose the table names wisely and remember the names chosen. ie to create two tables, Salary and Address in the data file "TestData" : 
	CREATE "TestData FIELDS emp_id,salary,bonus TO salaries",S,emp$,dosh,bonus
	CLOSE
	CREATE "TestData FIELDS emp_id,address1,address2,postcode TO addresses",A,emp$,add1$,add2$,pc$
	CLOSE
Note : the mapping of field names to variable names can differ - the field names (emp_id, salary etc) are used in SQL constructs, while the variable names (emp$, dosh etc) are used when assigning to and from the variables ie S.dosh=100000.0

There is nothing to stop you now opening two tables simultaneously and adding data etc.

Adding data to a table

Simple : 
	OPEN "TestData SELECT * FROM salaries",S,emp_id$,dosh,bonus
	USE S
	INSERT
	S.emp_id="A667663-HJ"
	S.dosh=30000.0
	S.bonus=4000.0
	PUT
	CLOSE
Note the "USE" statement. When dealing with multiple views, open at the same time, be liberal with "USE", as it may avoid confusion and errors, as to which view/table you are using.

Opening a simple view on a table

If you don't need to read all fields from a table then simplify the view you are using : 
	OPEN "TestData SELECT emp_id,bonus FROM salaries",S,emp_id$,bonus
	USE S
	PRINT COUNT
	WHILE (NOT EOF)
		PRINT S.emp_id$,S.bonus
		NEXT
	ENDWH
	CLOSE

Creating Indexes

To speed searches through a database, when selecting data with a "SELECT, WHERE" clause, you can create indexes. Apart from needing an example of this, the thing that caught me out, was that when you create a table having string fields, the length of these fields is set to be 255. When you come to create an index using a 255 character string field as a key, the index creation returns the error "Disk Full" !!

The trick is to limit the size of a string field you wish to use as a key, at table creation time ie : 

	CREATE "TestData FIELDS emp_id(20) etc etc",A,emp$,etc etc
Then to create the index : 
	LOCAL key1&

	key1&=DbNewKey&:
	DBSetComparison:(key1&,kDbCompareNormal&)
	DbAddField:(key1&,"emp_id",kDbAscending&)
	DbCreateIndex:("Ind1",key1&,"TestData","salaries")

Searching a table

To search a table, you need to open the table using a "SELECT, WHERE" clause. ie to search the salaries table for all employees with salary greater than 50,000 and a bonus of less than 5,000, (and only return the emp_id) : 
	LOCAL salaryVal,bonuVal
	LOCAL buff$(255)

	salaryVal=50000.0
	bonusVal=5000.0

	buff$="salary > "+GEN$(salaryVal,8)
	buff$=buff$+" AND bonus < "+GEN$(bonusVal,8)

	OPEN "TestData SELECT emp_id FROM salaries WHERE "+buff$,S,emp_id$
	USE S
	PRINT COUNT
	WHILE (NOT EOF)
		PRINT S.emp_id$
		NEXT
	ENDWH
	CLOSE
Note the way in which the SQL can be dynamically constructed using strings (buff$). Also note that after a "SELECT,WHERE" clause has opened a view on a table, COUNT returns the number of matching records, and not the total number of records in that table.

Using LIKE

Another thing that caught me out was the LIKE clause. LIKE allows you to search a table for records where a string field is 'like' the string value you wish to search for. The trick is to properly delimit the string pattern you wish to match : 
	OPEN "TestData SELECT emp_id, salary WHERE emp_id LIKE 'A*'",S,emp_id$,dosh
or : 
	buff$="A*"
	OPEN "TestData SELECT emp_id, salary WHERE emp_id LIKE '"+buff$+"'",S,emp_id$,dosh
Simple yes, but I went round and round in circles ...

Compaction and growing file sizes

One thing that alarmed me when creating a data file with DBMS/SQL was the apparent file size of my data file. I read in a 30k Data file created by the Data app, and rewrote the data to my hand crafted DBMS file and it grew to 300k !!

The answer was to switch on automatic compaction using 

	SETFLAGS KAutoCompact&
Now when the same file is created, it's size still grows to 300k as records are added, but when it is closed it is compacted down to the expected 30k. If anyone knows how to keep the size down at all times let me know !
If you find that anything I have documented here is incorrect, or have any comments or additions, feel free to mail me.
©1998 Phil Whiles
Go Home ! 		Last revised: 26th May '98