sqlite3_prepare
| Definition: |
include pSQLite.e
sqlite3_stmt pStmt = sqlite3_prepare(sqlite3 db, string cmd) |
| Description: |
The sqlite3_prepare interface compiles a single SQL statement into byte code for later execution. This interface is now the preferred way of accessing the database [but see comments]. db: an open database, a result from sqlite3_open(). cmd: An SQL statement (a UTF-8 string) to be prepared. Only the first SQL statement in the input string is compiled. Returns NULL if the cmd string contained no SQL (an empty string or a comment), otherwise it returns an opaque pointer to an internal structure as used by the routines mentioned below. Non-atom values may be returned if SQLITE3_NON_FATAL or a similar user provided error handler routine is in force. |
| pwa/p2js: | Not supported |
| Comments: |
To execute an SQL query, it must first be compiled into a byte-code program using sqlite3_prepare(). That happens automatically within sqlite3_exec() and sqlite3_get_table(). You can probably do everything you need with those routines, and live a long and happy life without knowing that sqlite3_prepare() even exists. If the sqlite3_prepare() process seems much more complicated, that’s because it is - you should only use it if the ability to set and extract individual fields somehow simplifies whatever it is you are trying to do. There may be a performance gain when compiling very large and complex SQL statements once rather than repeatedly, but for most, say less that 250 characters, any such gain is probably neglible. On the other hand, constructing thousands of 250+ character strings, as well as then parsing/compiling them, is likely to be a more significant overhead, compared to using the sqlite3_bind() routines. I might also hazard (without any proof) that sqlite3_prepare() is more likely to offer savings on large numbers of write operations rather than read operations, but that judgement may be clouded by preconceived notions of typical usage patterns. The SQL statement may contain tokens of the form "?" or "?nnn" or ":nnn:" where "nnn" is an integer, or ":AAA" where AAA is an alphanumeric name. You can also use/may encounter "@AAA", and "$AAA", as a hangover from SQLite’s Tcl origins, which behave similarly to ":AAA". Such tokens represent unspecified literal values (or wildcards) to be filled in later by the sqlite3_bind interface. Each wildcard has an associated number given by the "nnn" that follows the "?". If the "?" is not followed by an integer, then it is numbered one more than the number of prior wildcards in the same SQL statement. It is allowed for the same wildcard to occur more than once in the same SQL statement, in which case all instances of that wildcard will be filled in with the same value. Unbound wildcards have a value of NULL. The mixing of named, numbered and un-numbered wildcards is undertaken at your own risk. There are no detailed specifications or guarantees for how such things might actually work out, however there are almost certainly cases such as values for the last 72 hours, best handled by locating [1] by name, and using offsets from that for the anonymous [2..72]. [PL: were it down to me, I’d make say ?4 a fatal error were it neither the 4th field, nor a second/later reference to an already defined ?4.] Obviously named wildcards promise the best clarity and lowest maintenance costs, followed by numbered, and unnumbered should only be used when to do otherwise is simply just too tedious. Of course named wildcards also get assigned a number, but it is sheer madness, on anything not utterly trivial, to use hard-coded numeric literals in the subsequent code, or squint at an SQL statement tying to guess the numbers and meanings of un-named things. After an SQL statement has been prepared and optionally bound, it is executed using sqlite3_step(pStmt). The sqlite3_reset() routine resets a prepared SQL statement so that it can be executed again. The sqlite3_finalize() routine deallocates a prepared SQL statement. All prepared statements must be finalized before the database can be closed. |
| Example: |
include pSQLite.e
?sqlite3_libversion()
sqlite3 db = sqlite3_open("test.sqlite")
integer res
procedure db_exec(string cmd, bool fatal=true)
res = sqlite3_exec(db,cmd)
if fatal and res!=SQLITE_OK then ?9/0 end if
end procedure
-- nb "IF NOT EXISTS" is only supported in 3.3.0 and later (2006-01-11)
--db_exec("CREATE TABLE IF NOT EXISTS test(id INTEGER, desc TEXT);")
--db_exec("DELETE FROM test;")
-- whereas this approach works fine on earlier versions:
db_exec("DROP TABLE test;",fatal:=false)
db_exec("CREATE TABLE test(id INTEGER, desc TEXT);")
constant descs = {"one","two","three"}
sqlite3_stmt pStmt = sqlite3_prepare(db,"INSERT INTO test VALUES(:id, :desc);")
constant k_id = sqlite3_bind_parameter_index(pStmt, ":id"),
k_desc = sqlite3_bind_parameter_index(pStmt, ":desc")
db_exec("BEGIN TRANSACTION;")
for i=1 to 3 do
sqlite3_bind_int(pStmt, k_id, i)
sqlite3_bind_text(pStmt,k_desc, descs[i])
res = sqlite3_step(pStmt)
if res!=SQLITE_DONE then ?9/0 end if
sqlite3_reset(pStmt)
end for
if sqlite3_finalize(pStmt)!=SQLITE_OK then ?9/0 end if
db_exec("END TRANSACTION;")
pStmt = sqlite3_prepare(db,"SELECT id,desc FROM test;")
while 1 do
res = sqlite3_step(pStmt)
if res=SQLITE_DONE then exit end if
if res!=SQLITE_ROW then ?9/0 end if
?"row:"
for c=1 to sqlite3_column_count(pStmt) do
integer ctype = sqlite3_column_type(pStmt,c)
string name = sqlite3_column_name(pStmt,c)
if ctype=SQLITE_INTEGER then
?{"SQLITE_INTEGER",name,sqlite3_column_int(pStmt,c)}
elsif ctype=SQLITE_TEXT then
?{"SQLITE_TEXT",name,sqlite3_column_text(pStmt,c)}
elsif ctype=SQLITE_FLOAT then
?{"SQLITE_FLOAT",name,sqlite3_column_double(pStmt,c)}
else
?9/0
end if
end for
end while
if sqlite3_finalize(pStmt)!=SQLITE_OK then ?9/0 end if
sqlite3_close(db)
-- output:
-- "3.23.1"
-- "row:"
-- {"SQLITE_INTEGER","id",1}
-- {"SQLITE_TEXT","desc","one"}
-- "row:"
-- {"SQLITE_INTEGER","id",2}
-- {"SQLITE_TEXT","desc","two"}
-- "row:"
-- {"SQLITE_INTEGER","id",3}
-- {"SQLITE_TEXT","desc","three"}
|