1 <!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.0 Transitional//EN">
5 <meta http-equiv=
"Content-Type" content=
"text/html; charset=utf-8">
6 <title>SQLITE - Sqlite package
</title>
7 <link rel=
"stylesheet" type=
"text/css" href=
"style.css">
8 <style type=
"text/css">
9 pre
{ padding:5px; background-color:#e0e0e0 }
10 h3
, h4
{ text-decoration: underline
; }
11 a
{ text-decoration: none
; padding: 1px 2px 1px 2px; }
12 a:visited
{ text-decoration: none
; padding: 1px 2px 1px 2px; }
13 a:hover
{ text-decoration: none
; padding: 1px 1px 1px 1px; border: 1px solid
#000000; }
14 a:focus
{ text-decoration: none
; padding: 1px 2px 1px 2px; border: none
; }
15 a
.none
{ text-decoration: none
; padding: 0; }
16 a
.none:visited
{ text-decoration: none
; padding: 0; }
17 a
.none:hover
{ text-decoration: none
; border: none
; padding: 0; }
18 a
.none:focus
{ text-decoration: none
; border: none
; padding: 0; }
19 a
.noborder
{ text-decoration: none
; padding: 0; }
20 a
.noborder:visited
{ text-decoration: none
; padding: 0; }
21 a
.noborder:hover
{ text-decoration: none
; border: none
; padding: 0; }
22 a
.noborder:focus
{ text-decoration: none
; border: none
; padding: 0; }
23 pre
.none
{ padding:5px; background-color:#ffffff }
34 <br> <br><h3><a name=abstract class=none
>Abstract
</a></h3>
36 <p>CL-SQLITE package is an interface to the SQLite embedded relational database engine.
</p>
38 <p>The code is in public domain so you can basically do with it whatever you want.
</p>
40 <p style='color: red;'
>This documentation describes only the CL-SQLITE package, not the SQLite database itself. SQLite documentation is available at
<a href=
"http://sqlite.org/docs.html">http://sqlite.org/docs.html
</a></p>
43 <p>CL-SQLITE together with this documentation can be downloaded from
<a href=
"http://common-lisp.net/project/cl-sqlite/releases/cl-sqlite-0.1.6.tar.gz">http://common-lisp.net/project/cl-sqlite/releases/cl-sqlite-
0.1.6.tar.gz
</a>.
</p>
45 <p>CL-SQLITE source code is available in Git repository at
<code>git://repo.or.cz/cl-sqlite.git
</code> (
<a href=
"http://repo.or.cz/w/cl-sqlite.git">gitweb
</a>) and at
<code>git://github.com/dmitryvk/cl-sqlite.git
</code> (
<a href=
"http://github.com/dmitryvk/cl-sqlite/tree/master">gitweb
</a>).
</p>
50 <br> <br><h3><a class=none
name=
"contents">Contents
</a></h3>
52 <li><a href=
"#installation">Installation
</a>
53 <li><a href=
"#example">Example
</a>
54 <li><a href=
"#usage">Usage
</a>
55 <li><a href=
"#dictionary">The SQLITE dictionary
</a>
57 <li><a href=
"#bind-parameter"><code>bind-parameter
</code></a>
58 <li><a href=
"#clear-statement-bindings"><code>clear-statement-bindings
</code></a>
59 <li><a href=
"#connect"><code>connect
</code></a>
60 <li><a href=
"#disconnect"><code>disconnect
</code></a>
61 <li><a href=
"#execute-non-query"><code>execute-non-query
</code></a>
62 <li><a href=
"#execute-one-row-m-v"><code>execute-one-row-m-v
</code></a>
63 <li><a href=
"#execute-single"><code>execute-single
</code></a>
64 <li><a href=
"#execute-to-list"><code>execute-to-list
</code></a>
65 <li><a href=
"#finalize-statement"><code>finalize-statement
</code></a>
66 <li><a href=
"#last-insert-rowid"><code>last-insert-rowid
</code></a>
67 <li><a href=
"#prepare-statement"><code>prepare-statement
</code></a>
68 <li><a href=
"#reset-statement"><code>reset-statement
</code></a>
69 <li><a href=
"#sqlite-error"><code>sqlite-error
</code></a>
70 <li><a href=
"#sqlite-constraint-error"><code>sqlite-constraint-error
</code></a>
71 <li><a href=
"#sqlite-error-code"><code>sqlite-error-code
</code></a>
72 <li><a href=
"#sqlite-error-db-handle"><code>sqlite-error-db-handle
</code></a>
73 <li><a href=
"#sqlite-error-message"><code>sqlite-error-message
</code></a>
74 <li><a href=
"#sqlite-error-sql"><code>sqlite-error-sql
</code></a>
75 <li><a href=
"#sqlite-handle"><code>sqlite-handle
</code></a>
76 <li><a href=
"#sqlite-statement"><code>sqlite-statement
</code></a>
77 <li><a href=
"#statement-bind-parameter-names"><code>statement-bind-parameter-names
</code></a>
78 <li><a href=
"#statement-column-names"><code>statement-column-names
</code></a>
79 <li><a href=
"#statement-column-value"><code>statement-column-value
</code></a>
80 <li><a href=
"#step-statement"><code>step-statement
</code></a>
81 <li><a href=
"#with-transaction"><code>with-transaction
</code></a>
82 <li><a href=
"#with-open-database"><code>with-open-database
</code></a>
84 <li><a href=
"#support">Support
</a>
85 <li><a href=
"#changelog">Changelog
</a>
86 <li><a href=
"#ack">Acknowledgements
</a>
89 <br> <br><h3><a class=none
name=
"installation">Installation
</a></h3>
91 <p>The package can be downloaded from
<a href=
"http://common-lisp.net/project/cl-sqlite/releases/cl-sqlite-0.1.6.tar.gz">http://common-lisp.net/project/cl-sqlite/releases/cl-sqlite-
0.1.6.tar.gz
</a>. CL-SQLITE package has the following dependencies:
</p>
93 <li><a href=
"http://common-lisp.net/project/cffi/">CFFI
</a></li>
94 <li><a href=
"http://common-lisp.net/project/iterate/">iterate
</a></li>
97 <p>SQLITE has a system definition for
<a href=
"http://www.cliki.net/asdf">ASDF
</a>. Compile and load it in the usual way.
</p>
100 <br> <br><h3><a class=none
name=
"example">Example
</a></h3>
103 <span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"font-weight: bold;color: #000000;">use-package
</span><span style=
"color: #000000;"> :sqlite
</span><span style=
"font-weight: bold;color: #0000ff;">)
</span>
104 <span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"font-weight: bold;color: #000000;">use-package
</span><span style=
"color: #000000;"> :iter
</span><span style=
"font-weight: bold;color: #0000ff;">)
</span>
106 <span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"font-weight: bold;color: #d22811;">defvar
</span><span style=
"color: #000080;"> *db*
</span><span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #000000;">connect
</span><span style=
"color: #dd0000;">":memory:"</span><span style=
"font-weight: bold;color: #0000ff;">))
</span><span style=
"color: #000000;"> </span><span style=
"font-style: italic;color: #808080;">;;Connect to the sqlite database. :memory: is the temporary in-memory database
</span>
108 <span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #000000;">execute-non-query *db*
</span><span style=
"color: #dd0000;">"create table users (id integer primary key, user_name text not null, age integer null)"</span><span style=
"font-weight: bold;color: #0000ff;">)
</span><span style=
"color: #000000;"> </span><span style=
"font-style: italic;color: #808080;">;;Create the table
</span>
110 <span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #000000;">execute-non-query *db*
</span><span style=
"color: #dd0000;">"insert into users (user_name, age) values (?, ?)"</span><span style=
"color: #000000;"> </span><span style=
"color: #dd0000;">"joe"</span><span style=
"color: #000000;"> </span><span style=
"color: #0000ff;">18</span><span style=
"font-weight: bold;color: #0000ff;">)
</span>
111 <span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #000000;">execute-non-query *db*
</span><span style=
"color: #dd0000;">"insert into users (user_name, age) values (?, ?)"</span><span style=
"color: #000000;"> </span><span style=
"color: #dd0000;">"dvk"</span><span style=
"color: #000000;"> </span><span style=
"color: #0000ff;">22</span><span style=
"font-weight: bold;color: #0000ff;">)
</span>
112 <span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #000000;">execute-non-query *db*
</span><span style=
"color: #dd0000;">"insert into users (user_name, age) values (?, ?)"</span><span style=
"color: #000000;"> </span><span style=
"color: #dd0000;">"qwe"</span><span style=
"color: #000000;"> </span><span style=
"color: #0000ff;">30</span><span style=
"font-weight: bold;color: #0000ff;">)
</span>
113 <span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #000000;">execute-non-query *db*
</span><span style=
"color: #dd0000;">"insert into users (user_name, age) values (?, ?)"</span><span style=
"color: #000000;"> </span><span style=
"font-weight: bold;color: #000000;">nil
</span><span style=
"color: #000000;"> </span><span style=
"font-weight: bold;color: #000000;">nil
</span><span style=
"font-weight: bold;color: #0000ff;">)
</span><span style=
"color: #000000;"> </span><span style=
"font-style: italic;color: #808080;">;; ERROR: constraint failed
</span>
115 <span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #000000;">execute-single *db*
</span><span style=
"color: #dd0000;">"select id from users where user_name = ?"</span><span style=
"color: #000000;"> </span><span style=
"color: #dd0000;">"dvk"</span><span style=
"font-weight: bold;color: #0000ff;">)
</span>
116 <span style=
"font-style: italic;color: #808080;">;; =
> 2</span>
117 <span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #000000;">execute-one-row-m-v *db*
</span><span style=
"color: #dd0000;">"select id, user_name, age from users where user_name = ?"</span><span style=
"color: #000000;"> </span><span style=
"color: #dd0000;">"joe"</span><span style=
"font-weight: bold;color: #0000ff;">)
</span>
118 <span style=
"font-style: italic;color: #808080;">;; =
> (values
1 "joe" 18)
</span>
120 <span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #000000;">execute-to-list *db*
</span><span style=
"color: #dd0000;">"select id, user_name, age from users"</span><span style=
"font-weight: bold;color: #0000ff;">)
</span>
121 <span style=
"font-style: italic;color: #808080;">;; =
> ((
1 "joe" 18) (
2 "dvk" 22) (
3 "qwe" 30))
</span>
123 <span style=
"font-style: italic;color: #808080;">;; Use iterate
</span>
124 <span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #000000;">iter
</span><span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #000000;">for
</span><span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #000000;">id user-name age
</span><span style=
"font-weight: bold;color: #0000ff;">)
</span><span style=
"color: #000000;"> in-sqlite-query
</span><span style=
"color: #dd0000;">"select id, user_name, age from users where age < ?"</span><span style=
"color: #000000;"> on-database *db* with-parameters
</span><span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #0000ff;">25</span><span style=
"font-weight: bold;color: #0000ff;">))
</span>
125 <span style=
"color: #000000;"> </span><span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #000000;">collect
</span><span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"font-weight: bold;color: #000000;">list
</span><span style=
"color: #000000;"> id user-name age
</span><span style=
"font-weight: bold;color: #0000ff;">)))
</span>
126 <span style=
"font-style: italic;color: #808080;">;; =
> ((
1 "joe" 18) (
2 "dvk" 22))
</span>
128 <span style=
"font-style: italic;color: #808080;">;; Use prepared statements directly
</span>
129 <span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"font-weight: bold;color: #000000;">loop
</span>
130 <span style=
"color: #000000;"> with statement
</span><span style=
"font-weight: bold;color: #d22811;">=
</span><span style=
"color: #000000;"> </span><span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #000000;">prepare-statement *db*
</span><span style=
"color: #dd0000;">"select id, user_name, age from users where age < ?"</span><span style=
"font-weight: bold;color: #0000ff;">)
</span>
131 <span style=
"color: #000000;"> initially
</span><span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #000000;">bind-parameter statement
</span><span style=
"color: #0000ff;">1</span><span style=
"color: #000000;"> </span><span style=
"color: #0000ff;">25</span><span style=
"font-weight: bold;color: #0000ff;">)
</span>
132 <span style=
"color: #000000;"> while
</span><span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #000000;">step-statement statement
</span><span style=
"font-weight: bold;color: #0000ff;">)
</span>
133 <span style=
"color: #000000;"> collect
</span><span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"font-weight: bold;color: #000000;">list
</span><span style=
"color: #000000;"> </span><span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #000000;">statement-column-value statement
</span><span style=
"color: #0000ff;">0</span><span style=
"font-weight: bold;color: #0000ff;">)
</span><span style=
"color: #000000;"> </span><span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #000000;">statement-column-value statement
</span><span style=
"color: #0000ff;">1</span><span style=
"font-weight: bold;color: #0000ff;">)
</span><span style=
"color: #000000;"> </span><span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #000000;">statement-column-value statement
</span><span style=
"color: #0000ff;">2</span><span style=
"font-weight: bold;color: #0000ff;">))
</span>
134 <span style=
"color: #000000;"> finally
</span><span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #000000;">finalize-statement statement
</span><span style=
"font-weight: bold;color: #0000ff;">))
</span>
135 <span style=
"font-style: italic;color: #808080;">;; =
> ((
1 "joe" 18) (
2 "dvk" 22))
</span>
137 <span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #000000;">disconnect *db*
</span><span style=
"font-weight: bold;color: #0000ff;">)
</span><span style=
"color: #000000;"> </span><span style=
"font-style: italic;color: #808080;">;;Disconnect
</span></pre>
139 <br> <br><h3><a class=none
name=
"usage">Usage
</a></h3>
141 <p>Two functions and a macro are used to manage connections to the database:
</p>
143 <li>Function
<a href=
"#connect">connect
</a> connects to the database
</li>
144 <li>Function
<a href=
"#disconnect">disconnect
</a> disconnects from the database
</li>
145 <li>Macro
<a href=
"#with-open-database">with-open-database
</a> opens the database and ensures that it is properly closed after the code is run
</li>
148 <p>To make queries to the database the following functions are provided:
</p>
150 <li><a href=
"#execute-non-query">execute-non-query
</a> executes the query and returns nothing
</li>
151 <li><a href=
"#execute-single">execute-single
</a> returns the first column of the first row of the result
</li>
152 <li><a href=
"#execute-one-row-m-v">execute-one-row-m-v
</a> returns the first row of the result as multiple values
</li>
153 <li><a href=
"#execute-to-list">execute-to-list
</a> returns all rows as the list of lists
</li>
156 <p>Macro
<a href=
"#with-transaction">with-transaction
</a> is used to execute code within transaction.
</p>
158 <p>Support for
<a href=
"http://common-lisp.net/project/iterate/">ITERATE
</a> is provided. Use the following clause:
159 <blockquote><pre>(for (
<i>vars
</i>) in-sqlite-query
<i>sql
</i> on-database
<i>db
</i> &optional with-parameters (
<i>&rest parameters
</i>))
</pre></blockquote>
160 This clause will bind
<i>vars
</i> (a list of variables) to the values of the columns of query.
</p>
162 <p>Additionally, it is possible to use the prepared statements API of sqlite. Create the prepared statement with
<a href=
"#prepare-statement">prepare-statement
</a>, bind its parameters with
<a href=
"#bind-parameter">bind-parameter
</a>, step through it with
<a href=
"#step-statement">step-statement
</a>, retrieve the results with
<a href=
"#statement-column-value">statement-column-value
</a>, and finally reset it to be used again with
<a href=
"#reset-statement">reset-statement
</a> or dispose of it with
<a href=
"#finalize-statement">finalize-statement
</a>.
</p>
164 <p>Positional parameters in queries are supported (sqlite supports named parameters but this package does not support them). Parameters are denoted by question mark in SQL code.
</p>
166 <p>Following types are supported:
</p>
168 <li>Integer. Integers are stored as
64-bit integers.
</li>
169 <li>Float. Stored as double. Single-float, double-float and rational may be passed as a parameter, and double-float will be returned.
</li>
170 <li>String. Stored as an UTF-
8 string.
</li>
171 <li>Vector of bytes. Stored as a blob.
</li>
172 <li>Null. Passed as NIL to and from database.
</li>
175 <br> <br><h3><a class=none
name=
"dictionary">The SQLITE dictionary
</a></h3>
179 <!-- Entry for BIND-PARAMETER -->
181 <p><br>[Function]
<br><a class=none name='bind-parameter'
><b>bind-parameter
</b> <i>statement parameter value
</i></a>
184 Sets the
<i>parameter
</i>-th parameter in
<i>statement
</i> to the
<i>value
</i>.
<br>
185 Parameters are numbered from one.
<br>
188 <li>Null. Passed as NULL
189 <li>Integer. Passed as an
64-bit integer
190 <li>String. Passed as a string
191 <li>Float. Passed as a double
192 <li>(vector (unsigned-byte
8)) and vector that contains integers in range [
0,
256). Passed as a BLOB
197 <!-- End of entry for BIND-PARAMETER -->
199 <!-- Entry for CLEAR-STATEMENT-BINDINGS -->
201 <p><br>[Function]
<br><a class=none name='clear-statement-bindings'
><b>clear-statement-bindings
</b> <i>statement
</i></a>
204 Binds all parameters of the statement to NULL.
208 <!-- End of entry for CLEAR-STATEMENT-BINDINGS -->
210 <!-- Entry for CONNECT -->
212 <p><br>[Function]
<br><a class=none name='connect'
><b>connect
</b> <i>database-path
</i> <i>&key
</i> <i>busy-timeout
</i> =
> <i>sqlite-handle
</i></a>
215 Connect to the sqlite database at the given
<i>database-path
</i> (
<i>database-path
</i> is a string or a pathname). If
<i>database-path
</i> equal to
<code>":memory:"</code> is given, a new in-memory database is created. Returns the
<a href=
"#sqlite-handle">sqlite-handle
</a> connected to the database. Use
<a href=
"disconnect">disconnect
</a> to disconnect.
<br>
217 Operations will wait for locked databases for up to
<i>busy-timeout
</i> milliseconds; if
<i>busy-timeout
</i> is NIL, then operations on locked databases will fail immediately.
221 <!-- End of entry for CONNECT -->
224 <!-- Entry for DISCONNECT -->
226 <p><br>[Function]
<br><a class=none name='disconnect'
><b>disconnect
</b> <i>handle
</i></a>
229 Disconnects the given
<i>handle
</i> from the database. All further operations on the handle and on prepared statements (including freeing handle or statements) are invalid and will lead to memory corruption.
233 <!-- End of entry for DISCONNECT -->
236 <!-- Entry for EXECUTE-NON-QUERY -->
238 <p><br>[Function]
<br><a class=none name='execute-non-query'
><b>execute-non-query
</b> <i>db sql
<tt>&rest
</tt> parameters
</i></a>
241 Executes the query
<i>sql
</i> to the database
<i>db
</i> with given
<i>parameters
</i>. Returns nothing.
<br>
245 <pre>(execute-non-query db
"insert into users (user_name, real_name) values (?, ?)
" "joe
" "Joe the User
")
</pre>
247 See
<a href=
"#bind-parameter">bind-parameter
</a> for the list of supported parameter types.
251 <!-- End of entry for EXECUTE-NON-QUERY -->
254 <!-- Entry for EXECUTE-ONE-ROW-M-V -->
256 <p><br>[Function]
<br><a class=none name='execute-one-row-m-v'
><b>execute-one-row-m-v
</b> <i>db sql
<tt>&rest
</tt> parameters
</i> =
> (values
<i>result*
</i>)
</a>
259 Executes the query
<i>sql
</i> to the database
<i>db
</i> with given
<i>parameters
</i>. Returns the first row as multiple values.
<br>
262 <pre>(execute-one-row-m-v db
"select id, user_name, real_name from users where id = ?
" 1)
264 (values
1 "joe
" "Joe the User
")
</pre>
266 See
<a href=
"#bind-parameter">bind-parameter
</a> for the list of supported parameter types.
270 <!-- End of entry for EXECUTE-ONE-ROW-M-V -->
273 <!-- Entry for EXECUTE-SINGLE -->
275 <p><br>[Function]
<br><a class=none name='execute-single'
><b>execute-single
</b> <i>db sql
<tt>&rest
</tt> parameters
</i> =
> <i>result
</i></a>
278 Executes the query
<i>sql
</i> to the database
<i>db
</i> with given
<i>parameters
</i>. Returns the first column of the first row as single value.
<br>
281 <pre>(execute-single db
"select user_name from users where id = ?
" 1)
283 "joe
"</pre>
285 See
<a href=
"#bind-parameter">bind-parameter
</a> for the list of supported parameter types.
289 <!-- End of entry for EXECUTE-SINGLE -->
292 <!-- Entry for EXECUTE-TO-LIST -->
294 <p><br>[Function]
<br><a class=none name='execute-to-list'
><b>execute-to-list
</b> <i>db sql
<tt>&rest
</tt> parameters
</i> =
> <i>results
</i></a>
297 Executes the query
<i>sql
</i> to the database
<i>db
</i> with given
<i>parameters
</i>. Returns the results as list of lists.
<br>
301 <pre>(execute-to-list db
"select id, user_name, real_name from users where user_name = ?
" "joe
")
303 ((
1 "joe
" "Joe the User
")
304 (
2 "joe
" "Another Joe
"))
</pre>
306 See
<a href=
"#bind-parameter">bind-parameter
</a> for the list of supported parameter types.
310 <!-- End of entry for EXECUTE-TO-LIST -->
313 <!-- Entry for FINALIZE-STATEMENT -->
315 <p><br>[Function]
<br><a class=none name='finalize-statement'
><b>finalize-statement
</b> <i>statement
</i></a>
318 Finalizes the
<i>statement
</i> and signals that associated resources may be released.
<br>
319 Note: does not immediately release resources because statements are cached.
323 <!-- End of entry for FINALIZE-STATEMENT -->
326 <!-- Entry for LAST-INSERT-ROWID -->
328 <p><br>[Function]
<br><a class=none name='last-insert-rowid'
><b>last-insert-rowid
</b> <i>db
</i> =
> <i>result
</i></a>
331 Returns the auto-generated ID of the last inserted row on the database connection
<i>db
</i>.
335 <!-- End of entry for LAST-INSERT-ROWID -->
338 <!-- Entry for PREPARE-STATEMENT -->
340 <p><br>[Function]
<br><a class=none name='prepare-statement'
><b>prepare-statement
</b> <i>db sql
</i> =
> <i>sqlite-statement
</i></a>
343 Prepare the statement to the DB that will execute the commands that are in
<i>sql
</i>.
<br>
345 Returns the
<a href=
"#sqlite-statement">sqlite-statement
</a>.
<br>
347 <i>sql
</i> must contain exactly one statement.
<br>
348 <i>sql
</i> may have some positional (not named) parameters specified with question marks.
<br>
352 <pre>(prepare-statement db
"select name from users where id = ?
")
</pre>
356 <!-- End of entry for PREPARE-STATEMENT -->
359 <!-- Entry for RESET-STATEMENT -->
361 <p><br>[Function]
<br><a class=none name='reset-statement'
><b>reset-statement
</b> <i>statement
</i></a>
364 Resets the
<i>statement
</i> and prepares it to be called again. Note that bind parameter values are not cleared; use
<a href=
"#clear-statement-bindings">clear-statement-bindings
</a> for that.
368 <!-- End of entry for RESET-STATEMENT -->
370 <!-- Entry for SQLITE-ERROR -->
372 <p><br>[Condition]
<br><a class=none name='sqlite-error'
><b>sqlite-error
</b></a>
375 Error condition used by the library.
378 <!-- End of entry for SQLITE-ERROR -->
380 <!-- Entry for SQLITE-CONSTRAINT-ERROR -->
382 <p><br>[Condition]
<br><a class=none name='sqlite-constraint-error'
><b>sqlite-constraint-error
</b></a>
385 A subclass of sqlite-error used to distinguish constraint violation errors.
388 <!-- End of entry for SQLITE-CONSTRAINT-ERROR -->
390 <!-- Entry for SQLITE-ERROR-CODE -->
392 <p><br>[Accessor]
<br><a class=none name='sqlite-error-code'
><b>sqlite-error-code
</b> <i>sqlite-error
</i> =
> <i>keyword or null
</i></a>
395 Returns the SQLite error code represeting the error.
398 <!-- End of entry for SQLITE-ERROR-CODE -->
400 <!-- Entry for SQLITE-ERROR-DB-HANDLE -->
402 <p><br>[Accessor]
<br><a class=none name='sqlite-error-db-handle'
><b>sqlite-error-db-handle
</b> <i>sqlite-error
</i> =
> <i>sqlite-handle or null
</i></a>
405 Returns the SQLite database connection that caused the error.
408 <!-- End of entry for SQLITE-ERROR-DB-HANDLE -->
410 <!-- Entry for SQLITE-ERROR-MESSAGE -->
412 <p><br>[Accessor]
<br><a class=none name='sqlite-error-message'
><b>sqlite-error-message
</b> <i>sqlite-error
</i> =
> <i>string or null
</i></a>
415 Returns the SQLite error message corresponding to the error code.
418 <!-- End of entry for SQLITE-ERROR-MESSAGE -->
420 <!-- Entry for SQLITE-ERROR-SQL -->
422 <p><br>[Accessor]
<br><a class=none name='sqlite-error-sql'
><b>sqlite-error-sql
</b> <i>sqlite-error
</i> =
> <i>string or null
</i></a>
425 Returns the SQL statement source string that caused the error.
428 <!-- End of entry for SQLITE-ERROR-SQL -->
430 <!-- Entry for SQLITE-HANDLE -->
432 <p><br>[Standard class]
<br><a class=none name='sqlite-handle'
><b>sqlite-handle
</b></a>
435 Class that encapsulates the connection to the database.
439 <!-- End of entry for SQLITE-HANDLE -->
442 <!-- Entry for SQLITE-STATEMENT -->
444 <p><br>[Standard class]
<br><a class=none name='sqlite-statement'
><b>sqlite-statement
</b></a>
447 Class that represents the prepared statement.
451 <!-- End of entry for SQLITE-STATEMENT -->
453 <!-- Entry for STATEMENT-BIND-PARAMETER-NAMES -->
455 <p><br>[Accessor]
<br><a class=none name='statement-bind-parameter-names'
><b>statement-bind-parameter-names
</b> <i>statement
</i> =
> <i>list of strings
</i></a>
458 Returns the names of the bind parameters of the prepared statement. If a parameter does not have a name, the corresponding list item is NIL.
461 <!-- End of entry for STATEMENT-BIND-PARAMETER-NAMES -->
463 <!-- Entry for STATEMENT-COLUMN-NAMES -->
465 <p><br>[Accessor]
<br><a class=none name='statement-column-names'
><b>statement-column-names
</b> <i>statement
</i> =
> <i>list of strings
</i></a>
468 Returns the names of columns in the result set of the prepared statement.
471 <!-- End of entry for STATEMENT-COLUMN-NAMES -->
473 <!-- Entry for STATEMENT-COLUMN-VALUE -->
475 <p><br>[Function]
<br><a class=none name='statement-column-value'
><b>statement-column-value
</b> <i>statement column-number
</i> =
> <i>result
</i></a>
478 Returns the
<i>column-number
</i>-th column
's value of the current row of the
<i>statement
</i>. Columns are numbered from zero.
<br>
482 <li>integer for integers
483 <li>double-float for floats
485 <li>(simple-array (unsigned-byte
8)) for BLOBs
489 <!-- End of entry for STATEMENT-COLUMN-VALUE -->
492 <!-- Entry for STEP-STATEMENT -->
494 <p><br>[Function]
<br><a class=none name='step-statement'
><b>step-statement
</b> <i>statement
</i> =
> <i>boolean
</i></a>
497 Steps to the next row of the resultset of
<i>statement
</i>.
<br>
498 Returns T is successfully advanced to the next row and NIL if there are no more rows.
502 <!-- End of entry for STEP-STATEMENT -->
505 <!-- Entry for WITH-TRANSACTION -->
507 <p><br>[Macro]
<br><a class=none name='with-transaction'
><b>with-transaction
</b> <i>db
</i> <tt>&body
</tt> <i>body
</i></i></a>
510 Wraps the
<i>body
</i> inside the transaction. If
<i>body
</i> evaluates without error, transaction is commited. If evaluation of
<i>body
</i> is interrupted, transaction is rolled back.
514 <!-- End of entry for WITH-TRANSACTION -->
517 <!-- Entry for WITH-OPEN-DATABASE -->
519 <p><br>[Macro]
<br><a class=none name='with-open-database'
><b>with-open-database
</b> (
<i>db
</i> <i>path
</i> <i>&key
</i> <i>busy-timeout
</i>)
<tt>&body
</tt> <i>body
</i></i></a>
522 Executes the
<i>body
</i> with
<i>db
</i> being bound to the database handle for database located at
<i>path
</i>. Database is open before the
<i>body
</i> is run and it is ensured that database is closed after the evaluation of
<i>body
</i> finished or interrupted.
<br>
523 See
<a href=
"#connect">CONNECT
</a> for meaning of
<i>busy-timeout
</i> parameter.
527 <!-- End of entry for WITH-OPEN-DATABASE -->
529 <br> <br><h3><a class=none
name=
"support">Support
</a></h3>
531 This package is written by
<a href=
"mailto:Kalyanov.Dmitry@gmail.com">Kalyanov Dmitry
</a>.
<br>
533 This project has a
<a href=
"http://common-lisp.net/mailman/listinfo/cl-sqlite-devel">cl-sqlite-devel
</a> mailing list.
<br>
535 <br> <br><h3><a class=none
name=
"changelog">Changelog
</a></h3>
538 <li><span style=
"color:gray">23 Jan
2009</span> <strong>0.1</strong> Initial version
539 <li><span style=
"color:gray">03 Mar
2009</span> <strong>0.1.1</strong> Fixed bug with access to recently freed memory during statement preparation
540 <li><span style=
"color:gray">22 Mar
2009</span> <strong>0.1.2</strong> <a href=
"#disconnect">disconnect
</a> function now ensures that all non-finalized statements are finalized before closing the database (otherwise errors are signaled when database is being closed).
541 <li><span style=
"color:gray">28 Apr
2009</span> <strong>0.1.3</strong> Added support for passing all values of type REAL (including RATIONAL) as query parameter. cl-sqlite is made available as git repository.
542 <li><span style=
"color:gray">10 May
2009</span> <strong>0.1.4</strong> Added test suite (based on
<a href=
"http://common-lisp.net/project/bese/FiveAM.html">FiveAM
</a> testing framework); changed foreign library definition to work on Mac OS X (thanks to Patrick Stein) and removed the dependency on sqlite3_next_stmt function that appeared only in sqlite
3.6.0 (making cl-sqlite work with older sqlite versions)
543 <li><span style=
"color:gray">13 June
2009</span> <strong>0.1.5</strong> Allow passing pathnames to
<a href=
"#connect">CONNECT
</a> function.
544 <li><span style=
"color:gray">24 Oct
2009</span> <strong>0.1.6</strong> Add busy-timeout argument to
<a href=
"#connect">CONNECT
</a>. Fix library defininitions for running on Microsoft Windows.
547 <br> <br><h3><a class=none
name=
"ack">Acknowledgements
</a></h3>
550 This documentation was prepared with
<a href=
"http://weitz.de/documentation-template/">DOCUMENTATION-TEMPLATE
</a>.
553 $Header: /usr/local/cvsrep/documentation-template/output.lisp,v
1.14 2008/
05/
29 08:
23:
37 edi Exp $