1.\" $NetBSD: sqlite.3lua,v 1.4 2014/01/06 09:30:26 wiz Exp $ 2.\" 3.\" Copyright (c) 2013 Marc Balmer <mbalmer@NetBSD.org>. All rights reserved. 4.\" 5.\" Redistribution and use in source and binary forms, with or without 6.\" modification, are permitted provided that the following conditions 7.\" are met: 8.\" 1. Redistributions of source code must retain the above copyright 9.\" notice, this list of conditions and the following disclaimer. 10.\" 2. Redistributions in binary form must reproduce the above copyright 11.\" notice, this list of conditions and the following disclaimer in the 12.\" documentation and/or other materials provided with the distribution. 13.\" 3. Neither the name of the University nor the names of its contributors 14.\" may be used to endorse or promote products derived from this software 15.\" without specific prior written permission. 16.\" 17.\" THIS SOFTWARE IS PROVIDED BY THE REGENTS AND CONTRIBUTORS ``AS IS'' AND 18.\" ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE 19.\" IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE 20.\" ARE DISCLAIMED. IN NO EVENT SHALL THE REGENTS OR CONTRIBUTORS BE LIABLE 21.\" FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL 22.\" DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS 23.\" OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) 24.\" HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT 25.\" LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY 26.\" OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF 27.\" SUCH DAMAGE. 28.\" 29.\" 30.Dd October 28, 2013 31.Dt SQLITE 3lua 32.Os 33.Sh NAME 34.Nm sqlite 35.Nd access 36SQLite3 files from Lua 37.Sh SYNOPSIS 38.Cd "local sqlite = require 'sqlite'" 39.Pp 40.Bl -tag -width XXXX -compact 41.\" 42.\" GENERAL FUNCTIONS 43.\" 44.It Dv err = sqlite.initialize() 45.It Dv sqlite.shutdown() 46.It Dv db, err = sqlite.open(file [, flags]) 47.It Dv version = sqlite.libversion() 48.It Dv version = sqlite.libversion_number() 49.It Dv id = sqlite.sourceid() 50.\" 51.\" DATABASE FUNCTIONS 52.\" 53.Pp 54.It Dv err = sqlite.close(db) 55.It Dv stmt, err = sqlite.prepare(db, sql) 56.It Dv err = sqlite.exec(db, sql) 57.It Dv err = sqlite.errcode(db) 58.It Dv msg = sqlite.errmsg(db) 59.It Dv res = sqlite.get_autocommit(db) 60.It Dv res = sqlite.changes(db) 61.\" 62.\" STATEMENT FUNCTIONS 63.\" 64.Pp 65.It Dv err = sqlite.bind(stmt, pidx, value) 66.It Dv count = sqlite.bind_parameter_count(stmt) 67.It Dv pidx = sqlite.bind_parameter_index(stmt, name) 68.It Dv name = sqlite.bind_parameter_name(stmt, pidx) 69.It Dv err = sqlite.step(stmt) 70.It Dv value = sqlite.column(stmt, cidx) 71.It Dv sqlite.reset(stmt) 72.It Dv sqlite.clear_bindings(stmt) 73.It Dv sqlite.finalize(stmt) 74.It Dv name = sqlite.column_name(stmt, cidx) 75.It Dv count = sqlite.column_count(stmt) 76.El 77.Sh DESCRIPTION 78The 79.Nm 80Lua binding provides access to SQLite3 files. 81.Sh GENERAL FUNCTIONS 82.Bl -tag -width XXXX -compact 83.It Dv err = sqlite.initialize() 84Initialize the SQLite3 library. 85Workstation applications using SQLite normally do not need to invoke this 86function. 87.Pp 88.It Dv sqlite.shutdown() 89Deallocate any resources that were allocated by 90.Fn sqlite.initialize . 91Workstation applications using SQLite normally do not need to invoke this 92function. 93.Pp 94.It Dv db, err = sqlite.open(file [, flags]) 95Open a database, optionally passing flags. 96When called without flags, the database will be opened for reading and 97writing and it will be created if it does not yet exist. 98The following flags are defined: 99.Pp 100.Bl -tag -width XXXX -compact 101.It Dv sqlite.OPEN_READONLY 102The database is opened in read-only mode. 103If the database does not already exist, an error is returned. 104.Pp 105.It Dv sqlite.OPEN_READWRITE 106The database is opened for reading and writing if possible, or reading only if 107the file is write protected by the operating system. 108In either case the database must already exist, otherwise an error is returned. 109.Pp 110.It Dv sqlite.OPEN_CREATE 111The database is opened for reading and writing, and is created if it does not 112already exist. 113.El 114.Pp 115.It Dv version = sqlite.libversion() 116Return the SQLite3 library version number as a string. 117.Pp 118.It Dv version = sqlite.libversion_number() 119Return the SQLite3 library version number as a number. 120.Pp 121.It Dv id = sqlite.sourceid() 122Return the SQLite3 library source id as a string. 123.El 124.Sh DATABASE FUNCTIONS 125Database functions operate on database objects returned by 126.Fn sqlite.open . 127.Pp 128.Bl -tag -width XXXX -compact 129.It Dv err = sqlite.close(db) 130Close an open database. 131Like with all remaining database functions, this function can also be called 132using the Lua "colon" syntactic sugar as 133.Em db:close() . 134.Pp 135.It Dv stmt, err = sqlite.prepare(db, sql) 136Return a prepared statement. 137.Pp 138.It Dv err = sqlite.exec(db, sql) 139Directly execute an SQL statement. 140Be careful when creating SQL on the fly (SQL injection attacks). 141.Pp 142.It Dv err = sqlite.errcode(db) 143Return the numeric error code. 144.Pp 145.It Dv msg = sqlite.errmsg(db) 146Return the error message as a string. 147.Pp 148.It Dv res = sqlite.get_autocommit(db) 149Return the autocommit flag. 150.Pp 151.It Dv res = sqlite.changes(db) 152This function returns the number of database rows that were changed or inserted 153or deleted by the most recently completed SQL statement on the database. 154.El 155.Sh STATEMENT FUNCTIONS 156.Bl -tag -width XXXX -compact 157.It Dv err = sqlite.bind(stmt, pidx, value) 158Bind 159.Ar value 160to the parameter 161.Ar pidx 162in the prepared statement 163.Ar stmt . 164.Pp 165.It Dv count = sqlite.bind_parameter_count(stmt) 166Return the number of parameters in the prepared statement 167.Ar stmt . 168.Pp 169.It Dv pidx = sqlite.bind_parameter_index(stmt, name) 170Return the parameter index for 171.Ar name 172in the prepared statement 173.Ar stmt . 174.Pp 175.It Dv name = sqlite.bind_parameter_name(stmt, pidx) 176Return the parameter name for the parameter index 177.Ar pidx 178in the prepared statement 179.Ar stmt . 180.Pp 181.It Dv err = sqlite.step(stmt) 182Execute prepared statement 183.Ar stmt . 184.Pp 185.It Dv value = sqlite.column(stmt, cidx) 186Return the value at column 187.Ar cidx 188in the prepared statement 189.Ar stmt . 190.Pp 191.It Dv sqlite.reset(stmt) 192The 193.Fn sqlite.reset 194function is called to reset a prepared statement object back 195to its initial state, ready to be re-executed. 196.Pp 197.It Dv sqlite.clear_bindings(stmt) 198Contrary to the intuition of many, 199.Fn sqlite.reset 200does not reset the bindings on a prepared statement. 201Use this routine to reset all host parameters to 202.Dv NULL . 203.Pp 204.It Dv sqlite.finalize(stmt) 205The 206.Fn sqlite.finalize 207function is called to delete a prepared statement. 208.Pp 209.It Dv name = sqlite.column_name(stmt, cidx) 210Return the name assigned to a particular column in the result set of a SELECT 211statement. 212.Pp 213.It Dv count = sqlite.column_count(stmt) 214Return the number of columns in the result set returned by the prepared 215statement 216.Ar stmt . 217This routine returns 0 if 218.Ar stmt 219is an SQL statement that does not return data (for example an UPDATE). 220.El 221.Sh ERROR CODES 222Most functions return an error code, the following error codes 223are defined: 224.Pp 225.Bl -tag -width XXXX -compact 226.It Dv sqlite.OK 227Successful result. 228.Pp 229.It Dv sqlite.ERROR 230SQL error or missing database. 231.Pp 232.It Dv sqlite.INTERNAL 233Internal logic error in SQLite. 234.Pp 235.It Dv sqlite.PERM 236Access permission denied. 237.Pp 238.It Dv sqlite.ABORT 239Callback routine requested an abort. 240.Pp 241.It Dv sqlite.BUSY 242The database file is locked. 243.Pp 244.It Dv sqlite.LOCKED 245A table in the database is locked. 246.Pp 247.It Dv sqlite.NOMEM 248Out of memory. 249.Pp 250.It Dv sqlite.READONLY 251Attempt to write a readonly database. 252.Pp 253.It Dv sqlite.INTERRUPT 254Operation terminated by 255.Fn sqlite3_interrupt . 256.Pp 257.It Dv sqlite.IOERR 258Some kind of disk I/O error occurred. 259.Pp 260.It Dv sqlite.CORRUPT 261The database disk image is malformed. 262.Pp 263.It Dv sqlite.NOTFOUND 264Unknown opcode in 265.Fn sqlite3_file_control . 266.Pp 267.It Dv sqlite.FULL 268Insertion failed because database is full. 269.Pp 270.It Dv sqlite.CANTOPEN 271Unable to open the database file. 272.Pp 273.It Dv sqlite.PROTOCOL 274Database lock protocol error. 275.Pp 276.It Dv sqlite.EMPTY 277Database is empty. 278.Pp 279.It Dv sqlite.SCHEMA 280The database schema changed. 281.Pp 282.It Dv sqlite.TOOBIG 283String or BLOB exceeds size limit. 284.Pp 285.It Dv sqlite.CONSTRAINT 286Abort due to constraint violation. 287.Pp 288.It Dv sqlite.MISMATCH 289Data type mismatch. 290.Pp 291.It Dv sqlite.MISUSE 292Library used incorrectly. 293.Pp 294.It Dv sqlite.NOLFS 295Uses OS features not supported on host. 296.Pp 297.It Dv sqlite.AUTH 298Authorization denied. 299.Pp 300.It Dv sqlite.FORMAT 301Auxiliary database format error. 302.Pp 303.It Dv sqlite.RANGE 3042nd parameter to 305.Fn sqlite.bind 306out of range. 307.Pp 308.It Dv sqlite.NOTADB 309File opened that is not a database file. 310.Pp 311.It Dv sqlite.ROW 312.Fn sqlite.step 313has another row ready. 314.Pp 315.It Dv sqlite.DONE 316.Fn sqlite.step 317has finished executing. 318.El 319.Sh SEE ALSO 320.Xr lua 1 , 321.Xr luac 1 , 322.Xr sqlite3 1 , 323.Xr intro 3lua 324.Sh HISTORY 325An 326.Nm 327manual appeared in 328.Nx 7.0 . 329.Sh AUTHORS 330.An -nosplit 331The 332.Nm 333Lua binding was written by 334.An Marc Balmer Aq Mt mbalmer@NetBSD.org . 335