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
))

See also

query
do-query
simple-do-query
map-query
select
set-prepared-statement-variables
destroy-prepared-statement