> 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.
Just keep in mind that if the user puts blob column into a query (which IIRC is required by SQLGetData) then SQLite reads full blob value into memory before you can do anything with that. So you're not with a good luck here anyway. Pavel On Sun, Dec 27, 2009 at 6:46 PM, Mark Hessling <m...@rexx.org> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
