Text only | Text with Images

newLISP Fan Club

Forum => Whither newLISP? => Topic started by: Kirill on October 04, 2009, 08:37:57 AM

Title: SQLite api - why blob?
Post by: Kirill on October 04, 2009, 08:37:57 AM
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 for how it's used).



I do a select from command line:


Code Select

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


Then I try the same from newLISP:


Code Select

(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

()


Now I do a small change:


Code Select

(setf sql3:sqlite3_bind_blob sql3:sqlite3_bind_text)


Running the query again returns


Code Select

((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
Title:
Post by: Lutz on October 09, 2009, 11:07:42 AM
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.
Title:
Post by: Kirill on October 09, 2009, 02:58:44 PM
Thanks. I'm not expecting any troubles with this change.



Here are various datatypes of SQLite documented:



http://sqlite.org/datatype3.html



-- Kirill
Text only | Text with Images