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 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>
 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   <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.
 644 </ul>
 645
 646 <br>&nbsp;<br><h3><a class=none name="ack">Acknowledgements</a></h3>
 647
 648 <p>
 649 This documentation was prepared with <a href="http://weitz.de/documentation-template/">DOCUMENTATION-TEMPLATE</a>.
 650 </p>
 651 <p>
 652 $Header: /usr/local/cvsrep/documentation-template/output.lisp,v 1.14 2008/05/29 08:23:37 edi Exp $
 653
 654 </body>
 655 </html>