Sqlite has declared types and actual types. Both can be accessed
through the API.
What I do is look at the declared type, which defines the data and then
look at the actual type to determine how to process it.
Youn can declare the type to be anything you want. A name such a GEORGE
or INVOICE-DATE are each accpetable.
Andy Ross wrote:
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]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
