SQLite Module Function Reference
sqlite_new
sqlite_open
sqlite_get_table
Synopsis
Get a table of results from a SQLite query
Usage
String_Type[] sqlite_get_table(Sqlite_Type db, String_Type query)
Description
This executes a query and returns the result as a 2d array of strings.
The first row of the array contains the column headers. This function does
not support placeholders.
Notes
You should only use this function if you need the column headers.
Otherwise, use
sqlite_get_array
See Also
sqlite_open, sqlite_get_array, sqlite_get_row, sqlite_exec, sqlite_changes
sqlite_get_row
Synopsis
Get a row of results from a SQLite query
Usage
sqlite_get_row(Sqlite_Type db, String_Type query, ...)
Description
This executes a query and pushes the elements of the first row of the
result on the stack. This supports string, integer, float and blob
datatatypes. Blobs are returned as bstrings. If there are no rows, a
SqliteError exception is thrown even if the query executed flawlessly.
Question marks in the query are placeholders. Extra arguments to the
function are bound to these placeholders from left to right.
Example
(foo, bar) = sqlite_get_row("SELECT foo, bar FROM table WHERE baz = ?", "quux");
Notes
To get integers greater than INT_MAX, use
sqlite_get_array with a
LLONG_TYPE type.
To get the result of a query that returns multiple rows, use
sqlite_get_array or use
foreach foo, bar (db) using ("SELECT foo, bar FROM table WHERE baz = ?", "quux")
{
....
}
Or in the object-oriented interface:
foreach foo, bar (db.db) using ("SELECT foo, bar FROM table WHERE baz = ?", "quux")
{
....
}
See Also
sqlite_open, sqlite_get_table, sqlite_get_array, sqlite_exec, sqlite_changes
sqlite_get_array
Synopsis
Get a 2-D array from a SQLite query
Usage
Array_Type sqlite_get_array(Sqlite_Type db, DataType_type type, String_Type query, ...)
Description
Executes a query and returns the result as a 2d array of type
type.
This supports string, integer, long long, float and blob datatatypes.
Question marks in the query are placeholders. Extra arguments to the
function are bound to these placeholders from left to right.
See Also
sqlite_open, sqlite_get_table, sqlite_get_row, sqlite_exec, sqlite_changes
sqlite_exec
Synopsis
Execute a SQLite query
Usage
sqlite_exec(Sqlite_Type db, String_Type query, ...)
Description
Execute a SQL query on a sqlite database, without returning a result.
Question marks in the query are placeholders. Extra arguments to the
function are bound to these placeholders from left to right.
Example
sqlite_exec(db, "INSERT INTO table(foo,bar,baz) VALUES (?,?,?)", 1, 2, 3);
Or in the object-oriented interface:
db.exec("INSERT INTO table(foo,bar,baz) VALUES (?,?,?)", 1, 2, 3);
See Also
sqlite_open, sqlite_get_table, sqlite_get_array, sqlite_get_row, sqlite_changes
sqlite_changes
Synopsis
Get the number of rows affected by a SQLite query
Usage
Int_Type sqlite_changes(Sqlite_Type db)
Description
This function returns the number of database rows that were changed (or
inserted or deleted) by the most recently completed INSERT, UPDATE, or
DELETE statement. The change count for "DELETE FROM table" will be zero
regardless of the number of elements that were originally in the table.
See Also
sqlite_open, sqlite_get_table, sqlite_get_array, sqlite_get_row, sqlite_exec
sqlite_prepare
Synopsis
create a SQLite prepared statement
Usage
Sqlite_Statement_Type sqlite_prepare(Sqlite_Type db, String query)
Description
Prepare a SQL query.
Example
stmt = sqlite_prepare(db, "INSERT INTO table(foo,bar,baz) VALUES (?,?,?)");
stmt will now hold a Sqlite_Statement_Type.
Or using the object-oriented interface:
stmt = db.prepare("INSERT INTO table(foo,bar,baz) VALUES (?,?,?)");
stmt will now hold a struct with, a field
stmt holding the
Sqlite_Statement_Type, a field
db holding the Database object, and the
methods
bind_params
step
fetch
reset
Notes
In the procedural interface, you should destroy all prepared statements before
the database goes out of scope. Otherwise the database will not be
closed. In the object-oriented interface, this is ensured by the Statement
object holding a reference to the Database object.
See Also
sqlite_bind_params, sqlite_step, sqlite_fetch, sqlite_reset
sqlite_bind_params
Synopsis
bind values to a prepared statement
Usage
int sqlite_bind_params(Sqlite_Statement_Type, ..)
Description
Bind values to a prepared statement. Arguments are bound to placeholders
in the statement from left to right. If called with fewer arguments than
the prepared statement has placeholders, the remaining bindings are not modified.
Parameter bindings are not affected by
sqlite_reset.
Example
stmt = sqlite_prepare(db, "insert into table(foo, bar) values (?,?)");
sqlite_bind_params(stmt, "foo", "bar");
slite_bind_params(stmt, "baz");
The first parameter will now be set to ``baz'', the second to ``bar''.
The object-oriented method
bind_params also supports named
parameters as qualifiers.
stmt = db.prepare("insert into table(foo, bar) values (:foo,:bar)");
stmt.bind_params(stmt, "foo", "bar");
stmt.bind_params(; bar = "baz");
Now the first parameter will be ``foo'', the second will be ``baz''.
Notes
This function must be called after
sqlite_prepare or
sqlite_reset and before
sqlite_step. Bindings are not
cleared by the
sqlite_reset routine. Unbound parameters are
interpreted as NULL.
See Also
sqlite_prepare, sqlite_bind_param, sqlite_step, sqlite_fetch, sqlite_reset
sqlite_bind_param
Synopsis
bind a value to a prepared statement
Usage
int sqlite_bind_params(Sqlite_Statement_Type, Int_Type n, value)
Description
In the SQL strings input to
sqlite_prepare, literals may be
replaced by a parameter in one of these forms:
?
?NNN
:VVV
@VVV
$VVV
In the parameter forms shown above NNN is an integer literal, VVV is an
alpha-numeric parameter name. The values of these parameters can be set
using the
sqlite_bind_param function.
The first argument to
sqlite_bind_param is a prepared statement
returned by
sqlite_prepare. The second argument is the index of
the parameter, counting from 1. When the same named parameter is used
more than once, second and subsequent occurrences have the same index as
the first occurrence. The index for named parameters can be looked up
using the function
sqlite_bind_parameter_index if desired. The
index for ``?NNN'' parameters is the value of NNN.
Notes
You can set all parameters at once with
sqlite_bind_params. To set
a specific parameter, you can use named parameters of the ``:VVV'' form
with the object method
bind_params.
See Also
sqlite_prepare, sqlite_bind_params, sqlite_bind_parameter_index, sqlite_step, sqlite_fetch, sqlite_reset
sqlite_bind_parameter_index
Synopsis
Return the index of an SQL parameter given its name
Usage
int sqlite_bind_parameter_index(Sqlite_Statement_Type S, String_Type N)
Description
The
sqlite_bind_parameter_index function returns the index of the
SQL parameter in prepared statement
S whose name matches the string
N,
or 0 if there is no match.
See Also
sqlite_bind_param
sqlite_step
Synopsis
evaluate a prepared statement
Usage
int sqlite_step(Sqlite_Statement_Type, ..)
Description
This function evaluates a prepared statement.
This function returns a SQLite return code. If the query has completed, it
returns SQLITE_DONE (101). If the query has a result row ready, it returns
SQLITE_ROW (100). If SQLite returns an error code, this function throws a
SqliteError.
Notes
If you want to execute a query and iterate over the results, use
stmt = db.prepare("sql");
stmt.bind_params("foo", "bar")
while (SQLITE_ROW == stmt.step())
{
result = stmt.fetch();
}
Or
foreach result (db.db) using ("sql", "foo", "bar")
{
...
}
If you want to execute a query multiple times with different parameters,
you would use
stmt = db.prepare("sql");
foreach param (array)
{
stmt.bind_params(param);
()=stmt.step());
stmt.reset();
}
See Also
sqlite_prepare, sqlite_bind_params, sqlite_fetch, sqlite_reset
sqlite_fetch
Synopsis
Get a row of results from a prepared statement
Usage
sqlite_fetch(Sqlite_Statement_Type)
Description
This pushes the elements of the current result row of a prepared statement
on the stack.
See Also
sqlite_prepare, sqlite_step, sqlite_reset, sqlite_get_row
sqlite_reset