(p "The API of SQLite changed significantly from version 2.x to 3.x. These are new bindings to the modified API, which are reasonably complete -- most procedures that take callback arguments are missing, though.")

14

(p "For in-depth information on the functionality of the routines and general information you should consult the " (url "http://www.sqlite.org/" "SQLite documentation") " as well as this manual.")

15

16

(subsection

17

"Exceptions"

18

(p "Unless otherwise indicated, all procedures and methods in this egg may throw an exception of the kind " (tt "(exn sqlite3)") " if something goes wrong. This exception will contain a " (tt "status") " property indicating the return value of the operation that failed:"

(p "If " (tt "proc") " is given, registers a new collation sequence identified by " (tt "name") " for use in the context of database handle " (tt "db") ". If no procedure is passed, the collation sequence with the given name is removed.")

(p "As " (tt "proc") " will be called in a callback context from within " (tt "sqlite3:step!") ", safety measures are installed to avoid throwing any exceptions, invoking continuations or returning invalid values from it. Attempts to do so will result in a " (tt "0") " return value and warning messages."))

(p "If " (tt "proc") " is given, registers a new SQL function identified by " (tt "name") " for use in the context of database handle " (tt "db") ". If " (tt "step-proc") " and " (tt "final-proc") " are given, the new function becomes an aggregate function. Once registered, functions cannot be deleted.")

77

(p (tt "n") " is the number of parameters the new SQL function takes or " (tt "-1") " to allow any number of arguments.")

78

(p (tt "proc") " should have the signature " (tt "(proc . params) => <top>") ". It is called with the " (tt "n") " parameters given to the SQL function converted into Scheme objects like by " (tt "sqlite3:column-data") ". The return value is converted into an SQLite3 data object like by " (tt "sqlite3:bind!") ". A return value of " (tt "(void)") " corresponds to " (tt "NULL") " in SQLite3.")

79

(p (tt "step-proc") " should have the signature " (tt "(step-proc (seed <top>) . params) => <top>") ". It is called with the parameters given to the SQL function for every row being processed. The seed value passed is initially the one given as an argument to " (tt "sqlite3:define-function") "; for subsequent calls it is the last value returned by " (tt "step-proc") " and after completion of " (tt "final-proc") " it will be the initial value again.")

80

(p (tt "final-proc") " should have the signature " (tt "(final-proc (seed <top>)) => <top>") " and transforms the last seed value into the value to be returned from the aggregate function.")

81

(p "As " (tt "proc") ", " (tt "step-proc") " and " (tt "final-proc") " will be called in a callback context from within " (tt "sqlite3:step!") ", safety measures are installed to avoid throwing any exceptions, invoking continuations or returning invalid values from them. Attempts to do such things will result in " (tt "NULL") " return values and warning messages."))

(p "Installs a busy handler that waits at least the specified amount of milliseconds for locks on the given database. If " (tt "(<= ms 0)") " though, all busy handlers for the database are uninstalled."))

85

(procedure

86

"(sqlite3:interrupt! (db <sqlite3:database>)) => <void>"

87

(p "Cancels any running database operation as soon as possible.")

88

(p "This function is always successful and never throws an exception."))

89

(procedure

90

"(sqlite3:auto-committing? (db <sqlite3:database>)) => <bool>"

91

(p "Checks whether the database is currently in auto committing mode, i.e. no transaction is currently active.")

92

(p "This function always returns a state and never throws an exception."))

(p "Compiles the first SQL statement in " (tt "sql") " and returns a " (tt "<sqlite3:statement>") " and the rest of " (tt "sql") ", which was not compiled (or an empty string)."))

111

(procedure

112

"(sqlite3:repair! (stmt <sqlite3:statement>)) => <void>"

113

(p "Recompiles the SQL statement used to create " (tt "stmt") ", transfers all existing bindings from the old statement handle to the new one and destructively modifies " (tt "stmt") " to point to the new statement handle.")

114

(p "If the operation is successful, the old handle is finalized, in case of error, the new handle is finalized and the old one stays untouched.")

115

(p "Usually you should not have to call this routine by hand. It is invoked by " (tt "sqlite3:step!") " to automagically repair a stale statement handle after a database schema change."))

116

(procedure

117

"(sqlite3:column-count (stmt <sqlite3:statement>)) => <exact>"

118

(p "Can be applied to any statement and returns the number of columns it will return as results.")

(p "Can be applied to any statement and returns the declared type (as given in the " (tt "CREATE") " statement) of the column number " (tt "i") " (counting from 0) as a string or " (tt "#f") " if the column has no declared type.")

(p "Can be applied to any statement to bind its free parameter number " (tt "i") " (counting from 0) to the given value. Scheme types of the value map to SQLite types as follows:"

149

(table

150

(tr (th "Scheme type") (th "SQLite type"))

151

(tr (td "none") (td (tt "null")))

152

(tr (td (tt "<sqlite3:null-value>")) (td (tt "null")))

153

(tr (td (tt "<exact>")) (td (tt "integer")))

154

(tr (td (tt "<number>")) (td (tt "float")))

155

(tr (td (tt "<string>")) (td (tt "text")))

156

(tr (td (tt "<byte-vector>")) (td (tt "blob")))))

157

(p "Unless there is internal trouble in SQLite3, this method should always succeeds and never throw an exception. For invalid parameter indices the method just silently does nothing."))

158

(procedure

159

"(sqlite3:step! (stmt <sqlite3:statement>)) => <boolean>"

160

(p "Single-steps the execution of " (tt "stmt") " and returns " (tt "#t") " if a result row was produced, " (tt "#f") " if no further results are available as the statement has been stepped through. This procedure must be called at least once before any results can be retrieved from the statement."))

(p "Can be applied to a statement that has just been stepped. Consults " (tt "sqlite3:column-type") " to determine the type of the indicated column and to return its data as an appropriate scheme object.")

169

(p "See " (tt "sqlite3:bind!") " for the mapping between Scheme and SQLite data types. Columns of type " (tt "null") " are returned as " (tt "<sqlite3:null-value>") ". Also keep in mind that CHICKEN's " (tt "<exact>") " datatype can only hold a subset of the values an SQLite " (tt "integer") " can store. Large integer values may therefore be returned as floating point numbers from the database, but they will still be of class " (tt "<integer>") ".")

170

(p "This procedure always succeeds and never throws an exception."))

171

(procedure

172

"(sqlite3:reset! (stmt <sqlite3:statement>)) => <void>"

173

(p "Can be applied to any statement and resets it such that execution using " (tt "sqlite3:step!") " will perform all operations of the statement again."))

174

(method

175

"(sqlite3:finalize! (stmt <sqlite3:statement>)) => <void>"

176

(p "Must be applied to every statement to free its resources and discard it.")

177

(p (tt "sqlite3:close") " will not be able to close a database that has associated unfinalized statements."))))

(p "(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes the specified statement ignoring possible results from it, returning the result of applying " (tt "sqlite3:changes") " to the affected database after the execution of the statement instead."))

(p "(Compiles the given SQL), resets the statement, binds the statement's free parameters and single-steps the statement once returning the value of the first column in the first result row. Resets the statement again just before returning.")

200

(p "If the given statement does not yield any results, an " (tt "(exn sqlite3)") " is thrown with the " (tt "status") "-property set to " (tt "done") "."))

(p "(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes it step by step. After each step, the column values of the current result row are retrieved and " (tt "proc") " is applied to them. The results of this application are discarded."))

(p "(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes it step by step. After each step, the column values of the current result row are retrieved and " (tt "proc") " is applied to them. The results of these applications are collected into a list."))))

(p "The API of SQLite changed significantly from version 2.x to 3.x. These are new bindings to the modified API, which are reasonably complete -- most procedures that take callback arguments are missing, though.")

42

(p "For in-depth information on the functionality of the routines and general information you should consult the " (url "http://www.sqlite.org/" "SQLite documentation") " as well as this manual.")

43

(p "Unless otherwise indicated, all procedures and methods in this egg may throw an exception of the kind " (tt "(exn sqlite3)") " if something goes wrong. This exception will contain a " (tt "status") " property indicating the return value of the operation that failed:"

(p "If " (tt "proc") " is given, registers a new collation sequence identified by " (tt "name") " for use in the context of database handle " (tt "db") ". If no procedure is passed, the collation sequence with the given name is removed.")

(p "As " (tt "proc") " will be called in a callback context from within " (tt "sqlite3:step!") ", safety measures are installed to avoid throwing any exceptions, invoking continuations or returning invalid values from it. Attempts to do so will result in a " (tt "0") " return value and warning messages."))

(p "If " (tt "proc") " is given, registers a new SQL function identified by " (tt "name") " for use in the context of database handle " (tt "db") ". If " (tt "step-proc") " and " (tt "final-proc") " are given, the new function becomes an aggregate function. Once registered, functions cannot be deleted.")

