CONTENTS 1. INTRODUCTION 1.1 The Database Server Objects 1.1.1 Database 1.1.2 Table 1.1.3 Index 1.1.4 View 2. THE DATABASE SERVER C API 2.1 The Server 2.1.1 Connection to the server - DbsConnect 2.1.2 Disconnection from the server - DbsDisconnect 2.1.3 Error number conversion - DbsErrorString 2.1.4 Obtaining the database server version - DbsVersion 2.1.5 Changing the default path for temporary files - DbsSetTempDir 2.2 Database 2.2.1 Opening a database - DbsDatabaseOpen 2.2.2 Closing a database - DbsDatabaseClose 2.2.3 Obtaining the number of tables - DbsDatabaseGetTableCount 2.2.4 Obtaining the name of a table - DbsDatabaseGetTableName 2.3 Table 2.3.1 Creating a table - DbsTableCreate 2.3.2 Opening a table - DbsTableOpen 2.3.3 Deleting a table - DbsTableDelete 2.3.4 Closing a table - DbsTableClose 2.3.5 Compressing a table - DbsTableCompress 2.3.6 Obtaining a table's field count - DbsTableGetFieldCount 2.3.7 Obtaining the definition of a field - DbsTableGetFieldDef 2.3.8 Obtaining a table's index count - DbsTableGetIndexCount 2.3.9 Obtaining the name of an index - DbsTableGetIndexName 2.4 Index 2.4.1 Creating an index - DbsIndexCreate 2.4.2 Opening an index - DbsIndexOpen 2.4.3 Closing an index - DbsIndexClose 2.4.4 Deleting an index - DbsIndexDelete 2.4.5 Rebuilding an index - DbsIndexRebuild 2.4.6 Obtaining an index's definition - DbsIndexGetDef 2.5 TblView 2.5.1 Creating a TblView - DbsTblViewCreate 2.5.2 Setting a TblView's current index - DbsTblViewSetIndex 2.5.3 Seeking for a record on an index - DbsTblViewSeek 2.5.4 Adding a record to a sparse index - DbsTblViewAddIndex 2.5.4 Removing a record from a sparse index - DbsTblViewRemoveIndex 2.6 DynaView 2.6.1 Creating a DynaView - DbsDynaViewCreate 2.7 View 2.7.1 Closing a view - DbsViewClose 2.7.2 Cloning a view - DbsViewClone 2.7.3 Obtaining a record count - DbsViewGetRecordCount 2.7.4 Finding a record - DbsViewFindX 2.7.5 Moving around a view - DbsViewMoveX 2.7.6 Creating a bookmark - DbsViewGetBookmark 2.7.7 Getting the bookmark of the last modified record - DbsViewGetLastModified 2.7.8 Moving to a bookmark - DbsViewMoveBookmark 2.7.9 Obtaining the size of a field - DbsViewGetFieldSize 2.7.10 Obtaining the value of a field - DbsViewGetField 2.7.11 Setting the value of a field - DbsViewSetField 2.7.12 Obtaining the value of a long binary field - DbsViewLBinGetChunk 2.7.13 Appending to a long binary field - DbsViewLBinAppendChunk 2.7.14 Clearing a long binary field - DbsViewLBinClear 2.7.15 Appending a record - DbsViewAppendRecord 2.7.16 Editing a record - DbsViewEditRecord 2.7.17 Updating a record - DbsViewUpdateRecord 2.7.18 Cancelling an append or edit - DbsViewCancelUpdate 2.7.19 Deleting a record - DbsViewDeleteRecord 3. SQL SUPPORT 3.1 Find and DynaView Filters 3.2 DynaView and Index Sort Order 4. DATABASE SERVER ERROR NUMBERS 5. DATABASE FORMATS 5.1 Psion 5.2 dBase III 5.3 dBase IV 5.4 Index tracking ---------------------------------------------------------------------- 1. Introduction The database server, SYS$DATA, is designed to allow easy manipulation of database files from both C and OPL applications via a set of API function calls. Currently three database formats are supported: * Psion: DBF table files with binary-tree PDX index files. * dBase III: DBF table files with binary-tree NDX index files. * dBase IV: DBF table files with binary-tree MDX and NDX index files. The server allows several clients to access the same database files simultaneously without having to know anything about each other. 1.1 The Database Server Objects The server provides 4 different `object' types: Database, Table, View and Index. A simple diagram showing their relationship to each other is shown below ============ = Database = ============ | ========= = Table = ========= / \ / \ / \ ========= ======== = Index = = View = ========= ======== Fig.1. Database server object diagram Before any of the objects can be used, the application must first connect to the database server by calling the DbsConnect function. This will start the database server process if it is not already running, and initialise the application as a client. 1.1.1 Database A database must be opened or created before any tables can be opened. All the supported database formats are directory based and so creating a database will create the corresponding directory on the disk. Similarly opening a database will expect the specified directory to exist (even though it won't necessarily contain any tables). 1.1.2 Table The server table provides a means of creating and opening table files that exist in the database. To actually access any data contained within the table, a view must first be created. 1.1.3 Index An index provides a means of sorting the data which is contained within a table. 1.1.4 View A view provides a means of accessing the data contained within the table. The user can easily move around the table, search for records, and edit the data through a view. Two types of view are provided, a TblView and a DynaView. A TblView supports the use of an associated index which allows the records to be viewed in a specified order, and fast binary searches to be performed using the DbsTblViewSeek function. A DynaView does not support indexes, but does allow a filtered and ordered view of the records in the table to be produced easily. For example, given a table file which contains a user's address book data, a TblView would allow the user to sort the records in alphabetical order and quickly move to a particular person's entry. A DynaView would allow the user to selectively view only those people who lived in, say, London. 2. The Database Server C API The C API exists as a library file, DBS.LIB, which must be linked in at compile time with any application which uses the database server. The .PR file for the project should contain a the line #pragma link(dbs.lib) Any C files which use the server must #include the DBS.H header file. This file sets up all the data types, flags and function prototypes associated with the server. Field types that are supported by the server (though not necessarily by all database formats) are: DBS_TYPE_LOGICAL Passed as an INT. 0 is False, any other value is True. DBS_TYPE_INT8 8-bit signed integer. DBS_TYPE_UINT8 8-bit unsigned integer. DBS_TYPE_INT16 16-bit signed integer. DBS_TYPE_UINT16 16-bit unsigned integer. DBS_TYPE_INT32 32-bit signed integer. DBS_TYPE_UINT32 32-bit unsigned integer. DBS_TYPE_DOUBLE 8-byte IEEE format floating point number. DBS_TYPE_STRING 0-255 bytes in length. Maybe variable length if database supports it. DBS_TYPE_DATE Passed as a DBS_DAYSEC structure. DBS_TYPE_LONGBINARY Large binary object. The DBS_DAYSEC structure is defined: typedef struct DbsDaySec_tag { LONG days; // days since 1/1/1900 (or before if negative) ULONG secs; // seconds since midnight on that day } DBS_DAYSEC; All strings are passed to functions as zero terminated strings, except for string field data which is passed in a buffer with the length of the string as a separate parameter - this allows zeros to be stored in string fields. 2.1 The Server 2.1.1 Connection to the server - DbsConnect INT DbsConnect(INT flags) Must be done before any other server requests are made. flags allows the client process to indicate to the server how it is to be informed of errors. If set to DBS_MODE_LEAVE, then the API will call p_leave on errors, otherwise the API will return errors as a negative error code. 2.1.2 Disconnection from the server - DbsDisconnect INT DbsDisconnect() Disconnects the client from the server and shuts down the server if no other clients are connected to it. All databases opened by the client must be closed before disconnection otherwise an E_DBS_OPEN error will be returned and the client will remain connected. 2.1.3 Error number conversion - DbsErrorString INT DbsErrorString(INT errorno,TEXT *errorstr) Converts the database server error number, errorno, into a zero-terminated error string in the buffer pointed to by errorstr. There must be at least E_MAX_ERROR_TEXT_SIZE (64) bytes at address errorstr. 2.1.4 Obtaining the database server version - DbsVersion INT DbsVersion(INT *version,TEXT *versionstr) Returns the server version number as an integer and a string. There should be at least 8 bytes reserved at versionstr. 2.1.5 Changing the default path for temporary files - DbsSetTempDir INT DbsSetTempDir(TEXT *path) Sets the path that the database server uses to store its temporary files in. By default the root of M: is used. The path must have a trailing backslash (eg. A:\TEMP\). 2.2 Database 2.2.1 Opening a database - DbsDatabaseOpen INT DbsDatabaseOpen(DBS_DBASE *phDBase,TEXT *format,TEXT *database, INT flags) format is a zero terminated string describing the database format. If the format is NULL or empty, Psion DBF format is assumed, otherwise the string must one of `Psion', `dBase III', or `dBase IV', any other string will result in an E_DBS_NOISAM error being generated, indicating that no ISAM library was found for the format string, i.e. the format is not supported. database is the ZTS database name or directory. For directory-based database formats (all those currently supported) a directory name must end with a final `\'. The database handle is returned in *phDBase. flags can specify any of DBS_CREATE, DBS_READONLY, DBS_APPENDONLY, DBS_LOCKREAD, DBS_LOCKWRITE and DBS_EXCLUSIVE. If create is indicated, then a new directory will be created for the database. The append-only flag will restrict the user to being able to use only the following functions on any views they create: DbsViewAppendRecord DbsViewCancelUpdate DbsViewUpdateRecord DbsViewLBinAppendChunk DbsViewLBinAppendChunkByName DbsViewSetField DbsViewSetFieldByName They will also be unable to delete any indexes or tables. The exclusive flag prevents the database being opened by more than one process at any time, the lockread flag forces any other clients to open the database in append-only mode and the lockwrite flag forces any other clients which open the database to open it in read-only mode. 2.2.2 Closing a database - DbsDatabaseClose INT DbsDatabaseClose(DBS_DBASE hDBase) Close the database. Do not use the database handle after this call. There must be no open tables on the database otherwise the call will fail with E_DBS_OPEN. 2.2.3 Obtaining the number of tables - DbsDatabaseGetTableCount INT DbsDatabaseGetTableCount(DBS_DBASE hDBase) Returns the number of tables in the database. 2.2.4 Obtaining the name of a table - DbsDatabaseGetTableName INT DbsDatabaseGetTableName(DBS_DBASE hDBase,INT num,TEXT *name) Return the name of table number, num, as a ZTS in *name. There should be at least DBS_NAMESIZE+1 bytes of memory at name. The table names are only refreshed from the database the first time this function is called or when DbsDatabaseGetTableCount is called. If tables have been deleted or created since the last call to DbsDatabaseGetTableName then it is possible that the server's list of tables will be out of date. The client should call DbsDatabaseGetTableCount first if they need to be certain of obtaining the correct table name. 2.3 Table 2.3.1 Creating a table - DbsTableCreate INT DbsTableCreate(DBS_DBASE hDBase,DBS_TABLE *phTable,TEXT *table, INT flags,DBS_TABLEDEF *pDef) typedef struct DbsTableDef_tag { UWORD count; // number of fields DBS_FIELDDEF *fields[1]; // pointers to field data } DBS_TABLEDEF; typedef struct DbsFieldDef_tag { UBYTE type; // field type UBYTE size; // size of field for fixed length strings only UBYTE attributes; // field attributes TEXT name[DBS_NAMESIZE+1]; // ZTS field name } DBS_FIELDDEF; Create a table in a database with name table, as a ZTS. The table name has a default extension applied if no extension is given. If flags specifies DBS_TEMPORARY then the name is ignored and a unique name is used, the table will be deleted automatically when closed. The field definition pointed to by pDef is used to define the new tables structure. The field definition structure contains a count word giving the number of fields, and an array pointing to a DBS_FIELDDEF structure for each field in the table. name gives the ZTS name of the field up to DBS_NAMESIZE bytes long, though the maximum length supported is database dependent and over-long names are truncated. type gives the field type, size gives the size of the field - this is ignored for all but text fields - and attributes which indicate what the attributes of the field are. This can be either/both of DBS_NOUPDATE or DBS_NOTNULL which indicate whether once set, the value of a field can be altered or not and whether records with a null (zero) value in the field can be added to the table. flags can also specify DBS_EXCLUSIVE, DBS_APPENDONLY, DBS_LOCKREAD or DBS_LOCKWRITE. These have the same effect as for DbsDatabaseOpen except they only apply to the table being opened. The new table's handle is written back to *phTable. 2.3.2 Opening a table - DbsTableOpen INT DbsTableOpen(DBS_DBASE hDBase,DBS_TABLE *phTable,TEXT *table,INT flags) Open a table in a database with name table as a ZTS. The table name has a default extension applied if no extension is given. flags can also specify DBS_EXCLUSIVE, DBS_READONLY, DBS_APPENDONLY, DBS_LOCKREAD or DBS_LOCKWRITE. These have the same effect as for DbsDatabaseOpen except they only apply to the table being opened. The opened table handle is written back to *phTable. 2.3.3 Deleting a table - DbsTableDelete INT DbsTableDelete(DBS_TABLE hTable) Delete a table from a database. This will delete the data and any associated indexes (not only the open ones). This function will fail with E_DBS_LOCKED if any other clients have the table open, to be sure that this is not the case open the table with DBS_EXCLUSIVE set. 2.3.4 Closing a table - DbsTableClose INT DbsTableClose(DBS_TABLE hTable) Close the table. Do not use the table handle after this call. There must be no open indexes, views on the table otherwise the call will fail with E_DBS_OPEN. 2.3.5 Compressing a table - DbsTableCompress INT DbsTableCompress(DBS_DBASE hDBase,TEXT *tableName) Compress the table. The table must not be open by any clients, the table to compress is specified by the handle of the database and the table name rather than the usual table handle. It will only compress Psion tables. This function compresses the specified table using DbfCompress, then rebuilds any indexes that exist on the table. 2.3.6 Obtaining a table's field count - DbsTableGetFieldCount INT DbsTableGetFieldCount(DBS_TABLE hTable) Returns the number of fields in a table. 2.3.7 Obtaining the definition of a field - DbsTableGetFieldDef INT DbsTableGetFieldDef(DBS_TABLE hTable,INT field, DBS_FIELDINFO *pFieldDef) INT DbsTableGetFieldDefByName(DBS_TABLE hTable,TEXT *field, DBS_FIELDINFO *pFieldDef) typedef struct DbsFieldInfo_tag { UWORD field_number; \\ Ordinal field number UBYTE type; \\ Field type UBYTE size; \\ Field size UBYTE attributes; \\ Field attributes (whether UPDATABLE or not) TEXT name[DBS_NAMESIZE + 1]; \\ Field name as ZTS } DBS_FIELDINFO; Get the definition of a field into the DBS_FIELDINFO structure pointed to by pFieldDef. 2.3.8 Obtaining a table's index count - DbsTableGetIndexCount INT DbsTableGetIndexCount(DBS_TABLE hTable) Returns the number of indexes that exist on a table. 2.3.9 Obtaining the name of an index - DbsTableGetIndexName INT DbsTableGetIndexName(DBS_TABLE hTable,INT num,TEXT *name) Returns the name of index number, num, as a ZTS in *name. There should be at least DBS_NAMESIZE+1 bytes of memory at name. The index names are only refreshed from the table the first time this function is called or when DbsTableGetIndexCount is called. If indexes have been deleted or created since the last call to DbsTableGetIndexName then it is possible that the server's list of indexes will be out of date. The client should call DbsTableGetIndexCount first if they need to be certain of obtaining the correct index name. 2.4 Index 2.4.1 Creating an index - DbsIndexCreate INT DbsIndexCreate(DBS_TABLE hTable,DBS_INDEX *phIndex,TEXT *index, INT flags,DBS_KEYDEF *pDef) INT DbsIndexCreate(DBS_TABLE hTable,DBS_INDEX *phIndex,TEXT *index, INT flags,TEXT *pDef) typedef struct DbsKeyDef_tag { WORD count; \\ number of keys in the index DBS_KEYELEM keys[DBS_MAX_KEYS]; \\ key data } DBS_KEYDEF; typedef struct DbsKeyelem_tag { WORD field_number; \\ ordinal field number BYTE dummy; \\ not used by the server - only by ISAM UBYTE flags; \\ sort in ascending or descending order WORD len; \\ number of characters to use in key for } DBS_KEYELEM; \\ variable-length string fields Create and build an index on the table. The ZTS index name has a default extension applied if none is given (See also section 5.3 about dBase IV index naming conventions). If flags specifies DBS_TEMPORARY then the index name is ignored, and the index will be deleted automatically when it is closed. The index key definition in pDef is used to build the index and flags can also specify DBS_UNIQUE. DBS_MANUAL can also be specified, meaning that the index will not automatically be built on creation, and will not automatically have records added to it when they are appended to the table. To add a record to a manual index use the DbsTblViewAddIndex() function. Note that a manual index must be opened with the DBS_MANUAL flag set every time it is opened, not just when it is created. It is also possible to open an index which was created without the DBS_MANUAL flag with it set to add/remove records from it. The index key definition contains a count word giving the number of keys to build the index on, and an array of DBS_KEYDEF key definition structures. Each DBS_KEYDEF structure contains a field_number word to indicate the key field, and a flags word which can be set to either DBS_ASCEND or DBS_DESCEND to indicate the sort order. If not set then the default DBS_ASCEND is used. Instead of specifying the index definition as a DBS_KEYDEF structure, it may be passed as a pointer to a ZTS string which is parsed as an SQL order by clause as in section 3.2 Note that if a string field is included in a textual index definition, then only the first 20 characters of the string field will be used in the key definition (i.e. the len parameter in the DBS_KEYELEM structure is set to 20) - if a larger range of characters is required, then the user should specifiy the definition as a DBS_KEYDEF instead. The handle of the new index is written back to *phIndex. Creating an index will automatically build it. 2.4.2 Opening an index - DbsIndexOpen INT DbsIndexOpen(DBS_TABLE hTable,DBS_INDEX *phIndex,TEXT *index, INT flags) Open an index index on the table. The ZTS index name index is interpreted as for DbsTableCreateIndex. flags may specify DBS_READONLY. The handle is written back to *phIndex. 2.4.3 Closing an index - DbsIndexClose INT DbsIndexClose(DBS_TABLE hTable,DBS_INDEX hIndex) Close the index with handle hIndex. Do not use the index handle after this call. 2.4.4 Deleting an index - DbsIndexDelete INT DbsIndexDelete(DBS_TABLE hTable,DBS_INDEX hIndex) Delete the index with handle hIndex. Do not use the index handle after this call. 2.4.5 Rebuilding an index - DbsIndexRebuild INT DbsIndexRebuild(DBS_TABLE hTable,DBS_INDEX hIndex) Rebuilds the index with handle hIndex from the table. 2.4.6 Obtaining an index's definition - DbsIndexGetDef INT DbsIndexGetDef(DBS_TABLE hTable,DBS_INDEX hIndex,DBS_KEYDEF *pDef, INT *pAttrib) Get the key fields and attributes of the index with handle hIndex. pDef points to a DBS_KEYDEF structure which contains the key field numbers and sorting order, *pAttrib indicates the index attributes, DBS_READONLY, DBS_TEMPORARY and DBS_UNIQUE. 2.5 TblView 2.5.1 Creating a TblView - DbsTblViewCreate INT DbsTblViewCreate(DBS_TABLE hTable,DBS_VIEW *phTblView,INT flags) Create a TblView on the table. flags can specify DBS_READONLY in which case records in the table can only be read and not written/created through this view, or DBS_APPENDONLY in which records can only be appended to the table, no existing records can be accessed. TblViews provide a more direct view of a table's contents than a DynaView. If a record is added to a table, it is also added to all the TblViews on that table. It is also possible to a perform fast binary searches on a TblView using an index, which is not possible using a DynaView. 2.5.2 Setting a TblView's current index - DbsTblViewSetIndex INT DbsTblViewSetIndex(DBS_VIEW hTblView,DBS_INDEX hIndex) Sets the TblView's current index property to the index hIndex, which must previously have been opened using DbsIndexOpen. If hIndex is NULL, then the current index is cleared, and all Move and Find methods will use the underlying table order. 2.5.3 Seeking for a record on an index - DbsTblViewSeek INT DbsTblViewSeek(DBS_VIEW hTblView,INT comparison,DBS_SEEKDEF *pSeek) typedef struct DbsSeekDef_tag { WORD keycount; \\ Number of keys to search on VOID *seekdata[1]; \\ seek data } DBS_SEEKDEF; Seek for a record using the current index. comparison indicates the comparison type used: one of DBS_COMP_EQUAL, DBS_COMP_NOTEQ, DBS_COMP_LESS, DBS_COMP_LESSEQ, DBS_COMP_GREAT and DBS_COMP_GREATEQ. Sets the current record to the matching record and returns 0 if a match is found. If not, leaves the current record as is, and returns E_DBS_NOMATCH. The seek parameter gives the conditions for the search keys for the seek in the form of a DBS_SEEKDEF structure. keycount gives the number of keys to search on, from 1 to the number of keys in the current index. seekdata holds a pointer to the raw data for each search key field in the index definition order. The length of each piece of data is already known from the index definition, except for string fields where the data is stored as a byte-counted string. 2.5.4 Adding a record to a sparse index - DbsTblViewAddIndex DbsTblViewAddIndex(DBS_VIEW hTblView) Add the current record to the current index. The index must have been created with the DBS_MANUAL flag set. 2.5.5 Removing a record from a sparse index - DbsTblViewRemoveIndex DbsTblViewRemoveIndex(DBS_VIEW hTblView) Remove the current record from the current index. The index must have been created with the DBS_MANUAL flag set. 2.6 DynaView 2.6.1 Creating a DynaView - DbsDynaViewCreate INT DbsDynaViewCreateOnTable(DBS_TABLE hTable,DBS_VIEW *phDynaView, TEXT *filter,DBS_KEYDEF *pKeys,INT flags) INT DbsDynaViewCreateOnDynaView(DBS_VIEW hDynaView,DBS_VIEW *phDynaView, TEXT *filter,DBS_KEYDEF *pKeys,INT flags) INT DbsDynaViewCreateOnTable(DBS_TABLE hTable,DBS_VIEW *phDynaView, TEXT *filter,TEXT *sort,INT flags) INT DbsDynaViewCreateOnDynaView(DBS_VIEW hDynaView,DBS_VIEW *phDynaView, TEXT *filter,TEXT *sort,INT flags) Build a DynaView on the table/DynaView using the filter, filter, and sort keys in pKeys. If filter is not NULL, it should be a ZTS filter-string (see section 3), this generates a sparse set containing only those records which match the filter, otherwise all records in the table will be included. If filter is "none", then an empty DynaView will be generated. If pKeys is not NULL, it should point to a DBS_KEYDEF structure as used by DbsIndexCreate. This controls the sort order of the DynaView. If no key is given the order of the table/DynaView is used to set the record order in the new DynaView. The sort order of the DynaView can also be specified as a string containing an SQL order by clause (see section 3). Note that if a string field is included in a text sort order, then only the first 20 characters of the string field will be used in the key definition - if a larger range of characters is required, then the user should specifiy the sort as a DBS_KEYDEF instead. flags can specify DBS_READONLY in which case records in the table can only be read and not written/created through this view, or DBS_APPENDONLY in which records can only be appended to the table, no existing records can be accessed. A DynaView is a selective view of a table, providing a filtering and sorting mechanism not available in TblViews. They are always temporary, and are destroyed when closed. The filter is only relevant when building the view, records modified or added to the view are always added, whether or not they match the filter. Records added to the table directly are not added to the DynaView. Records updated on the table are updated in the DynaView. 2.7 View 2.7.1 Closing a view - DbsViewClose INT DbsViewClose(DBS_VIEW hView) Close the view. Do not use the view handle after this call. 2.7.2 Cloning a view - DbsViewClone INT DbsViewClone(DBS_VIEW hView,DBS_VIEW *phView) Clone a view. This will produce a new View object identical to the old view object. For TblViews, this is no different to creating a new TblView object using DbsTblViewCreate. For DynaView objects things are slightly different, records which are added to one DynaView object will also be added to all clones of that DynaView object, similarly for records which are deleted. The cloned DynaView always contains exactly the same records as the DynaView it was cloned from. All cloned views must be separately closed before the table can be closed. 2.7.3 Obtaining a record count - DbsViewGetRecordCount INT DbsViewGetRecordCount(DBS_VIEW hView,LONG *pCount) Get the number of records in a view. 2.7.4 Finding a record - DbsViewFindX INT DbsViewFindFirst(DBS_VIEW hView,TEXT *search) INT DbsViewFindLast(DBS_VIEW hView,TEXT *search) INT DbsViewFindNext(DBS_VIEW hView,TEXT *search) INT DbsViewFindPrevious(DBS_VIEW hView,TEXT *search) Finds the first, last, next or previous record in the view which matches the search criteria (see section 3) in the ZTS search and makes it the current record. Returns 0 if a match is found and makes the matching record the current record, else returns E_DBS_NOMATCH and leaves the current record undefined. The view's current index is used to give the record order, or if there is no current index, the underlying record order of the table is used. 2.7.5 Moving around a view - DbsViewMoveX INT DbsViewMoveFirst(DBS_VIEW hView) INT DbsViewMoveLast(DBS_VIEW hView) INT DbsViewMoveNext(DBS_VIEW hView) INT DbsViewMovePrevious(DBS_VIEW hView) Move to the first, last, next or previous record in the view and make it the current record. Returns E_FILE_EOF if there are no more records in the specified direction. If the view is a TblView, then the current index is used to give the record order, or if there is no current index, the underlying record order of the table is used. 2.7.6 Creating a bookmark - DbsViewGetBookmark INT DbsViewGetBookmark(DBS_VIEW hView,DBS_BOOKMARK *pBookmark) Creates a bookmark at the current record in the view. A bookmark can only be used on the view from which it was created. 2.7.7 Getting the bookmark of the last modified record - DbsViewGetLastModified INT DbsViewGetLastModified(DBS_VIEW hView,DBS_BOOKMARK *pBookmark) Returns the bookmark of the last modified record in the view, the last modified record being the last record in the view to be either appended or edited. 2.7.8 Moving to a bookmark - DbsViewMoveBookmark INT DbsViewMoveBookmark(DBS_VIEW hView,DBS_BOOKMARK *pBookmark) Sets the view's current record to the record at the bookmark bookmark. 2.7.9 Obtaining the size of a field - DbsViewGetFieldSize INT DbsViewGetFieldSize(DBS_VIEW hView,INT field,LONG *pSize) INT DbsViewGetFieldSizeByName(DBS_VIEW hView,TEXT *field,LONG *pSize) Returns the size of the field indicated by field. For non-variable length fields (anything but variable length strings and long binary fields), this will return the same size as given in the field definition. 2.7.10 Obtaining the value of a field - DbsViewGetField INT DbsViewGetField(DBS_VIEW hView,INT field,VOID *pValue,INT len) INT DbsViewGetFieldByName(DBS_VIEW hView,TEXT *field,VOID *pValue, INT len) Get the value of a field from the current record. For the first function field is the ordinal number, for the second it is the field name as a ZTS. For non string fields len is ignored, otherwise a maximum of len bytes of data are written to *pValue, the function returns the actual number of bytes written. Will return an error if used on long binary fields, use DbsViewLBinGetChunk instead. 2.7.11 Setting the value of a field - DbsViewSetField INT DbsViewSetField(DBS_VIEW hView,INT field,VOID *pValue,UINT len) INT DbsViewSetFieldByName(DBS_VIEW hView,TEXT *field,VOID *pValue, UINT len) Set the value of a field in the record buffer using the data pointed to by pValue. For the first method field is the ordinal number, for the second it is the field name as a ZTS. For text fields len bytes of data are written to the record buffer, otherwise len is ignored. The record must have already been prepared for editing by using either the DbsViewAppendRecord or DbsViewEditRecord method. Any changes made to the record are not written to the table until the DbsViewUpdateRecord method is used. Will return an error if used on long binary fields, use DbsViewLBinAppendChunk instead. 2.7.12 Obtaining the value of a long binary field - DbsViewLBinGetChunk INT DbsViewLBinGetChunk(DBS_VIEW hView,INT field,UBYTE *pValue,INT len, LONG *pOffset) INT DbsViewLBinGetChunkByName(DBS_VIEW hView,TEXT *field,UBYTE *pValue, INT len,LONG *pOffset) Gets up to len bytes of the long binary field field starting from *pOffset bytes into it. The data is put into a buffer pointed to by pValue. The function returns the actual number of bytes read. Will return an error if used on any type of field other than long binary. 2.7.13 Appending to a long binary field - DbsViewLBinAppendChunk INT DbsViewLBinAppendChunk(DBS_VIEW hView,INT field,UBYTE *pValue, UINT len) INT DbsViewLBinAppendChunkByName(DBS_VIEW hView,TEXT *field, UBYTE *pValue,UINT len) Appends len bytes of data from the buffer pointed to by pValue onto the end of the long binary field field. No changes are made to the actual record until it is updated using the DbsViewUpdateRecord method. Will return an error if used on any type of field other than long binary. 2.7.14 Clearing a long binary field - DbsViewLBinClear INT DbsViewLBinClear(DBS_VIEW hView,INT field) INT DbsViewLBinClearByName(DBS_VIEW hView,TEXT *field) Clears the long binary field field, and sets its size to zero bytes. No changes are made to the actual record until it is updated using the DbsViewUpdateRecord method. Will return an error if used on any type of field other than long binary. 2.7.15 Appending a record - DbsViewAppendRecord INT DbsViewAppendRecord(DBS_VIEW hView) Clear the record buffer ready for a new record to be created. Use the DbsViewSetField method to set the value of the fields in the record, and the DbsViewUpdateRecord method to save the new record. Using any function which changes the current record will lose the changes made in the record buffer and cancel the Append operation. 2.7.16 Editing a record - DbsViewEditRecord INT DbsViewEditRecord(DBS_VIEW hView) Read the current record into the record buffer ready for editing. Use the DbsViewSetField method to change the value of the fields in the record, and the DbsViewUpdateRecord method to save the changed record. Using any function which changes the current record will lose the changes made in the record buffer and cancel the Edit operation. Note that if another client is already editing the record then this function will give an E_DBS_LOCKED error. 2.7.17 Updating a record - DbsViewUpdateRecord INT DbsViewUpdateRecord(DBS_VIEW hView) Update the table with the record in the record buffer. Any open indexes are automatically updated. If for any reason the update fails (for example a NOTNULL field contains a null value), then an error is signalled and the record remains in append or edit mode to allow the error to be corrected easily. 2.7.18 Cancelling an append or edit - DbsViewCancelUpdate INT DbsViewCancelUpdate(DBS_VIEW hView) This will cancel any DbsViewAppendRecord or DbsViewEditRecord and lose the changes which have been made in the record buffer. The current record will return to where it was prior to the append or edit being started. If there has been no append or edit, then this function will have no effect. 2.7.19 Deleting a record - DbsViewDeleteRecord INT DbsViewDeleteRecord(DBS_VIEW hView) Delete the current record from the table. Any open indexes are automatically updated. 3. SQL Support 3.1 Find and DynaView Filters The database server provides limited SQL support for the declaration of search expressions in a Find call and the DynaView filter in a CreateDynaView call. The expression corresponds to the WHERE clause in a SQL statement, but only the following syntax is implemented: search-condition boolean-term [OR search-condition] boolean-term boolean-factor [AND boolean-term] boolean-factor [NOT] boolean-primary boolean-primary predicate | ( search-condition ) predicate comparison-predicate | like-predicate comparison- field-identifier comparison-operator value predicate The type of the value must match the field type like-predicate field-identifier LIKE string-value The field must be of type DBS_TYPE_STRING field-identifier fieldname | [fieldname] | [#fieldnumber] comparison- = | <> | < | > | >= | <= operator When comparing logical fields only the = and <> operators have any effect, all other operators will give a type mismatch error. value string-value | numeric-value | logical-value| date-value string-value "{character}" | '{character}' "" embedded is interpreted as a ", ditto for ' numeric-value number standard floating point and exponential formats supported logical-value number | YES | TRUE | NO | FALSE If a number, zero is FALSE, any other value is TRUE date-value #{character}# The contained string must in the form of a date followed by an optional time, with both date and time interpretable by PLIB/EPOC eg. #20/01/95 15:43# or #20/01/1995# 3.2 DynaView and Index Sort Order The database server allows the sort order of a DynaView to be specified as text sort list with the same syntax as an SQL ORDER BY clause: sort-list sort-item | sort-list ',' sort-item sort-item field-identifier [ASC|DESC] field-identifier fieldname | [fieldname] | [#fieldnumber] 4. Database Server Error Numbers The server will return errors to the calling process either as a negative integer value returned by the API function, or by leaving, depending on the value of the error parameter to DbsConnect. The following error numbers are defined: Number #Define Meaning -1000 E_DBS_INVALID The current record is invalid -1001 E_DBS_OPEN Index is already open. -1002 E_DBS_INUPDATE The current record is already being updated -1003 E_DBS_NOUPDATE An attempt was made to update a record before edit or append -1004 E_DBS_NOINDEX The specified index is not open / set -1005 E_DBS_TYPE The specified field type is not valid -1006 E_DBS_CONVERT Internal error - Invalid type conversion -1007 E_DBS_FIELD The field size specified in the table definition is too large -1008 E_DBS_NAME No such field name -1009 E_DBS_LOCKED The specified database, table, record etc. is locked by another client, or an attempt was made to update a NOUPDATE field. -1010 E_DBS_NULL An attempt was made to write a null value to a NOTNULL field -1011 E_DBS_KEY The key field size specified in the index definition was too large -1012 E_DBS_DUP A duplicate key was found in the index -1013 E_DBS_BUFFER Field is outside buffer -1014 E_DBS_NOMATCH No matching record was found -1015 E_DBS_LBIN Non-LBin operation on long binary field or vice versa -1016 E_DBS_FIELDNAME Invalid field name -1020 E_DBS_SYNTAXERR Syntax error in find, filter or sort expression -1021 E_DBS_BRACKET Missing bracket in find, filter or sort expression -1022 E_DBS_NOFIELD Field name/number was expected in find, filter or sort expression -1023 E_DBS_MISMATCH Type mismatch in find or filter expression -1024 E_DBS_BADHANDLE Object handle is corrupted -1025 E_DBS_NOISAM Requested ISAM format is not found -1026 E_DBS_CONNECTED Client is already connected to server -1027 E_DBS_CANTLOCK Can't lock database, table or index -1028 E_DBS_BMARK An invalid bookmark was specified -1029 E_DBS_NUMBER The specified field number does not exist -1030 E_DBS_TOOMANYKEYS Too many keys were given in the key definition -1031 E_DBS_NOTCLOSED An attempt was made to close an object whilst other objects were still open on it -1032 E_DBS_BADMESSAGE Internal error - Unrecognised server message number 5. Database Formats Currently, the database server supports three formats - Psion, dBase III and dBase IV - this section documents the differences between them. 5.1 Psion This is the default database format, if no database format is specified when a database is opened or created, then this format is assumed. Psion databases support all database server field types, flags and functions with the following exceptions: DbsViewMovePrevious, DbsViewFindPrevious, DbsViewMoveLast and DbsViewFindLast are not supported on TblViews which have no current index set. The long binary field type is not supported. 5.2 dBase III The dBase III database format is the most limited of the three supported formats. The following restrictions apply upon their usage: * When a table is created, all field types can be specified. However, the underlying table file does not support either DBS_TYPE_INT16 or DBS_TYPE_INT32 fields, both of these field types are actually stored as doubles. Things will work fine whilst the table is still open after creation, and both these field types can be both read and written to the table, however, once the table is closed and reopened these fields will appear to the server as DBS_TYPE_DOUBLE. * DBS_TYPE_DATE fields only store the days part of the DBS_DAYSEC structure, any time component is ignored. The date must lie between 1st January 1900 and 31st December 2155. * DBS_TYPE_STRING fields are fixed length with a maximum length of 254 characters. When setting the value of a string field, the string is either trucated or padded out with spaces. * dBase field names can be a maximum of 10 characters in length (any extra characters are truncated) and may only contain alphanumeric characters and underscores. * dBase III index files (.NDX files) only support indexing on one field, and only in ascending order. Indexes can't be created on a DBS_TYPE_LOGICAL field. 5.3 dBase IV dBase IV tables still have all of the limitations of the dBase III database format. * The default index format also supports sorting in descending order. * dBase IV supports both .MDX and .NDX index files. By default an index is created as a tag in the production .MDX index file. To open or create an index tag in a different .MDX file the index name should be specified as *<.mdx filename>. To open or create a dBase III .NDX index file the index name should be ., note that the file extension (default .NDX) must be specified, otherwise the name is interpreted as a tag in the production index .MDX file. * The DbsTableGetIndexName function returns index names in the above `canonical' format. 5.4 Index tracking All three database formats track indexes using a text .INF file for each table (similar to Visual Basic). This is used for the DbsTableGetIndexCount, DbsTableGetIndexName functions and in the process of deleting a table. To create a file for any existing tables/indexes follow the example: There is a dBase IV table \DBASE\ADDRESS.DBF with a production index file \DBASE\ADDRESS.MDX and an extra index file \DBASE\INDEX\ADD_NAME.NDX, so the file \DBASE\ADDRESS.INF will contain, [dbase] MDX1=ADDRESS NDX1=INDEX\ADD_NAME If there is more than one .NDX or .MDX file, use NDX2, NDX3, MDX2, etc... as the keys in the .INF file. The entries are minimal, so paths and extensions are only required if the file is not in the database directory (in this case \DBASE\). For Psion tables the .INF file should contain a section called [psion], and the keys are PDX1, PDX2, etc...