PageRenderTime 36ms CodeModel.GetById 2ms app.highlight 26ms RepoModel.GetById 1ms app.codeStats 1ms

/index.html

http://github.com/dmitryvk/cl-sqlite
HTML | 664 lines | 374 code | 230 blank | 60 comment | 0 complexity | 8277898f1c147ecef45d45d72b36ac6f MD5 | raw file
  1<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
  2<html> 
  3
  4<head>
  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 }
 24  </style>
 25</head>
 26
 27<body bgcolor=white>
 28
 29<div class="header">
 30  <h1>CL-SQLITE</h1>
 31</div>
 32
 33<blockquote>
 34<br>&nbsp;<br><h3><a name=abstract class=none>Abstract</a></h3>
 35
 36<p>CL-SQLITE package is an interface to the SQLite embedded relational database engine.</p>
 37
 38<p>The code is in public domain so you can basically do with it whatever you want.</p>
 39
 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>
 41
 42
 43<p>CL-SQLITE together with this documentation can be downloaded from <a 
 44href="http://common-lisp.net/project/cl-sqlite/releases/cl-sqlite-0.2.tar.gz">http://common-lisp.net/project/cl-sqlite/releases/cl-sqlite-0.2.tar.gz</a>.</p>
 45
 46<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/TeMPOraL/cl-sqlite.git</code> (<a href="http://github.com/TeMPOraL/cl-sqlite/tree/master">gitweb</a>).</p>
 47
 48<p>
 49</blockquote>
 50
 51<br>&nbsp;<br><h3><a class=none name="contents">Contents</a></h3>
 52<ol>
 53  <li><a href="#installation">Installation</a>
 54  <li><a href="#example">Example</a>
 55  <li><a href="#usage">Usage</a>
 56  <li><a href="#dictionary">The SQLITE dictionary</a>
 57    <ol>
 58      <li><a href="#bind-parameter"><code>bind-parameter</code></a>
 59      <li><a href="#clear-statement-bindings"><code>clear-statement-bindings</code></a>
 60      <li><a href="#connect"><code>connect</code></a>
 61      <li><a href="#disconnect"><code>disconnect</code></a>
 62      <li><a href="#execute-non-query"><code>execute-non-query</code></a>
 63      <li><a href="#execute-non-query/named"><code>execute-non-query/named</code></a>
 64      <li><a href="#execute-one-row-m-v"><code>execute-one-row-m-v</code></a>
 65      <li><a href="#execute-one-row-m-v/named"><code>execute-one-row-m-v/named</code></a>
 66      <li><a href="#execute-single"><code>execute-single</code></a>
 67      <li><a href="#execute-singled/named"><code>execute-single/named</code></a>
 68      <li><a href="#execute-to-list"><code>execute-to-list</code></a>
 69      <li><a href="#execute-to-list/named"><code>execute-to-list/named</code></a>
 70      <li><a href="#finalize-statement"><code>finalize-statement</code></a>
 71      <li><a href="#last-insert-rowid"><code>last-insert-rowid</code></a>
 72      <li><a href="#prepare-statement"><code>prepare-statement</code></a>
 73      <li><a href="#reset-statement"><code>reset-statement</code></a>
 74      <li><a href="#sqlite-error"><code>sqlite-error</code></a>
 75      <li><a href="#sqlite-constraint-error"><code>sqlite-constraint-error</code></a>
 76      <li><a href="#sqlite-error-code"><code>sqlite-error-code</code></a>
 77      <li><a href="#sqlite-error-db-handle"><code>sqlite-error-db-handle</code></a>
 78      <li><a href="#sqlite-error-message"><code>sqlite-error-message</code></a>
 79      <li><a href="#sqlite-error-sql"><code>sqlite-error-sql</code></a>
 80      <li><a href="#sqlite-handle"><code>sqlite-handle</code></a>
 81      <li><a href="#sqlite-statement"><code>sqlite-statement</code></a>
 82      <li><a href="#statement-bind-parameter-names"><code>statement-bind-parameter-names</code></a>
 83      <li><a href="#statement-column-names"><code>statement-column-names</code></a>
 84      <li><a href="#statement-column-value"><code>statement-column-value</code></a>
 85      <li><a href="#step-statement"><code>step-statement</code></a>
 86      <li><a href="#with-transaction"><code>with-transaction</code></a>
 87      <li><a href="#with-open-database"><code>with-open-database</code></a>
 88    </ol>
 89  <li><a href="#support">Support</a>
 90  <li><a href="#changelog">Changelog</a>
 91  <li><a href="#ack">Acknowledgements</a>
 92</ol>
 93
 94<br>&nbsp;<br><h3><a class=none name="installation">Installation</a></h3>
 95
 96<p>The package can be downloaded from <a 
 97href="http://common-lisp.net/project/cl-sqlite/releases/cl-sqlite-0.2.tar.gz">http://common-lisp.net/project/cl-sqlite/releases/cl-sqlite-0.2.tar.gz</a>. 
 98CL-SQLITE package has the following dependencies:</p>
 99<ul>
