Sorry, but I can't give you a definitive answer myself since I working on
the same problem. I'll let you know when/if I solve it and I'm sure you'll
do the same. But for what its worth below is what i'm trying in C++.
first the disclaimer - i'm very new to sqlite and its been a while since
i've done sql/odbc type of work.
SHORT ANSWER: i agree you don't use sqlite_exec. i'm pretty sure you need to
use the sqlite3_bind_blob call with a prepared sqlite3_statement.
LONG ANSWER:
this is what i think you're supposed to do, but it isn't working yet.
GOAL:
Briefly, the goal is to add a blob field (here for a newly created record,
as opposed to updating)
0. assume a table was created with 3 fields.
"create table xtable (id primary integer, stuff as blob, type as
integer);"
1. create a sqlite3_stmt via the prepare call.
const char *sql = "INSERT INTO xtable (id, stuff, type) VALUES
(NULL,?,?)";
rc = sqlite3_prepare( sql, &pstmt, .... );
2. then bind the columns using an index to represent each '?' field.
Notes:
a. since the id is "autoincrement"; (i.e. autoassigned) that field
was not bound
b. use INDEX #1 for the first ? (the blob)
sql_bind_blob( pstmt, 1, pData, nSizeOfData, .. ); // use
appropriate flag here
c. use INDEX #2 for the type
sql_bind_int( pstmt, 2, nTypeValue );
d. unless i'm mistaken the calls to read the values from a query are
0 based and these
are 1 based. perhaps i'm wrong.
e. be sure to check all return codes!
3. then execute the statement via sqlite3_step(pstmt);
4. close/free the statement.
RESULT:
the record was added, the type value is correct, but the blob data is
corrupt.
Note: i have additional code to read the object that verified the type, was
correct, but the blob is wrong. Yes the error could be in my "read from db"
code...
Note: In addition when trying to read that column after a query it states
the blob data type is TEXT as opposed to BLOB. I'm not sure why that is the
case.
Sorry, I couldn't be of more help but perhaps this will lead you in the
right direction.
John
-----Original Message-----
From: Will Leshner [mailto:[EMAIL PROTECTED]
Sent: Friday, October 15, 2004 11:05 AM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] BLOBs and sqlite_exec
On Oct 15, 2004, at 7:50 AM, Will Leshner wrote:
> Sorry if this is terribly obvious, but I'm assuming that, in SQLite3,
> we can't use the sqlite_exec convenience API to store BLOBs, right? I
> see that if we go through the VM directly the API allows us to specify
> a data size, so that it doesn't depend on NULL-terminated C strings.
> But sqlite_exec does no such thing, right?
>
>
>
Sorry to respond my own question, but I'm digging into the SQLite3 code
a bit more and I'm a little confused. I see that sqlite3_prepare takes
a SQL statement as a char* and also the number of bytes in the SQL
statement. I assumed this was so that you could put BLOB data in the
SQL statement. But sqlite3_prepare doesn't seem to actually use the
nBytes parameter anywhere. And sqlite3RunParser seems to be running the
parse until it finds a NULL byte in the zSql string. In fact,
sqlite3RunParser doesn't even take a number of bytes for the SQL
string, so I don't see how it could be using that for the length of the
string at all.
So I guess my question is: How do you put BLOB (binary) data into a
SQLite3 database?
