Re: [sqlite] how to get the meta type of a column
I think the OP wanted to know how to find this programatically. In that case see sqlite3_column_type() function call. Cheers, Mark On Fri, 2010-02-05 at 02:47 +, Simon Slavin wrote: > On 5 Feb 2010, at 2:41am, gujx wrote: > > > Maybe I didn't express the problem clearly. > >> e.g. if you have a text value, you use _text(), if you have an int, you > >> use _int() > > I just don't know what type I have, so I want to get the meta type of the > > column somebody defined. > > e.g. I'd like to use a bind routine to bind a variable t to a "?", but I > > don't konw this t is a string or a number or it is a object, so I want to > > know that the table is defined like "id(varchar)" ,then I will use > > sqlite3_bind_text; or it is defined like "id(integer)", then I will choose > > sqlite3_bind_int. > > Open the database in the command-line tool, and use the .schema command: > > <http://www.sqlite.org/sqlite.html> > > Simon. > > > _______ > 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
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] Using incremental BLOB I/O when processing result set
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
