Contact: zeng  @  zegraph.com      Last update: September 2016

SQLITE Library

The SQLITE works directly on database files. This makes it very easy to manage SQL database by a stand-alone computer. Although the library includes only a few functions, they are enough to perform nearly all SQL operations supported by SQLITE.

Function Parameter Type Remark
sqlite(filename) string Opens the database file and returns a SQLITE object. If the file does not exist, the function tries to create the file.
.blob(sql, ptr, size) string, user, integer Stores a blob data pointed to by ptr of size. The sql argument must be a string of valid "insert" query statement and the blob variable value must be represented by a question marck (?).
.blob(sql, fname) string, string Extracts blob data and calls the fname function with three arguments: the row number, the number of bytes, and the pointer to the data. The sql argument must be a string of valid "select" query statement.
.callback(func) string Sets a ZeScript function to handle SQL output. The callback function must handle the first argument as the index of output rows; the second as the index of columns, and the third as the content string. If the count is 0, the content string is the column names.
.error()   Returns the last error message as string.
.limit(n) integer Sets the max number of lines the a query may produce. Use n=0 to remove the limit..
.missing()   Returns the missing value used to fill the matrix for missing values.
.nrow()   Returns the number of rows of the query results.
.ncol()   Returns the number of columns of the query results.
.NULL(flag) boolean Sets the flag for using "NULL" (flag=true) or "" as output for NULL values.
.pack([flag]) boolean Returns an string containing all query results if the flag is false; otherwise an array containing the pointer to the zipped results and the size of the pointer.
.ptr()   Returns an array containing the pointer to the matrix that holds the query results, the number of elements in the matrix, and the number of bytes of an element.
.query(str[, flag]) string, boolean
Performs SQL query. Returns true if no error or false otherwise. If the optional flag is set to 1, the results will be saved in an array of strings; if set to 2, the results will be saved in a double-floating pointer; and if set to 3, the results will be saved in a single string.
.reset()   Resets the opened database.
.version()   Returns the SQLITE version as string.
.__get(i[, j]) integers Returns the item at the ith row and the jth column. If the optional parameter j is not given, it retunrns the ith row.

Example

load("sqlite.dll");

sql = sqlite("test.sql");

sql.query("create table mytable(first_name, last_name, address);");

// strings must be qouted
sql.query("insert into mytable values(\"Jiye\", \"Zeng\", \"123 ABC street\");");
sql.query("insert into mytable values(\"Georgy\", \"Bush\", \"123 USA street\");");

// numbers can be inserted directly
sql.query("insert into mytable values(123, 456, \"numerical street\");");

// output to diaplay by default
sql.query("select * from mytable;");

// control output by callback function
sql.callback("callback");
sql.query("select * from mytable;");

function callback(row, col, val)
{
csv(row, col, val);
}