103

(p (tt "n") " is the number of parameters the new SQL function takes or " (tt "-1") " to allow any number of arguments.")

104

(p (tt "proc") " should have the signature " (tt "(proc . params) => <top>") ". It is called with the " (tt "n") " parameters given to the SQL function converted into Scheme objects like by " (tt "sqlite3:column-data") ". The return value is converted into an SQLite3 data object like by " (tt "sqlite3:bind!") ". A return value of " (tt "(void)") " corresponds to " (tt "NULL") " in SQLite3.")

105

(p (tt "step-proc") " should have the signature " (tt "(step-proc (seed <top>) . params) => <top>") ". It is called with the parameters given to the SQL function for every row being processed. The seed value passed is initially the one given as an argument to " (tt "sqlite3:define-function") "; for subsequent calls it is the last value returned by " (tt "step-proc") " and after completion of " (tt "final-proc") " it will be the initial value again.")

106

(p (tt "final-proc") " should have the signature " (tt "(final-proc (seed <top>)) => <top>") " and transforms the last seed value into the value to be returned from the aggregate function.")

107

(p "As " (tt "proc") ", " (tt "step-proc") " and " (tt "final-proc") " will be called in a callback context from within " (tt "sqlite3:step!") ", safety measures are installed to avoid throwing any exceptions, invoking continuations or returning invalid values from them. Attempts to do such things will result in " (tt "NULL") " return values and warning messages."))

(p "Installs a busy handler that waits at least the specified amount of milliseconds for locks on the given database. If " (tt "(<= ms 0)") " though, all busy handlers for the database are uninstalled."))

111

(procedure

112

"(sqlite3:interrupt! (db <sqlite3:database>)) => <void>"

113

(p "Cancels any running database operation as soon as possible.")

114

(p "This function is always successful and never throws an exception."))

115

(procedure

116

"(sqlite3:auto-committing? (db <sqlite3:database>)) => <bool>"

117

(p "Checks whether the database is currently in auto committing mode, i.e. no transaction is currently active.")

118

(p "This function always returns a state and never throws an exception."))

(p "Compiles the first SQL statement in " (tt "sql") " and returns a " (tt "<sqlite3:statement>") " and the rest of " (tt "sql") ", which was not compiled (or an empty string)."))

137

(procedure

138

"(sqlite3:repair! (stmt <sqlite3:statement>)) => <void>"

139

(p "Recompiles the SQL statement used to create " (tt "stmt") ", transfers all existing bindings from the old statement handle to the new one and destructively modifies " (tt "stmt") " to point to the new statement handle.")

140

(p "If the operation is successful, the old handle is finalized, in case of error, the new handle is finalized and the old one stays untouched.")

141

(p "Usually you should not have to call this routine by hand. It is invoked by " (tt "sqlite3:step!") " to automagically repair a stale statement handle after a database schema change."))

142

(procedure

143

"(sqlite3:column-count (stmt <sqlite3:statement>)) => <exact>"

144

(p "Can be applied to any statement and returns the number of columns it will return as results.")

(p "Can be applied to any statement and returns the declared type (as given in the " (tt "CREATE") " statement) of the column number " (tt "i") " (counting from 0) as a string or " (tt "#f") " if the column has no declared type.")

(p "Can be applied to any statement to bind its free parameter number " (tt "i") " (counting from 0) to the given value. Scheme types of the value map to SQLite types as follows:"

175

(table

176

(tr (th "Scheme type") (th "SQLite type"))

177

(tr (td "none") (td (tt "null")))

178

(tr (td (tt "<void>")) (td (tt "null")))

179

(tr (td (tt "<exact>")) (td (tt "integer")))

180

(tr (td (tt "<number>")) (td (tt "float")))

181

(tr (td (tt "<string>")) (td (tt "text")))

182

(tr (td (tt "<byte-vector>")) (td (tt "blob")))))

183

(p "Unless there is internal trouble in SQLite3, this method should always succeeds and never throw an exception. For invalid parameter indices the method just silently does nothing."))

184

(procedure

185

"(sqlite3:step! (stmt <sqlite3:statement>)) => <boolean>"

186

(p "Single-steps the execution of " (tt "stmt") " and returns " (tt "#t") " if a result row was produced, " (tt "#f") " if no further results are available as the statement has been stepped through. This procedure must be called at least once before any results can be retrieved from the statement."))

(p "Can be applied to a statement that has just been stepped. Consults " (tt "sqlite3:column-type") " to determine the type of the indicated column and to return its data as an appropriate scheme object.")

195

(p "See " (tt "sqlite3:bind!") " for the mapping between Scheme and SQLite data types. Columns of type " (tt "null") " are returned as " (tt "(void)") ". Also keep in mind that CHICKEN's " (tt "<exact>") " datatype can only hold a subset of the values an SQLite " (tt "integer") " can store. Large integer values may therefore be returned as floating point numbers from the database, but they will still be of class " (tt "<integer>") ".")

196

(p "This procedure always succeeds and never throws an exception."))

197

(procedure

198

"(sqlite3:reset! (stmt <sqlite3:statement>)) => <void>"

199

(p "Can be applied to any statement and resets it such that execution using " (tt "sqlite3:step!") " will perform all operations of the statement again."))

200

(method

201

"(sqlite3:finalize! (stmt <sqlite3:statement>)) => <void>"

202

(p "Must be applied to every statement to free its resources and discard it.")

203

(p (tt "sqlite3:close") " will not be able to close a database that has associated unfinalized statements."))))

(p "(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes the specified statement ignoring possible results from it, returning the result of applying " (tt "sqlite3:changes") " to the affected database after the execution of the statement instead."))

(p "(Compiles the given SQL), resets the statement, binds the statement's free parameters and single-steps the statement once returning the value of the first column in the first result row. Resets the statement again just before returning.")

226

(p "If the given statement does not yield any results, an " (tt "(exn sqlite3)") " is thrown with the " (tt "status") "-property set to " (tt "done") "."))

(p "(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes it step by step. After each step, the column values of the current result row are retrieved and " (tt "proc") " is applied to them. The results of this application are discarded."))

(p "(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes it step by step. After each step, the column values of the current result row are retrieved and " (tt "proc") " is applied to them. The results of these applications are collected into a list."))))

<p>The API of SQLite changed significantly from version 2.x to 3.x. These are new bindings to the modified API, which are reasonably complete -- most procedures that take callback arguments are missing, though.</p>

170

<p>For in-depth information on the functionality of the routines and general information you should consult the <a href="http://www.sqlite.org/">SQLite documentation</a> as well as this manual.</p>

171

<div class="subsection">

172

<h4>Exceptions</h4>

173

<p>Unless otherwise indicated, all procedures and methods in this egg may throw an exception of the kind <tt>(exn sqlite3)</tt> if something goes wrong. This exception will contain a <tt>status</tt> property indicating the return value of the operation that failed:

174

<table>

175

<tr>

176

<th>Symbol</th>

177

<th>Meaning</th></tr>

178

<tr>

179

<td><tt>error</tt></td>

180

<td>SQL error or missing database </td></tr>

181

<tr>

182

<td><tt>internal</tt></td>

183

<td>An internal logic error in SQLite </td></tr>

184

<tr>

185

<td><tt>permission</tt></td>

186

<td>Access permission denied </td></tr>

187

<tr>

188

<td><tt>abort</tt></td>

189

<td>Callback routine requested an abort </td></tr>

190

<tr>

191

<td><tt>busy</tt></td>

192

<td>The database file is locked </td></tr>

193

<tr>

194

<td><tt>locked</tt></td>

195

<td>A table in the database is locked </td></tr>

196

<tr>

197

<td><tt>no-memory</tt></td>

198

<td>A malloc() failed </td></tr>

199

<tr>

200

<td><tt>read-only</tt></td>

201

<td>Attempt to write a readonly database </td></tr>

202

<tr>

203

<td><tt>interrupt</tt></td>

204

<td>Operation terminated by sqlite-interrupt() </td></tr>

205

<tr>

206

<td><tt>io-error</tt></td>

207

<td>Some kind of disk I/O error occurred </td></tr>

208

<tr>

209

<td><tt>corrupt</tt></td>

210

<td>The database disk image is malformed </td></tr>

211

<tr>

212

<td><tt>not-found</tt></td>

213

<td>(Internal Only) Table or record not found </td></tr>

214

<tr>

215

<td><tt>full</tt></td>

216

<td>Insertion failed because database is full </td></tr>

217

<tr>

218

<td><tt>cant-open</tt></td>

219

<td>Unable to open the database file </td></tr>

220

<tr>

221