100  <li><a href="http://common-lisp.net/project/cffi/">CFFI</a></li>
101  <li><a href="http://common-lisp.net/project/iterate/">iterate</a></li>
102</ul>
103
104<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>
105
106<p>This package does not include SQLite library. It should be installed 
107and loadable with regular FFI mechanisms. On Linux and Mac OS X SQLite 
108is probably already installed (if it's not installed, use native package 
109manager to install it). On Windows PATH environment variable should 
110contain path to sqlite3.dll.</p>
111
112<br>&nbsp;<br><h3><a class=none name="example">Example</a></h3>
113
114<pre>
115<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>
116<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>
117
118<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>
119
120<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>
121
122<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>
123<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>
124<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>
125<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>
126<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>
127
128<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>
129<span style="font-style: italic;color: #808080;">;; =&gt; 2</span>
130<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>
131<span style="font-style: italic;color: #808080;">;; =&gt; (values 1 "joe" 18)</span>
132
133<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>
134<span style="font-style: italic;color: #808080;">;; =&gt; ((1 "joe" 18) (2 "dvk" 22) (3 "qwe" 30))</span>
135
136<span style="font-style: italic;color: #808080;">;; Use iterate</span>
137<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 &lt; ?"</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>
138<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>
139<span style="font-style: italic;color: #808080;">;; =&gt; ((1 "joe" 18) (2 "dvk" 22))</span>
140
141<span style="font-style: italic;color: #808080;">;; Use iterate with named parameters</span>
142<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 &lt; :age"</span>
143<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>
144<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>
145<span style="font-style: italic;color: #808080;">;; =&gt; ((1 "joe" 18) (2 "dvk" 22))</span>
146
147<span style="font-style: italic;color: #808080;">;; Use prepared statements directly</span>
148<span style="font-weight: bold;color: #0000ff;">(</span><span style="font-weight: bold;color: #000000;">loop</span>
149<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 &lt; ?"</span><span style="font-weight: bold;color: #0000ff;">)</span>
150<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>
151<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>
152<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>
153<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>
154<span style="font-style: italic;color: #808080;">;; =&gt; ((1 "joe" 18) (2 "dvk" 22))</span>
155
156<span style="font-style: italic;color: #808080;">;; Use prepared statements with named parameters</span>
157<span style="font-weight: bold;color: #0000ff;">(</span><span style="font-weight: bold;color: #000000;">loop</span>
158<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 &lt; :age"</span><span style="font-weight: bold;color: #0000ff;">)</span>
159<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>
160<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>
161<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>
162<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>
163<span style="font-style: italic;color: #808080;">;; =&gt; ((1 "joe" 18) (2 "dvk" 22))</span>
164
165<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>
166
167<br>&nbsp;<br><h3><a class=none name="usage">Usage</a></h3>
168
169<p>Two functions and a macro are used to manage connections to the database:</p>
170<ul>
171<li>Function <a href="#connect">connect</a> connects to the database</li>
172<li>Function <a href="#disconnect">disconnect</a> disconnects from the database</li>
173<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>
174</ul>
175
176<p>To make queries to the database the following functions are provided:</p>
177<ul>
178<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>
179<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>
180<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>
181<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>
182</ul>
183
184<p>Macro <a href="#with-transaction">with-transaction</a> is used to execute code within transaction.</p>
185
186<p>Support for <a href="http://common-lisp.net/project/iterate/">ITERATE</a> is provided. Use the following clause:
187  <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>
188This clause will bind <i>vars</i> (a list of variables) to the values of the columns of query.</p>
189
190<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>
191
192<p>Positional and named parameters in queries are supported. Positional parameters are denoted by question mark in SQL code, and named parameters are denoted by prefixing color (:), at sign (@) or dollar sign ($) before parameter name.</p>
193
194<p>Following types are supported:</p>
195<ul>
196  <li>Integer. Integers are stored as 64-bit integers.</li>
197  <li>Float. Stored as double. Single-float, double-float and rational may be passed as a parameter, and double-float will be returned.</li>
198  <li>String. Stored as an UTF-8 string.</li>
199  <li>Vector of bytes. Stored as a blob.</li>
200  <li>Null. Passed as NIL to and from database.</li>
201</ul>
202
203<br>&nbsp;<br><h3><a class=none name="dictionary">The SQLITE dictionary</a></h3>
204
205
206
207<!-- Entry for BIND-PARAMETER -->
208
209<p><br>[Function]<br><a class=none name='bind-parameter'><b>bind-parameter</b> <i>statement parameter value</i></a>
210<blockquote><br>
211
212Sets the <i>parameter</i> in <i>statement</i> to the <i>value</i>.<br>
213<i>parameter</i> is an index (parameters are numbered from one) or the name of a parameter.<br>
214Supported types:<br>
215<ul>
216<li>Null. Passed as NULL
217<li>Integer. Passed as an 64-bit integer
218<li>String. Passed as a string
219<li>Float. Passed as a double
220<li>(vector (unsigned-byte 8)) and vector that contains integers in range [0,256). Passed as a BLOB
221</ul>
222
223</blockquote>
224
225<!-- End of entry for BIND-PARAMETER -->
226
227<!-- Entry for CLEAR-STATEMENT-BINDINGS -->
228
229<p><br>[Function]<br><a class=none name='clear-statement-bindings'><b>clear-statement-bindings</b> <i>statement</i></a>
230<blockquote><br>
231
232Binds all parameters of the statement to NULL.
233
234</blockquote>
235
236<!-- End of entry for CLEAR-STATEMENT-BINDINGS -->
237
238<!-- Entry for CONNECT -->
239
240<p><br>[Function]<br><a class=none name='connect'><b>connect</b> <i>database-path</i> <i>&amp;key</i> <i>busy-timeout</i> =&gt; <i>sqlite-handle</i></a>
241<blockquote><br>
242
243Connect 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>
244
245Operations 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.
246
247</blockquote>
248
249<!-- End of entry for CONNECT -->
250
251
252<!-- Entry for DISCONNECT -->
253
254<p><br>[Function]<br><a class=none name='disconnect'><b>disconnect</b> <i>handle</i></a>
255<blockquote><br>
256
257Disconnects 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.
258
259</blockquote>
260
261<!-- End of entry for DISCONNECT -->
262
263
264<!-- Entry for EXECUTE-NON-QUERY -->
265
266<p><br>[Function]<br><a class=none name='execute-non-query'><b>execute-non-query</b> <i>db sql <tt>&amp;rest</tt> parameters</i></a>
267<blockquote><br>
268
269Executes the query <i>sql</i> to the database <i>db</i> with given <i>parameters</i>. Returns nothing.<br>
270
271Example:<br>
272
273<pre>(execute-non-query db &quot;insert into users (user_name, real_name) values (?, ?)&quot; &quot;joe&quot; &quot;Joe the User&quot;)</pre>
274
275See <a href="#bind-parameter">bind-parameter</a> for the list of supported parameter types.
276
277</blockquote>
278
279<!-- End of entry for EXECUTE-NON-QUERY -->
280
281
282<!-- Entry for EXECUTE-NON-QUERY/NAMED -->
283
284<p><br>[Function]<br><a class=none name='execute-non-query/named'><b>execute-non-query/named</b> <i>db sql <tt>&amp;rest</tt> parameters</i></a>
285<blockquote><br>
286
287Executes 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>
288
289Example:<br>
290
291<pre>(execute-non-query/named db &quot;insert into users (user_name, real_name) values (:user_name, :real_name)&quot;
292                         &quot;:user_name&quot; &quot;joe&quot; &quot;:real_name&quot; &quot;Joe the User&quot;)</pre>
293
294See <a href="#bind-parameter">bind-parameter</a> for the list of supported parameter types.
295
296</blockquote>
297
298<!-- End of entry for EXECUTE-NON-QUERY -->
299
300
301<!-- Entry for EXECUTE-ONE-ROW-M-V -->
302
303<p><br>[Function]<br><a class=none name='execute-one-row-m-v'><b>execute-one-row-m-v</b> <i>db sql <tt>&amp;rest</tt> parameters</i> =&gt; (values <i>result*</i>)</a>
304<blockquote><br>
305
306Executes the query <i>sql</i> to the database <i>db</i> with given <i>parameters</i>. Returns the first row as multiple values.<br>
307
308Example:<br>
309<pre>(execute-one-row-m-v db &quot;select id, user_name, real_name from users where id = ?&quot; 1)
310=&gt;
311(values 1 &quot;joe&quot; &quot;Joe the User&quot;)</pre>
312
313See <a href="#bind-parameter">bind-parameter</a> for the list of supported parameter types.
314
315</blockquote>
316
317<!-- End of entry for EXECUTE-ONE-ROW-M-V -->
318
319
320<!-- Entry for EXECUTE-ONE-ROW-M-V/NAMED -->
321
322<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>&amp;rest</tt> parameters</i> =&gt; (values <i>result*</i>)</a>
323<blockquote><br>
324
325Executes 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>
326
327Example:<br>
328<pre>(execute-one-row-m-v/named db &quot;select id, user_name, real_name from users where id = :id&quot; &quot;:id&quot; 1)
329=&gt;
330(values 1 &quot;joe&quot; &quot;Joe the User&quot;)</pre>
331
332See <a href="#bind-parameter">bind-parameter</a> for the list of supported parameter types.
333
334</blockquote>
335
336<!-- End of entry for EXECUTE-ONE-ROW-M-V -->
337
338
339<!-- Entry for EXECUTE-SINGLE -->
340
341<p><br>[Function]<br><a class=none name='execute-single'><b>execute-single</b> <i>db sql <tt>&amp;rest</tt> parameters</i> =&gt; <i>result</i></a>
342<blockquote><br>
343
344Executes 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>
345
346Example:<br>
347<pre>(execute-single db &quot;select user_name from users where id = ?&quot; 1)
348=&gt;
349&quot;joe&quot;</pre>
350
351See <a href="#bind-parameter">bind-parameter</a> for the list of supported parameter types.
352
353</blockquote>
354
355<!-- End of entry for EXECUTE-SINGLE -->
356
357
358<!-- Entry for EXECUTE-SINGLE/NAMED -->
359
360<p><br>[Function]<br><a class=none name='execute-single/named'><b>execute-single/named</b> <i>db sql <tt>&amp;rest</tt> parameters</i> =&gt; <i>result</i></a>
361<blockquote><br>
362
363Executes 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>
364
365Example:<br>
366<pre>(execute-single/named db &quot;select user_name from users where id = :id&quot; &quot;:id&quot; 1)
367=&gt;
368&quot;joe&quot;</pre>
369
370See <a href="#bind-parameter">bind-parameter</a> for the list of supported parameter types.
371
372</blockquote>
373
374<!-- End of entry for EXECUTE-SINGLE -->
375
376
377<!-- Entry for EXECUTE-TO-LIST -->
378
379<p><br>[Function]<br><a class=none name='execute-to-list'><b>execute-to-list</b> <i>db sql <tt>&amp;rest</tt> parameters</i> =&gt; <i>results</i></a>
380<blockquote><br>
381
382Executes the query <i>sql</i> to the database <i>db</i> with given <i>parameters</i>. Returns the results as list of lists.<br>
383
384Example:<br>
385
386<pre>(execute-to-list db &quot;select id, user_name, real_name from users where user_name = ?&quot; &quot;joe&quot;)
387=&gt;
388((1 &quot;joe&quot; &quot;Joe the User&quot;)
389 (2 &quot;joe&quot; &quot;Another Joe&quot;)) </pre>
390
391See <a href="#bind-parameter">bind-parameter</a> for the list of supported parameter types.
392
393</blockquote>
394
395<!-- End of entry for EXECUTE-TO-LIST -->
396
397
398<!-- Entry for EXECUTE-TO-LIST -->
399
400<p><br>[Function]<br><a class=none name='execute-to-list/named'><b>execute-to-list/named</b> <i>db sql <tt>&amp;rest</tt> parameters</i> =&gt; <i>results</i></a>
401<blockquote><br>
402
403Executes 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>
404
405Example:<br>
406
407<pre>(execute-to-list db &quot;select id, user_name, real_name from users where user_name = :name&quot; &quot;:name&quot; &quot;joe&quot;)
408=&gt;
409((1 &quot;joe&quot; &quot;Joe the User&quot;)
410 (2 &quot;joe&quot; &quot;Another Joe&quot;)) </pre>
411
412See <a href="#bind-parameter">bind-parameter</a> for the list of supported parameter types.
413
414</blockquote>
415
416<!-- End of entry for EXECUTE-TO-LIST -->
417
418
419<!-- Entry for FINALIZE-STATEMENT -->
420
421<p><br>[Function]<br><a class=none name='finalize-statement'><b>finalize-statement</b> <i>statement</i></a>
422<blockquote><br>
423
424Finalizes the <i>statement</i> and signals that associated resources may be released.<br>
425Note: does not immediately release resources because statements are cached.
426
427</blockquote>
428
429<!-- End of entry for FINALIZE-STATEMENT -->
430
431
432<!-- Entry for LAST-INSERT-ROWID -->
433
434<p><br>[Function]<br><a class=none name='last-insert-rowid'><b>last-insert-rowid</b> <i>db</i> =&gt; <i>result</i></a>
435<blockquote><br>
436
437Returns the auto-generated ID of the last inserted row on the database connection <i>db</i>.
438
439</blockquote>
440
441<!-- End of entry for LAST-INSERT-ROWID -->
442
443
444<!-- Entry for PREPARE-STATEMENT -->
445
446<p><br>[Function]<br><a class=none name='prepare-statement'><b>prepare-statement</b> <i>db sql</i> =&gt; <i>sqlite-statement</i></a>
447<blockquote><br>
448
449Prepare the statement to the DB that will execute the commands that are in <i>sql</i>.<br>
450
451Returns the <a href="#sqlite-statement">sqlite-statement</a>.<br>
452
453<i>sql</i> must contain exactly one statement.<br>
454<i>sql</i> may have some positional (not named) parameters specified with question marks.<br>
455
456Example:<br>
457
458<pre>(prepare-statement db &quot;select name from users where id = ?&quot;)</pre>
459
460</blockquote>
461
462<!-- End of entry for PREPARE-STATEMENT -->
463
464
465<!-- Entry for RESET-STATEMENT -->
466
467<p><br>[Function]<br><a class=none name='reset-statement'><b>reset-statement</b> <i>statement</i></a>
468<blockquote><br>
469
470Resets 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.
471
472</blockquote>
473
474<!-- End of entry for RESET-STATEMENT -->
475
476<!-- Entry for SQLITE-ERROR -->
477
478<p><br>[Condition]<br><a class=none name='sqlite-error'><b>sqlite-error</b></a>
479<blockquote><br>
480
481Error condition used by the library.
482</blockquote>
483
484<!-- End of entry for SQLITE-ERROR -->
485
486<!-- Entry for SQLITE-CONSTRAINT-ERROR -->
487
488<p><br>[Condition]<br><a class=none name='sqlite-constraint-error'><b>sqlite-constraint-error</b></a>
489<blockquote><br>
490
491A subclass of sqlite-error used to distinguish constraint violation errors.
492</blockquote>
493
494<!-- End of entry for SQLITE-CONSTRAINT-ERROR -->
495
496<!-- Entry for SQLITE-ERROR-CODE -->
497
498<p><br>[Accessor]<br><a class=none name='sqlite-error-code'><b>sqlite-error-code</b> <i>sqlite-error</i> =&gt; <i>keyword or null</i></a>
499<blockquote><br>
500
501Returns the SQLite error code represeting the error.
502</blockquote>
503
504<!-- End of entry for SQLITE-ERROR-CODE -->
505
506<!-- Entry for SQLITE-ERROR-DB-HANDLE -->
507
508<p><br>[Accessor]<br><a class=none name='sqlite-error-db-handle'><b>sqlite-error-db-handle</b> <i>sqlite-error</i> =&gt; <i>sqlite-handle or null</i></a>
509<blockquote><br>
510
511Returns the SQLite database connection that caused the error.
512</blockquote>
513
514<!-- End of entry for SQLITE-ERROR-DB-HANDLE -->
515
516<!-- Entry for SQLITE-ERROR-MESSAGE -->
517
518<p><br>[Accessor]<br><a class=none name='sqlite-error-message'><b>sqlite-error-message</b> <i>sqlite-error</i> =&gt; <i>string or null</i></a>
519<blockquote><br>
520
521Returns the SQLite error message corresponding to the error code.
522</blockquote>
523
524<!-- End of entry for SQLITE-ERROR-MESSAGE -->
525
526<!-- Entry for SQLITE-ERROR-SQL -->
527
528<p><br>[Accessor]<br><a class=none name='sqlite-error-sql'><b>sqlite-error-sql</b> <i>sqlite-error</i> =&gt; <i>string or null</i></a>
529<blockquote><br>
530
531Returns the SQL statement source string that caused the error.
532</blockquote>
533
534<!-- End of entry for SQLITE-ERROR-SQL -->
535
536<!-- Entry for SQLITE-HANDLE -->
537
538<p><br>[Standard class]<br><a class=none name='sqlite-handle'><b>sqlite-handle</b></a>
539<blockquote><br>
540
541Class that encapsulates the connection to the database.
542
543</blockquote>
544
545<!-- End of entry for SQLITE-HANDLE -->
546
547
548<!-- Entry for SQLITE-STATEMENT -->
549
550<p><br>[Standard class]<br><a class=none name='sqlite-statement'><b>sqlite-statement</b></a>
551<blockquote><br>
552
553Class that represents the prepared statement.
554
555</blockquote>
556
557<!-- End of entry for SQLITE-STATEMENT -->
558
559<!-- Entry for STATEMENT-BIND-PARAMETER-NAMES -->
560
561<p><br>[Accessor]<br><a class=none name='statement-bind-parameter-names'><b>statement-bind-parameter-names</b> <i>statement</i> =&gt; <i>list of strings</i></a>
562<blockquote><br>
563
564Returns the names of the bind parameters of the prepared statement. If a parameter does not have a name, the corresponding list item is NIL.
565</blockquote>
566
567<!-- End of entry for STATEMENT-BIND-PARAMETER-NAMES -->
568
569<!-- Entry for STATEMENT-COLUMN-NAMES -->
570
571<p><br>[Accessor]<br><a class=none name='statement-column-names'><b>statement-column-names</b> <i>statement</i> =&gt; <i>list of strings</i></a>
572<blockquote><br>
573
574Returns the names of columns in the result set of the prepared statement.
575</blockquote>
576
577<!-- End of entry for STATEMENT-COLUMN-NAMES -->
578
579<!-- Entry for STATEMENT-COLUMN-VALUE -->
580
581<p><br>[Function]<br><a class=none name='statement-column-value'><b>statement-column-value</b> <i>statement column-number</i> =&gt; <i>result</i></a>
582<blockquote><br>
583
584Returns the <i>column-number</i>-th column&#039;s value of the current row of the <i>statement</i>. Columns are numbered from zero.<br>
585Returns:<br>
586<ul>
587<li>NIL for NULL
588<li>integer for integers
589<li>double-float for floats
590<li>string for text
591<li>(simple-array (unsigned-byte 8)) for BLOBs
592</ul>
593</blockquote>
594
595<!-- End of entry for STATEMENT-COLUMN-VALUE -->
596
597
598<!-- Entry for STEP-STATEMENT -->
599
600<p><br>[Function]<br><a class=none name='step-statement'><b>step-statement</b> <i>statement</i> =&gt; <i>boolean</i></a>
601<blockquote><br>
602
603Steps to the next row of the resultset of <i>statement</i>.<br>
604Returns T is successfully advanced to the next row and NIL if there are no more rows.
605
606</blockquote>
607
608<!-- End of entry for STEP-STATEMENT -->
609
610
611<!-- Entry for WITH-TRANSACTION -->
612
613<p><br>[Macro]<br><a class=none name='with-transaction'><b>with-transaction</b> <i>db</i> <tt>&amp;body</tt> <i>body</i></i></a>
614<blockquote><br>
615
616Wraps 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.
617
618</blockquote>
619
620<!-- End of entry for WITH-TRANSACTION -->
621
622
623<!-- Entry for WITH-OPEN-DATABASE -->
624
625<p><br>[Macro]<br><a class=none name='with-open-database'><b>with-open-database</b> (<i>db</i> <i>path</i> <i>&amp;key</i> <i>busy-timeout</i>) <tt>&amp;body</tt> <i>body</i></i></a>
626<blockquote><br>
627
628Executes 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>
629See <a href="#connect">CONNECT</a> for meaning of <i>busy-timeout</i> parameter.
630
631</blockquote>
632
633<!-- End of entry for WITH-OPEN-DATABASE -->
634
635<br>&nbsp;<br><h3><a class=none name="support">Support</a></h3>
636
637This package is written by <a href="mailto:Kalyanov.Dmitry@gmail.com">Kalyanov Dmitry</a>.<br>
638
639This project has a <a href="http://common-lisp.net/mailman/listinfo/cl-sqlite-devel">cl-sqlite-devel</a> mailing list.<br>
640
641<br>&nbsp;<br><h3><a class=none name="changelog">Changelog</a></h3>
642
643<ul>
644  <li><span style="color:gray">23 Jan 2009</span> <strong>0.1</strong> Initial version
645  <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
646  <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).
647  <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.
648  <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)
649  <li><span style="color:gray">13 June 2009</span> <strong>0.1.5</strong> Allow passing pathnames to <a href="#connect">CONNECT</a> function.
650  <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.
651  <li><span style="color:gray">14 Nov 2010</span> <strong>0.2</strong> Added support for named parameters. Made statement reset and connection close more safe by clearing statements' bindings and unbinding slot of connection object. Added error condition for SQLite errors. Changes are courtesy of Alexander Gavrilov.
652  <li><span style="color:gray">02 Aug 2019</span> <strong>0.2.1</strong> Added metadata to system definitions. Fixed symbol conflict with FiveAM in tests. Project maintenance is now handled by Jacek Złydach.
653</ul>
654
655<br>&nbsp;<br><h3><a class=none name="ack">Acknowledgements</a></h3>
656
657<p>
658This documentation was prepared with <a href="http://weitz.de/documentation-template/">DOCUMENTATION-TEMPLATE</a>.
659</p>
660<p>
661$Header: /usr/local/cvsrep/documentation-template/output.lisp,v 1.14 2008/05/29 08:23:37 edi Exp $
662
663</body>
664</html>