I'm working with the sqlite3 bindings to my "nasal" languages
(http://plausible.org/nasal, if anyone is curious) and I'm having a
problem with type conversion.
Take a look at the attached sample code. What it basically does is to
try to extract a row from a table with a bound parameter:
"select val from tab where val = ?"
The val column in the table is declared as a "text" column. But
because I'm working in a language that doesn't distinguish between
strings and byte arrays, I have to do the binding with
sqlite3_bind_blob() instead of sqlite3_bind_text().
Problem is, the *blob* value of "testval" does not, apparently, equal
the *text* value of "testval" in the database.
The workaround right now is to always define the columns as type blob,
never text. But this strikes me as pretty unambiguously a bug.
Clearly an ASCII string should be equal in either representation: what
possible blob value of "testval" could there be except a 7 byte
string: {'t','e','s','t','v','a','l'}?
Any ideas, or have I misunderstood something?
Andy
#include <stdio.h>
#include <string.h>
#include <sqlite3.h>
// Initialize with: (note column "val" has type "text"):
//
// rm -f test.db
// echo "create table tab (val text);" | sqlite3 test.db
// echo "insert into tab values ('testval');" | sqlite3 test.db
//
// Validate: (prints 'testval' as expected)
//
// echo "select val from tab where val = 'testval';" | sqlite3 test.db
//
// Test: (note column "val" is bound with sqlite3_bind_blob()):
//
// gcc -o test test.c -lsqlite3 && ./test
//
// Result:
//
// The blob apparently tests as not equal to the identitcal string,
// and the query returns zero rows.
#define DB "test.db"
#define QUERY "select val from tab where val = ?"
#define FIELD "testval"
#define PERR(msg) { printf(msg); printf("%s\n", sqlite3_errmsg(db)); }
int main()
{
int stat, cols, i;
sqlite3 *db;
sqlite3_stmt *stmt;
const char *tail;
if(sqlite3_open(DB, &db)) {
PERR("open failure\n");
return 1;
}
if(sqlite3_prepare(db, QUERY, strlen(QUERY), &stmt, &tail)) {
PERR("prepare failure\n");
return 1;
}
// Calling bind_text() here works, bind_blob() does not:
if(sqlite3_bind_blob(stmt, 1, FIELD, strlen(FIELD), SQLITE_TRANSIENT)) {
PERR("bind failure\n");
return 1;
}
while((stat = sqlite3_step(stmt)) != SQLITE_DONE) {
cols = sqlite3_column_count(stmt);
for(i=0; i<cols; i++) {
if(i != 0) printf("|");
fwrite(sqlite3_column_blob(stmt, i),
sqlite3_column_bytes(stmt, i), 1, stdout);
}
}
return 0;
}
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