<td><tt>protocol</tt></td>

222

<td>Database lock protocol error </td></tr>

223

<tr>

224

<td><tt>empty</tt></td>

225

<td>(Internal Only) Database table is empty </td></tr>

226

<tr>

227

<td><tt>schema</tt></td>

228

<td>The database schema changed </td></tr>

229

<tr>

230

<td><tt>too-big</tt></td>

231

<td>Too much data for one row of a table </td></tr>

232

<tr>

233

<td><tt>constraint</tt></td>

234

<td>Abort due to contraint violation </td></tr>

235

<tr>

236

<td><tt>mismatch</tt></td>

237

<td>Data type mismatch </td></tr>

238

<tr>

239

<td><tt>misuse</tt></td>

240

<td>Library used incorrectly </td></tr>

241

<tr>

242

<td><tt>no-lfs</tt></td>

243

<td>Uses OS features not supported on host </td></tr>

244

<tr>

245

<td><tt>authorization</tt></td>

246

<td> Authorization denied</td></tr>

247

<tr>

248

<td><tt>done</tt></td>

249

<td><tt>sqlite3:step!</tt> has finished executing, so no further data is ready</td></tr></table></p></div>

<p>If <tt>proc</tt> is given, registers a new collation sequence identified by <tt>name</tt> for use in the context of database handle <tt>db</tt>. If no procedure is passed, the collation sequence with the given name is removed.</p>

270

<p><tt>proc</tt> should have the signature <tt>(proc (a &lt;string&gt;) (b &lt;string&gt;)) =&gt; &lt;exact&gt;</tt>. It should return a negative number if <tt>a</tt> sorts before <tt>b</tt>, a positive number if <tt>b</tt> sorts before <tt>a</tt> and zero if <tt>a</tt> and <tt>b</tt> are equal.</p>

271

<p>As <tt>proc</tt> will be called in a callback context from within <tt>sqlite3:step!</tt>, safety measures are installed to avoid throwing any exceptions, invoking continuations or returning invalid values from it. Attempts to do so will result in a <tt>0</tt> return value and warning messages.</p></dd>

<p>If <tt>proc</tt> is given, registers a new SQL function identified by <tt>name</tt> for use in the context of database handle <tt>db</tt>. If <tt>step-proc</tt> and <tt>final-proc</tt> are given, the new function becomes an aggregate function. Once registered, functions cannot be deleted.</p>

276

<p><tt>n</tt> is the number of parameters the new SQL function takes or <tt>-1</tt> to allow any number of arguments.</p>

277

<p><tt>proc</tt> should have the signature <tt>(proc . params) =&gt; &lt;top&gt;</tt>. It is called with the <tt>n</tt> parameters given to the SQL function converted into Scheme objects like by <tt>sqlite3:column-data</tt>. The return value is converted into an SQLite3 data object like by <tt>sqlite3:bind!</tt>. A return value of <tt>(void)</tt> corresponds to <tt>NULL</tt> in SQLite3.</p>

278

<p><tt>step-proc</tt> should have the signature <tt>(step-proc (seed &lt;top&gt;) . params) =&gt; &lt;top&gt;</tt>. It is called with the parameters given to the SQL function for every row being processed. The seed value passed is initially the one given as an argument to <tt>sqlite3:define-function</tt>; for subsequent calls it is the last value returned by <tt>step-proc</tt> and after completion of <tt>final-proc</tt> it will be the initial value again.</p>

279

<p><tt>final-proc</tt> should have the signature <tt>(final-proc (seed &lt;top&gt;)) =&gt; &lt;top&gt;</tt> and transforms the last seed value into the value to be returned from the aggregate function.</p>

280

<p>As <tt>proc</tt>, <tt>step-proc</tt> and <tt>final-proc</tt> will be called in a callback context from within <tt>sqlite3:step!</tt>, safety measures are installed to avoid throwing any exceptions, invoking continuations or returning invalid values from them. Attempts to do such things will result in <tt>NULL</tt> return values and warning messages.</p></dd>

<p>Installs a busy handler that waits at least the specified amount of milliseconds for locks on the given database. If <tt>(&lt;= ms 0)</tt> though, all busy handlers for the database are uninstalled.</p></dd>

<p>Recompiles the SQL statement used to create <tt>stmt</tt>, transfers all existing bindings from the old statement handle to the new one and destructively modifies <tt>stmt</tt> to point to the new statement handle.</p>

312

<p>If the operation is successful, the old handle is finalized, in case of error, the new handle is finalized and the old one stays untouched.</p>

313

<p>Usually you should not have to call this routine by hand. It is invoked by <tt>sqlite3:step!</tt> to automagically repair a stale statement handle after a database schema change.</p></dd>

<p>Can be applied to any statement and returns the declared type (as given in the <tt>CREATE</tt> statement) of the column number <tt>i</tt> (counting from 0) as a string or <tt>#f</tt> if the column has no declared type.</p>

325

<p>This procedure always succeeds and never throws an exception.</p></dd>

<p>Single-steps the execution of <tt>stmt</tt> and returns <tt>#t</tt> if a result row was produced, <tt>#f</tt> if no further results are available as the statement has been stepped through. This procedure must be called at least once before any results can be retrieved from the statement.</p></dd>

<p>Can be applied to a statement that has just been stepped. Consults <tt>sqlite3:column-type</tt> to determine the type of the indicated column and to return its data as an appropriate scheme object.</p>

380

<p>See <tt>sqlite3:bind!</tt> for the mapping between Scheme and SQLite data types. Columns of type <tt>null</tt> are returned as <tt>&lt;sqlite3:null-value&gt;</tt>. Also keep in mind that CHICKEN's <tt>&lt;exact&gt;</tt> datatype can only hold a subset of the values an SQLite <tt>integer</tt> can store. Large integer values may therefore be returned as floating point numbers from the database, but they will still be of class <tt>&lt;integer&gt;</tt>.</p>

381

<p>This procedure always succeeds and never throws an exception.</p></dd>

<p>Compiles the SQL sources in <tt>sqls</tt> into statements in the context of <tt>db</tt>, applies <tt>proc</tt> to these statements and returns <tt>proc</tt>'s result. The statements are created and finalized in <tt>dynamic-wind</tt> entry and exit blocks around the application of <tt>proc</tt>.</p></dd>

