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)

This can be used to retrieve the column names and types for a table in the database.

"resulttable" has rows set to contain a row for every column in the named table. Three columns must be present in "resulttable" before use, with arrangement and type as follows:

Column 1: Text format, will be set to the name of the sqlite table's column.
Column 2: Text format, will be set to the SQL definition type for the column.
Column 3: Value, will be set to the data type of the first row (if any).

SQLite data can differ from the declared column type, which is only a hint. This is transparent to the use of the database in Planimate but will be important to know if you interface to the database directly.

Data types in sqlite are defined in sqlite.h and in the SQLTypes label list in the demo model, as follows:

SQLITE_INTEGER 1
SQLITE_FLOAT 2
SQLITE_TEXT 3
SQLITE_BLOB 4
SQLITE_NULL 5

RunQuery

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

Runs a query and is useful for building the database's structure, inserting from manually built queries, getting a count or other operations where at most a single outpui value will suffice.

"returnvalue" may be set to s.NullAttribut if you don't need or care about a return value.

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. The "AS" SQL clause may be used to associate a given column with a differently named column in the Planimate table. If no column names match, no data is returned.

SELECT * FROM testData     -- matches columns by name

SELECT Time AS MyTime, LocationID AS MyLocation, Quantity AS MyQuantity,
Notes AS MyNotes, ColorI AS Color FROM testData ORDER BY Time

You can also create new columns which match Planimate columns:

SELECT *,(Quantity * 100) AS UsagePC FROM testData ORDER BY Time

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

In some cases it may be worth using RunQuery to execute the query to retrieve the size of the result set first, then reallocate the Planimate table using AllocateTableRowMemory(), then run the query again to retrieve the actual data, eg:

SELECT COUNT(*) FROM testData

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/append rows, it operates over an existing start row / row count range. "rowsRead" returns the actual number of rows retrieved.

This operation is useful for reading small record sets (eg: from/to data) or "paging in" data from logs for graphing.

Column name matching works the same as QueryIntoTable.

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