Re: [sqlite] any plans for out-of-row blobs?

[Kervin L. Pierre]

Kurt Welgehausen wrote:

The common way to handle this in SQLite is to store the
blob in a file and store the file name in the db.



Right, but then we lose the many benefits
of keeping the data in a database.

What I was really getting at is support for
retrieving chunks of data using the SUBSTR()
function as with Oracle and SQL Server.

Regards,
Kervin

Re: [sqlite] any plans for out-of-row blobs?

[Robert Simpson]

- Original Message - 
From: "Kurt Welgehausen" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, September 13, 2005 12:46 PM
Subject: Re: [sqlite] any plans for out-of-row blobs?



There is no way to retrieve part of a
blob value in SQLite.

 From my understanding, most databases store
blob values separate from the rest of the row.


The common way to handle this in SQLite is to store the
blob in a file and store the file name in the db.


There's also plenty of other ways to get creative with the storage ... 
here's one off the top of my head:


CREATE TABLE FOO (Id INTEGER PRIMARY KEY, ... extra columns, whatever ... );
CREATE TABLE FOO_BlobData (FooId INTEGER, BlobData BLOB);

Then when you write blob data, do it in 4k chunks to FOO_BlobData.  To 
select it out,


SELECT * FROM FOO_BlobData WHERE FooId = $myid ORDER BY row_id

Just step through it at this point, you'll get it back in 4k chunks.

Robert

Re: [sqlite] any plans for out-of-row blobs?

[Kurt Welgehausen]

> There is no way to retrieve part of a
> blob value in SQLite.
>
>  From my understanding, most databases store
> blob values separate from the rest of the row.

The common way to handle this in SQLite is to store the
blob in a file and store the file name in the db.

Regards

[sqlite] any plans for out-of-row blobs?

[Kervin L. Pierre]

I need to retrieve parts of the Blob column
value.  The blob may simply be to large for
memory at runtime.

There is no way to retrieve part of a
blob value in SQLite.

From my understanding, most databases store
blob values separate from the rest of the row.

Would that be a possibility for SQLite?  This
would also solve the issue of blobs being
read entirely into memory if a column defined
after the blob column is retrieved.

Regards,
Kervin