<p>(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes the specified statement ignoring possible results from it, returning the result of applying <tt>sqlite3:changes</tt> to the affected database after the execution of the statement instead.</p></dd>

<p>(Compiles the given SQL), resets the statement, binds the statement's free parameters and single-steps the statement once returning the value of the first column in the first result row. Resets the statement again just before returning.</p>

407

<p>If the given statement does not yield any results, an <tt>(exn sqlite3)</tt> is thrown with the <tt>status</tt>-property set to <tt>done</tt>.</p></dd>

<p>(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes it step by step. After each step, the column values of the current result row are retrieved and <tt>proc</tt> is applied to them. The results of this application are discarded.</p></dd>

<p>(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes it step by step. After each step, the column values of the current result row are retrieved and <tt>proc</tt> is applied to them. The results of these applications are collected into a list.</p></dd></dl></div>

<p>Runs <tt>thunk</tt> within the scope of a transaction on the dataase <tt>db</tt>.</p>

427

<p>The transaction is committed upon exit from <tt>thunk</tt> if <tt>thunk</tt> returns a true value. If <tt>thunk</tt> returns a false value or throws an exception, the transaction is rolled back.</p>

428

<p>The <tt>type</tt> of the transaction can be specified as one of the symbols <tt>deferred</tt>, <tt>immediate</tt> or <tt>exclusive</tt>.</p></dd>

<p>The API of SQLite changed significantly from version 2.x to 3.x. These are new bindings to the modified API, which are reasonably complete -- most procedures that take callback arguments are missing, though.</p>

197

<p>For in-depth information on the functionality of the routines and general information you should consult the <a href="http://www.sqlite.org/">SQLite documentation</a> as well as this manual.</p>

198

<p>Unless otherwise indicated, all procedures and methods in this egg may throw an exception of the kind <tt>(exn sqlite3)</tt> if something goes wrong. This exception will contain a <tt>status</tt> property indicating the return value of the operation that failed:

199

<table>

200

<tr>

201

<th>Symbol</th>

202

<th>Meaning</th></tr>

203

<tr>

204

<td><tt>error</tt></td>

205

<td>SQL error or missing database </td></tr>

206

<tr>

207

<td><tt>internal</tt></td>

208

<td>An internal logic error in SQLite </td></tr>

209

<tr>

210

<td><tt>permission</tt></td>

211

<td>Access permission denied </td></tr>

212

<tr>

213

<td><tt>abort</tt></td>

214

<td>Callback routine requested an abort </td></tr>

215

<tr>

216

<td><tt>busy</tt></td>

217

<td>The database file is locked </td></tr>

218

<tr>

219

<td><tt>locked</tt></td>

220

<td>A table in the database is locked </td></tr>

221

<tr>

222

<td><tt>no-memory</tt></td>

223

<td>A malloc() failed </td></tr>

224

<tr>

225

<td><tt>read-only</tt></td>

226

<td>Attempt to write a readonly database </td></tr>

227

<tr>

228

<td><tt>interrupt</tt></td>

229

<td>Operation terminated by sqlite-interrupt() </td></tr>

230

<tr>

231

<td><tt>io-error</tt></td>

232

<td>Some kind of disk I/O error occurred </td></tr>

233

<tr>

234

<td><tt>corrupt</tt></td>

235

<td>The database disk image is malformed </td></tr>

236

<tr>

237

<td><tt>not-found</tt></td>

238

<td>(Internal Only) Table or record not found </td></tr>

239

<tr>

240

<td><tt>full</tt></td>

241

<td>Insertion failed because database is full </td></tr>

242

<tr>

243

<td><tt>cant-open</tt></td>

244

<td>Unable to open the database file </td></tr>

245

<tr>

246

<td><tt>protocol</tt></td>

247

<td>Database lock protocol error </td></tr>

248

<tr>

249

<td><tt>empty</tt></td>

250

<td>(Internal Only) Database table is empty </td></tr>

251

<tr>

252

<td><tt>schema</tt></td>

253

<td>The database schema changed </td></tr>

254

<tr>

255

<td><tt>too-big</tt></td>

256

<td>Too much data for one row of a table </td></tr>

257

<tr>

258

<td><tt>constraint</tt></td>

259

<td>Abort due to contraint violation </td></tr>

260

<tr>

261

<td><tt>mismatch</tt></td>

262

<td>Data type mismatch </td></tr>

263

<tr>

264

<td><tt>misuse</tt></td>

265

<td>Library used incorrectly </td></tr>

266

<tr>

267

<td><tt>no-lfs</tt></td>

268

<td>Uses OS features not supported on host </td></tr>

269

<tr>

270

<td><tt>authorization</tt></td>

271

<td> Authorization denied</td></tr>

272

<tr>

273

<td><tt>done</tt></td>

274

<td><tt>sqlite3:step!</tt> has finished executing, so no further data is ready</td></tr></table></p>

<p>If <tt>proc</tt> is given, registers a new collation sequence identified by <tt>name</tt> for use in the context of database handle <tt>db</tt>. If no procedure is passed, the collation sequence with the given name is removed.</p>

295

<p><tt>proc</tt> should have the signature <tt>(proc (a &lt;string&gt;) (b &lt;string&gt;)) =&gt; &lt;exact&gt;</tt>. It should return a negative number if <tt>a</tt> sorts before <tt>b</tt>, a positive number if <tt>b</tt> sorts before <tt>a</tt> and zero if <tt>a</tt> and <tt>b</tt> are equal.</p>

296

<p>As <tt>proc</tt> will be called in a callback context from within <tt>sqlite3:step!</tt>, safety measures are installed to avoid throwing any exceptions, invoking continuations or returning invalid values from it. Attempts to do so will result in a <tt>0</tt> return value and warning messages.</p></dd>

<p>If <tt>proc</tt> is given, registers a new SQL function identified by <tt>name</tt> for use in the context of database handle <tt>db</tt>. If <tt>step-proc</tt> and <tt>final-proc</tt> are given, the new function becomes an aggregate function. Once registered, functions cannot be deleted.</p>

301

<p><tt>n</tt> is the number of parameters the new SQL function takes or <tt>-1</tt> to allow any number of arguments.</p>

302

<p><tt>proc</tt> should have the signature <tt>(proc . params) =&gt; &lt;top&gt;</tt>. It is called with the <tt>n</tt> parameters given to the SQL function converted into Scheme objects like by <tt>sqlite3:column-data</tt>. The return value is converted into an SQLite3 data object like by <tt>sqlite3:bind!</tt>. A return value of <tt>(void)</tt> corresponds to <tt>NULL</tt> in SQLite3.</p>

303

<p><tt>step-proc</tt> should have the signature <tt>(step-proc (seed &lt;top&gt;) . params) =&gt; &lt;top&gt;</tt>. It is called with the parameters given to the SQL function for every row being processed. The seed value passed is initially the one given as an argument to <tt>sqlite3:define-function</tt>; for subsequent calls it is the last value returned by <tt>step-proc</tt> and after completion of <tt>final-proc</tt> it will be the initial value again.</p>

304

<p><tt>final-proc</tt> should have the signature <tt>(final-proc (seed &lt;top&gt;)) =&gt; &lt;top&gt;</tt> and transforms the last seed value into the value to be returned from the aggregate function.</p>

305

<p>As <tt>proc</tt>, <tt>step-proc</tt> and <tt>final-proc</tt> will be called in a callback context from within <tt>sqlite3:step!</tt>, safety measures are installed to avoid throwing any exceptions, invoking continuations or returning invalid values from them. Attempts to do such things will result in <tt>NULL</tt> return values and warning messages.</p></dd>

<p>Installs a busy handler that waits at least the specified amount of milliseconds for locks on the given database. If <tt>(&lt;= ms 0)</tt> though, all busy handlers for the database are uninstalled.</p></dd>

<p>Recompiles the SQL statement used to create <tt>stmt</tt>, transfers all existing bindings from the old statement handle to the new one and destructively modifies <tt>stmt</tt> to point to the new statement handle.</p>

337

<p>If the operation is successful, the old handle is finalized, in case of error, the new handle is finalized and the old one stays untouched.</p>

338

<p>Usually you should not have to call this routine by hand. It is invoked by <tt>sqlite3:step!</tt> to automagically repair a stale statement handle after a database schema change.</p></dd>

<p>Can be applied to any statement and returns the declared type (as given in the <tt>CREATE</tt> statement) of the column number <tt>i</tt> (counting from 0) as a string or <tt>#f</tt> if the column has no declared type.</p>

350

<p>This procedure always succeeds and never throws an exception.</p></dd>

<p>Single-steps the execution of <tt>stmt</tt> and returns <tt>#t</tt> if a result row was produced, <tt>#f</tt> if no further results are available as the statement has been stepped through. This procedure must be called at least once before any results can be retrieved from the statement.</p></dd>

<p>Can be applied to a statement that has just been stepped. Consults <tt>sqlite3:column-type</tt> to determine the type of the indicated column and to return its data as an appropriate scheme object.</p>

405

<p>See <tt>sqlite3:bind!</tt> for the mapping between Scheme and SQLite data types. Columns of type <tt>null</tt> are returned as <tt>(void)</tt>. Also keep in mind that CHICKEN's <tt>&lt;exact&gt;</tt> datatype can only hold a subset of the values an SQLite <tt>integer</tt> can store. Large integer values may therefore be returned as floating point numbers from the database, but they will still be of class <tt>&lt;integer&gt;</tt>.</p>

406

<p>This procedure always succeeds and never throws an exception.</p></dd>

<p>Compiles the SQL sources in <tt>sqls</tt> into statements in the context of <tt>db</tt>, applies <tt>proc</tt> to these statements and returns <tt>proc</tt>'s result. The statements are created and finalized in <tt>dynamic-wind</tt> entry and exit blocks around the application of <tt>proc</tt>.</p></dd>

<p>(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes the specified statement ignoring possible results from it, returning the result of applying <tt>sqlite3:changes</tt> to the affected database after the execution of the statement instead.</p></dd>

<p>(Compiles the given SQL), resets the statement, binds the statement's free parameters and single-steps the statement once returning the value of the first column in the first result row. Resets the statement again just before returning.</p>

432

<p>If the given statement does not yield any results, an <tt>(exn sqlite3)</tt> is thrown with the <tt>status</tt>-property set to <tt>done</tt>.</p></dd>

<p>(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes it step by step. After each step, the column values of the current result row are retrieved and <tt>proc</tt> is applied to them. The results of this application are discarded.</p></dd>

<p>(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes it step by step. After each step, the column values of the current result row are retrieved and <tt>proc</tt> is applied to them. The results of these applications are collected into a list.</p></dd></dl></div>

<p>Runs <tt>thunk</tt> within the scope of a transaction on the dataase <tt>db</tt>.</p>

452

<p>The transaction is committed upon exit from <tt>thunk</tt> if <tt>thunk</tt> returns a true value. If <tt>thunk</tt> returns a false value or throws an exception, the transaction is rolled back.</p>

453

<p>The <tt>type</tt> of the transaction can be specified as one of the symbols <tt>deferred</tt>, <tt>immediate</tt> or <tt>exclusive</tt>.</p></dd>

(p "The API of SQLite changed significantly from version 2.x to 3.x. These are new bindings to the modified API, which are reasonably complete -- most procedures that take callback arguments are missing, though.")

14

(p "For in-depth information on the functionality of the routines and general information you should consult the " (url "http://www.sqlite.org/" "SQLite documentation") " as well as this manual.")

15

16

(subsection

17

"Exceptions"

18

(p "Unless otherwise indicated, all procedures and methods in this egg may throw an exception of the kind " (tt "(exn sqlite3)") " if something goes wrong. This exception will contain a " (tt "status") " property indicating the return value of the operation that failed:"

(p "If " (tt "proc") " is given, registers a new collation sequence identified by " (tt "name") " for use in the context of database handle " (tt "db") ". If no procedure is passed, the collation sequence with the given name is removed.")

(p "As " (tt "proc") " will be called in a callback context from within " (tt "sqlite3:step!") ", safety measures are installed to avoid throwing any exceptions, invoking continuations or returning invalid values from it. Attempts to do so will result in a " (tt "0") " return value and warning messages."))

(p "If " (tt "proc") " is given, registers a new SQL function identified by " (tt "name") " for use in the context of database handle " (tt "db") ". If " (tt "step-proc") " and " (tt "final-proc") " are given, the new function becomes an aggregate function. Once registered, functions cannot be deleted.")

77

(p (tt "n") " is the number of parameters the new SQL function takes or " (tt "-1") " to allow any number of arguments.")

78

(p (tt "proc") " should have the signature " (tt "(proc . params) => <top>") ". It is called with the " (tt "n") " parameters given to the SQL function converted into Scheme objects like by " (tt "sqlite3:column-data") ". The return value is converted into an SQLite3 data object like by " (tt "sqlite3:bind!") ". A return value of " (tt "(void)") " corresponds to " (tt "NULL") " in SQLite3.")

79

(p (tt "step-proc") " should have the signature " (tt "(step-proc (seed <top>) . params) => <top>") ". It is called with the parameters given to the SQL function for every row being processed. The seed value passed is initially the one given as an argument to " (tt "sqlite3:define-function") "; for subsequent calls it is the last value returned by " (tt "step-proc") " and after completion of " (tt "final-proc") " it will be the initial value again.")

80

(p (tt "final-proc") " should have the signature " (tt "(final-proc (seed <top>)) => <top>") " and transforms the last seed value into the value to be returned from the aggregate function.")

81

(p "As " (tt "proc") ", " (tt "step-proc") " and " (tt "final-proc") " will be called in a callback context from within " (tt "sqlite3:step!") ", safety measures are installed to avoid throwing any exceptions, invoking continuations or returning invalid values from them. Attempts to do such things will result in " (tt "NULL") " return values and warning messages."))

(p "Installs a busy handler that waits at least the specified amount of milliseconds for locks on the given database. If " (tt "(<= ms 0)") " though, all busy handlers for the database are uninstalled."))

85

(procedure

86

"(sqlite3:interrupt! (db <sqlite3:database>)) => <void>"

87

(p "Cancels any running database operation as soon as possible.")

88

(p "This function is always successful and never throws an exception."))

89

(procedure

90

"(sqlite3:auto-committing? (db <sqlite3:database>)) => <bool>"

91

(p "Checks whether the database is currently in auto committing mode, i.e. no transaction is currently active.")

92

(p "This function always returns a state and never throws an exception."))

(p "Compiles the first SQL statement in " (tt "sql") " and returns a " (tt "<sqlite3:statement>") " and the rest of " (tt "sql") ", which was not compiled (or an empty string)."))

111

(procedure

112

"(sqlite3:repair! (stmt <sqlite3:statement>)) => <void>"

113

(p "Recompiles the SQL statement used to create " (tt "stmt") ", transfers all existing bindings from the old statement handle to the new one and destructively modifies " (tt "stmt") " to point to the new statement handle.")

114

(p "If the operation is successful, the old handle is finalized, in case of error, the new handle is finalized and the old one stays untouched.")

115

(p "Usually you should not have to call this routine by hand. It is invoked by " (tt "sqlite3:step!") " to automagically repair a stale statement handle after a database schema change."))

116

(procedure

117

"(sqlite3:column-count (stmt <sqlite3:statement>)) => <exact>"

118

(p "Can be applied to any statement and returns the number of columns it will return as results.")

(p "Can be applied to any statement and returns the declared type (as given in the " (tt "CREATE") " statement) of the column number " (tt "i") " (counting from 0) as a string or " (tt "#f") " if the column has no declared type.")

(p "Can be applied to any statement to bind its free parameter number " (tt "i") " (counting from 0) to the given value. Scheme types of the value map to SQLite types as follows:"

149

(table

150

(tr (th "Scheme type") (th "SQLite type"))

151

(tr (td "none") (td (tt "null")))

152

(tr (td (tt "<sqlite3:null-value>")) (td (tt "null")))

153

(tr (td (tt "<exact>")) (td (tt "integer")))

154

(tr (td (tt "<number>")) (td (tt "float")))

155

(tr (td (tt "<string>")) (td (tt "text")))

156

(tr (td (tt "<byte-vector>")) (td (tt "blob")))))

157

(p "Unless there is internal trouble in SQLite3, this method should always succeeds and never throw an exception. For invalid parameter indices the method just silently does nothing."))

158

(procedure

159

"(sqlite3:step! (stmt <sqlite3:statement>)) => <boolean>"

160

(p "Single-steps the execution of " (tt "stmt") " and returns " (tt "#t") " if a result row was produced, " (tt "#f") " if no further results are available as the statement has been stepped through. This procedure must be called at least once before any results can be retrieved from the statement."))

(p "Can be applied to a statement that has just been stepped. Consults " (tt "sqlite3:column-type") " to determine the type of the indicated column and to return its data as an appropriate scheme object.")

169

(p "See " (tt "sqlite3:bind!") " for the mapping between Scheme and SQLite data types. Columns of type " (tt "null") " are returned as " (tt "<sqlite3:null-value>") ". Also keep in mind that CHICKEN's " (tt "<exact>") " datatype can only hold a subset of the values an SQLite " (tt "integer") " can store. Large integer values may therefore be returned as floating point numbers from the database, but they will still be of class " (tt "<integer>") ".")

170

(p "This procedure always succeeds and never throws an exception."))

171

(procedure

172

"(sqlite3:reset! (stmt <sqlite3:statement>)) => <void>"

173

(p "Can be applied to any statement and resets it such that execution using " (tt "sqlite3:step!") " will perform all operations of the statement again."))

174

(method

175

"(sqlite3:finalize! (stmt <sqlite3:statement>)) => <void>"

176

(p "Must be applied to every statement to free its resources and discard it.")

177

(p (tt "sqlite3:close") " will not be able to close a database that has associated unfinalized statements."))))

(p "(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes the specified statement ignoring possible results from it, returning the result of applying " (tt "sqlite3:changes") " to the affected database after the execution of the statement instead."))

