SQLite and the new ABC SQLexecutor

Clarion 9 introduces a new driver for working with SQLite databases.  SQLite is one of the most widely deployed databases in use today. SQLite databases are widely used on both iOS and Android tablets and cell phones.  SQLite databases are useful in any situations where simplicity of administration and easy maintenance are more important than scalability.

The driver introduces a new prop; PROP:CreateDB. PROP:CreateDB is a command property that tells the SQL driver to create the SQLite physical database file that is specified in the OWNER attribute of the file. If the database file already exists, this command does nothing.  The existing database file will not be destroyed.

The CREATE statement converts Clarion data types to SQLite Data types using the following table:

Clarion Data Type SQLite Data Type
STRING CHAR
CSTRING VARCHAR
STRING(8);GROUP OVER(STRING);DATE;TIME DATETIME
DATE DATE
PDECIMAL NUMBER
DECIMAL NUMBER
BYTE TINYINT
SHORT SMALLINT
LONG INTEGER
SREAL FLOAT
REAL REAL
BLOB CLOB
BLOB,BINARY BLOB

However Clarion 9 introduces new ABC support so that you can easily deploy and execute SQL scripts. For example a create script like below;
CREATE TABLE Courses(
Number INTEGER,
Description CHAR(40),
CompleteDescription CLOB,
CONSTRAINT KeyNumber PRIMARY KEY (Number));
CREATE INDEX KeyDescription ON Courses(Description);

The new class executes SQL code as a series of commands separated by an end of statement marker which you specify.  Any errors encountered executing the script are reported using the passed error handler. In short, you provide an external file that contains your SQL script and its parsed and executed.  The SQL code isn’t limited to just Create and Alter database scenarios, you can execute any valid SQL code. And of course the new ABC SQL script support is applicable across any Driver and SQL backend.