SQLite api - why blob?

Started by Kirill, October 04, 2009, 08:37:57 AM

Previous topic - Next topic


The standard sqlite library sends newLISP strings to SQLite as blobs, while in SQLite the column might have been defined as containing text. This causes troubles.

E.g. I have a SQLite database with positions (see http://km.krot.org/pos.cgi">http://km.krot.org/pos.cgi for how it's used).

I do a select from command line:

$ echo 'select timestamp from gps_data where message="newLISP";' | sqlite3 pos-db3

Then I try the same from newLISP:

(load "/usr/local/share/newlisp/modules/sqlite3.lsp")
(sql3:open "pos-db3")
(println (sql3:sql "SELECT timestamp FROM gps_data WHERE message=?" '("newLISP")))

It returns


Now I do a small change:

(setf sql3:sqlite3_bind_blob sql3:sqlite3_bind_text)

Running the query again returns


I load the hack in by default, but was wondering about the reason behind having to assume strings are blobs.

Or is it only me having this kind of issue?




I have changed from sqlite3_bind_blob to sqlite3_bind_text in function bind-parameter. The tests still run ok. When inserting text containing non-displayable characters as BLOP they still can be retrieved using sqlite3_bind_text. But I am no SQL3 expert and don't know what other impact that will have on things not tested.


Thanks. I'm not expecting any troubles with this change.

Here are various datatypes of SQLite documented:


-- Kirill