# Database manipulation

## Contents

- Overview

- Terminology — Files, tables, records and fields

- Accessing an open database

- The current record
- The current view
- Assigning values to fields
- Field names
- Opening a file for sharing

- Creating databases and tables
- Opening databases and tables
- Writing records
- Transactions

- Record position
- Sequential access
- The number of records

- Closing views and databases
- Indexes and `dBase.opx`
- Additional database functionality
- Compacting databases
- Finding a record

- `DbFind&:` — Finding records using string wildcards
- `DbFindField&:` — More controlled finding

- Opening a database created by the Data application

## Overview

Section Contents

- Terminology — Files, tables, records and fields

- ‘View’ vs. ‘table’

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.

### Terminology — Files, tables, records and fields

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:

- Create a new file or table with `CREATE`, or open an existing file with `OPEN`, and copy, delete and rename files with `COPY`, `DELETE` and `RENAME`.
- Add a new record with `INSERT`, change an existing one with `MODIFY`, and remove a record with `ERASE`.

#### ‘View’ vs. ‘table’

This document makes the distinction between views and tables.

- A table is the physical representation of data inside a database file.
- A view is a way of looking at a table. OPL doesn’t have to load an entire table into memory in order to examine or alter its data; instead it uses a view of the data in the file, and keeps the data in the file until it needs to be read.

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.

## Accessing an open database

Section Contents

- The current record
- The current view
- Assigning values to fields
- Field names
- Opening a file for sharing

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:

  current-record-lookup :
  logical-name `.` field-handle-name [ `%` | `$` | `&` ]

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`.

### The current record

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.

### The current view

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.

### Assigning values to fields

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.

### Field names

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.

### Opening a file for sharing

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.

## Creating databases and tables

Views of tables in databases are created with the OPL command `CREATE`:

  `CREATE` opl-create-string `,` logical-name `,` field-handle-list

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.

#### Examples of using `CREATE`

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`.

## Opening databases and tables

The `OPEN` command opens a view of an existing table in a database:

  `OPEN` opl-select-string `,` logical-name `,` field-handle-list

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).

#### Examples of using `OPEN`

        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.

## Writing records

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` begins a modification phase on the current record. It makes a copy
- `INSERT` adds a new record to the current view with numeric fields set to zero and string fields empty if values have not been assigned to them. It also marks the start of a modification phase on the new record.
- `PUT` marks the end of a database’s `INSERT` or `MODIFY` phase and makes the changes permanent.
- `CANCEL` marks the end of a database’s `INSERT` or `MODIFY` phase and discards the changes made during that phase.

`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

## Transactions

Section Contents

- Record position
- Sequential access
- The number of records

A set of related records should be committed only on successfully `PUT`ting 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:

- `BEGINTRANS` begins a transaction on the current view. Once a transaction has been started on a view (or table) then all database keywords will function as usual, but the changes to that view will not be made until `COMMITTRANS` is used.
- `COMMITTRANS` commits the transaction and writes all changes into the file.
- `ROLLBACK` cancels the current transaction on the current view. Changes made to the database with respect to this particular view since `BEGINTRANS` was called will be discarded.
- `INTRANS` finds out whether the current view is in a transaction.

Transactions can be thought of as single, unitary operations on the file in which the current view is stored.

### Record position

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.

### Sequential access

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`:

- `FIRST` positions to the first record in the current view.
- `LAST` positions to the last record in the current view.
- `BACK` positions to the last record in the current view.
- `NEXT` positions to the last record in the current view.

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 number of records

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.

## Closing views and databases

`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.

## Indexes and `dBase.opx`

Indexes 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 database functionality

Additional utilities for managing databases are provided in dbUtils.opx, which is supplied with the EPOC Release 5 OPL SDK.

## Compacting databases

`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.

## Finding a record

Section Contents

- `DbFind&:` — Finding records using string wildcards
- `DbFindField&:` — More controlled finding

`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.

### `DbFind&:` — Finding records using string wildcards

`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&:` — More controlled finding

`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&:`.

## Opening a database created by the Data application

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:

1. long integer field
2. date/time field (ignored by OPL)
3. string field
4. floating-point number field

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