This section describes particular issues in Common SQL with SQLite databases.
By default, when doing queries (select, query, map-query, do-query, simple-do-query, loop with
each record and print-query) the LispWorks checks the data type of each field it reads in each row, and fetches the data accordingly (using the C functions
sqlite3_column_int in the SQLite3 library). Values of SQLite data types
TEXT are mapped to Lisp objects of type
string respectively ("mapped" means returned from select or query, printed in print-query, or passed to your code in the other APIs). A value of data type BLOB is mapped to an array with element type
(unsigned-byte 8) containing all of the bytes of the BLOB.
You can force the value to a specific type by specifying the type explicitly. This is done by specifying the type with the identifier, either using the symbolic SQL syntax (see Enclosing database identifiers) or using sql-expression. For select and query you can also use the keyword argument
The types that LispWorks recognizes for SQLite are the common types:
:binary. These match the SQLite data types INTEGER, TEXT, REAL and BLOB respectively. When these keywords are used, LispWorks asks SQLite for a value of the corresponding data type, and converts it to the matching Lisp object type as above. Note that the value can also be
nil, if the the value is
null or cannot be converted to the requested Lisp object type.
Use the default behavior. Useful if you use
:result-types and want to force the type of some of the fields but not all of them.
Returns a handle to the raw data of a BLOB, from which you can read the data using the APIs described in Reading from blobs using a handle (sqlite-raw-blob) and modifying blobs (sqlite-blob). This allows more flexible access to BLOB values.
When the value that is stored in the database does not match the value that it is asked for, the SQLite library converts the value to the required type, so you always get a value of the correct type, but not necessarily a useful value. See the documentation for SQLite for details: https://www.sqlite.org/c3ref/column_blob.html "Result Values From a Query".
When you connect to a database, you can use the SQLite-specific keyword
:uniform-type-per-column in sqlite-keywords with value
t to tell LispWorks that all of the values of a column returned by a query have the same data type.
When the type of a field in a query is specified as
:blob, the SQLite BLOB is mapped to an object of type sqlite-raw-blob. You can then read data from the SQLite BLOB using any the functions copy-from-sqlite-raw-blob, replace-from-sqlite-raw-blob or sqlite-raw-blob-ref. The function sqlite-raw-blob-length can be used to find the size of the BLOB (in bytes).
The sqlite-raw-blob is valid only within the dynamic extent of the function that is called from the Common SQL interface. If you try to read from a sqlite-raw-blob outside this dynamic context, an error of type sql-user-error will be signaled. You can use sqlite-raw-blob-valid-p to check if a blob is valid.
Using sqlite-raw-blob makes it more convenient to read the data when a BLOB contains elements larger than bytes, and makes it more efficient when you retrieve large BLOBs (a few kilobytes or more) but need only a small part of the data.
SQLite allows reading and writing of BLOBs (fields with type BLOB or TEXT) directly, which you can do using the sqlite-blob interface. The functions sqlite-open-blob and sqlite-close-blob are used to open and close a BLOB field, or the macro with-sqlite-blob can be used to do both. Once you have opened a BLOB, you call replace-from-sqlite-blob or replace-into-sqlite-blob to copy data to or from it. Note that the sqlite-blob is not thread-safe, so you must do all of the operations in a "single thread" context (either all in one thread, or serialized by a lock).
When modifying a table in SQLite, either directly by using insert-records or update-records, or by executing a prepared-statement statement with bind-variables, the values that are passed are treated as follows:
In a prepared-statement, if the variable-type is
:string, then the value is converted to a string.
A binary array is an array with an integer or float element type.
base-string are also binary arrays in some contexts, but they are treated as strings in this case (
text-string is not a binary array).
base-string, then the list must be of form
)and the bytes between start and end in array are inserted as a BLOB. If start is omitted, it defaults to 0. If end is omitted, it defaults to the length of array.
Note that start and end are denoted in elements rather than bytes, so the number of the bytes in the BLOB is
(* (- end
). Note also that, for arrays of more than one byte per element, the contents of the BLOB will depend on the byte order of the host machine.
:zeroblob, then the second element is treated as a size, which must be a positive integer smaller than 231, that is of type
(integer 0 #x7fffffff). LispWorks inserts a zero blob of this size (using the C function
Any value that does not match the description above, including integers out of range and lists that do not match the patterns described, cause an error (of type sql-user-error) to be signaled.
ATTACHed databases in SQLite, that is databases that were attached using the SQLite ATTACH statement, are identified by their schema names. You can specify the schema name in the "[...]" syntax, for example, if you attach a file called "another-database" as follows:
(execute-command "ATTACH another-database as AttachedDB")
(select [*] :from [AttachedDB SomeTable])
(sql:list-tables :owner "AttachedDB")
and use AttachedDB as the "owner" in sql-expression:
LispWorks User Guide and Reference Manual - 20 Sep 2017