Methods for Prepared Statements
After creating a prepared statement with db:prepare the returned statement object should be used for all further calls in connection with that statement. Statement objects support the following methods.
stmt:bind(n[,value])
Binds value to statement parameter n. If the type of value is string or number, it is bound as text or DOUBLE, respectively. If value is a boolean or nil or missing, any previous binding is removed. The function returns sqlite3.OK on success or else a numerical error code (see Numerical Error and Result Codes ).
stmt:bind_blob(n,blob)
Binds string blob (which can be a binary string) as a blob to statement parameter n. The function returns sqlite3.OK on success or else a numerical error code (see Numerical Error and Result Codes ).
stmt:bind_names(nametable)
Binds the values in nametable to statement parameters. If the statement parameters are named (i.e., of the form ":AAA" or "$AAA") then this function looks for appropriately named fields in nametable; if the statement parameters are not named, it looks for numerical fields 1 to the number of statement parameters. The function returns sqlite3.OK on success or else a numerical error code (see Numerical Error and Result Codes ).
stmt:bind_parameter_count()
Returns the largest statement parameter index in prepared statement stmt. When the statement parameters are of the forms ":AAA" or "?", then they are assigned sequentially increasing numbers beginning with one, so the value returned is the number of parameters. However if the same statement parameter name is used multiple times, each occurrence is given the same number, so the value returned is the number of unique statement parameter names.
If statement parameters of the form "?NNN" are used (where NNN is an integer) then there might be gaps in the numbering and the value returned by this interface is the index of the statement parameter with the largest index value.
stmt:bind_parameter_name(n)
Returns the name of the n-th parameter in prepared statement stmt. Statement parameters of the form ":AAA" or "@AAA" or "$VVV" have a name which is the string ":AAA" or "@AAA" or "$VVV". In other words, the initial ":" or "$" or "@" is included as part of the name. Parameters of the form "?" or "?NNN" have no name. The first bound parameter has an index of 1. If the value n is out of range or if the n-th parameter is nameless, then nil is returned. The function returns sqlite3.OK on success or else a numerical error code (see Numerical Error and Result Codes ).
stmt:bind_values(value1,value2,...,valueN)
Binds the given values to statement parameters. The function returns sqlite3.OK on success or a numerical error code otherwise (see Numerical Error and Result Codes ).
stmt:columns()
Returns the number of columns in the result set returned by statement stmt or 0 if the statement does not return data (for example, an UPDATE).
stmt:finalize()
This function frees prepared statement stmt. If the statement was executed successfully, or not executed at all, then sqlite3.OK is returned. If execution of the statement failed then an error code is returned.
stmt:get_name(n)
Returns the name of column n in the result set of statement stmt. (The left-most column is number 0.)
stmt:get_named_types()
Returns a table with the names and types of all columns in the result set of statement stmt.
stmt:get_named_values()
This function returns a table with names and values of all columns in the current result row of a query.
stmt:get_names()
This function returns an array with the names of all columns in the result set returned by statement stmt.
stmt:get_type(n)
Returns the type of column n in the result set of statement stmt. (The left-most column is number 0.)
stmt:get_types()
This function returns an array with the types of all columns in the result set returned by statement stmt.
stmt:get_unames()
This function returns a list with the names of all columns in the result set returned by statement stmt.
stmt:get_utypes()
This function returns a list with the types of all columns in the result set returned by statement stmt.
stmt:get_uvalues()
This function returns a list with the values of all columns in the current result row of a query.
stmt:get_value(n)
Returns the value of column n in the result set of statement stmt. (The left-most column is number 0.)
stmt:get_values()
This function returns an array with the values of all columns in the result set returned by statement stmt.
stmt:isopen()
Returns TRUE if stmt has not yet been finalized, FALSE otherwise.
stmt:nrows()
Returns an function that iterates over the names and values of the result set of statement stmt. Each iteration returns a table with the names and values for the current row. This is the prepared statement equivalent of db:nrows .
stmt:reset()
This function resets SQL statement stmt, so that it is ready to be re-executed. Any statement variables that had values bound to them using the stmt:bind*() functions retain their values.
stmt:rows()
Returns a function that iterates over the values of the result set of statement stmt. Each iteration returns an array with the values for the current row. This is the prepared statement equivalent of db:rows .
stmt:step()
This function must be called to evaluate the (next iteration of the) prepared statement stmt. It will return one of the following values:
sqlite3.BUSY: the engine was unable to acquire the locks needed. If the statement is a COMMIT or occurs outside of an explicit transaction, then you can retry the statement. If the statement is not a COMMIT and occurs within a explicit transaction then you should rollback the transaction before continuing.
sqlite3.DONE: the statement has finished executing successfully. stmt:step() should not be called again on this statement without first calling stmt:reset to reset the virtual machine back to the initial state.
sqlite3.ROW: this is returned each time a new row of data is ready for processing by the caller. The values may be accessed using the column access functions. stmt:step() can be called again to retrieve the next row of data.
sqlite3.ERROR: a run-time error (such as a constraint violation) has occurred. stmt:step() should not be called again. More information may be found by calling db:errmsg . A more specific error code (can be obtained by calling stmt:reset .
sqlite3.MISUSE: the function was called inappropriately, perhaps because the statement has already been finalized or a previous call to stmt:step() has returned sqlite3.ERROR or sqlite3.DONE.
stmt:urows()
Returns a function that iterates over the values of the result set of statement stmt. Each iteration returns the values for the current row. This is the prepared statement equivalent of db:urows .