SQLite¶
Simba has support for reading and manipulating SQLite databases. Open connections are all represented by integers in scripts. The integers point to an index in an internal array of pointers which is managed by Simba. sqlite_open and function sqlite_open_v2 return an integer that you use for most other functions. This page documents only the functions, and not SQLite or the SQL language. After opening a connection, you should use sqlite_close on it when you are no longer using it. If, however, for some reason you forget, Simba will free all unfreed connections automatically.
Functions¶
sqlite_open¶
function sqlite_open(filename : string) : integer;
Will try to open a connection to the database file specified and returns an index to the internal array. If the database file does not exist, it will attempt to create it. This will return -1 if no connection could be established.
Example:
DB := sqlite_open('test.db'); // DB would be an integer that you pass as the index parameter to the other methods.
sqlite_open_v2¶
function sqlite_open_v2(filename : string; flags : integer) : integer;
Does the same as sqlite_open however you can provide flags to use when opening.
Open flags:
const
SQLITE_OPEN_READONLY = 1;
SQLITE_OPEN_READWRITE = 2;
SQLITE_OPEN_CREATE = 4;
SQLITE_OPEN_URI = 40;
SQLITE_OPEN_NOMUTEX = 8000;
SQLITE_OPEN_FULLMUTEX = 10000;
SQLITE_OPEN_SHAREDCACHE = 20000;
SQLITE_OPEN_PRIVATECACHE = 40000;
Example:
DB := sqlite_open_v2('test.db', SQLITE_OPEN_READONLY); // Open as read only, will not create file.
DB := sqlite_open_v2('test.db', SQLITE_OPEN_READWRITE or SQLITE_OPEN_CREATE); // Open as read write, will create file.
sqlite_version¶
function sqlite_version() : string;
Returns the version of the loaded SQLite library expressed as a string (x.y.z).
Writeln(sqlite_version()); // Outputs 3.7.10 for me
sqlite_version_num¶
function sqlite_version_num() : integer;
Returns the version of the loaded SQLite library expressed as an integer (x * 1000000 + y * 1000 + x).
Example:
Writeln(sqlite_version_num()); // Outputs 3007010 for me
sqlite_query¶
function sqlite_query(index : integer; sql : string) : boolean;
Attempts to execute a query on the database handle specified by index. Returns true if SQLITE_OK is returned by SQLite. If it returns false, it is useful to see what sqlite_errMsg outputs.
Example:
sqlite_query(DB, 'CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(50) UNIQUE NOT NULL);');
sqlite_query(DB, 'INSERT INTO test (name) VALUES (''Sex'');');
sqlite_queryValue¶
function sqlite_queryValue(index : integer; sql : string; out results : T2DStringArray) : boolean;
Attempts to execute a query on the database handle specified by index. Return true if SQLITE_OK is returned by SQLite. This will also save the resulting rows in the Results variable provided. The first array will be an array containing column names. If it returns false, it is useful to see what sqlite_errMsg outputs.
Example:
sqlite_queryValue(DB, 'SELECT * FROM test;', Results);
Writeln(Results); // Should output [['id', 'name'], ['1', 'Sex']]
sqlite_queryResult¶
function sqlite_queryResult(index : integer; sql : string; var error : boolean) : T2DStringArray;
Attempts to execute a query on the database handle specified by index. The resulting rows are returned. If an error occurred during the query, the error boolean will be set to true. Otherwise, it will be false.
Example:
Results := sqlite_queryResult(DB, 'SELECT * FROM test;', error);
if error then
[...] // do your error handling here...
Writeln(Results); // Should output [['id', 'name'], ['1', 'Sex']]
sqlite_escape¶
function sqlite_escape(s : string): string;
Sanitizes a string for input into the database by replacing apostrophes with anothe apostrophe. It will return the escaped string.
Example:
Writeln(sqlite_escape('foo '' or 1=1')); // Outputs foo '' or 1=1. Note that it looks as I inputted it as SQLite uses the same escaping conventions for apostrophes as Pascal.
sqlite_close¶
procedure sqlite_close(index : integer);
Closes the database handle specified by index (removing file locks, etc.). Don’t forget to use this when you’re done working on a database!
Example:
DB := sqlite_open('test.db');
// [...]
sqlite_close(DB);
sqlite_errMsg¶
function sqlite_errMsg(index : integer) : string;
Returns the error message returned by the last SQLite library call. You must provide an index to a database handle. If no error has occurred, this will return ‘not an error’.
Example:
sqlite_query(DB, 'asdfghjkl');
Writeln(sqlite_errmsg(DB)); // near "asdfghjkl": syntax error
sqlite_errCode¶
function sqlite_errCode(index : integer) : integer;
Returns the result code returned by the last SQLite library call. You must provide an index to a database handle. If no error has occurred, this will return SQLITE_OK.
Result codes:
const
SQLITE_OK = 0; // Successful result
SQLITE_ERROR = 1; // SQL error or missing database
SQLITE_INTERNAL = 2; // Internal logic error in SQLite
SQLITE_PERM = 3; // Access permission denied
SQLITE_ABORT = 4; // Callback routine requested an abort
SQLITE_BUSY = 5; // The database file is locked
SQLITE_LOCKED = 6; // A table in the database is locked
SQLITE_NOMEM = 7; // A malloc() failed
SQLITE_READONLY = 8; // Attempt to write a readonly database
SQLITE_INTERRUPT = 9; // Operation terminated by sqlite3_interrupt()
SQLITE_IOERR = 10; // Some kind of disk I/O error occurred
SQLITE_CORRUPT = 11; // The database disk image is malformed
SQLITE_NOTFOUND := 12; // Unknown opcode in sqlite3_file_control()
SQLITE_FULL := 13; // Insertion failed because database is full
SQLITE_CANTOPEN := 14; // Unable to open the database file
SQLITE_PROTOCOL = 15; // Database lock protocol error
SQLITE_EMPTY = 16; // Database is empty
SQLITE_SCHEMA = 17; // The database schema changed
SQLITE_TOOBIG = 18; // String or BLOB exceeds size limit
SQLITE_CONSTRAINT = 19; // Abort due to constraint violation
SQLITE_MISMATCH = 20; // Data type mismatch
SQLITE_MISUSE = 21; // Library used incorrectly
SQLITE_NOLFS = 22; // Uses OS features not supported on host
SQLITE_AUTH = 23; // Authorization denied
SQLITE_FORMAT = 24; // Auxiliary database format error
SQLITE_RANGE = 25; // 2nd parameter to sqlite3_bind out of range
SQLITE_NOTADB = 26; // File opened that is not a database file
SQLITE_ROW = 27; // sqlite3_step() has another row ready
SQLITE_DONE = 28; // sqlite3_step() has finished executing
Example:
sqlite_query(DB, 'asdfghjkl');
Writeln(sqlite_errmsg(DB)); // 1 (SQLITE_ERROR)