sqlite-open-blob

sqlite-close-blob

sqlite-blob-p

sqlite-blob-length

replace-from-sqlite-blob

replace-into-sqlite-blob

sqlite-reopen-blob Functions

Summary

Read/write access to a BLOB or TEXT field in a SQLite database.

Package

sql

Signatures

sqlite-open-blob table-name column-name rowid &key database owner read-only => sqlite-blob

sqlite-close-blob sqlite-blob => boolean

sqlite-blob-p object => boolean

sqlite-blob-length sqlite-blob => length

replace-from-sqlite-blob binary-array sqlite-blob &key array-start array-end blob-start blob-end => binary-array

replace-into-sqlite-blob sqlite-blob binary-array &key blob-start blob-end array-start array-end => sqlite-blob

sqlite-reopen-blob sqlite-blob rowid

Arguments

Values

Description

Instances of the system class sqlite-blob allow reading and writing from/to BLOB or TEXT fields in a SQLite database. It corresponds to the C structure sqlite3_blob (see "A Handle To An Open BLOB" in the SQLite documentation, https://www.sqlite.org/c3ref/blob.html).

The function sqlite-open-blob creates an object of type sqlite-blob, which can be used to access the data in a specific column and row of a SQLite database table, as specified by database, owner, table-name, column-name and rowid. owner specifies the schema-name (which defaults to "main"), and thus allows access to attached databases. table-name and column-name specify the table and column. rowid specifies the row where the value is. For documentation about rowid, see "ROWIDs and the INTEGER PRIMARY KEY" in "CREATE TABLE" in the SQLite documentation (https://www.sqlite.org/lang_createtable.html#rowid), and also the notes below. read-only (which defaults to nil) specifies whether the result sqlite-blob is read-only or not.

The function sqlite-blob-p returns true if object is of type sqlite-blob and false otherwise.

The function sqlite-blob-length returns the length of sqlite-blob in bytes. Note that there is no way to change the length.

The functions replace-from-sqlite-blob and replace-into-sqlite-blob are used to copy from/to sqlite-blob, similar to replace or fli:replace-foreign-array. binary-array must be a binary array, which means an array of element type base-char, bmp-char, single-float, double-float, (unsigned-byte bit-size) or (signed-byte bit-size), where bit-size is one of 8, 16, 32 or (64-bit LispWorks only) 64. Note that simple-string is not regarded as a binary array, but bmp-string and base-string are. The length of sqlite-blob in elements is the length in bytes, as returned by the function sqlite-blob-length, truncated by the number bytes per element in binary-array. The values of array-start, array-end, blob-start and blob-end are all in elements (rather than bytes).

The function replace-from-sqlite-blob replaces the elements of binary-array between array-start and array-end by the elements of sqlite-blob between blob-start and blob-end. The function replace-into-sqlite-blob replaces in the other direction.

blob-start and array-start default to 0, array-end defaults to nil, meaning the length of binary-array, and blob-end defaults to nil, meaning the length of sqlite-blob in elements. When supplied, array-start must be a non-negative integer and not bigger than the length of binary-array, array-end must be not smaller than array-start and not bigger than the length of binary-array, blob-start must be a non-negative integer and not bigger than the length of sqlite-blob in elements, and blob-end must be not smaller than blob-start and not bigger than the length of sqlite-blob in elements. The number of elements copied is the smaller of the difference between blob-start and blob-end, and the difference between array-start and array-end.

replace-from-sqlite-blob and replace-into-sqlite-blob return their first argument.

The function sqlite-close-blob closes sqlite-blob and returns t if it closed, or nil if sqlite-blob was already closed.

The function sqlite-reopen-blob changes sqlite-blob to refer to a field in another row. In effect it closes sqlite-blob and reopens it with a different rowid but otherwise the same arguments as the sqlite-open-blob call that opened it.

Notes

You can obtain a ROWID by using rowid in the selection list of a query. For example, the following query returns a list of ROWIDs for records that match somecondition in the table SomeTable (in *default-database*):

(sql:select [rowid] :from [SomeTable] 
                    :where somecondition
                    :flatp t)

The ROWID may be also be the value of a primary key in the table, as described in the SQLite documentation: "ROWIDs and the INTEGER PRIMARY KEY" in "CREATE TABLE" https://www.sqlite.org/lang_createtable.html#rowid.

It is also possible to find the ROWID of the last inserted row by sqlite-last-insert-rowid.

If the row where the field that sqlite-blob is accessing is modified, further access to sqlite-blob by replace-into-sqlite-blob or replace-from-sqlite-blob signals an error (of type sql-user-error). That is because SQLite itself does not allow further access. As a result, using sqlite-blob is not thread-safe, and you need be sure that no other code is trying to modify the same row while sqlite-blob is open.

sqlite-open-blob may fail for various reasons. When this happens, LispWorks retrieves the error message using the C function sqlite3_errmsg, which is not thread-safe (an apparent misdesign of SQLite). As a result, you will get a misleading error message in very rare occasions, if another thread executing on the same database got an error in parallel. However, the error number, is always correct and its values are documented in the SQLite documentation "Result Code Meanings" https://www.sqlite.org/rescode.html#error.

Leaving a sqlite-blob opened is not only a resource leak, but also leaves some locks in the database connection that prevents some operations in the future (dropping the table or disconnecting the database for example). Therefore, you should close a sqlite-blob as soon as possible. We recommend using with-sqlite-blob to open and close the sqlite-blob when possible.

See also

with-sqlite-blob
sqlite-blob
23.13 Using SQLite