Error retrieving BLOB field using System.Data.SQLi

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?

Answer 1

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.

Answer 2

To make this code work, remove the System.Data.CommandBehavior.SequentialAccess flag and select at least the rowid and blob field.