index.html

   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
  44 href="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/dmitryvk/cl-sqlite.git</code> (<a href="http://github.com/dmitryvk/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
  97 href="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>.
  98 CL-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
 107 and loadable with regular FFI mechanisms. On Linux and Mac OS X SQLite
 108 is probably already installed (if it's not installed, use native package
 109 manager to install it). On Windows PATH environment variable should
 110 contain 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>
 188 This 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
 212 Sets 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>
 214 Supported 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
 232 Binds 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
 243 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>
 244
 245 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.
 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
 257 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.
 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
 269 Executes the query <i>sql</i> to the database <i>db</i> with given <i>parameters</i>. Returns nothing.<br>
 270
 271 Example:<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
 275 See <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
 287 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>
 288
 289 Example:<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
 294 See <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
 306 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>
 307
 308 Example:<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
 313 See <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
 325 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>
 326
 327 Example:<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
 332 See <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
 344 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>
 345
 346 Example:<br>
 347 <pre>(execute-single db &quot;select user_name from users where id = ?&quot; 1)
 348 =&gt;
 349 &quot;joe&quot;</pre>
 350
 351 See <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
 363 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>
 364
 365 Example:<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
 370 See <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
 382 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>
 383
 384 Example:<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
 391 See <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
 403 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>
 404
 405 Example:<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
 412 See <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
 424 Finalizes the <i>statement</i> and signals that associated resources may be released.<br>
 425 Note: 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
 437 Returns 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
 449 Prepare the statement to the DB that will execute the commands that are in <i>sql</i>.<br>
 450
 451 Returns 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
 456 Example:<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
 470 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.
 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
 481 Error 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
 491 A 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
 501 Returns 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
 511 Returns 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
 521 Returns 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
 531 Returns 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
 541 Class 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
 553 Class 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
 564 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.
 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
 574 Returns 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
 584 Returns 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>
 585 Returns:<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
 603 Steps to the next row of the resultset of <i>statement</i>.<br>
 604 Returns 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
 616 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.
 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
 628 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>
 629 See <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
 637 This package is written by <a href="mailto:Kalyanov.Dmitry@gmail.com">Kalyanov Dmitry</a>.<br>
 638
 639 This 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 </ul>
 653
 654 <br>&nbsp;<br><h3><a class=none name="ack">Acknowledgements</a></h3>
 655
 656 <p>
 657 This documentation was prepared with <a href="http://weitz.de/documentation-template/">DOCUMENTATION-TEMPLATE</a>.
 658 </p>
 659 <p>
 660 $Header: /usr/local/cvsrep/documentation-template/output.lisp,v 1.14 2008/05/29 08:23:37 edi Exp $
 661
 662 </body>
 663 </html>