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-non-query/named"><code>execute-non-query/named
</code></a>
63 <li><a href=
"#execute-one-row-m-v"><code>execute-one-row-m-v
</code></a>
64 <li><a href=
"#execute-one-row-m-v/named"><code>execute-one-row-m-v/named
</code></a>
65 <li><a href=
"#execute-single"><code>execute-single
</code></a>
66 <li><a href=
"#execute-singled/named"><code>execute-single/named
</code></a>
67 <li><a href=
"#execute-to-list"><code>execute-to-list
</code></a>
68 <li><a href=
"#execute-to-list/named"><code>execute-to-list/named
</code></a>
69 <li><a href=
"#finalize-statement"><code>finalize-statement
</code></a>
70 <li><a href=
"#last-insert-rowid"><code>last-insert-rowid
</code></a>
71 <li><a href=
"#prepare-statement"><code>prepare-statement
</code></a>
72 <li><a href=
"#reset-statement"><code>reset-statement
</code></a>
73 <li><a href=
"#sqlite-error"><code>sqlite-error
</code></a>
74 <li><a href=
"#sqlite-constraint-error"><code>sqlite-constraint-error
</code></a>
75 <li><a href=
"#sqlite-error-code"><code>sqlite-error-code
</code></a>
76 <li><a href=
"#sqlite-error-db-handle"><code>sqlite-error-db-handle
</code></a>
77 <li><a href=
"#sqlite-error-message"><code>sqlite-error-message
</code></a>
78 <li><a href=
"#sqlite-error-sql"><code>sqlite-error-sql
</code></a>
79 <li><a href=
"#sqlite-handle"><code>sqlite-handle
</code></a>
80 <li><a href=
"#sqlite-statement"><code>sqlite-statement
</code></a>
81 <li><a href=
"#statement-bind-parameter-names"><code>statement-bind-parameter-names
</code></a>
82 <li><a href=
"#statement-column-names"><code>statement-column-names
</code></a>
83 <li><a href=
"#statement-column-value"><code>statement-column-value
</code></a>
84 <li><a href=
"#step-statement"><code>step-statement
</code></a>
85 <li><a href=
"#with-transaction"><code>with-transaction
</code></a>
86 <li><a href=
"#with-open-database"><code>with-open-database
</code></a>
88 <li><a href=
"#support">Support
</a>
89 <li><a href=
"#changelog">Changelog
</a>
90 <li><a href=
"#ack">Acknowledgements
</a>
93 <br> <br><h3><a class=none
name=
"installation">Installation
</a></h3>
95 <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>
97 <li><a href=
"http://common-lisp.net/project/cffi/">CFFI
</a></li>
98 <li><a href=
"http://common-lisp.net/project/iterate/">iterate
</a></li>
101 <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>
104 <br> <br><h3><a class=none
name=
"example">Example
</a></h3>
107 <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>
108 <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>
110 <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>
112 <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>
114 <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>
115 <span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #000000;">execute-non-query/named *db*
</span><span style=
"color: #dd0000;">"insert into users (user_name, age) values (:user_name, :user_age)"</span><span style=
"color: #000000;"> </span>
116 <span style=
"color: #000000"> </span><span style=
"color: #dd0000;">":user_name"</span><span style=
"color: #000000;"> </span><span style=
"color: #dd0000;">"dvk"</span><span style=
"color: #000000;"> </span><span style=
"color: #dd0000;">":user_age"</span><span style=
"color: #000000;"> </span><span style=
"color: #0000ff;">22</span><span style=
"font-weight: bold;color: #0000ff;">)
</span>
117 <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>
118 <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>
120 <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>
121 <span style=
"font-style: italic;color: #808080;">;; =
> 2</span>
122 <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>
123 <span style=
"font-style: italic;color: #808080;">;; =
> (values
1 "joe" 18)
</span>
125 <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>
126 <span style=
"font-style: italic;color: #808080;">;; =
> ((
1 "joe" 18) (
2 "dvk" 22) (
3 "qwe" 30))
</span>
128 <span style=
"font-style: italic;color: #808080;">;; Use iterate
</span>
129 <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>
130 <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>
131 <span style=
"font-style: italic;color: #808080;">;; =
> ((
1 "joe" 18) (
2 "dvk" 22))
</span>
133 <span style=
"font-style: italic;color: #808080;">;; Use iterate with named parameters
</span>
134 <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/named
</span><span style=
"color: #dd0000;">"select id, user_name, age from users where age < :age"</span>
135 <span style=
"color: #000000;"> on-database *db* with-parameters
</span><span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"color: #dd0000;">":age"</span><span style=
"color: #000000"> </span><span style=
"color: #0000ff;">25</span><span style=
"font-weight: bold;color: #0000ff;">))
</span>
136 <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>
137 <span style=
"font-style: italic;color: #808080;">;; =
> ((
1 "joe" 18) (
2 "dvk" 22))
</span>
139 <span style=
"font-style: italic;color: #808080;">;; Use prepared statements directly
</span>
140 <span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"font-weight: bold;color: #000000;">loop
</span>
141 <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>
142 <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>
143 <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>
144 <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>
145 <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>
146 <span style=
"font-style: italic;color: #808080;">;; =
> ((
1 "joe" 18) (
2 "dvk" 22))
</span>
148 <span style=
"font-style: italic;color: #808080;">;; Use prepared statements with named parameters
</span>
149 <span style=
"font-weight: bold;color: #0000ff;">(
</span><span style=
"font-weight: bold;color: #000000;">loop
</span>
150 <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 < :age"</span><span style=
"font-weight: bold;color: #0000ff;">)
</span>
151 <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: #ff0000;">":age"</span><span style=
"color: #000000;"> </span><span style=
"color: #0000ff;">25</span><span style=
"font-weight: bold;color: #0000ff;">)
</span>
152 <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>
153 <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>
154 <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>
155 <span style=
"font-style: italic;color: #808080;">;; =
> ((
1 "joe" 18) (
2 "dvk" 22))
</span>
157 <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>
159 <br> <br><h3><a class=none
name=
"usage">Usage
</a></h3>
161 <p>Two functions and a macro are used to manage connections to the database:
</p>
163 <li>Function
<a href=
"#connect">connect
</a> connects to the database
</li>
164 <li>Function
<a href=
"#disconnect">disconnect
</a> disconnects from the database
</li>
165 <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>
168 <p>To make queries to the database the following functions are provided:
</p>
170 <li><a href=
"#execute-non-query">execute-non-query
</a> (
<a href=
"#execute-non-query/named">execute-non-query/named
</a>) executes the query and returns nothing
</li>
171 <li><a href=
"#execute-single">execute-single
</a> (
<a href=
"#execute-single/named">execute-single/named
</a>) returns the first column of the first row of the result
</li>
172 <li><a href=
"#execute-one-row-m-v">execute-one-row-m-v
</a> (
<a href=
"#execute-one-row-m-v/named">execute-one-row-m-v/named
</a>) returns the first row of the result as multiple values
</li>
173 <li><a href=
"#execute-to-list">execute-to-list
</a> (
<a href=
"#execute-to-list/named">execute-to-list/named
</a>) returns all rows as the list of lists
</li>
176 <p>Macro
<a href=
"#with-transaction">with-transaction
</a> is used to execute code within transaction.
</p>
178 <p>Support for
<a href=
"http://common-lisp.net/project/iterate/">ITERATE
</a> is provided. Use the following clause:
179 <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>
180 This clause will bind
<i>vars
</i> (a list of variables) to the values of the columns of query.
</p>
182 <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>
184 <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>
186 <p>Following types are supported:
</p>
188 <li>Integer. Integers are stored as
64-bit integers.
</li>
189 <li>Float. Stored as double. Single-float, double-float and rational may be passed as a parameter, and double-float will be returned.
</li>
190 <li>String. Stored as an UTF-
8 string.
</li>
191 <li>Vector of bytes. Stored as a blob.
</li>
192 <li>Null. Passed as NIL to and from database.
</li>
195 <br> <br><h3><a class=none
name=
"dictionary">The SQLITE dictionary
</a></h3>
199 <!-- Entry for BIND-PARAMETER -->
201 <p><br>[Function]
<br><a class=none name='bind-parameter'
><b>bind-parameter
</b> <i>statement parameter value
</i></a>
204 Sets the
<i>parameter
</i> in
<i>statement
</i> to the
<i>value
</i>.
<br>
205 <i>parameter
</i> is an index (parameters are numbered from one) or the name of a parameter.
<br>
208 <li>Null. Passed as NULL
209 <li>Integer. Passed as an
64-bit integer
210 <li>String. Passed as a string
211 <li>Float. Passed as a double
212 <li>(vector (unsigned-byte
8)) and vector that contains integers in range [
0,
256). Passed as a BLOB
217 <!-- End of entry for BIND-PARAMETER -->
219 <!-- Entry for CLEAR-STATEMENT-BINDINGS -->
221 <p><br>[Function]
<br><a class=none name='clear-statement-bindings'
><b>clear-statement-bindings
</b> <i>statement
</i></a>
224 Binds all parameters of the statement to NULL.
228 <!-- End of entry for CLEAR-STATEMENT-BINDINGS -->
230 <!-- Entry for CONNECT -->
232 <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>
235 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>
237 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.
241 <!-- End of entry for CONNECT -->
244 <!-- Entry for DISCONNECT -->
246 <p><br>[Function]
<br><a class=none name='disconnect'
><b>disconnect
</b> <i>handle
</i></a>
249 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.
253 <!-- End of entry for DISCONNECT -->
256 <!-- Entry for EXECUTE-NON-QUERY -->
258 <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>
261 Executes the query
<i>sql
</i> to the database
<i>db
</i> with given
<i>parameters
</i>. Returns nothing.
<br>
265 <pre>(execute-non-query db
"insert into users (user_name, real_name) values (?, ?)
" "joe
" "Joe the User
")
</pre>
267 See
<a href=
"#bind-parameter">bind-parameter
</a> for the list of supported parameter types.
271 <!-- End of entry for EXECUTE-NON-QUERY -->
274 <!-- Entry for EXECUTE-NON-QUERY/NAMED -->
276 <p><br>[Function]
<br><a class=none name='execute-non-query/named'
><b>execute-non-query/named
</b> <i>db sql
<tt>&rest
</tt> parameters
</i></a>
279 Executes the query
<i>sql
</i> to the database
<i>db
</i> with given
<i>parameters
</i>. Returns nothing. Parameters are alternating names and values.
<br>
283 <pre>(execute-non-query/named db
"insert into users (user_name, real_name) values (:user_name, :real_name)
"
284 ":user_name
" "joe
" ":real_name
" "Joe the User
")
</pre>
286 See
<a href=
"#bind-parameter">bind-parameter
</a> for the list of supported parameter types.
290 <!-- End of entry for EXECUTE-NON-QUERY -->
293 <!-- Entry for EXECUTE-ONE-ROW-M-V -->
295 <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>
298 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>
301 <pre>(execute-one-row-m-v db
"select id, user_name, real_name from users where id = ?
" 1)
303 (values
1 "joe
" "Joe the User
")
</pre>
305 See
<a href=
"#bind-parameter">bind-parameter
</a> for the list of supported parameter types.
309 <!-- End of entry for EXECUTE-ONE-ROW-M-V -->
312 <!-- Entry for EXECUTE-ONE-ROW-M-V/NAMED -->
314 <p><br>[Function]
<br><a class=none name='execute-one-row-m-v'
><b>execute-one-row-m-v/named
</b> <i>db sql
<tt>&rest
</tt> parameters
</i> =
> (values
<i>result*
</i>)
</a>
317 Executes the query
<i>sql
</i> to the database
<i>db
</i> with given
<i>parameters
</i>. Returns the first row as multiple values. Parameters are alternating names and values.
<br>
320 <pre>(execute-one-row-m-v/named db
"select id, user_name, real_name from users where id = :id
" ":id
" 1)
322 (values
1 "joe
" "Joe the User
")
</pre>
324 See
<a href=
"#bind-parameter">bind-parameter
</a> for the list of supported parameter types.
328 <!-- End of entry for EXECUTE-ONE-ROW-M-V -->
331 <!-- Entry for EXECUTE-SINGLE -->
333 <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>
336 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>
339 <pre>(execute-single db
"select user_name from users where id = ?
" 1)
341 "joe
"</pre>
343 See
<a href=
"#bind-parameter">bind-parameter
</a> for the list of supported parameter types.
347 <!-- End of entry for EXECUTE-SINGLE -->
350 <!-- Entry for EXECUTE-SINGLE/NAMED -->
352 <p><br>[Function]
<br><a class=none name='execute-single/named'
><b>execute-single/named
</b> <i>db sql
<tt>&rest
</tt> parameters
</i> =
> <i>result
</i></a>
355 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. Parameters are alternating names and values.
<br>
358 <pre>(execute-single/named db
"select user_name from users where id = :id
" ":id
" 1)
360 "joe
"</pre>
362 See
<a href=
"#bind-parameter">bind-parameter
</a> for the list of supported parameter types.
366 <!-- End of entry for EXECUTE-SINGLE -->
369 <!-- Entry for EXECUTE-TO-LIST -->
371 <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>
374 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>
378 <pre>(execute-to-list db
"select id, user_name, real_name from users where user_name = ?
" "joe
")
380 ((
1 "joe
" "Joe the User
")
381 (
2 "joe
" "Another Joe
"))
</pre>
383 See
<a href=
"#bind-parameter">bind-parameter
</a> for the list of supported parameter types.
387 <!-- End of entry for EXECUTE-TO-LIST -->
390 <!-- Entry for EXECUTE-TO-LIST -->
392 <p><br>[Function]
<br><a class=none name='execute-to-list/named'
><b>execute-to-list/named
</b> <i>db sql
<tt>&rest
</tt> parameters
</i> =
> <i>results
</i></a>
395 Executes the query
<i>sql
</i> to the database
<i>db
</i> with given
<i>parameters
</i>. Returns the results as list of lists. Parameters are alternating names and values.
<br>
399 <pre>(execute-to-list db
"select id, user_name, real_name from users where user_name = :name
" ":name
" "joe
")
401 ((
1 "joe
" "Joe the User
")
402 (
2 "joe
" "Another Joe
"))
</pre>
404 See
<a href=
"#bind-parameter">bind-parameter
</a> for the list of supported parameter types.
408 <!-- End of entry for EXECUTE-TO-LIST -->
411 <!-- Entry for FINALIZE-STATEMENT -->
413 <p><br>[Function]
<br><a class=none name='finalize-statement'
><b>finalize-statement
</b> <i>statement
</i></a>
416 Finalizes the
<i>statement
</i> and signals that associated resources may be released.
<br>
417 Note: does not immediately release resources because statements are cached.
421 <!-- End of entry for FINALIZE-STATEMENT -->
424 <!-- Entry for LAST-INSERT-ROWID -->
426 <p><br>[Function]
<br><a class=none name='last-insert-rowid'
><b>last-insert-rowid
</b> <i>db
</i> =
> <i>result
</i></a>
429 Returns the auto-generated ID of the last inserted row on the database connection
<i>db
</i>.
433 <!-- End of entry for LAST-INSERT-ROWID -->
436 <!-- Entry for PREPARE-STATEMENT -->
438 <p><br>[Function]
<br><a class=none name='prepare-statement'
><b>prepare-statement
</b> <i>db sql
</i> =
> <i>sqlite-statement
</i></a>
441 Prepare the statement to the DB that will execute the commands that are in
<i>sql
</i>.
<br>
443 Returns the
<a href=
"#sqlite-statement">sqlite-statement
</a>.
<br>
445 <i>sql
</i> must contain exactly one statement.
<br>
446 <i>sql
</i> may have some positional (not named) parameters specified with question marks.
<br>
450 <pre>(prepare-statement db
"select name from users where id = ?
")
</pre>
454 <!-- End of entry for PREPARE-STATEMENT -->
457 <!-- Entry for RESET-STATEMENT -->
459 <p><br>[Function]
<br><a class=none name='reset-statement'
><b>reset-statement
</b> <i>statement
</i></a>
462 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.
466 <!-- End of entry for RESET-STATEMENT -->
468 <!-- Entry for SQLITE-ERROR -->
470 <p><br>[Condition]
<br><a class=none name='sqlite-error'
><b>sqlite-error
</b></a>
473 Error condition used by the library.
476 <!-- End of entry for SQLITE-ERROR -->
478 <!-- Entry for SQLITE-CONSTRAINT-ERROR -->
480 <p><br>[Condition]
<br><a class=none name='sqlite-constraint-error'
><b>sqlite-constraint-error
</b></a>
483 A subclass of sqlite-error used to distinguish constraint violation errors.
486 <!-- End of entry for SQLITE-CONSTRAINT-ERROR -->
488 <!-- Entry for SQLITE-ERROR-CODE -->
490 <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>
493 Returns the SQLite error code represeting the error.
496 <!-- End of entry for SQLITE-ERROR-CODE -->
498 <!-- Entry for SQLITE-ERROR-DB-HANDLE -->
500 <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>
503 Returns the SQLite database connection that caused the error.
506 <!-- End of entry for SQLITE-ERROR-DB-HANDLE -->
508 <!-- Entry for SQLITE-ERROR-MESSAGE -->
510 <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>
513 Returns the SQLite error message corresponding to the error code.
516 <!-- End of entry for SQLITE-ERROR-MESSAGE -->
518 <!-- Entry for SQLITE-ERROR-SQL -->
520 <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>
523 Returns the SQL statement source string that caused the error.
526 <!-- End of entry for SQLITE-ERROR-SQL -->
528 <!-- Entry for SQLITE-HANDLE -->
530 <p><br>[Standard class]
<br><a class=none name='sqlite-handle'
><b>sqlite-handle
</b></a>
533 Class that encapsulates the connection to the database.
537 <!-- End of entry for SQLITE-HANDLE -->
540 <!-- Entry for SQLITE-STATEMENT -->
542 <p><br>[Standard class]
<br><a class=none name='sqlite-statement'
><b>sqlite-statement
</b></a>
545 Class that represents the prepared statement.
549 <!-- End of entry for SQLITE-STATEMENT -->
551 <!-- Entry for STATEMENT-BIND-PARAMETER-NAMES -->
553 <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>
556 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.
559 <!-- End of entry for STATEMENT-BIND-PARAMETER-NAMES -->
561 <!-- Entry for STATEMENT-COLUMN-NAMES -->
563 <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>
566 Returns the names of columns in the result set of the prepared statement.
569 <!-- End of entry for STATEMENT-COLUMN-NAMES -->
571 <!-- Entry for STATEMENT-COLUMN-VALUE -->
573 <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>
576 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>
580 <li>integer for integers
581 <li>double-float for floats
583 <li>(simple-array (unsigned-byte
8)) for BLOBs
587 <!-- End of entry for STATEMENT-COLUMN-VALUE -->
590 <!-- Entry for STEP-STATEMENT -->
592 <p><br>[Function]
<br><a class=none name='step-statement'
><b>step-statement
</b> <i>statement
</i> =
> <i>boolean
</i></a>
595 Steps to the next row of the resultset of
<i>statement
</i>.
<br>
596 Returns T is successfully advanced to the next row and NIL if there are no more rows.
600 <!-- End of entry for STEP-STATEMENT -->
603 <!-- Entry for WITH-TRANSACTION -->
605 <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>
608 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.
612 <!-- End of entry for WITH-TRANSACTION -->
615 <!-- Entry for WITH-OPEN-DATABASE -->
617 <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>
620 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>
621 See
<a href=
"#connect">CONNECT
</a> for meaning of
<i>busy-timeout
</i> parameter.
625 <!-- End of entry for WITH-OPEN-DATABASE -->
627 <br> <br><h3><a class=none
name=
"support">Support
</a></h3>
629 This package is written by
<a href=
"mailto:Kalyanov.Dmitry@gmail.com">Kalyanov Dmitry
</a>.
<br>
631 This project has a
<a href=
"http://common-lisp.net/mailman/listinfo/cl-sqlite-devel">cl-sqlite-devel
</a> mailing list.
<br>
633 <br> <br><h3><a class=none
name=
"changelog">Changelog
</a></h3>
636 <li><span style=
"color:gray">23 Jan
2009</span> <strong>0.1</strong> Initial version
637 <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
638 <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).
639 <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.
640 <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)
641 <li><span style=
"color:gray">13 June
2009</span> <strong>0.1.5</strong> Allow passing pathnames to
<a href=
"#connect">CONNECT
</a> function.
642 <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.
645 <br> <br><h3><a class=none
name=
"ack">Acknowledgements
</a></h3>
648 This documentation was prepared with
<a href=
"http://weitz.de/documentation-template/">DOCUMENTATION-TEMPLATE
</a>.
651 $Header: /usr/local/cvsrep/documentation-template/output.lisp,v
1.14 2008/
05/
29 08:
23:
37 edi Exp $