(p "(Compiles the given SQL), resets the statement, binds the statement's free parameters and single-steps the statement once returning the value of the first column in the first result row. Resets the statement again just before returning.")

200

(p "If the given statement does not yield any results, an " (tt "(exn sqlite3)") " is thrown with the " (tt "status") "-property set to " (tt "done") "."))

(p "(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes it step by step. After each step, the column values of the current result row are retrieved and " (tt "proc") " is applied to them. The results of this application are discarded."))

(p "(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes it step by step. After each step, the column values of the current result row are retrieved and " (tt "proc") " is applied to them. The results of these applications are collected into a list."))))

(p "The API of SQLite changed significantly from version 2.x to 3.x. These are new bindings to the modified API, which are reasonably complete -- most procedures that take callback arguments are missing, though.")

42

(p "For in-depth information on the functionality of the routines and general information you should consult the " (url "http://www.sqlite.org/" "SQLite documentation") " as well as this manual.")

43

(p "Unless otherwise indicated, all procedures and methods in this egg may throw an exception of the kind " (tt "(exn sqlite3)") " if something goes wrong. This exception will contain a " (tt "status") " property indicating the return value of the operation that failed:"

(p "If " (tt "proc") " is given, registers a new collation sequence identified by " (tt "name") " for use in the context of database handle " (tt "db") ". If no procedure is passed, the collation sequence with the given name is removed.")

(p "As " (tt "proc") " will be called in a callback context from within " (tt "sqlite3:step!") ", safety measures are installed to avoid throwing any exceptions, invoking continuations or returning invalid values from it. Attempts to do so will result in a " (tt "0") " return value and warning messages."))

(p "If " (tt "proc") " is given, registers a new SQL function identified by " (tt "name") " for use in the context of database handle " (tt "db") ". If " (tt "step-proc") " and " (tt "final-proc") " are given, the new function becomes an aggregate function. Once registered, functions cannot be deleted.")

