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.

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)

Open an sqlite database file. 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.

BeginTransaction and EndTransaction

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 speed up performance of multiple inserts but will use a correspondingly larger amount of memory as more occurs between a begin and commit.

GetTableStructure

status = GetTableStructure(IN VALUE databasehandle,

                          IN STRING       tablename,
                         OUT TABLEDIRECT 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
column 3 is the data type of the first row if any

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 TABLEDIRECT 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 TABLEDIRECT 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  TABLEDIRECT  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