SQLite api - why blob?

[Kirill]

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:

Code Select Expand


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


Then I try the same from newLISP:

Code Select Expand


(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

Code Select Expand


()


Now I do a small change:

Code Select Expand


(setf sql3:sqlite3_bind_blob sql3:sqlite3_bind_text)


Running the query again returns

Code Select Expand


((1254562769))


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?



Thanks.



Kirill

[Lutz]

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.

[Kirill]

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



Here are various datatypes of SQLite documented:



http://sqlite.org/datatype3.html">http://sqlite.org/datatype3.html



-- Kirill