1.\" $NetBSD: sqlite.3lua,v 1.2 2013/12/23 12:50:56 njoly 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 sqlite.initialize(). 90Workstation applications using SQLite normally do not need to invoke this 91function. 92.Pp 93.It Dv db, err = sqlite.open(file [, flags]) 94Open a database, optionally passing flags. 95When called without flags, the database will be opened for reading and 96writing and it will be created if it does not yet exist. 97The following flags are defined: 98.Pp 99.Bl -tag -width XXXX -compact 100.It Dv sqlite.OPEN_READONLY 101The database is opened in read-only mode. 102If the database does not already exist, an error is returned. 103.Pp 104.It Dv sqlite.OPEN_READWRITE 105The database is opened for reading and writing if possible, or reading only if 106the file is write protected by the operating system. 107In either case the database must already exist, otherwise an error is returned. 108.Pp 109.It Dv sqlite.OPEN_CREATE 110The database is opened for reading and writing, and is created if it does not 111already exist. 112.El 113.Pp 114.It Dv version = sqlite.libversion() 115Return the SQLite3 library version number as a string. 116.Pp 117.It Dv version = sqlite.libversion_number() 118Return the SQLite3 library version number as a number. 119.Pp 120.It Dv id = sqlite.sourceid() 121Return the SQLite3 library source id as a string. 122.El 123.Sh DATABASE FUNCTIONS 124Database functions operate on database objects returned by 125.Em sqlite.open() . 126.Pp 127.Bl -tag -width XXXX -compact 128.It Dv err = sqlite.close(db) 129Close an open database. 130Like with all remaining database functions, this function can also be called 131using the Lua "colon" syntactic sugar as 132.Em db:close() . 133.Pp 134.It Dv stmt, err = sqlite.prepare(db, sql) 135Return a prepared statement. 136.Pp 137.It Dv err = sqlite.exec(db, sql) 138Directly execute an SQL statement. 139Be careful when creating SQL on the fly (SQL injection attacks). 140.Pp 141.It Dv err = sqlite.errcode(db) 142Return the numeric error code. 143.Pp 144.It Dv msg = sqlite.errmsg(db) 145Return the error message as a string. 146.Pp 147.It Dv res = sqlite.get_autocommit(db) 148Return the autocommit flag. 149.Pp 150.It Dv res = sqlite.changes(db) 151This function returns the number of database rows that were changed or inserted 152or deleted by the most recently completed SQL statement on the database. 153.El 154.Sh STATEMENT FUNCTIONS 155.Bl -tag -width XXXX -compact 156.It Dv err = sqlite.bind(stmt, pidx, value) 157Bind 158.Ar value 159to the parameter 160.Ar pidx 161in the prepared statement 162.Ar stmt . 163.Pp 164.It Dv count = sqlite.bind_parameter_count(stmt) 165Return the number of parameters in the prepared statement 166.Ar stmt . 167.Pp 168.It Dv pidx = sqlite.bind_parameter_index(stmt, name) 169Return the parameter index for 170.Ar name 171in the prepared statement 172.Ar stmt . 173.Pp 174.It Dv name = sqlite.bind_parameter_name(stmt, pidx) 175Return the parameter name for the parameter index 176.Ar pidx 177in the prepared statement 178.Ar stmt . 179.Pp 180.It Dv err = sqlite.step(stmt) 181Execute prepared statement 182.Ar stmt . 183.Pp 184.It Dv value = sqlite.column(stmt, cidx) 185Return the value at column 186.Ar cidx 187in the prepared statement 188.Ar stmt . 189.Pp 190.It Dv sqlite.reset(stmt) 191The sqlite.reset() function is called to reset a prepared statement object back 192to its initial state, ready to be re-executed. 193.Pp 194.It Dv sqlite.clear_bindings(stmt) 195Contrary to the intuition of many, sqlite.reset() does not reset the bindings on 196a prepared statement. 197Use this routine to reset all host parameters to NULL. 198.Pp 199.It Dv sqlite.finalize(stmt) 200The sqlite.finalize() function is called to delete a prepared statement. 201.Pp 202.It Dv name = sqlite.column_name(stmt, cidx) 203Return the name assigned to a particular column in the result set of a SELECT 204statement. 205.Pp 206.It Dv count = sqlite.column_count(stmt) 207Return the number of columns in the result set returned by the prepared 208statement 209.Ar stmt . 210This routine returns 0 if 211.Ar stmt 212is an SQL statement that does not return data (for example an UPDATE). 213.El 214.Sh ERROR CODES 215Most functions return an error code, the following error codes 216are defined: 217.Pp 218.Bl -tag -width XXXX -compact 219.It Dv sqlite.OK 220Successful result. 221.Pp 222.It Dv sqlite.ERROR 223SQL error or missing database. 224.Pp 225.It Dv sqlite.INTERNAL 226Internal logic error in SQLite. 227.Pp 228.It Dv sqlite.PERM 229Access permission denied. 230.Pp 231.It Dv sqlite.ABORT 232Callback routine requested an abort. 233.Pp 234.It Dv sqlite.BUSY 235The database file is locked. 236.Pp 237.It Dv sqlite.LOCKED 238A table in the database is locked. 239.Pp 240.It Dv sqlite.NOMEM 241Out of memory. 242.Pp 243.It Dv sqlite.READONLY 244Attempt to write a readonly database. 245.Pp 246.It Dv sqlite.INTERRUPT 247Operation terminated by sqlite3_interrupt(). 248.Pp 249.It Dv sqlite.IOERR 250Some kind of disk I/O error occurred. 251.Pp 252.It Dv sqlite.CORRUPT 253The database disk image is malformed. 254.Pp 255.It Dv sqlite.NOTFOUND 256Unknown opcode in sqlite3_file_control(). 257.Pp 258.It Dv sqlite.FULL 259Insertion failed because database is full. 260.Pp 261.It Dv sqlite.CANTOPEN 262Unable to open the database file. 263.Pp 264.It Dv sqlite.PROTOCOL 265Database lock protocol error. 266.Pp 267.It Dv sqlite.EMPTY 268Database is empty. 269.Pp 270.It Dv sqlite.SCHEMA 271The database schema changed. 272.Pp 273.It Dv sqlite.TOOBIG 274String or BLOB exceeds size limit. 275.Pp 276.It Dv sqlite.CONSTRAINT 277Abort due to constraint violation. 278.Pp 279.It Dv sqlite.MISMATCH 280Data type mismatch. 281.Pp 282.It Dv sqlite.MISUSE 283Library used incorrectly. 284.Pp 285.It Dv sqlite.NOLFS 286Uses OS features not supported on host. 287.Pp 288.It Dv sqlite.AUTH 289Authorization denied. 290.Pp 291.It Dv sqlite.FORMAT 292Auxiliary database format error. 293.Pp 294.It Dv sqlite.RANGE 2952nd parameter to sqlite.bind() out of range. 296.Pp 297.It Dv sqlite.NOTADB 298File opened that is not a database file. 299.Pp 300.It Dv sqlite.ROW 301sqlite.step() has another row ready. 302.Pp 303.It Dv sqlite.DONE 304sqlite.step() has finished executing. 305.El 306.Sh SEE ALSO 307.Xr lua 1 , 308.Xr luac 1 , 309.Xr sqlite3 1 , 310.Xr intro 3lua 311.Sh HISTORY 312An 313.Nm 314manual appeared in 315.Nx 7.0 . 316.Sh AUTHORS 317.An -nosplit 318The 319.Nm 320Lua binding was written by 321.An Marc Balmer Aq Mt mbalmer@NetBSD.org . 322