SQLite api - why blob?

Pondering the philosophy behind the language
Locked
Kirill
Posts: 90
Joined: Wed Oct 31, 2007 1:21 pm

SQLite api - why blob?

Post by 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 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

Lutz
Posts: 5289
Joined: Thu Sep 26, 2002 4:45 pm
Location: Pasadena, California
Contact:

Post by 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
Posts: 90
Joined: Wed Oct 31, 2007 1:21 pm

Post by Kirill »

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

Here are various datatypes of SQLite documented:

http://sqlite.org/datatype3.html

-- Kirill

Locked