I have supported a generic Rexx interface to several SQL databases over
the last 10 years, including SQLite, Oracle,
ODBC, etc.
The Rexx API is loosely based on ODBC function calls. I'm now trying to
support BLOBs in SQLite.
I've read the API for incremental I/O for BLOBs, and also checked the
source for the SQLite ODBC driver.
I'm looking for a way to support the incremental reading of a BLOB from
a column while processing a result set.
Here is a scenario:
Table: PERSON: id int, name text, photoid int
Table: IMAGEDATA: photoid int, image blob
Query: "select name, image from person as a, imagedata as b where
a.photoid = b.photoid where name = 'Smith'
Rexx Pseduo Code for processing a query and writing the BLOB returned
from each row to a file:
Call SQLPrepare query -- prepare query
Call SQLOpen -- open cursor
Do Forever -- loop forever
rc = SQLFetch -- fetch a row
If rc = 0 Then Leave -- if no more rows leave loop
Do Forever -- loop forever
rc = SQLGetData 1000 -- get 1000 byte chunk from BLOB
If rc = 0 Then Leave -- if no more data leave loop
-- append chunk to file
End
End
Before I try and implement SQLGetData using the incremental BLOB I/O I
need to know if the approach I am taking is possible.
The major issue is with the API function sqlite3_blob_open(). It
requires:
const char *zDb - according to documentation this could be set to
"main"
const char *zTable - this can be obtained from
sqlite3_column_table_name()
const char *zColumn - this is supplied in the call to SQLGetData()
sqlite3_int64 iRow - requires the rowid of the table - how is this
obtained???
The implementation for the SQLite ODBC driver does not use incremental
BLOB I/O, it reads a BLOB into memory for each row
fetched. Not much use if the size of each image in the SQLite database
is 3gb and you only have 2gb memory. I'm looking for a solution that
will work on BLOBs larger than available memory.
Remember this is a generic wrapper for the SQLite API. I cannot force
the user to code their SQL in a certain way (eg include rowid in each
query), or assume anything else. Everything about using the SQlite API
must be available from the SQLite API.
Thanks for your time.
Cheers, Mark
--
* Mark Hessling, m...@rexx.org http://www.rexx.org/
* Author of THE, a Free XEDIT/KEDIT editor, Rexx/SQL, Rexx/CURL, etc.
* Maintainer of Regina Rexx interpreter and Rexx/Tk
* Use Rexx? join the Rexx Language Association: http://www.rexxla.org/
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