103

(p (tt "n") " is the number of parameters the new SQL function takes or " (tt "-1") " to allow any number of arguments.")

104

(p (tt "proc") " should have the signature " (tt "(proc . params) => <top>") ". It is called with the " (tt "n") " parameters given to the SQL function converted into Scheme objects like by " (tt "sqlite3:column-data") ". The return value is converted into an SQLite3 data object like by " (tt "sqlite3:bind!") ". A return value of " (tt "(void)") " corresponds to " (tt "NULL") " in SQLite3.")

105

(p (tt "step-proc") " should have the signature " (tt "(step-proc (seed <top>) . params) => <top>") ". It is called with the parameters given to the SQL function for every row being processed. The seed value passed is initially the one given as an argument to " (tt "sqlite3:define-function") "; for subsequent calls it is the last value returned by " (tt "step-proc") " and after completion of " (tt "final-proc") " it will be the initial value again.")

106

(p (tt "final-proc") " should have the signature " (tt "(final-proc (seed <top>)) => <top>") " and transforms the last seed value into the value to be returned from the aggregate function.")

107

(p "As " (tt "proc") ", " (tt "step-proc") " and " (tt "final-proc") " will be called in a callback context from within " (tt "sqlite3:step!") ", safety measures are installed to avoid throwing any exceptions, invoking continuations or returning invalid values from them. Attempts to do such things will result in " (tt "NULL") " return values and warning messages."))

(p "Installs a busy handler that waits at least the specified amount of milliseconds for locks on the given database. If " (tt "(<= ms 0)") " though, all busy handlers for the database are uninstalled."))

111

(procedure

112

"(sqlite3:interrupt! (db <sqlite3:database>)) => <void>"

113

(p "Cancels any running database operation as soon as possible.")

114

(p "This function is always successful and never throws an exception."))

115

(procedure

116

"(sqlite3:auto-committing? (db <sqlite3:database>)) => <bool>"

117

(p "Checks whether the database is currently in auto committing mode, i.e. no transaction is currently active.")

118

(p "This function always returns a state and never throws an exception."))

(p "Compiles the first SQL statement in " (tt "sql") " and returns a " (tt "<sqlite3:statement>") " and the rest of " (tt "sql") ", which was not compiled (or an empty string)."))

137

(procedure

138

"(sqlite3:repair! (stmt <sqlite3:statement>)) => <void>"

139

(p "Recompiles the SQL statement used to create " (tt "stmt") ", transfers all existing bindings from the old statement handle to the new one and destructively modifies " (tt "stmt") " to point to the new statement handle.")

140

(p "If the operation is successful, the old handle is finalized, in case of error, the new handle is finalized and the old one stays untouched.")

141

(p "Usually you should not have to call this routine by hand. It is invoked by " (tt "sqlite3:step!") " to automagically repair a stale statement handle after a database schema change."))

142

(procedure

143

"(sqlite3:column-count (stmt <sqlite3:statement>)) => <exact>"

144

(p "Can be applied to any statement and returns the number of columns it will return as results.")

(p "Can be applied to any statement and returns the declared type (as given in the " (tt "CREATE") " statement) of the column number " (tt "i") " (counting from 0) as a string or " (tt "#f") " if the column has no declared type.")

(p "Can be applied to any statement to bind its free parameter number " (tt "i") " (counting from 0) to the given value. Scheme types of the value map to SQLite types as follows:"

175

(table

176

(tr (th "Scheme type") (th "SQLite type"))

177

(tr (td "none") (td (tt "null")))

178

(tr (td (tt "<void>")) (td (tt "null")))

179

(tr (td (tt "<exact>")) (td (tt "integer")))

180

(tr (td (tt "<number>")) (td (tt "float")))

181

(tr (td (tt "<string>")) (td (tt "text")))

182

(tr (td (tt "<byte-vector>")) (td (tt "blob")))))

183

(p "Unless there is internal trouble in SQLite3, this method should always succeeds and never throw an exception. For invalid parameter indices the method just silently does nothing."))

184

(procedure

185

"(sqlite3:step! (stmt <sqlite3:statement>)) => <boolean>"

186

(p "Single-steps the execution of " (tt "stmt") " and returns " (tt "#t") " if a result row was produced, " (tt "#f") " if no further results are available as the statement has been stepped through. This procedure must be called at least once before any results can be retrieved from the statement."))

(p "Can be applied to a statement that has just been stepped. Consults " (tt "sqlite3:column-type") " to determine the type of the indicated column and to return its data as an appropriate scheme object.")

195

(p "See " (tt "sqlite3:bind!") " for the mapping between Scheme and SQLite data types. Columns of type " (tt "null") " are returned as " (tt "(void)") ". Also keep in mind that CHICKEN's " (tt "<exact>") " datatype can only hold a subset of the values an SQLite " (tt "integer") " can store. Large integer values may therefore be returned as floating point numbers from the database, but they will still be of class " (tt "<integer>") ".")

196

(p "This procedure always succeeds and never throws an exception."))

197

(procedure

198

"(sqlite3:reset! (stmt <sqlite3:statement>)) => <void>"

199

(p "Can be applied to any statement and resets it such that execution using " (tt "sqlite3:step!") " will perform all operations of the statement again."))

200

(method

201

"(sqlite3:finalize! (stmt <sqlite3:statement>)) => <void>"

202

(p "Must be applied to every statement to free its resources and discard it.")

203

(p (tt "sqlite3:close") " will not be able to close a database that has associated unfinalized statements."))))

(p "(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes the specified statement ignoring possible results from it, returning the result of applying " (tt "sqlite3:changes") " to the affected database after the execution of the statement instead."))

(p "(Compiles the given SQL), resets the statement, binds the statement's free parameters and single-steps the statement once returning the value of the first column in the first result row. Resets the statement again just before returning.")

226

(p "If the given statement does not yield any results, an " (tt "(exn sqlite3)") " is thrown with the " (tt "status") "-property set to " (tt "done") "."))

(p "(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes it step by step. After each step, the column values of the current result row are retrieved and " (tt "proc") " is applied to them. The results of this application are discarded."))

(p "(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes it step by step. After each step, the column values of the current result row are retrieved and " (tt "proc") " is applied to them. The results of these applications are collected into a list."))))

<p>The API of SQLite changed significantly from version 2.x to 3.x. These are new bindings to the modified API, which are reasonably complete -- most procedures that take callback arguments are missing, though.</p>

170

<p>For in-depth information on the functionality of the routines and general information you should consult the <a href="http://www.sqlite.org/">SQLite documentation</a> as well as this manual.</p>

171

<div class="subsection">

172

<h4>Exceptions</h4>

173

<p>Unless otherwise indicated, all procedures and methods in this egg may throw an exception of the kind <tt>(exn sqlite3)</tt> if something goes wrong. This exception will contain a <tt>status</tt> property indicating the return value of the operation that failed:

174

<table>

175

<tr>

176

<th>Symbol</th>

177

<th>Meaning</th></tr>

178

<tr>

179

<td><tt>error</tt></td>

180

<td>SQL error or missing database </td></tr>

181

<tr>

182

<td><tt>internal</tt></td>

183

<td>An internal logic error in SQLite </td></tr>

184

<tr>

185

<td><tt>permission</tt></td>

186

<td>Access permission denied </td></tr>

187

<tr>

188

<td><tt>abort</tt></td>

189

<td>Callback routine requested an abort </td></tr>

190

<tr>

191

<td><tt>busy</tt></td>

192

<td>The database file is locked </td></tr>

193

<tr>

194

<td><tt>locked</tt></td>

195

<td>A table in the database is locked </td></tr>

196

<tr>

197

<td><tt>no-memory</tt></td>

198

<td>A malloc() failed </td></tr>

199

<tr>

200

<td><tt>read-only</tt></td>

201

<td>Attempt to write a readonly database </td></tr>

202

<tr>

203

<td><tt>interrupt</tt></td>

204

<td>Operation terminated by sqlite-interrupt() </td></tr>

205

<tr>

206

<td><tt>io-error</tt></td>

207

<td>Some kind of disk I/O error occurred </td></tr>

208

<tr>

209

<td><tt>corrupt</tt></td>

210

<td>The database disk image is malformed </td></tr>

211

<tr>

212

<td><tt>not-found</tt></td>

213

<td>(Internal Only) Table or record not found </td></tr>

214

<tr>

215

<td><tt>full</tt></td>

216

<td>Insertion failed because database is full </td></tr>

217

<tr>

218

<td><tt>cant-open</tt></td>

219

<td>Unable to open the database file </td></tr>

220

<tr>

221

