|
|
|
|
|
|
|
dBase.opx
Section Contents
You can use OPL to create data files (databases) like those used by the Data application. You can store any kind of information in a data file, and retrieve it for display, editing or calculations.
EPOC devices use a relational database management system (DBMS) which supports a subset of SQL, a standardised language for querying relational databases. A full description of the SQL queries possible in OPL is given in SQL specification.
Data files (or databases) are made up of tables. These tables consist of records which contain data in each of their fields. Fields are named, and each record in a table has the same fields. For example, in a table of names and addresses, each record might have a name field, a telephone number field, and separate fields for each line of the address.
In OPL you can:
This document makes the distinction between views and tables.
Views and tables both represent data in records with fields. This document often says ‘view’ rather than ‘table’ to emphasise that the data being looked at is really stored in a file outside the program. The whole database file is not read into memory when the database manipulation commands are used and it doesn’t have to be.
Section Contents
You can have up to 26 views open at any time. Each of these must have a logical name, from A to Z. Logical names allow you to refer to a view many times without having to keep using the same file name or a lengthy SQL expression.
A different logical name must be used for each currently open view; when a view is closed the logical name is freed for use by another view. More than one view can be opened on a single database file.
Field handles are used like variables, so they have a %, $ or & appended to specify their type. They refer to the fields of a database view, so when they are used the logical name standing for the view must also be given. The two are separated by a dot:
e.g., A.name$. This gives the contents of a particular field in the current record.
CREATE "mydatabase",A,name$,price,contact$,numordered&
:
PRINT "Product name:"; a.name$
PRINT "Number ordered:"; a.numordered&
Field handle names can be up to 32 characters long, including any qualifier like $ or &.
See also Creating databases and tables for details of CREATE.
OPL uses the idea of the current record within a view. Each database view has one record which is current, and subsequent field assignment operates on this record.
The FIRST, LAST, BACK, and NEXT keywords do not alter data in the database, but do change which record is current in the current view; INSERT also sets the current record. Records can also be searched for in a view with DbFind&: or DbFindField&: (see Finding a record below), and these keywords also set which record is current.
When OPL creates or opens a view, that view is current. This means that PUT saves records to this view, and the record-position commands move around this view. You can still use other open views, for example A.field1=B.field2
USE makes current one of the other opened files. For example USE B selects the file with the logical name B. If you attempt to USE a file which has not yet been opened or created, an error is reported.
Fields can be assigned values just like variables. The field name must be used with the logical file name like this: A.f%=1 or INPUT A.f$. Field assignment operates on the current record in the named view.
If you try to give the wrong type of value to a field (for example "Davis" to A.f%) an error message will be displayed.
You can access the fields of an open view from other procedures, just like GLOBAL variables.
You must know the type of each field, and you must give each a separate name - you cannot refer to the fields in any indexed way, e.g. as an array.
The OPENR command works in exactly the same way as OPEN, except that the file cannot be written to with UPDATE, only read. However, more than one running program can then look at the file at the same time.
Views of tables in databases are created with the OPL command CREATE:
Which creates a table in a database, and assigns a view of it to logical-name. The database file given in create-expression is opened, or created if needed. After a CREATE statement, logical-name can be used to refer to the new database view.
field-handle-list is a comma-separated list of OPL variables which is used to provide the field handles for OPL (see Accessing an open database, above).
opl-create-string is a SQL table creation query encapsulated in an OPL string. It gives the name of the file to open, and optionally the table to look at and the names of the fields to put in it. Table creation queries are documented fully in Table creation.
If the table and field names are not given, then the table is called Table1, and the fields are named after the field handles. See below for examples of these.
N.B.: New tables cannot be created in databases that are already open. CREATE can be used to create a new table in an existing database, but the database must not be open when CREATE is called. If the database file is already open, use CLOSE to close it. OPEN can be used to reopen existing views.
A new table in a database contains a single record whose fields are blank — zero for numbers and the empty string for strings.
create-expressions can be simple or more complex. The simplest example is:
CREATE "datafile",A,f1%,f2%
This creates a view of the data in the default table in the file datafile. The default table is called Table1. The field names in the new table are named after the names of the field handle variables, which in this case are f1% and f2%.
A more complex example:
CREATE "people FIELDS name, number TO phoneBook",A,n$,number$
This will create a table called phoneBook in the database called people, creating the database too if it does not exist. The table will have fields name and number, whose respective types are specified by the field handles n$ and number$, both strings in this example.
In both examples, the contents of the database can then be accessed through A.
The OPEN command opens a view of an existing table in a database:
opl-select-string is a SQL selection query encapsulated in an OPL string. Selection queries are documented fully in Select statements.
field-handle-list is a comma-separated list of database field handles. These are used with logical-name to access the view as described in Accessing an open database (above).
OPEN "datafile",A,f1%,f2%
Opens the default table Table1 and provide access to as many fields as there are handles supplied.
It is also possible to open multiple views on a table simultaneously and to specify which fields are to be available in a view, e.g.
OPEN "people SELECT name FROM phoneBook",A,n$
This view gives you access to just the name field from the phoneBook table.
The string from SELECT onwards in the OPEN statement forms an SQL query which is passed straight on to the underlying EPOC DBMS. The SQL command-set is specified in SQL specification.
A more advanced view, ordered by an index (described later), would be opened as follows,
OPEN "people SELECT name,number FROM phoneBook ORDER BY name
ASC, number DESC",A,n$,num%
This would open a view with name fields in ascending alphabetical order and if any names were the same then the number field would be used to order these records in descending numerical order.
Changes can be made to a view, and can be written into a database file at a later date.
When using CREATE and OPEN, you should also use the MODIFY, INSERT, PUT and CANCEL keywords. There are older keywords in OPL retained for compatibility — APPEND and UPDATE — but these are deprecated in the current release.
MODIFY and INSERT must take place within a transaction (see below). You’d use MODIFY or INSERT like this:
BEGINTRANS REM Start a transaction on some related records
INSERT
A.species$ = "King penguin"
A.height = 42.102
A.name$ = "Gerald"
PUT
: REM Maybe some more additions
COMMITTRANS REM End the transaction
REM write all PUTs to the file
Section Contents
A set of related records should be committed only on successfully PUTting the last one. Otherwise all new records may be discarded using ROLLBACK. This ensures the atomicity of the whole transaction.
Transactions allow changes to a database to be committed in stages. It is necessary to use transactions in database operations to achieve reasonable speeds.
A transaction is carried out using the following commands:
Transactions can be thought of as single, unitary operations on the file in which the current view is stored.
In the EPOC DBMS model, as with most modern relational database models, absolute record position does not have much significance.
Bookmarks can be assigned to particular records to provide fast record access and should be used in preference to POS and POSITION when opening views using the OPEN...SELECT.. or CREATE ... FIELDS... statements. POS and POSITION can be used safely on tables opened or created using a filename-only OPEN or CREATE statement. However, POS and POSITION should not be used in conjunction with bookmarks as bookmarks can cause these keywords — kept mainly for compatibility with older versions of OPL — to become inaccurate.
Note that if bookmarks are used in conjunction with POS and POSTION accuracy can be restored by using FIRST or LAST on the current view.
The bookmark commands are as follows: BOOKMARK puts a bookmark at the current record of the current database view. The value returned can be passed to GOTOMARK to make the record current again and to KILLMARK to delete the bookmark.
It is often useful to iterate through a database from the first record to the last. This can be achieved with the FIRST or LAST keywords in conjunction with BACK and NEXT:
You’d normally iterate forwards through a view using EOF:
OPEN "mysortedfile",A,a$,b%
FIRST
DO
PRINT A.a$
PRINT A.b%
NEXT
UNTIL EOF
GET
The COUNT function returns the number of records in the file. If you try to count the number of records between MODIFY/INSERT and PUT, an ‘Incompatible update mode’ error will be raised.
CLOSE closes the current view on a database. If there are no other views open on the database then the database itself will be closed.
You should always ‘close’ a view (with the CLOSE command) when you have finished using it. Data files are fully closed automatically when a program ends.
You can use up to 26 logical names at a time — if you are using 26 logical names and you want to use another one, you must close one of the open files or views first. CLOSE closes the file or view referred to by the current logical name.
dBase.opxIndexes can be constructed on a table using several fields as keys. These indexes are subsequently used to provide major speed improvements when opening a table or views on them. This further functionality is provided in dBase.opx, which is supplied on the Release 5 EPOC ROMs.
Additional utilities for managing databases are provided in dbUtils.opx, which is supplied with the EPOC Release 5 OPL SDK.
COMPACT compacts a database, rewriting the file in place without any removed or deleted data. All views on the database and the hence the file itself should be closed before calling this command. Compaction may also be done automatically on closing a file by setting the automatic compaction flag using SETFLAGS. See Keyword Reference for full details of this.
Section Contents
DbFind&: (in SysRam1.opx) makes current the next record which has a field matching your search string. Capitals and lower-case letters match. For example:
r&=DbFind&:("Brown")
would select the first record containing a string field with the value "Brown", "brown" or "BROWN", etc. The number of that record in the database is returned, in this case to the variable r&. If the number returned is zero, no matching field was found. Any other number means that a match was found.
The search includes the current record. So after finding a matching record, you need to use NEXT before you can continue searching through the following records.
DbFind&:("Brown") would not find a field "Mr Brown". To find this, use wildcards, as explained below.
You can only search string fields, not number fields. For example, if you assigned the value 71 to the field a%, you could not find this with DbFind&:. But if you assigned the value "71" to a$, you could find this.
r&=DbFind&:("*Brown*") would make current the next record containing a string field in which Brown occurred - for example, the fields "MR BROWN", "Brown A.R." and "Browns Plumbing" would be matched. The wildcards you can use are:
|
? |
matches any one character |
|
* |
matches any number of characters. |
Once you’ve found a matching record, you might display it on the screen, erase it or edit it. For example, to display all the records containing "BROWN":
FIRST
WHILE DbFind&:("*BROWN*")
PRINT a.name$,a.phone$
NEXT
GET
ENDWH
Note: DbFind&: replaces the buggy language keyword FIND, which is deprecated. See Including header files for details on how to include the header file SysRam1.oph, which defines DbFind&:.
DbFindField&:, like DbFind&:, finds a string, makes the record with this string the current record, and returns the number of this record. However you can also use it to do case-dependent searching, to search backwards through the file, to search from the first record (forwards) or from the last record (backwards), and to search in one or more fields.
f& = DbFindField&:(string$,start&,num&,flags&)
searches for the string string$ in num& fields in each record, starting at the field with number start& (1 is the number of the first field). start& and num& may refer to string fields only and other types will be ignored. If you want to search in all fields, use 1 as the second argument and for the third argument use the number of fields you used in the OPEN/CREATE command.
The flags& argument specifies the type of search as explained in the SysRam1 OPX reference documentation. Constants for flags& are supplied in Const.oph.
flags& can be used to make the search case-dependent, where case-dependent means that the record will exactly match the search string in case as well as characters. Other wise the search will case-independent which means that upper case and lower case characters will match.
For example, if the following OPEN (or CREATE) statement had been used:
OPEN "clients",B,nm$,tel$,ad1$,ad2$,ad3$
then the command
r&=DbFindField&:("*Brown*",1,3,16)
will search the nm$, tel$ and ad1$ fields of each record for strings containing "Brown" searching case-dependently backwards from the current record.
If you find a matching record and then you want to search again from this record, you must first use NEXT or BACK (according to the direction in which you are searching) to move past the record you have just found, otherwise the search will find the same match in the current record again.
Note: DbFindField&: replaces the buggy language keyword FINDFIELD, which is deprecated. See Including header files for details on how to include the header file Sysram.oph, which defines DbFindField&:.
It is not possible to open an OPL database from the Data application. You can however open a file created by the Data application in an OPL program. The file is opened for reading only because if it were written to, OPL would have to discard all the formatting characters and prevent the Data application from reopening the file subsequently. An OPL program can create a new OPL database and copy the Data application records into it if necessary.
To open a Data application database that has one string field which you need to access, you could use:
OPEN "file",a,a$
Types not supported by OPL will be ignored. Note that integer fields in the Data application correspond to long integer fields in OPL: the Data application does not support 16-bit integer fields. The types and order of the OPL field handles must match the fields in the Data file. For example, if the data file Data2 contains:
You could access the fields supported by OPL using:
OPEN "Data2",A, f1&,f2$,f3
It would be better, however, to use the SQL SELECT clause to name the required Data file fields explicitly. For this to be possible it is necessary to use table name and the same field names as are used by Data. All Data files have a single table called Table1. The fields (referred to internally as columns in Data) are named ColA1, ColA2, etc.
So, with the field types from the previous example, the Data file could be opened using:
OPEN "Data2 SELECT ColA1,ColA3,ColA4 FROM Table1",a,f1&,f2$,f3
|
|
|
|
|
|
|