exn: sqlite
exec/ ignore
with-transaction/ lock
2 Notes

SQLite: An interface to SQLite databases

By Jay McCarthy (jay at cs dot byu dot edu) & Noel Welsh (noelwelsh at GMail)

SQLite gives you access to SQLite database in PLT Scheme.


 (require (planet jaymccarthy/sqlite:3))

(struct exn:sqlite (message continuation-marks)
  message : string?
  continuation-marks : continuation-mark-set?

This exception is thrown whenever the library encounters an error. The message field gives more information about the error.

(db? x)  boolean?
  x : any/c

Identifies if x represents an SQLite database.

(open db-path)  db?
  db-path : (or/c path-string? (symbols ':memory: ':temp:))

Opens the SQLite database at db-path.

If ':memory: or ':temp: are passed they correspond to the string arguments ":memory:" and "" to SQLite’s open function. These correspond to a private, temporary in-memory database and a private, temporary on-disk database.

(close db)  void
  db : db?

Closes the database referred to by db.

(exec db sql callback)  void
  db : db?
  sql : string?
  callback : ((listof vector?) (listof vector?) . -> . integer?)

Executes sql with the given db, calling callback for each row of the results. callback is passed two vectors, one of the column names and one of the column values. callback returns an integer status code. If the status code is anything other than zero execution halts with an exception. If the query does not return results, callback will not be called.

(exec/ignore db sql)  void
  db : db?
  sql : string?

A wrapper around exec that provides a void callback.

(insert db sql)  integer?
  db : db?
  sql : string?

Executes sql with the db. The query is assumed to be an INSERT statement, and the result is the ID of the last row inserted. This is useful when using AUTOINCREMENT or INTEGER PRIMARY KEY fields as the database will choose a unique value for this field

If the SQL is not an insertion statement it is still executed, the results if any are discarded, and the returned value is unspecified.

(select db sql)  (listof vector?)
  db : db?
  sql : string?

Executes sql with the given db, collating the results in to a list where each element is a vector of the columns values as strings. The first vector contains the column names. If the statement returns no results an empty list is returned.

(prepare db sql)  statement?
  db : db?
  sql : string?

Compiles sql into a statement object for the given db. The query may contain “?” to mark a parameter. Make sure you free the statement after use with finalize. A statement can be reused by calling reset.

(load-params stmt param ...)  void
  stmt : statement?
  param : any/c

Loads params into stmt, filling in the `?’s.

(step stmt)  (or/c vector? false/c)
  stmt : statement?

Steps stmt to the next result, returning the column values as a vector, or #f if the statement does not return values or there are no more values. Unlike select, values are converted to the appropriate Scheme type:

A NULL becomes #f. An INTEGER becomes an integer. A FLOAT becomes an inexact number. A STRING or TEXT becomes a string. A BLOB becomes a bytes.

(step* stmt)  (listof vector?)
  stmt : statement?

Runs step until it is done collecting the results in a list. Use this rather than select or exec when you want to use a placeholder (?) in the query and have SQLite do the quoting for you.

(run stmt param ...)  void
  stmt : statement
  param : any/c

Loads the params in the statement, then runs the statement. (If the statement returns results, they are not available.) (Use for UPDATE and INSERT)

(reset stmt)  void
  stmt : statement?

Resets a statement for re-execution.

(finalize stmt)  void
  stmt : statement?

Releases the resources held by a statement.

(with-transaction* db lock-type action)  any/c
  db : db?
  lock-type : (symbols 'none 'deferred 'immediate 'exclusive)
  action : ((-> void) . -> . any/c)

Runs action in a transaction in the given database with the given lock type, returning the result of the action. The action is passed a function of one argument which aborts the transaction when called. If the transaction is aborted the result of the with-transaction* expression is the value passed to the abort function. If control leaves the action via an exception or other continuation jump (i.e. without action exiting normally) the transaction is aborted.

Refer to the SQLite documentation for the meaning of the lock-types.

(with-transaction (db fail) body ...)

Equivalent to: (with-transaction* db 'none (lambda (fail) body ...)).

(with-transaction/lock (db lock-type fail) body ...)

Equivalent to: (with-transaction* db lock-type (lambda (fail) body ...)).

(errmsg db)  string?
  db : db?

Returns the message for the last error with the database.

(changes-count db)  integer?
  db : db?

Returns a count of how many rows were changed by the most recently completed INSERT, UPDATE, or DELETE statement.

(total-changes-count db)  integer?
  db : db?

Returns a count of how many changes have been made to the database since its creation.

2 Notes

If you encounter unexpected errors with the message "SQLite Error: The database file is locked" check you haven’t got any un-finalized statements around.