<td><tt>protocol</tt></td>

222

<td>Database lock protocol error </td></tr>

223

<tr>

224

<td><tt>empty</tt></td>

225

<td>(Internal Only) Database table is empty </td></tr>

226

<tr>

227

<td><tt>schema</tt></td>

228

<td>The database schema changed </td></tr>

229

<tr>

230

<td><tt>too-big</tt></td>

231

<td>Too much data for one row of a table </td></tr>

232

<tr>

233

<td><tt>constraint</tt></td>

234

<td>Abort due to contraint violation </td></tr>

235

<tr>

236

<td><tt>mismatch</tt></td>

237

<td>Data type mismatch </td></tr>

238

<tr>

239

<td><tt>misuse</tt></td>

240

<td>Library used incorrectly </td></tr>

241

<tr>

242

<td><tt>no-lfs</tt></td>

243

<td>Uses OS features not supported on host </td></tr>

244

<tr>

245

<td><tt>authorization</tt></td>

246

<td> Authorization denied</td></tr>

247

<tr>

248

<td><tt>done</tt></td>

249

<td><tt>sqlite3:step!</tt> has finished executing, so no further data is ready</td></tr></table></p></div>

<p>If <tt>proc</tt> is given, registers a new collation sequence identified by <tt>name</tt> for use in the context of database handle <tt>db</tt>. If no procedure is passed, the collation sequence with the given name is removed.</p>

270

<p><tt>proc</tt> should have the signature <tt>(proc (a &lt;string&gt;) (b &lt;string&gt;)) =&gt; &lt;exact&gt;</tt>. It should return a negative number if <tt>a</tt> sorts before <tt>b</tt>, a positive number if <tt>b</tt> sorts before <tt>a</tt> and zero if <tt>a</tt> and <tt>b</tt> are equal.</p>

271

<p>As <tt>proc</tt> will be called in a callback context from within <tt>sqlite3:step!</tt>, safety measures are installed to avoid throwing any exceptions, invoking continuations or returning invalid values from it. Attempts to do so will result in a <tt>0</tt> return value and warning messages.</p></dd>

<p>If <tt>proc</tt> is given, registers a new SQL function identified by <tt>name</tt> for use in the context of database handle <tt>db</tt>. If <tt>step-proc</tt> and <tt>final-proc</tt> are given, the new function becomes an aggregate function. Once registered, functions cannot be deleted.</p>

276

<p><tt>n</tt> is the number of parameters the new SQL function takes or <tt>-1</tt> to allow any number of arguments.</p>

277

<p><tt>proc</tt> should have the signature <tt>(proc . params) =&gt; &lt;top&gt;</tt>. It is called with the <tt>n</tt> parameters given to the SQL function converted into Scheme objects like by <tt>sqlite3:column-data</tt>. The return value is converted into an SQLite3 data object like by <tt>sqlite3:bind!</tt>. A return value of <tt>(void)</tt> corresponds to <tt>NULL</tt> in SQLite3.</p>

278

<p><tt>step-proc</tt> should have the signature <tt>(step-proc (seed &lt;top&gt;) . params) =&gt; &lt;top&gt;</tt>. It is called with the parameters given to the SQL function for every row being processed. The seed value passed is initially the one given as an argument to <tt>sqlite3:define-function</tt>; for subsequent calls it is the last value returned by <tt>step-proc</tt> and after completion of <tt>final-proc</tt> it will be the initial value again.</p>

279

<p><tt>final-proc</tt> should have the signature <tt>(final-proc (seed &lt;top&gt;)) =&gt; &lt;top&gt;</tt> and transforms the last seed value into the value to be returned from the aggregate function.</p>

280

<p>As <tt>proc</tt>, <tt>step-proc</tt> and <tt>final-proc</tt> will be called in a callback context from within <tt>sqlite3:step!</tt>, safety measures are installed to avoid throwing any exceptions, invoking continuations or returning invalid values from them. Attempts to do such things will result in <tt>NULL</tt> return values and warning messages.</p></dd>

<p>Installs a busy handler that waits at least the specified amount of milliseconds for locks on the given database. If <tt>(&lt;= ms 0)</tt> though, all busy handlers for the database are uninstalled.</p></dd>

<p>Recompiles the SQL statement used to create <tt>stmt</tt>, transfers all existing bindings from the old statement handle to the new one and destructively modifies <tt>stmt</tt> to point to the new statement handle.</p>

312

<p>If the operation is successful, the old handle is finalized, in case of error, the new handle is finalized and the old one stays untouched.</p>

313

<p>Usually you should not have to call this routine by hand. It is invoked by <tt>sqlite3:step!</tt> to automagically repair a stale statement handle after a database schema change.</p></dd>

<p>Can be applied to any statement and returns the declared type (as given in the <tt>CREATE</tt> statement) of the column number <tt>i</tt> (counting from 0) as a string or <tt>#f</tt> if the column has no declared type.</p>

325

<p>This procedure always succeeds and never throws an exception.</p></dd>

<p>Single-steps the execution of <tt>stmt</tt> and returns <tt>#t</tt> if a result row was produced, <tt>#f</tt> if no further results are available as the statement has been stepped through. This procedure must be called at least once before any results can be retrieved from the statement.</p></dd>

<p>Can be applied to a statement that has just been stepped. Consults <tt>sqlite3:column-type</tt> to determine the type of the indicated column and to return its data as an appropriate scheme object.</p>

380

<p>See <tt>sqlite3:bind!</tt> for the mapping between Scheme and SQLite data types. Columns of type <tt>null</tt> are returned as <tt>&lt;sqlite3:null-value&gt;</tt>. Also keep in mind that CHICKEN's <tt>&lt;exact&gt;</tt> datatype can only hold a subset of the values an SQLite <tt>integer</tt> can store. Large integer values may therefore be returned as floating point numbers from the database, but they will still be of class <tt>&lt;integer&gt;</tt>.</p>

381

<p>This procedure always succeeds and never throws an exception.</p></dd>

<p>Compiles the SQL sources in <tt>sqls</tt> into statements in the context of <tt>db</tt>, applies <tt>proc</tt> to these statements and returns <tt>proc</tt>'s result. The statements are created and finalized in <tt>dynamic-wind</tt> entry and exit blocks around the application of <tt>proc</tt>.</p></dd>

