Re: [sqlite] Using incremental BLOB I/O when processing result set
> Its a shame that a BLOB handle is not returned as a result of a query > rather than the BLOB itself. Let me correct you. It's not a shame, it's database specifics and it has its good points. For me personally I'd hate if SQLite returned me some abstract handle if I requested blob value. Note: if you select blob value for example from MS SQL it's also loaded as a whole into servers memory - you just don't care about that. But depending on client implementation the blob value can be also loaded in full into client's memory as well (despite your usage of SQLGetData() function), so this behavior is not something SQLite-specific. Also note: for latest versions of MS SQL Microsoft strongly recommends to not use text and image datatypes (real LOB types) but use varchar(max) and varbinary(max) instead. These types can obtain any value LOBs could get but they also will never return to you any handle in case you've selected them - only the whole value as a bunch. So again as you see this behavior is not SQLite-specific. And the last note: I've never heard of any database engine developer who cares about making it possible to use his DBMS with any other DBMS in a "database independent manner". If somebody needs that "independence" usually he writes database-specific drivers which have independent API and internally handle all cases differently. Pavel On Tue, Dec 29, 2009 at 2:52 AM, Mark Hessling wrote: > Well that is very disappointing :-( > > Not being able to extract a portion of a BLOB from a result set without > having the complete BLOB in memory makes it impractical to use BLOBs in > SQLite in a database independent manner. > > Its a shame that a BLOB handle is not returned as a result of a query > rather than the BLOB itself. > > Thanks for the clarification anyway. > > Cheers, Mark > > On Mon, 2009-12-28 at 21:17 -0500, Igor Tandetnik wrote: >> Zaher Dirkey wrote: >> > If i want to extract BLOB to a file that mean it is must the whale >> > blob be loaded to memory before can save it? >> >> No, not with BLOB I/O. But you have to be careful: don't mention the BLOB >> field in your SELECT statement, retrieve the ROWID instead. Then use it in >> sqlite3_blob_open call. >> >> Igor Tandetnik >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > -- > > * 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
Re: [sqlite] Using incremental BLOB I/O when processing result set
Well that is very disappointing :-( Not being able to extract a portion of a BLOB from a result set without having the complete BLOB in memory makes it impractical to use BLOBs in SQLite in a database independent manner. Its a shame that a BLOB handle is not returned as a result of a query rather than the BLOB itself. Thanks for the clarification anyway. Cheers, Mark On Mon, 2009-12-28 at 21:17 -0500, Igor Tandetnik wrote: > Zaher Dirkey wrote: > > If i want to extract BLOB to a file that mean it is must the whale > > blob be loaded to memory before can save it? > > No, not with BLOB I/O. But you have to be careful: don't mention the BLOB > field in your SELECT statement, retrieve the ROWID instead. Then use it in > sqlite3_blob_open call. > > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- * 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
Re: [sqlite] Using incremental BLOB I/O when processing result set
On 29 Dec 2009, at 2:18am, Igor Tandetnik wrote: > Simon Slavin wrote: >> On 29 Dec 2009, at 2:02am, Zaher Dirkey wrote: >>> If i want to extract BLOB to a file that mean it is must the whale >>> blob be loaded to memory before can save it? >> >> Yes. Also, the whole BLOB must have been in memory in the first place, when >> you saved the record. > > Not true. The whole point of BLOB I/O API is to avoid precisely that. Thanks for the correction. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using incremental BLOB I/O when processing result set
Simon Slavin wrote: > On 29 Dec 2009, at 2:02am, Zaher Dirkey wrote: >> If i want to extract BLOB to a file that mean it is must the whale >> blob be loaded to memory before can save it? > > Yes. Also, the whole BLOB must have been in memory in the first place, when > you saved the record. Not true. The whole point of BLOB I/O API is to avoid precisely that. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using incremental BLOB I/O when processing result set
Zaher Dirkey wrote: > If i want to extract BLOB to a file that mean it is must the whale > blob be loaded to memory before can save it? No, not with BLOB I/O. But you have to be careful: don't mention the BLOB field in your SELECT statement, retrieve the ROWID instead. Then use it in sqlite3_blob_open call. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using incremental BLOB I/O when processing result set
On 29 Dec 2009, at 2:02am, Zaher Dirkey wrote: > Pavel Ivanov wrote: >> > >> 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. > > If i want to extract BLOB to a file that mean it is must the whale > blob be loaded to memory before can save it? Yes. Also, the whole BLOB must have been in memory in the first place, when you saved the record. When you INSERT or SELECT a record the whole record exists in memory. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using incremental BLOB I/O when processing result set
Pavel Ivanov wrote: > > 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. > If i want to extract BLOB to a file that mean it is must the whale blob be loaded to memory before can save it? -- Zaher Dirkey ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using incremental BLOB I/O when processing result set
> 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 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
Re: [sqlite] Using incremental BLOB I/O when processing result set
Mark Hessling wrote: > 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??? Usually with something like "select rowid from mytable where ...; ". If your ids are declared INTEGER PRIMARY KEY, then they are just aliases for rowid, and you can use their values directly. Otherwise you would have to retrieve the rowid with a separate query, or add it to your query as an extra column. > 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) You could try and rewrite the statement automatically to add an extra column. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
