DBSQLite3

Module with object oriented access to a SQLite database.

The class will automatic release any resources when garbage collected.

Const

OPEN_READONLY*          = SQLite3Dll.OPEN_READONLY;

OPEN_READWRITE*         = SQLite3Dll.OPEN_READWRITE;

OPEN_CREATE*                = SQLite3Dll.OPEN_CREATE;

OPEN_URI*                   = SQLite3Dll.OPEN_URI;

OPEN_MEMORY*                = SQLite3Dll.OPEN_MEMORY;

TINTEGER*                   = SQLite3Dll.SQLITE_INTEGER;

TFLOAT*                     = SQLite3Dll.SQLITE_FLOAT;

TTEXT*                      = SQLite3Dll.SQLITE_TEXT;

TBLOB*                      = SQLite3Dll.SQLITE_BLOB;

TNULL*                      = SQLite3Dll.SQLITE_NULL;

ROW*                = SQLite3Dll.ROW;

DONE*                               = SQLite3Dll.DONE;

Types

Db* = POINTER TO DbDesc;

Stmt* = POINTER TO StmtDesc;

Procedures

Db.Open

Open a connection to a new or existing SQLite database with mode defined in Opts. This defaults to try to create a new database.

Return TRUE on success.

PROCEDURE (this : Db) Open*(filename- : ARRAY OF CHAR; Opts := OPEN_READWRITE OR OPEN_CREATE : INTEGER): BOOLEAN;

Db.Close

Close database

PROCEDURE (this : Db) Close*();

Db.ErrorMsg

Return last error description from SQLite.

PROCEDURE (this : Db) ErrorMsg*(): String.STRING;

Db.Execute

Directly execute sql.

Return TRUE on success.

PROCEDURE (this : Db) Execute*(sql- : ARRAY OF CHAR): BOOLEAN;

Db.Prepare

Create a prepared sql statement for further processing. Return NIL on failure.

PROCEDURE (this : Db) Prepare*(sql- : ARRAY OF CHAR): Stmt;

Stmt.BindLongReal

Bind LONGREAL value to column col. Return TRUE on success.

PROCEDURE (this : Stmt) BindLongReal*(col : LONGINT; value : LONGREAL): BOOLEAN;

Stmt.BindInt

Bind LONGINT value to column col. Return TRUE on success.

PROCEDURE (this : Stmt) BindInt*(col : LONGINT; value : LONGINT): BOOLEAN;

Stmt.BindInt64

Bind LONGLONGINT value to column col. Return TRUE on success.

PROCEDURE (this : Stmt) BindInt64*(col : LONGINT; value : LONGLONGINT): BOOLEAN;

Stmt.BindText

Bind string value to column col. Return TRUE on success.

PROCEDURE (this : Stmt) BindText*(col : LONGINT; value- : ARRAY OF CHAR): BOOLEAN;

Stmt.BindNull

Bind SQLite NULL value to column col. Return TRUE on success.

PROCEDURE (this : Stmt) BindNull*(col : LONGINT): BOOLEAN;

Stmt.Step

Evaluate the prepared statement and return status.

DONE if finished.

ROW if further rows exists.

Any other value indicate an error.

PROCEDURE (this : Stmt) Step*(): LONGINT;

Stmt.Finalize

Finalize prepared statment and release resources.

PROCEDURE (this : Stmt) Finalize*(): BOOLEAN;

Stmt.Reset

Reset prepared statment for further processing.

PROCEDURE (this : Stmt) Reset*(): BOOLEAN;

Stmt.ColumnCount

Result set column count

PROCEDURE (this : Stmt) ColumnCount*(): LONGINT;

Stmt.DataCount

Result set row count

PROCEDURE (this : Stmt) DataCount*(): LONGINT;

Stmt.ColumnType

Column data type:

TINTEGER

TFLOAT

TTEXT

TBLOB

TNULL

PROCEDURE (this : Stmt) ColumnType*(col : LONGINT): LONGINT;

Stmt.ColumnInt

Return LONGINT in column col.

This function will try to cast the type and is the returned value is possible undefined.

PROCEDURE (this : Stmt) ColumnInt*(col : LONGINT): LONGINT;

Stmt.ColumnInt64

Return LONGLONGINT in column col.

This function will try to cast the type and is the returned value is possible undefined.

PROCEDURE (this : Stmt) ColumnInt64*(col : LONGINT): LONGLONGINT;

Stmt.ColumnLongReal

Return LONGREAL in column col.

This function will try to cast the type and is the returned value is possible undefined.

PROCEDURE (this : Stmt) ColumnLongReal*(col : LONGINT): LONGREAL;

Stmt.ColumnText

Return STRING in column col.

This function will try to cast the type and is expected to always succed for the STRING type.

PROCEDURE (this : Stmt) ColumnText*(VAR str : String.STRING; col : LONGINT);

Example

Basic Example

<* +MAIN *>
MODULE SQLite3BasicExample;

IMPORT SQLite3, OSStream, String;

PROCEDURE Test*();
CONST
    sql = "DROP TABLE IF EXISTS Cars;" +
        "CREATE TABLE Cars(Id INT, Name TEXT, Price INT);" +
        "INSERT INTO Cars VALUES(1, 'Audi', 52642);" +
        "INSERT INTO Cars VALUES(2, 'Mercedes', 57127);" +
        "INSERT INTO Cars VALUES(3, 'Skoda', 9000);" +
        "INSERT INTO Cars VALUES(4, 'Volvo', 29000);" +
        "INSERT INTO Cars VALUES(5, 'Bentley', 350000);" +
        "INSERT INTO Cars VALUES(6, 'Citroen', 21000);" +
        "INSERT INTO Cars VALUES(7, 'Hummer', 41400);" +
        "INSERT INTO Cars VALUES(8, 'Volkswagen', 21600);";
VAR
    db : SQLite3.Db;
    stmt : SQLite3.Stmt;
    s : String.STRING;
    ret : BOOLEAN;

    PROCEDURE Error();
    BEGIN
        s := db.ErrorMsg();
        OSStream.stdout.Format("Error : '%s'\n", s^);
        HALT;
    END Error;
BEGIN
    NEW(db);
    IF ~db.Open("") THEN Error() END;
    IF ~db.Execute(sql) THEN Error() END;

    stmt := db.Prepare("SELECT * from Cars WHERE Price > ?;");
    IF stmt = NIL THEN Error() END;

    ret := stmt.BindLongReal(1, 9000.);
    IF ~ret THEN Error() END;

    OSStream.stdout.Format("ID      Name    Price  \n");
    OSStream.stdout.Format("------------------------\n");
    WHILE stmt.Step() # SQLite3.DONE DO
        stmt.ColumnText(s, 1);
        OSStream.stdout.Format("%02d % 12s % 8d\n", stmt.ColumnInt(0), s^, stmt.ColumnInt(2));
    END;
END Test;

BEGIN
    Test();
END SQLite3BasicExample.