prepare-statement

prepare-statement Function

Summary

Returns a prepared-statement object for a sql-exp in a database.

Package

sql

Signature

prepare-statement sql-exp &key database variable-types count flatp result-types => prepared-statement

Arguments

`sql-exp`⇩	A SQL expression.
`database`⇩	A database.
`variable-types`⇩	A list.
`count`⇩	A non-negative integer or `nil`.
`flatp`⇩	A boolean.
`result-types`⇩	A list of symbols.

Values

prepared-statement

A prepared-statement.

Description

The function prepare-statement returns a prepared-statement object for the SQL statement sql-exp in the database database. sql-exp can contain bind-variables in the form :n where n is a positive integer.

If database is supplied, then the prepared-statement is associated with the database. Otherwise set-prepared-statement-variables will do the association even if it is called without a database.

If variable-types is supplied, then it should be a list containing a keyword element for each bind-variable in sql-exp. It has an effect in two cases:

:string forces the variable to be passed to the database as a string. That may be useful if you have numeric values in Lisp which are stored as strings in the database.
:date cause an integer to be interpreted as a universal-time and be converted properly to an Oracle date. This is not supported on SQLite databases, which do not support date fields.

If variable-types is not supplied, then the types will be chosen dynamically from the values passed to set-prepared-statement-variables.

If count is supplied, then it should equal the maximum number of bind-variables in the sql-exp. If count is not supplied, then it is calculated from sql-exp.

flatp and result-types are interpreted the same as in select.

The result of prepare-statement is a prepared-statement. This can be used by calling set-prepared-statement-variables to actually bind the variables, and then use one of the querying or executing interfaces that take a SQL expression argument: execute-command, query, do-query, simple-do-query, map-query and the loop for...being each record construct.

A prepared-statement that is associated with a database should be destroyed (by destroy-prepared-statement) before the database is closed, otherwise it may leak memory.

Notes

sql-exp can be any valid SQL expression, not only a query.

Examples

Create a prepared-statement for a SQL expression:

(setq ps
      (sql:prepare-statement
       "insert into TABLETWO values(:1, :2)"))

Then insert records into TABLETWO (which has two columns) by repeatedly doing:

(sql:set-prepared-statement-variables
  ps
  (list value1 value2))
 
(sql:execute-command ps))