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 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>
  44
  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>
  46
  47 <p>
  48 </blockquote>
  49
  50 <br>&nbsp;<br><h3><a class=none name="contents">Contents</a></h3>
  51 <ol>
  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>
  56     <ol>
  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>
  87     </ol>
  88   <li><a href="#support">Support</a>
  89   <li><a href="#changelog">Changelog</a>
  90   <li><a href="#ack">Acknowledgements</a>
  91 </ol>
  92
  93 <br>&nbsp;<br><h3><a class=none name="installation">Installation</a></h3>
  94
  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>
  96 <ul>
  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>
  99 </ul>
 100
 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>
 102
 103
 104 <br>&nbsp;<br><h3><a class=none name="example">Example</a></h3>
 105
 106 <pre>
 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>
 109
 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>
 111
 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>
 113
 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>
 119
 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;">;; =&gt; 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;">;; =&gt; (values 1 "joe" 18)</span>
 124
 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;">;; =&gt; ((1 "joe" 18) (2 "dvk" 22) (3 "qwe" 30))</span>
 127
 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 &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>
 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;">;; =&gt; ((1 "joe" 18) (2 "dvk" 22))</span>
 132
 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 &lt; :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;">;; =&gt; ((1 "joe" 18) (2 "dvk" 22))</span>
 138
 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 &lt; ?"</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;">;; =&gt; ((1 "joe" 18) (2 "dvk" 22))</span>
 147
 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 &lt; :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;">;; =&gt; ((1 "joe" 18) (2 "dvk" 22))</span>
 156
 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>
 158
 159 <br>&nbsp;<br><h3><a class=none name="usage">Usage</a></h3>
 160
 161 <p>Two functions and a macro are used to manage connections to the database:</p>
 162 <ul>
 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>
 166 </ul>
 167
 168 <p>To make queries to the database the following functions are provided:</p>
 169 <ul>
 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>
 174 </ul>
 175
 176 <p>Macro <a href="#with-transaction">with-transaction</a> is used to execute code within transaction.</p>
 177
 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>
 181
 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>
 183
 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>
 185
 186 <p>Following types are supported:</p>
 187 <ul>
 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>
 193 </ul>
 194
 195 <br>&nbsp;<br><h3><a class=none name="dictionary">The SQLITE dictionary</a></h3>
 196
 197
 198
 199 <!-- Entry for BIND-PARAMETER -->
 200
 201 <p><br>[Function]<br><a class=none name='bind-parameter'><b>bind-parameter</b> <i>statement parameter value</i></a>
 202 <blockquote><br>
 203
 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>
 206 Supported types:<br>
 207 <ul>
 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
 213 </ul>
 214
 215 </blockquote>
 216
 217 <!-- End of entry for BIND-PARAMETER -->
 218
 219 <!-- Entry for CLEAR-STATEMENT-BINDINGS -->
 220
 221 <p><br>[Function]<br><a class=none name='clear-statement-bindings'><b>clear-statement-bindings</b> <i>statement</i></a>
 222 <blockquote><br>
 223
 224 Binds all parameters of the statement to NULL.
 225
 226 </blockquote>
 227
 228 <!-- End of entry for CLEAR-STATEMENT-BINDINGS -->
 229
 230 <!-- Entry for CONNECT -->
 231
 232 <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>
 233 <blockquote><br>
 234
 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>
 236
 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.
 238
 239 </blockquote>
 240
 241 <!-- End of entry for CONNECT -->
 242
 243
 244 <!-- Entry for DISCONNECT -->
 245
 246 <p><br>[Function]<br><a class=none name='disconnect'><b>disconnect</b> <i>handle</i></a>
 247 <blockquote><br>
 248
 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.
 250
 251 </blockquote>
 252
 253 <!-- End of entry for DISCONNECT -->
 254
 255
 256 <!-- Entry for EXECUTE-NON-QUERY -->
 257
 258 <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>
 259 <blockquote><br>
 260
 261 Executes the query <i>sql</i> to the database <i>db</i> with given <i>parameters</i>. Returns nothing.<br>
 262
 263 Example:<br>
 264
 265 <pre>(execute-non-query db &quot;insert into users (user_name, real_name) values (?, ?)&quot; &quot;joe&quot; &quot;Joe the User&quot;)</pre>
 266
 267 See <a href="#bind-parameter">bind-parameter</a> for the list of supported parameter types.
 268
 269 </blockquote>
 270
 271 <!-- End of entry for EXECUTE-NON-QUERY -->
 272
 273
 274 <!-- Entry for EXECUTE-NON-QUERY/NAMED -->
 275
 276 <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>
 277 <blockquote><br>
 278
 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>
 280
 281 Example:<br>
 282
 283 <pre>(execute-non-query/named db &quot;insert into users (user_name, real_name) values (:user_name, :real_name)&quot;
 284                          &quot;:user_name&quot; &quot;joe&quot; &quot;:real_name&quot; &quot;Joe the User&quot;)</pre>
 285
 286 See <a href="#bind-parameter">bind-parameter</a> for the list of supported parameter types.
 287
 288 </blockquote>
 289
 290 <!-- End of entry for EXECUTE-NON-QUERY -->
 291
 292
 293 <!-- Entry for EXECUTE-ONE-ROW-M-V -->
 294
 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>&amp;rest</tt> parameters</i> =&gt; (values <i>result*</i>)</a>
 296 <blockquote><br>
 297
 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>
 299
 300 Example:<br>
 301 <pre>(execute-one-row-m-v db &quot;select id, user_name, real_name from users where id = ?&quot; 1)
 302 =&gt;
 303 (values 1 &quot;joe&quot; &quot;Joe the User&quot;)</pre>
 304
 305 See <a href="#bind-parameter">bind-parameter</a> for the list of supported parameter types.
 306
 307 </blockquote>
 308
 309 <!-- End of entry for EXECUTE-ONE-ROW-M-V -->
 310
 311
 312 <!-- Entry for EXECUTE-ONE-ROW-M-V/NAMED -->
 313
 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>&amp;rest</tt> parameters</i> =&gt; (values <i>result*</i>)</a>
 315 <blockquote><br>
 316
 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>
 318
 319 Example:<br>
 320 <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)
 321 =&gt;
 322 (values 1 &quot;joe&quot; &quot;Joe the User&quot;)</pre>
 323
 324 See <a href="#bind-parameter">bind-parameter</a> for the list of supported parameter types.
 325
 326 </blockquote>
 327
 328 <!-- End of entry for EXECUTE-ONE-ROW-M-V -->
 329
 330
 331 <!-- Entry for EXECUTE-SINGLE -->
 332
 333 <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>
 334 <blockquote><br>
 335
 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>
 337
 338 Example:<br>
 339 <pre>(execute-single db &quot;select user_name from users where id = ?&quot; 1)
 340 =&gt;
 341 &quot;joe&quot;</pre>
 342
 343 See <a href="#bind-parameter">bind-parameter</a> for the list of supported parameter types.
 344
 345 </blockquote>
 346
 347 <!-- End of entry for EXECUTE-SINGLE -->
 348
 349
 350 <!-- Entry for EXECUTE-SINGLE/NAMED -->
 351
 352 <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>
 353 <blockquote><br>
 354
 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>
 356
 357 Example:<br>
 358 <pre>(execute-single/named db &quot;select user_name from users where id = :id&quot; &quot;:id&quot; 1)
 359 =&gt;
 360 &quot;joe&quot;</pre>
 361
 362 See <a href="#bind-parameter">bind-parameter</a> for the list of supported parameter types.
 363
 364 </blockquote>
 365
 366 <!-- End of entry for EXECUTE-SINGLE -->
 367
 368
 369 <!-- Entry for EXECUTE-TO-LIST -->
 370
 371 <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>
 372 <blockquote><br>
 373
 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>
 375
 376 Example:<br>
 377
 378 <pre>(execute-to-list db &quot;select id, user_name, real_name from users where user_name = ?&quot; &quot;joe&quot;)
 379 =&gt;
 380 ((1 &quot;joe&quot; &quot;Joe the User&quot;)
 381  (2 &quot;joe&quot; &quot;Another Joe&quot;)) </pre>
 382
 383 See <a href="#bind-parameter">bind-parameter</a> for the list of supported parameter types.
 384
 385 </blockquote>
 386
 387 <!-- End of entry for EXECUTE-TO-LIST -->
 388
 389
 390 <!-- Entry for EXECUTE-TO-LIST -->
 391
 392 <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>
 393 <blockquote><br>
 394
 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>
 396
 397 Example:<br>
 398
 399 <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;)
 400 =&gt;
 401 ((1 &quot;joe&quot; &quot;Joe the User&quot;)
 402  (2 &quot;joe&quot; &quot;Another Joe&quot;)) </pre>
 403
 404 See <a href="#bind-parameter">bind-parameter</a> for the list of supported parameter types.
 405
 406 </blockquote>
 407
 408 <!-- End of entry for EXECUTE-TO-LIST -->
 409
 410
 411 <!-- Entry for FINALIZE-STATEMENT -->
 412
 413 <p><br>[Function]<br><a class=none name='finalize-statement'><b>finalize-statement</b> <i>statement</i></a>
 414 <blockquote><br>
 415
 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.
 418
 419 </blockquote>
 420
 421 <!-- End of entry for FINALIZE-STATEMENT -->
 422
 423
 424 <!-- Entry for LAST-INSERT-ROWID -->
 425
 426 <p><br>[Function]<br><a class=none name='last-insert-rowid'><b>last-insert-rowid</b> <i>db</i> =&gt; <i>result</i></a>
 427 <blockquote><br>
 428
 429 Returns the auto-generated ID of the last inserted row on the database connection <i>db</i>.
 430
 431 </blockquote>
 432
 433 <!-- End of entry for LAST-INSERT-ROWID -->
 434
 435
 436 <!-- Entry for PREPARE-STATEMENT -->
 437
 438 <p><br>[Function]<br><a class=none name='prepare-statement'><b>prepare-statement</b> <i>db sql</i> =&gt; <i>sqlite-statement</i></a>
 439 <blockquote><br>
 440
 441 Prepare the statement to the DB that will execute the commands that are in <i>sql</i>.<br>
 442
 443 Returns the <a href="#sqlite-statement">sqlite-statement</a>.<br>
 444
 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>
 447
 448 Example:<br>
 449
 450 <pre>(prepare-statement db &quot;select name from users where id = ?&quot;)</pre>
 451
 452 </blockquote>
 453
 454 <!-- End of entry for PREPARE-STATEMENT -->
 455
 456
 457 <!-- Entry for RESET-STATEMENT -->
 458
 459 <p><br>[Function]<br><a class=none name='reset-statement'><b>reset-statement</b> <i>statement</i></a>
 460 <blockquote><br>
 461
 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.
 463
 464 </blockquote>
 465
 466 <!-- End of entry for RESET-STATEMENT -->
 467
 468 <!-- Entry for SQLITE-ERROR -->
 469
 470 <p><br>[Condition]<br><a class=none name='sqlite-error'><b>sqlite-error</b></a>
 471 <blockquote><br>
 472
 473 Error condition used by the library.
 474 </blockquote>
 475
 476 <!-- End of entry for SQLITE-ERROR -->
 477
 478 <!-- Entry for SQLITE-CONSTRAINT-ERROR -->
 479
 480 <p><br>[Condition]<br><a class=none name='sqlite-constraint-error'><b>sqlite-constraint-error</b></a>
 481 <blockquote><br>
 482
 483 A subclass of sqlite-error used to distinguish constraint violation errors.
 484 </blockquote>
 485
 486 <!-- End of entry for SQLITE-CONSTRAINT-ERROR -->
 487
 488 <!-- Entry for SQLITE-ERROR-CODE -->
 489
 490 <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>
 491 <blockquote><br>
 492
 493 Returns the SQLite error code represeting the error.
 494 </blockquote>
 495
 496 <!-- End of entry for SQLITE-ERROR-CODE -->
 497
 498 <!-- Entry for SQLITE-ERROR-DB-HANDLE -->
 499
 500 <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>
 501 <blockquote><br>
 502
 503 Returns the SQLite database connection that caused the error.
 504 </blockquote>
 505
 506 <!-- End of entry for SQLITE-ERROR-DB-HANDLE -->
 507
 508 <!-- Entry for SQLITE-ERROR-MESSAGE -->
 509
 510 <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>
 511 <blockquote><br>
 512
 513 Returns the SQLite error message corresponding to the error code.
 514 </blockquote>
 515
 516 <!-- End of entry for SQLITE-ERROR-MESSAGE -->
 517
 518 <!-- Entry for SQLITE-ERROR-SQL -->
 519
 520 <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>
 521 <blockquote><br>
 522
 523 Returns the SQL statement source string that caused the error.
 524 </blockquote>
 525
 526 <!-- End of entry for SQLITE-ERROR-SQL -->
 527
 528 <!-- Entry for SQLITE-HANDLE -->
 529
 530 <p><br>[Standard class]<br><a class=none name='sqlite-handle'><b>sqlite-handle</b></a>
 531 <blockquote><br>
 532
 533 Class that encapsulates the connection to the database.
 534
 535 </blockquote>
 536
 537 <!-- End of entry for SQLITE-HANDLE -->
 538
 539
 540 <!-- Entry for SQLITE-STATEMENT -->
 541
 542 <p><br>[Standard class]<br><a class=none name='sqlite-statement'><b>sqlite-statement</b></a>
 543 <blockquote><br>
 544
 545 Class that represents the prepared statement.
 546
 547 </blockquote>
 548
 549 <!-- End of entry for SQLITE-STATEMENT -->
 550
 551 <!-- Entry for STATEMENT-BIND-PARAMETER-NAMES -->
 552
 553 <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>
 554 <blockquote><br>
 555
 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.
 557 </blockquote>
 558
 559 <!-- End of entry for STATEMENT-BIND-PARAMETER-NAMES -->
 560
 561 <!-- Entry for STATEMENT-COLUMN-NAMES -->
 562
 563 <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>
 564 <blockquote><br>
 565
 566 Returns the names of columns in the result set of the prepared statement.
 567 </blockquote>
 568
 569 <!-- End of entry for STATEMENT-COLUMN-NAMES -->
 570
 571 <!-- Entry for STATEMENT-COLUMN-VALUE -->
 572
 573 <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>
 574 <blockquote><br>
 575
 576 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>
 577 Returns:<br>
 578 <ul>
 579 <li>NIL for NULL
 580 <li>integer for integers
 581 <li>double-float for floats
 582 <li>string for text
 583 <li>(simple-array (unsigned-byte 8)) for BLOBs
 584 </ul>
 585 </blockquote>
 586
 587 <!-- End of entry for STATEMENT-COLUMN-VALUE -->
 588
 589
 590 <!-- Entry for STEP-STATEMENT -->
 591
 592 <p><br>[Function]<br><a class=none name='step-statement'><b>step-statement</b> <i>statement</i> =&gt; <i>boolean</i></a>
 593 <blockquote><br>
 594
 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.
 597
 598 </blockquote>
 599
 600 <!-- End of entry for STEP-STATEMENT -->
 601
 602
 603 <!-- Entry for WITH-TRANSACTION -->
 604
 605 <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>
 606 <blockquote><br>
 607
 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.
 609
 610 </blockquote>
 611
 612 <!-- End of entry for WITH-TRANSACTION -->
 613
 614
 615 <!-- Entry for WITH-OPEN-DATABASE -->
 616
 617 <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>
 618 <blockquote><br>
 619
 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.
 622
 623 </blockquote>
 624
 625 <!-- End of entry for WITH-OPEN-DATABASE -->
 626
 627 <br>&nbsp;<br><h3><a class=none name="support">Support</a></h3>
 628
 629 This package is written by <a href="mailto:Kalyanov.Dmitry@gmail.com">Kalyanov Dmitry</a>.<br>
 630
 631 This project has a <a href="http://common-lisp.net/mailman/listinfo/cl-sqlite-devel">cl-sqlite-devel</a> mailing list.<br>
 632
 633 <br>&nbsp;<br><h3><a class=none name="changelog">Changelog</a></h3>
 634
 635 <ul>
 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.
 643 </ul>
 644
 645 <br>&nbsp;<br><h3><a class=none name="ack">Acknowledgements</a></h3>
 646
 647 <p>
 648 This documentation was prepared with <a href="http://weitz.de/documentation-template/">DOCUMENTATION-TEMPLATE</a>.
 649 </p>
 650 <p>
 651 $Header: /usr/local/cvsrep/documentation-template/output.lisp,v 1.14 2008/05/29 08:23:37 edi Exp $
 652
 653 </body>
 654 </html>