Hi,
I've been trying to modify the ODBC driver
(http://www.ch-werner.de/sqliteodbc/) to be able to work with BLOBs. The current
0.65 ODBC driver can only handle CLOBs (i.e. null terminated strings) inspite
of it being built on sqlite3 .
I've managed to get BLOB writing to work by using sqlite3_bind_blob(...) at
write time. This is a bit problematic though as it requires
the entire BLOB to be available at binding time.
Lets say a 10MB file is to be stored in the database, usual procedure would be
to divide it up in chunks and do SQLPutData on each chunk. With the current
SQLite API that is not possible.
I'd propose an added API called sqlite3_bind_chunk(...) that has the same
function prototype (as _bind_blob) but with the added function that calling
sqlite3_step() returns SQLITE_OK until the data size parameter to
sqlite3_bind_chunk(...) is zero, like:
sqlite3_prepare(db, "INSERT INTO T_TABLE (DATA) VALUES(?) WHERE ID=42", -1,
&stmt, &szTail);
while (true)
{
int chunkLength = min(dataLength, CHUNK_LENGTH);
sqlite3_bind_chunk(stmt, 0, dataPtr, chunkLength, SQLITE_TRANSIENT);
dataLength -= chunkLength;
rc = sqlite3_step();
if (rc == SQLITE_DONE)
{
break;
}
}
I.e. bind the blob in several consecutive chunks instead of one. Make sense? Or
is it RTFM?? ;)
However, getting the data back is a bit problematic as the SQLite statement
scope is only within SQLExecute. When SQLGetData is called, there no longer is
a statement to use for sqlite3_column_blob(...). The 0.65 driver solves this by
copying the rowset as returned in SQLExecute (by sqlite3_get_table)
into a char** structure which is then used in SQLGetData to pass data on to the
calling application. However, I'd prefer not to have this
behavior as it means more data copy than needed.
I'll try not to sqlite3_finalize the statement until SQLFreeStmt is called
(from the application), so that the actual data pointers
are available in SQLGetData during BLOB fetch. Does anybody see any hazard in
doing this?
/Rob
