newlispfanclub.alh.net

Posted: **Sun Oct 04, 2009 3:37 pm**

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 all

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

Then I try the same from newLISP:

Code: Select all

(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 all

()

Now I do a small change:

Code: Select all

(setf sql3:sqlite3_bind_blob sql3:sqlite3_bind_text)

Running the query again returns

Code: Select all

((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

Posted: **Fri Oct 09, 2009 6:07 pm**

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.

Posted: **Fri Oct 09, 2009 9:58 pm**

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

Here are various datatypes of SQLite documented:

http://sqlite.org/datatype3.html

-- Kirill

newlispfanclub.alh.net

SQLite api - why blob?

SQLite api - why blob?