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