SQLite Database Interface

The SQLite interface is a DLL with callable routines which enables Planimate to store and access large amounts of data in database files using Structured Query Language (SQL. It features high speed, mapping between Planimate and SQL table columns, support for Planimate dates, flexible handling of labels and support for text data as well as numeric.

Function Call Notation

At the time of writing, DLLs are called in Planimate routines using the CallDLL routine operation, eg:

r.result = CallDLL("PLSQLite.dll","OpenDatabase","mydatabase.sqlite",1,r.dbhandle)

In the descriptions below, the CallDLL part is omitted in anticipation that a future version of Planimate will present the DLL calls in a more streamlined way.

In other words, the DLL functions are described as if they are stand-alone functions. when in fact you need to use CallDLL with the DLL name and function name as the first two parameters, as in the example above.

Return code

Most of the functions in the PLSQLite DLL return a status value which is zero on success or a _dllerrors label value otherwise.

The codes are as defined in the PLDLL.HPP API and are currently:

PLDLL_OK = 0
PLDLL_ERROR = 1
PLDLL_BADFUNCTION = 2
PLDLL_BADPARAMS = 3
PLDLL_MEMORY = 4
PLDLL_BADROUTINECONFIG = 5
PLDLL_BADPARAMCONFIG = 6

These codes are generic codes used by the Planimate DLL framework and typically users of the SQLite DLL will have to deal with errors in their queries etc. These are all returned as PLDLL_ERROR, for which case the modeller should call GetErrorDetail() which is described below.

Data Types

The functions below use the following names to indicate what they expect for parameters. This summarises the corresponding Planimate data types you can use for each.

Type Name	Planimate Data Example
VALUE	Constant number (IN only)  (123)  Attribute (p.attribute, r.result, s.Time) Cell reference (t.table[r.therow][c.Location]) Result of a numerical expression (IN only) (p.attribute+1)
STRING	Quoted text ("SELECT * FROM") Attribute/Cell - its formatted value will be used, very useful with Text or Label formatted data. Text expression ("SELECT * FROM " & p.TableName)
TABLE	Reference to an entire Planimate table (t.tablename)

Function Reference

The following are the callable functions/routines that the PLSQlite DLL provides to modellers. The TestDB demo provides excellent examples of the use of these functions from which useful routine code can be copied and pasted into your own model.

OpenDatabase

status = OpenDatabase(IN STRING databaseFilename,
                      IN  VALUE allowCreateNew,
                      OUT VALUE databaseHandle)

Opens a sqlite database file for subsequent use. On success it sets databaseHandle (used in most other calls) and returns zero. Failure codes include:

PLDLL_MEMORY - too many databases open or memory allocation failure

PLDLL_ERROR - sqlite error (detail available via GetErrorDetail)

CloseDatabase

status = CloseDatabase(IN VALUE databaseHandle)

Closes a previously opened database. If the database handle was not opened this returns PLDLL_BADPARAMS.

GetErrorDetail

sqliteerrorcode = GetErrorDetail()

This returns the sqlite error code which is set in cases where status returned from a just-previous call was PLDLL_ERROR. Do not rely on this value otherwise. This value may indicate a problem in SQL parsing or the preparation of the parameters (eg: bad Planimate table row number or row count return SQLITE_RANGE). These are defined in sqlite.h and part of the sqlite API.

The testsql demo model has a SQLiteErrors label list you may wish to import into your own model.

SQLITE_ERROR 1
SQLITE_INTERNAL 2
SQLITE_PERM 3
SQLITE_ABORT 4
SQLITE_BUSY 5
SQLITE_LOCKED 6
SQLITE_NOMEM 7
SQLITE_READONLY 8
SQLITE_INTERRUPT 9
SQLITE_IOERR 10
SQLITE_CORRUPT 11
SQLITE_NOTFOUND 12
SQLITE_FULL 13
SQLITE_CANTOPEN 14
SQLITE_PROTOCOL 15
SQLITE_EMPTY 16
SQLITE_SCHEMA 17
SQLITE_TOOBIG 18
SQLITE_CONSTRAINT 19
SQLITE_MISMATCH 20
SQLITE_MISUSE 21
SQLITE_NOLFS 22
SQLITE_AUTH 23
SQLITE_FORMAT 24
SQLITE_RANGE 25
SQLITE_NOTADB 26
SQLITE_NOTICE 27
SQLITE_WARNING 28

BeginTransaction and CommitTransaction

status = BeginTransaction(IN VALUE databaseHandle)

status = CommitTransaction(IN VALUE databaseHandle)

These enable combining of a number of operations as a single transactional unit. This guarantees an all-or-nothing operation. Transactions also greatly speed up performance of multiple inserts but will use a correspondingly larger amount of memory as more occurs between a begin and commit.

It is recommended that InsertRows() be placed in between BeginTransaction() and CommitTransaction().

GetTableStructure

status = GetTableStructure(IN VALUE databasehandle,
                           IN STRING tablename,
                           OUT TABLE resulttable)

Sets resulttable to contain a row for every column in the named table, resulttable is resized as needed. Resulttable is set to:

column 1 is the name of the sqlite table's column (text).
Column 2 is the SQL type used when creating the table (text).
Column 3 is the data type of the first row (if any) since SQLite database rows can differ from the declared column type, which is only a hint. This value is defined in sqlite.h and in the SQLTypes label list in the demo model, as follows:

RunQuery

status = RunQuery(IN VALUE databaseHandle
                  IN STRING querytext,
                  OUT VALUE returnvalue)

Runs a query. Useful for building the database's structure, inserting, getting a count or other operations where at most a single outpui value will suffice. returnvalue may be s.NullAttribute.

QueryIntoTable

status = QueryIntoTable(IN VALUE databasehandle,
                        IN STRING querytext,
                        OUT TABLE resulttable)

Runs a query which reads data into the provided planimate table.

Columns in the result set are matched by name to columns in the Planimate table, so the order/presence of the columns in the Planimate table does not matter so long as they are matched somewhere.

Rows are appeneded and reallocation in resulttable when required. As this is done incrementally, memory fragmentation will be a problem if very large tables are read without preallocating the Planimate table.

QueryIntoRows

status = QueryIntoRows(IN VALUE  databasehandle,
                       IN STRING querytext,
                       IN VALUE  startRow
                       IN VALUE  rowCount
                       OUT TABLE resulttable,
                       OUT VALUE rowsRead)

Runs a query which reads data into the provided planimate table, like QueryIntoTable. However this does not allocate rows and can have an arbitrary start row and row limit after which the operation returns.

InsertRows

status = InsertRows(IN VALUE databasehandle,
                    IN STRING querytext,
                    IN VALUE startRow,
                    IN VALUE rowCount,
                    IN TABLE datatable)

Performs repeated inserts over a range of rows. The query should specify an insert operation, table name and the columns to be inserted.

The DLL interprets PL column types as follows:

• any PL DATETIME column is converted to Unix time (taking into account PLs run startt date)
• Text and label columns are saved as text
• otherwise the column value is saved as a raw number

The type you create a column as is only a hint as sqlite does cell by cell formatting, very differently to PL and most databases