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-handle"><code>sqlite-handle
</code></a>
70 <li><a href=
"#sqlite-statement"><code>sqlite-statement
</code></a>
71 <li><a href=
"#statement-bind-parameter-names"><code>statement-bind-parameter-names
</code></a>
72 <li><a href=
"#statement-column-names"><code>statement-column-names
</code></a>
73 <li><a href=
"#statement-column-value"><code>statement-column-value
</code></a>
74 <li><a href=
"#step-statement"><code>step-statement
</code></a>
75 <li><a href=
"#with-transaction"><code>with-transaction
</code></a>
76 <li><a href=
"#with-open-database"><code>with-open-database
</code></a>
78 <li><a href=
"#support">Support
</a>
79 <li><a href=
"#changelog">Changelog
</a>
80 <li><a href=
"#ack">Acknowledgements
</a>
83 <br> <br><h3><a class=none
name=
"installation">Installation
</a></h3>
85 <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>
87 <li><a href=
"http://common-lisp.net/project/cffi/">CFFI
</a></li>
88 <li><a href=
"http://common-lisp.net/project/iterate/">iterate
</a></li>
91 <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>
94 <br> <br><h3><a class=none
name=
"example">Example
</a></h3>
97 <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>
98 <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>
100 <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>
102 <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>
104 <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>
105 <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>
106 <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>
107 <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>
109 <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>
110 <span style=
"font-style: italic;color: #808080;">;; =
> 2</span>
111 <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>
112 <span style=
"font-style: italic;color: #808080;">;; =
> (values
1 "joe" 18)
</span>
114 <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>
115 <span style=
"font-style: italic;color: #808080;">;; =
> ((
1 "joe" 18) (
2 "dvk" 22) (
3 "qwe" 30))
</span>
117 <span style=
"font-style: italic;color: #808080;">;; Use iterate
</span>
118 <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>
119 <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>
120 <span style=
"font-style: italic;color: #808080;">;; =
> ((
1 "joe" 18) (
2 "dvk" 22))
</span>
122 <span style=
"font-style: italic;color: #808080;">;; Use prepared statements directly
</span>
123 <span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"font-weight: bold;color: #000000;">loop
</span>
124 <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>
125 <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>
126 <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>
127 <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>
128 <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>
129 <span style=
"font-style: italic;color: #808080;">;; =
> ((
1 "joe" 18) (
2 "dvk" 22))
</span>
131 <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>
133 <br> <br><h3><a class=none
name=
"usage">Usage
</a></h3>
135 <p>Two functions and a macro are used to manage connections to the database:
</p>
137 <li>Function
<a href=
"#connect">connect
</a> connects to the database
</li>
138 <li>Function
<a href=
"#disconnect">disconnect
</a> disconnects from the database
</li>
139 <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>
142 <p>To make queries to the database the following functions are provided:
</p>
144 <li><a href=
"#execute-non-query">execute-non-query
</a> executes the query and returns nothing
</li>
145 <li><a href=
"#execute-single">execute-single
</a> returns the first column of the first row of the result
</li>
146 <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>
147 <li><a href=
"#execute-to-list">execute-to-list
</a> returns all rows as the list of lists
</li>
150 <p>Macro
<a href=
"#with-transaction">with-transaction
</a> is used to execute code within transaction.
</p>
152 <p>Support for
<a href=
"http://common-lisp.net/project/iterate/">ITERATE
</a> is provided. Use the following clause:
153 <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>
154 This clause will bind
<i>vars
</i> (a list of variables) to the values of the columns of query.
</p>
156 <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>
158 <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>
160 <p>Following types are supported:
</p>
162 <li>Integer. Integers are stored as
64-bit integers.
</li>
163 <li>Float. Stored as double. Single-float, double-float and rational may be passed as a parameter, and double-float will be returned.
</li>
164 <li>String. Stored as an UTF-
8 string.
</li>
165 <li>Vector of bytes. Stored as a blob.
</li>
166 <li>Null. Passed as NIL to and from database.
</li>
169 <br> <br><h3><a class=none
name=
"dictionary">The SQLITE dictionary
</a></h3>
173 <!-- Entry for BIND-PARAMETER -->
175 <p><br>[Function]
<br><a class=none name='bind-parameter'
><b>bind-parameter
</b> <i>statement parameter value
</i></a>
178 Sets the
<i>parameter
</i>-th parameter in
<i>statement
</i> to the
<i>value
</i>.
<br>
179 Parameters are numbered from one.
<br>
182 <li>Null. Passed as NULL
183 <li>Integer. Passed as an
64-bit integer
184 <li>String. Passed as a string
185 <li>Float. Passed as a double
186 <li>(vector (unsigned-byte
8)) and vector that contains integers in range [
0,
256). Passed as a BLOB
191 <!-- End of entry for BIND-PARAMETER -->
193 <!-- Entry for CLEAR-STATEMENT-BINDINGS -->
195 <p><br>[Function]
<br><a class=none name='clear-statement-bindings'
><b>clear-statement-bindings
</b> <i>statement
</i></a>
198 Binds all parameters of the statement to NULL.
202 <!-- End of entry for CLEAR-STATEMENT-BINDINGS -->
204 <!-- Entry for CONNECT -->
206 <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>
209 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>
211 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.
215 <!-- End of entry for CONNECT -->
218 <!-- Entry for DISCONNECT -->
220 <p><br>[Function]
<br><a class=none name='disconnect'
><b>disconnect
</b> <i>handle
</i></a>
223 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.
227 <!-- End of entry for DISCONNECT -->
230 <!-- Entry for EXECUTE-NON-QUERY -->
232 <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>
235 Executes the query
<i>sql
</i> to the database
<i>db
</i> with given
<i>parameters
</i>. Returns nothing.
<br>
239 <pre>(execute-non-query db
"insert into users (user_name, real_name) values (?, ?)
" "joe
" "Joe the User
")
</pre>
241 See
<a href=
"#bind-parameter">bind-parameter
</a> for the list of supported parameter types.
245 <!-- End of entry for EXECUTE-NON-QUERY -->
248 <!-- Entry for EXECUTE-ONE-ROW-M-V -->
250 <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>
253 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>
256 <pre>(execute-one-row-m-v db
"select id, user_name, real_name from users where id = ?
" 1)
258 (values
1 "joe
" "Joe the User
")
</pre>
260 See
<a href=
"#bind-parameter">bind-parameter
</a> for the list of supported parameter types.
264 <!-- End of entry for EXECUTE-ONE-ROW-M-V -->
267 <!-- Entry for EXECUTE-SINGLE -->
269 <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>
272 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>
275 <pre>(execute-single db
"select user_name from users where id = ?
" 1)
277 "joe
"</pre>
279 See
<a href=
"#bind-parameter">bind-parameter
</a> for the list of supported parameter types.
283 <!-- End of entry for EXECUTE-SINGLE -->
286 <!-- Entry for EXECUTE-TO-LIST -->
288 <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>
291 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>
295 <pre>(execute-to-list db
"select id, user_name, real_name from users where user_name = ?
" "joe
")
297 ((
1 "joe
" "Joe the User
")
298 (
2 "joe
" "Another Joe
"))
</pre>
300 See
<a href=
"#bind-parameter">bind-parameter
</a> for the list of supported parameter types.
304 <!-- End of entry for EXECUTE-TO-LIST -->
307 <!-- Entry for FINALIZE-STATEMENT -->
309 <p><br>[Function]
<br><a class=none name='finalize-statement'
><b>finalize-statement
</b> <i>statement
</i></a>
312 Finalizes the
<i>statement
</i> and signals that associated resources may be released.
<br>
313 Note: does not immediately release resources because statements are cached.
317 <!-- End of entry for FINALIZE-STATEMENT -->
320 <!-- Entry for LAST-INSERT-ROWID -->
322 <p><br>[Function]
<br><a class=none name='last-insert-rowid'
><b>last-insert-rowid
</b> <i>db
</i> =
> <i>result
</i></a>
325 Returns the auto-generated ID of the last inserted row on the database connection
<i>db
</i>.
329 <!-- End of entry for LAST-INSERT-ROWID -->
332 <!-- Entry for PREPARE-STATEMENT -->
334 <p><br>[Function]
<br><a class=none name='prepare-statement'
><b>prepare-statement
</b> <i>db sql
</i> =
> <i>sqlite-statement
</i></a>
337 Prepare the statement to the DB that will execute the commands that are in
<i>sql
</i>.
<br>
339 Returns the
<a href=
"#sqlite-statement">sqlite-statement
</a>.
<br>
341 <i>sql
</i> must contain exactly one statement.
<br>
342 <i>sql
</i> may have some positional (not named) parameters specified with question marks.
<br>
346 <pre>(prepare-statement db
"select name from users where id = ?
")
</pre>
350 <!-- End of entry for PREPARE-STATEMENT -->
353 <!-- Entry for RESET-STATEMENT -->
355 <p><br>[Function]
<br><a class=none name='reset-statement'
><b>reset-statement
</b> <i>statement
</i></a>
358 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.
362 <!-- End of entry for RESET-STATEMENT -->
365 <!-- Entry for SQLITE-HANDLE -->
367 <p><br>[Standard class]
<br><a class=none name='sqlite-handle'
><b>sqlite-handle
</b></a>
370 Class that encapsulates the connection to the database.
374 <!-- End of entry for SQLITE-HANDLE -->
377 <!-- Entry for SQLITE-STATEMENT -->
379 <p><br>[Standard class]
<br><a class=none name='sqlite-statement'
><b>sqlite-statement
</b></a>
382 Class that represents the prepared statement.
386 <!-- End of entry for SQLITE-STATEMENT -->
388 <!-- Entry for STATEMENT-BIND-PARAMETER-NAMES -->
390 <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>
393 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.
396 <!-- End of entry for STATEMENT-BIND-PARAMETER-NAMES -->
398 <!-- Entry for STATEMENT-COLUMN-NAMES -->
400 <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>
403 Returns the names of columns in the result set of the prepared statement.
406 <!-- End of entry for STATEMENT-COLUMN-NAMES -->
408 <!-- Entry for STATEMENT-COLUMN-VALUE -->
410 <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>
413 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>
417 <li>integer for integers
418 <li>double-float for floats
420 <li>(simple-array (unsigned-byte
8)) for BLOBs
424 <!-- End of entry for STATEMENT-COLUMN-VALUE -->
427 <!-- Entry for STEP-STATEMENT -->
429 <p><br>[Function]
<br><a class=none name='step-statement'
><b>step-statement
</b> <i>statement
</i> =
> <i>boolean
</i></a>
432 Steps to the next row of the resultset of
<i>statement
</i>.
<br>
433 Returns T is successfully advanced to the next row and NIL if there are no more rows.
437 <!-- End of entry for STEP-STATEMENT -->
440 <!-- Entry for WITH-TRANSACTION -->
442 <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>
445 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.
449 <!-- End of entry for WITH-TRANSACTION -->
452 <!-- Entry for WITH-OPEN-DATABASE -->
454 <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>
457 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>
458 See
<a href=
"#connect">CONNECT
</a> for meaning of
<i>busy-timeout
</i> parameter.
462 <!-- End of entry for WITH-OPEN-DATABASE -->
464 <br> <br><h3><a class=none
name=
"support">Support
</a></h3>
466 This package is written by
<a href=
"mailto:Kalyanov.Dmitry@gmail.com">Kalyanov Dmitry
</a>.
<br>
468 This project has a
<a href=
"http://common-lisp.net/mailman/listinfo/cl-sqlite-devel">cl-sqlite-devel
</a> mailing list.
<br>
470 <br> <br><h3><a class=none
name=
"changelog">Changelog
</a></h3>
473 <li><span style=
"color:gray">23 Jan
2009</span> <strong>0.1</strong> Initial version
474 <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
475 <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).
476 <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.
477 <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)
478 <li><span style=
"color:gray">13 June
2009</span> <strong>0.1.5</strong> Allow passing pathnames to
<a href=
"#connect">CONNECT
</a> function.
479 <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.
482 <br> <br><h3><a class=none
name=
"ack">Acknowledgements
</a></h3>
485 This documentation was prepared with
<a href=
"http://weitz.de/documentation-template/">DOCUMENTATION-TEMPLATE
</a>.
488 $Header: /usr/local/cvsrep/documentation-template/output.lisp,v
1.14 2008/
05/
29 08:
23:
37 edi Exp $