<p>(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes the specified statement ignoring possible results from it, returning the result of applying <tt>sqlite3:changes</tt> to the affected database after the execution of the statement instead.</p></dd>

<p>(Compiles the given SQL), resets the statement, binds the statement's free parameters and single-steps the statement once returning the value of the first column in the first result row. Resets the statement again just before returning.</p>

407

<p>If the given statement does not yield any results, an <tt>(exn sqlite3)</tt> is thrown with the <tt>status</tt>-property set to <tt>done</tt>.</p></dd>

<p>(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes it step by step. After each step, the column values of the current result row are retrieved and <tt>proc</tt> is applied to them. The results of this application are discarded.</p></dd>

<p>(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes it step by step. After each step, the column values of the current result row are retrieved and <tt>proc</tt> is applied to them. The results of these applications are collected into a list.</p></dd></dl></div>

<p>Runs <tt>thunk</tt> within the scope of a transaction on the dataase <tt>db</tt>.</p>

427

<p>The transaction is committed upon exit from <tt>thunk</tt> if <tt>thunk</tt> returns a true value. If <tt>thunk</tt> returns a false value or throws an exception, the transaction is rolled back.</p>

428

<p>The <tt>type</tt> of the transaction can be specified as one of the symbols <tt>deferred</tt>, <tt>immediate</tt> or <tt>exclusive</tt>.</p></dd>

<p>The API of SQLite changed significantly from version 2.x to 3.x. These are new bindings to the modified API, which are reasonably complete -- most procedures that take callback arguments are missing, though.</p>

197

<p>For in-depth information on the functionality of the routines and general information you should consult the <a href="http://www.sqlite.org/">SQLite documentation</a> as well as this manual.</p>

198

<p>Unless otherwise indicated, all procedures and methods in this egg may throw an exception of the kind <tt>(exn sqlite3)</tt> if something goes wrong. This exception will contain a <tt>status</tt> property indicating the return value of the operation that failed:

199

<table>

200

<tr>

201

<th>Symbol</th>

202

<th>Meaning</th></tr>

203

<tr>

204

<td><tt>error</tt></td>

205

<td>SQL error or missing database </td></tr>

206

<tr>

207

<td><tt>internal</tt></td>

208

<td>An internal logic error in SQLite </td></tr>

209

<tr>

210

<td><tt>permission</tt></td>

211

<td>Access permission denied </td></tr>

212

<tr>

213

<td><tt>abort</tt></td>

214

<td>Callback routine requested an abort </td></tr>

215

<tr>

216

<td><tt>busy</tt></td>

217

<td>The database file is locked </td></tr>

218

<tr>

219

<td><tt>locked</tt></td>

220

<td>A table in the database is locked </td></tr>

221

<tr>

222

<td><tt>no-memory</tt></td>

223

<td>A malloc() failed </td></tr>

224

<tr>

225

<td><tt>read-only</tt></td>

226

<td>Attempt to write a readonly database </td></tr>

227

<tr>

228

<td><tt>interrupt</tt></td>

229

<td>Operation terminated by sqlite-interrupt() </td></tr>

230

<tr>

231

<td><tt>io-error</tt></td>

232

<td>Some kind of disk I/O error occurred </td></tr>

233

<tr>

234

<td><tt>corrupt</tt></td>

235

<td>The database disk image is malformed </td></tr>

236

<tr>

237

<td><tt>not-found</tt></td>

238

<td>(Internal Only) Table or record not found </td></tr>

239

<tr>

240

<td><tt>full</tt></td>

241

<td>Insertion failed because database is full </td></tr>

242

<tr>

243

<td><tt>cant-open</tt></td>

244

<td>Unable to open the database file </td></tr>

245

<tr>

246

<td><tt>protocol</tt></td>

247

<td>Database lock protocol error </td></tr>

248

<tr>

249

<td><tt>empty</tt></td>

250

<td>(Internal Only) Database table is empty </td></tr>

251

<tr>

252

<td><tt>schema</tt></td>

253

<td>The database schema changed </td></tr>

254

<tr>

255

<td><tt>too-big</tt></td>

256

<td>Too much data for one row of a table </td></tr>

257

<tr>

258

<td><tt>constraint</tt></td>

259

<td>Abort due to contraint violation </td></tr>

260

<tr>

261

<td><tt>mismatch</tt></td>

262

<td>Data type mismatch </td></tr>

263

<tr>

264

<td><tt>misuse</tt></td>

265

<td>Library used incorrectly </td></tr>

266

<tr>

267

<td><tt>no-lfs</tt></td>

268

<td>Uses OS features not supported on host </td></tr>

269

<tr>

270

<td><tt>authorization</tt></td>

271

<td> Authorization denied</td></tr>

272

<tr>

273

<td><tt>done</tt></td>

274

<td><tt>sqlite3:step!</tt> has finished executing, so no further data is ready</td></tr></table></p>

<p>If <tt>proc</tt> is given, registers a new collation sequence identified by <tt>name</tt> for use in the context of database handle <tt>db</tt>. If no procedure is passed, the collation sequence with the given name is removed.</p>

295

<p><tt>proc</tt> should have the signature <tt>(proc (a &lt;string&gt;) (b &lt;string&gt;)) =&gt; &lt;exact&gt;</tt>. It should return a negative number if <tt>a</tt> sorts before <tt>b</tt>, a positive number if <tt>b</tt> sorts before <tt>a</tt> and zero if <tt>a</tt> and <tt>b</tt> are equal.</p>

296

<p>As <tt>proc</tt> will be called in a callback context from within <tt>sqlite3:step!</tt>, safety measures are installed to avoid throwing any exceptions, invoking continuations or returning invalid values from it. Attempts to do so will result in a <tt>0</tt> return value and warning messages.</p></dd>

<p>If <tt>proc</tt> is given, registers a new SQL function identified by <tt>name</tt> for use in the context of database handle <tt>db</tt>. If <tt>step-proc</tt> and <tt>final-proc</tt> are given, the new function becomes an aggregate function. Once registered, functions cannot be deleted.</p>

301

<p><tt>n</tt> is the number of parameters the new SQL function takes or <tt>-1</tt> to allow any number of arguments.</p>

302

<p><tt>proc</tt> should have the signature <tt>(proc . params) =&gt; &lt;top&gt;</tt>. It is called with the <tt>n</tt> parameters given to the SQL function converted into Scheme objects like by <tt>sqlite3:column-data</tt>. The return value is converted into an SQLite3 data object like by <tt>sqlite3:bind!</tt>. A return value of <tt>(void)</tt> corresponds to <tt>NULL</tt> in SQLite3.</p>

303

<p><tt>step-proc</tt> should have the signature <tt>(step-proc (seed &lt;top&gt;) . params) =&gt; &lt;top&gt;</tt>. It is called with the parameters given to the SQL function for every row being processed. The seed value passed is initially the one given as an argument to <tt>sqlite3:define-function</tt>; for subsequent calls it is the last value returned by <tt>step-proc</tt> and after completion of <tt>final-proc</tt> it will be the initial value again.</p>

304

<p><tt>final-proc</tt> should have the signature <tt>(final-proc (seed &lt;top&gt;)) =&gt; &lt;top&gt;</tt> and transforms the last seed value into the value to be returned from the aggregate function.</p>

305

<p>As <tt>proc</tt>, <tt>step-proc</tt> and <tt>final-proc</tt> will be called in a callback context from within <tt>sqlite3:step!</tt>, safety measures are installed to avoid throwing any exceptions, invoking continuations or returning invalid values from them. Attempts to do such things will result in <tt>NULL</tt> return values and warning messages.</p></dd>

<p>Installs a busy handler that waits at least the specified amount of milliseconds for locks on the given database. If <tt>(&lt;= ms 0)</tt> though, all busy handlers for the database are uninstalled.</p></dd>

<p>Recompiles the SQL statement used to create <tt>stmt</tt>, transfers all existing bindings from the old statement handle to the new one and destructively modifies <tt>stmt</tt> to point to the new statement handle.</p>

337

<p>If the operation is successful, the old handle is finalized, in case of error, the new handle is finalized and the old one stays untouched.</p>

338

<p>Usually you should not have to call this routine by hand. It is invoked by <tt>sqlite3:step!</tt> to automagically repair a stale statement handle after a database schema change.</p></dd>

<p>Can be applied to any statement and returns the declared type (as given in the <tt>CREATE</tt> statement) of the column number <tt>i</tt> (counting from 0) as a string or <tt>#f</tt> if the column has no declared type.</p>

350

<p>This procedure always succeeds and never throws an exception.</p></dd>

<p>Single-steps the execution of <tt>stmt</tt> and returns <tt>#t</tt> if a result row was produced, <tt>#f</tt> if no further results are available as the statement has been stepped through. This procedure must be called at least once before any results can be retrieved from the statement.</p></dd>

<p>Can be applied to a statement that has just been stepped. Consults <tt>sqlite3:column-type</tt> to determine the type of the indicated column and to return its data as an appropriate scheme object.</p>

405

<p>See <tt>sqlite3:bind!</tt> for the mapping between Scheme and SQLite data types. Columns of type <tt>null</tt> are returned as <tt>(void)</tt>. Also keep in mind that CHICKEN's <tt>&lt;exact&gt;</tt> datatype can only hold a subset of the values an SQLite <tt>integer</tt> can store. Large integer values may therefore be returned as floating point numbers from the database, but they will still be of class <tt>&lt;integer&gt;</tt>.</p>

406

<p>This procedure always succeeds and never throws an exception.</p></dd>

<p>Compiles the SQL sources in <tt>sqls</tt> into statements in the context of <tt>db</tt>, applies <tt>proc</tt> to these statements and returns <tt>proc</tt>'s result. The statements are created and finalized in <tt>dynamic-wind</tt> entry and exit blocks around the application of <tt>proc</tt>.</p></dd>

<p>(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes the specified statement ignoring possible results from it, returning the result of applying <tt>sqlite3:changes</tt> to the affected database after the execution of the statement instead.</p></dd>

<p>(Compiles the given SQL), resets the statement, binds the statement's free parameters and single-steps the statement once returning the value of the first column in the first result row. Resets the statement again just before returning.</p>

432

<p>If the given statement does not yield any results, an <tt>(exn sqlite3)</tt> is thrown with the <tt>status</tt>-property set to <tt>done</tt>.</p></dd>

<p>(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes it step by step. After each step, the column values of the current result row are retrieved and <tt>proc</tt> is applied to them. The results of this application are discarded.</p></dd>

<p>(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes it step by step. After each step, the column values of the current result row are retrieved and <tt>proc</tt> is applied to them. The results of these applications are collected into a list.</p></dd></dl></div>

<p>Runs <tt>thunk</tt> within the scope of a transaction on the dataase <tt>db</tt>.</p>

452

<p>The transaction is committed upon exit from <tt>thunk</tt> if <tt>thunk</tt> returns a true value. If <tt>thunk</tt> returns a false value or throws an exception, the transaction is rolled back.</p>

453

<p>The <tt>type</tt> of the transaction can be specified as one of the symbols <tt>deferred</tt>, <tt>immediate</tt> or <tt>exclusive</tt>.</p></dd>