Error retrieving BLOB field using System.Data.SQLite.SQLIteDataReader.GetBlob

Question

Due to reasons beyond my control, I am creating a .NET 2.0 assembly in which I load a SQLite database and retrieve some binary data from it. Specifically, PDF documents.

The documentation for System.Data.SQLite.SQLiteDataReader.GetBlob(int i, bool ReadOnly) says: (emphasis mine)

Retrieves the column as a System.Data.SQLite.SQLiteBlob object. This will not work for tables that were created WITHOUT ROWID -OR- if the query does not include the "rowid" column or one of its aliases -OR- if the System.Data.SQLiteDataReader was not created with the System.Data.CommandBehavior.KeyInfo flag.

Here is my SQLiteCommand:

using (SQLiteCommand getBooklet = new SQLiteCommand($"SELECT \"rowid\", File_Name FROM Booklets WHERE Id = {int.Parse(key)}", dbConnection))

I have instantiated my SQLiteDataReader like so:

using (SQLiteDataReader currentCustomerReader = getBooklet.ExecuteReader(System.Data.CommandBehavior.KeyInfo & System.Data.CommandBehavior.SequentialAccess))

I call the GetBlob(int i, bool ReadOnly) function as follows:

currentCustomerPdf = currentCustomerReader.GetBlob(1, true);

And I'm greeted with this:

System.InvalidOperationException: No RowId is available
    at System.Data.SQLite.SQLiteBlob.Create(SQLiteDataReader dataReader, Int32 i, Boolean readOnly)
    at System.Data.SQLite.SQLiteDataReader.GetBlob(Int32 i, Boolean readOnly)
...

Am I doing something wrong? Am I missing a step? Do I need to file a bug? Is it an issue with .NET 2.0 that has been resolved in newer versions?

Do you really need random access to the blob? Can't you just read it as a byte array? — CL.
– CL., Commented Dec 8, 2016 at 14:49
I'm researching how to do that now. I'm just not sure why this is happening -- I'd really like to know -- and the code for the Blob object is simpler and takes fewer lines. — sonicbhoc
– sonicbhoc, Commented Dec 8, 2016 at 15:04
That SQLiteBlob object obviously uses incremental blob I/O. But I don't see why it doesn't work in this case. — CL.
– CL., Commented Dec 8, 2016 at 15:08
Thanks. I don't understand why it isn't working either. Smells like a bug to me. — sonicbhoc
– sonicbhoc, Commented Dec 8, 2016 at 15:11

General Grievance · Accepted Answer · 2025-05-07 17:58:32Z

2

To make this code work, either:

Remove the System.Data.CommandBehavior.SequentialAccess flag and select at least the rowid and blob field.
Just call ExecuteReader with parameter CommandBehavior.KeyInfo (no need for rowid in the query).

edited May 7 at 17:58

General Grievance

5,12039 gold badges39 silver badges60 bronze badges

answered Dec 8, 2016 at 15:15

sonicbhoc

4525 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Manjunath · Accepted Answer · 2018-01-08 06:44:28Z

0

though keyinfo works, but it does not solve the increment blob I/O of large blob objects due to the fact of executereader method's large memory consumption(as much as equal to the blob size). One way to avoid the huge memory usage is to get the rowid in a separate query and overload the sqliteblob.create method and pass the rowid directly. So basically keyinfo is not all needed here if you can overload the sqliteblob.create method.

answered Jan 8, 2018 at 6:44

Manjunath

1

Collectives™ on Stack Overflow

Error retrieving BLOB field using System.Data.SQLite.SQLIteDataReader.GetBlob

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related