with-prepared-statement

with-prepared-statement Macro

Summary

Execute code with a variable bound to a prepared-statement and destroys it afterwards.

Package

sql

Signature

with-prepared-statement (ps-var sql-exp &key database variable-types count flatp result-types) &body body => results*

Arguments

`ps-var`⇩	A symbol.
`sql-exp`⇩	An SQL expression.
`database`⇩	A database or `nil`.
`variable-types`⇩	A list.
`count`⇩	A non-negative integer or `nil`.
`flatp`⇩	A boolean.
`result-types`⇩	A list of symbols.
`body`⇩	Lisp forms.

Values

results*

The results of executing body.

Description

The macro with-prepared-statement binds ps-var to a new prepared-statement, executes the forms of body, destroys the prepared-statement, and returns the values that body returns.

The prepared-statement is created by calling prepare-statement, passing it sql-exp and any of database, variable-types, count, flatp and result-types that are supplied.

Examples

The following code shows insertion of multiple records using a prepared statement.

(progn
  (when (sql:table-exists-p "a_table_of_squares")
    (sql:drop-table "a_table_of_squares"))
  (sql:execute-command "create table a_table_of_squares (num integer, square_of_num integer)")
 
  (sql:with-prepared-statement (ps "insert into a_table_of_squares values (:1, :2)")
    (dotimes (x 10)
      (sql:prepared-statement-set-and-execute ps x (* x x))))
 
  ;; check it
  (pprint (sql:query "select * from a_table_of_squares")))