Re: [sqlite] accessing the rowid of a blob for incremental IO

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Jeremy Hinegardner wrote:
>   SELECT name, blob FROM blobs WHERE name = $name;
> 
> And the driver, under the covers uses the blob IO routines to retrieve
> the blob data from SQLite when the user accesses the result set.  

To get the rowid it needs to be in the SELECT (eg SELECT rowid, name,
blob ) as you mentioned, although there have been other requests on
the list to have some API to always get the rowid as you requested.

In any event it is a bad idea to provide the blobs "transparently"
because later SQL statements can make them go bad or have similar
effects which would be unexpected to your user.  Changeset 5313
documents how open blobs are affected, and you will want your users to
explicitly request them as needed.

http://sqlite.org/cvstrac/chngview?cn=5313

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIaEb2mOOfHg372QQRAtHmAJ96GjsG03Y2tEkQ3Iw4RD+6zQ6Z1ACgy6uA
tqQTzAoWsxO3MQTgAsJNTew=
=hY0M
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] accessing the rowid of a blob for incremental IO

[Jeremy Hinegardner]

Hi all,

I'm working on getting the incremental Blob API into my Amalgalite ruby
extension and I seem to have hit a roadblock.  And it is probably my
misunderstanding of something in SQLite.

I want to have that whomever uses my SQLite wrapper be able to use the
Blob IO, with minimal extra work.  To do that I'm imagining a scenario
where the my users execute a prepared statement similar to:

  SELECT name, blob FROM blobs WHERE name = $name;

And the driver, under the covers uses the blob IO routines to retrieve
the blob data from SQLite when the user accesses the result set.  

At the C level in SQLite this ends up executing something like:

  sqlite3_prepare_v2( ... )
  ...
  sqlite3_bind_text( ... )
  ...
  sqlite3_step( ... )

  /* access the column data after SQLITE_ROW is returned */
  sqlite3_column_text( ... )
  sqlite3_column_blob( ... ) /* <-- replace with sqlite3_blob access */

At this point, since the next column is a blob column I would like to
access it via the sqlite3_blob_open() call and return a handle to it in
the driver.  I have all the information at this point to call
sqlite3_blob_open() except for the rowid in the origin table of the blob
that is being returned.  And since my user hasn't specified the
appropriate ROWID, OID, or _ROWID_ as part of the select, I am unable to
get the rowid to invoke sqlite3_blob_open().

My question is: Is there a way to access the appropriate rowid that is
associated with a value in a result set if the user has not specified it
explicitly in the SQL query ?  

I'm using SQLITE_ENABLE_COLUMN_METADATA so I have origin information
available at result set time, I'm wondering if it is that much of a
stretch to have the rowid of the value in question at the same time.

If this doesn't make any sense, let me know and I'll try and rephrase.

enjoy,

-jeremy

-- 

 Jeremy Hinegardner  [EMAIL PROTECTED] 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users