SQLite Forum

Prepared Statements
Login

Prepared Statements

(1) By anonymous on 2021-01-26 09:21:33 [link]

I have a number of lengthy queries stored in a table by a unique name. <i>(This removes verbosity/clutter from the calling application)</i>

At runtime, an application requests the execution of a particular statement by name. For this I use the sequence

```
sqlite3_open
       sqlite3_prepare_v2
       sqlite3_step
       sqlite3_finalize
sqlite3_close       
```

A given query may or may not require parameters specified at runtime.

1. Is there an API to be called after sqlite_prepare_v2 that indicates  whether I should use the sqlite3_bind_* APIs <i><u>before</u></i> calling sqlite3_step? 
2. And, how many parameters I should expect?

and finally,

3. Is it possible to save a prepared statement and (may be) use it with sqlite3_exec?

<i>(As a workaround, I know that I can add more columns to the table to indicate whether parameters are  required with 0 = none required, n = n parameters required).</i>

<sup>Aask</sup>

(2) By Keith Medcalf (kmedcalf) on 2021-01-26 10:18:38 in reply to 1 [link]

> A given query may or may not require parameters specified at runtime.

 > - Is there an API to be called after sqlite_prepare_v2 that indicates whether I should use the sqlite3_bind_* APIs before calling sqlite3_step?  
 > - And, how many parameters I should expect?

<https://sqlite.org/c3ref/bind_parameter_count.html>

 > Is it possible to save a prepared statement and (may be) use it with sqlite3_exec?

No.

(3) By anonymous on 2021-01-26 10:23:54 in reply to 2 [link]

Perfect. Thank you.

(4) By Gunter Hick (gunter_hick) on 2021-01-26 10:30:50 in reply to 1

Even when you have determined the maximum number of parameters (there may be unused parameter numbers), you still need to determine which values to bind to which parameter and which of the bind routines to call.

A prepared statment is an SQL Program that implements a query plan against a specific schema. It may become invalid when the schema is changed - which is why SQLite also stores the text of the query and automatically re-prepares it after a schema change (if the V2 version of prepare() is called).

AFAIK none of the previous posters who asked about saving prepared statements for later use has ever reported back that whatever they attempted actually worked.

(5) By anonymous on 2021-01-26 11:08:05 in reply to 4 [link]

><b>SQLite also stores the text of the query</b> and automatically re-prepares it after a schema change

Is the text stored <i>after</i> the parameter substitutions or is it the version <i>before</i> preparation?

It might be

<u>When</u>|<u>Why?</u>
<b>after</b>|since the next API will be sqlite3_exec to execute
<b>before</b>|if <i> SQLite3 automatically re-prepares it after a schema change </i> applies.

Which?

(6) By Keith Medcalf (kmedcalf) on 2021-01-26 11:36:40 in reply to 5 [link]

Which part of "the SQL text so it can be re-prepared if the schema changes" was unclear?

If "diddled" SQL text was used then the statement could not be re-prepared.

<https://sqlite.org/c3ref/expanded_sql.html>

but since you already have prepared the statement and already bound the parameters, why would you want to go to all the bother and not just execute the statement using the standard sqlite3_step loop?

(7) By Gunter Hick (gunter_hick) on 2021-01-26 12:49:22 in reply to 5 [link]

The sqlite3_exec() function is just a convenience interface that allows parameter free SQL queries to be executed via a single call. It calls the sqlite3_prepare(), sqlite3_step() and sqlite3_finalize() routines. For each row returned from the query, the callabck function provided to sqlite3_exec() is called (and this function may indicate if it wishes to receive further rows or not).

The purpose of the sqlite3_prepare() function is to "compile" the SQL text into "SQL engine code" - the "SQLite program" - while considering the current schema. This requires substantial effort, which can be avoided if the application knows that it will need to run exactly the same query multiple times and "hangs on" to the prepared statement.

If the difference between two queries is restricted to one or more values, then the effort of preparing several of these queries can be saved by substituting an SQL parameter in place of these values. Instead of sqlite3_prepare() compiling a constant into the SQLite program, it will compile a variable reference instead. The value of each variable can/must be set by using the sqlite3_bind() family of functions, if something other than the default of NULL is desired.

Binding a parameter does not change the text of the query. For applications that suffer from memory loss, the sqlite3_expanded_sql() interface will determine and return the "effective text" of the query, which is neither stored nor used.

(8) By anonymous on 2021-01-26 13:33:35 in reply to 5 [link]

Thanks for all the pointers (no pun intended!). With these hints and those [in this thread](https://sqlite.org/forum/forumpost/e172132da8?t=h) I've got enough to try for myself.

>The sqlite3_exec() function is just a convenience interface that allows parameter free SQL queries to be executed via a single call.

Got that [sorted](https://sqlite.org/forum/forumpost/83e1e639ac?t=h).

I need to figure out this:

>select * from myTable where id between ? and ?;select * from myTable where id between ? and ?

In a prepare...step..finalize loop, one query, repeated twice, and to be executed with 2 sets of parameters (say, 0,999 and 1000, 1999), the intention being to manage the memory overheads in the calling application i.e. retrieving 1,000 records at a time.

Or should that be <i>one</i> query

>select * from myTable where id between ? and ?;

repeated n times (with sqlite3_reset() somewhere) to be able to pass n sets of parameters?

<sup>Aask</sup>

(9) By Gunter Hick (gunter_hick) on 2021-01-26 15:28:35 in reply to 8 [link]

Somewhere after creating the connection you need to call sqlite3_prepare() passing "SELECT <fieldlist> FROM <table> WHERE rowid between ? and ?;" and keep the statement handle somewhere convenient. You should be using an explicit field list to cater for schema changes and retrieve only the fields your application requires.

Before retrieving the first set of records, you need to call sqlite3_bind_integer() to set the lower and upper bounds.

Then you can retrieve the rows in a loop by calling sqlite3_step() as long as SQLITE_ROW is returned.

When you have retrieved all the rows you want, or have exhausted the available rows (SQLITE_DONE was returned), you need to call sqlite3_reset() to re-initialize the statement.

You can now go back to binding the parameters for the next result set.

When you are done with the statement, call sqlite3_finalize() to free up the resources held by the statement.

Consider using "SELECT <fieldlist> FROM <table> WHERE rowid > ? limit ?" to cater for deletions and return a specific number of rows. This also allows you to set the window size just once.

(10) By anonymous on 2021-01-26 15:42:57 in reply to 9 [link]

>Consider using "SELECT <fieldlist> FROM <table> WHERE rowid > ? limit ?" to cater for deletions and return a specific number of rows. This also allows you to set the window size just once.

Sound (forward-looking) advice; thank you.