Reading blobs

Jul 21, 2011 at 1:40 PM

Hi!

Thank you for a very useful program! Altough I'm not very skilled in either VBA or SQL I've successfully made an excel frontend to an SQLite database with the help of these tools!

However, I have a problem regarding blobs. Some fields in my database contain blobs created a long time ago, so I have no idea how that was done. I tried to update the StdCall.dll functions so it could read the functions "sqlite3_column_blob", "sqlite3_open_blob" and other functions relating blobs from the sqlite3.dll, but I don't really know how to use them. I've also tried to understand generally how to read blobs with SQLite, but most information is regarding images. In my case, I know that the data is in text format in some way (probably CSV or similar) and I cant find any useful information at all regarding this.

It's essential for me to be able to read this information in these blob fields. I just wonder if I'm missing something essential, because I just can't see a way of how to do it. Is it possible with the SQLite syntax, and therefore also possible through VBA?

Any help is greatly appreciated!

Coordinator
Jul 21, 2011 at 5:06 PM
Edited Jul 21, 2011 at 6:38 PM

Hi,

Firstly, have you tried to read those Blob column as Text (using SQLite3ColumnText)? Even if the columns are marked as Blob, you can extract the data as Text, and if the column really does contain some text format like a csv file, you should get the text out directly. Remember that the SQLite database doesn't really type the columns, and values can be put in and taken out in any type, for any column.  

If getting the data as Text doesn't help you, you'll have to get the raw bytes out, and figure out what to do with those in your VB program. (E.g. suppose the Blobs have zipped CSV files?)

To get the Blob data out as byte arrays won't be too hard. 

The SQLite extended blob functions (sqlite3_read_blob etc.) are really only useful if you have large blobs (100s of MB), and want to process steam in a streaming API.

I'm guessing for your case it's fine to read the whole blob into memory, so sqlite3_column_blob together with sqlite3_column_bytes should be good enough for reading, and sqlite_bind_blob should be fine for writing. These are already in the sqlite_stdcall.dll and have declares in the SQLite3.bas file, but don't have public functions exposed from the SQLite3 module.

Basically my understanding is that we could add a declaration and a function in SQLite3.bas that look like this:

Private Declare Sub RtlMoveMemory Lib "kernel32" (ByVal pDest As Long, ByVal pSource As Long, ByVal length As Long)

and two functions:

Public Function SQLite3ColumnBlob(ByVal stmtHandle As Long, ByVal ZeroBasedColIndex As Long) As Byte()
    Dim ptr As Long
    Dim length As Long
    Dim buf() As Byte
    
    ptr = sqlite3_stdcall_column_blob(stmtHandle, ZeroBasedColIndex)
    length = sqlite3_stdcall_column_bytes(stmtHandle, ZeroBasedColIndex)
    ReDim buf(length - 1)
    RtlMoveMemory VarPtr(buf(0)), ptr, length
    SQLite3ColumnBlob = buf
End Function
 Public Function SQLite3BindBlob(ByVal stmtHandle As Long, ByVal OneBasedParamIndex As Long, ByRef Value() As Byte) As Long
    Dim length As Long
    length = UBound(Value) - LBound(Value) + 1
    SQLite3BindBlob = sqlite3_stdcall_bind_blob(stmtHandle, OneBasedParamIndex, VarPtr(Value(0)), length, SQLITE_TRANSIENT)
 End Function

Then you easily get the blob out of the database into a byte array.

If you need more help looking at those Blob fields, you'll have to find more information about them, or post some examples of what they contain.

-Govert

Jul 22, 2011 at 10:42 AM

Hi,

Thank you for such a quick and extensive answer! I have succeeded into reading the blob file as a byte array with your SQLite3ColumnBlob function. I have found out that values in the blob file are coded as half precision floating points because the number of bytes in the blob file is equal to two times the number of values that should be in it. However, I have some problems with the translation from bytes to a readable format.

I guess it's an irrelevant question and not regarding your program, but you seem to be very skilled in this area so I wonder by any chance have you got any idea or lead on how to convert these two bytes into a half precision float with VBA code?

Thank you!

Best regards,

engerberg

Coordinator
Jul 22, 2011 at 11:17 AM

Hi Engerberg,

I presume you mean they are 32-bit floating point numbers (called single in VBA). If so, you could make a version of the blob-reading that interprets the data as a float array:

Public Function SQLite3ColumnBlobAsSingles(ByVal stmtHandle As Long, ByVal ZeroBasedColIndex As Long) As Float()
    Dim ptr As Long
    Dim length As Long
    Dim numSingles As Long
    Dim buf() As Single
    
    ptr = sqlite3_stdcall_column_blob(stmtHandle, ZeroBasedColIndex)
    length = sqlite3_stdcall_column_bytes(stmtHandle, ZeroBasedColIndex)
    numSingles = length / 4
    ReDim buf(numSingles - 1)
    RtlMoveMemory VarPtr(buf(0)), ptr, length
    SQLite3ColumnBlobAsSingles = buf
End Function

I'm not able to test the code, but something similar might work.

Otherwise you could convert every set of 4 bytes to a single, but that would be more tricky.

-Govert

Jul 22, 2011 at 2:04 PM

Hi again!

Finally I sorted things out! I managed to read the blob into Excel, but the data imported had integer vartype, something that took a while to figure out. Some values were negative and some positive (I know now that VBA handle integer values between -32768 to +32767) I used a function I got from microsoft support, "IntegerToUnsigned" to reallocate the numbers. The numbers were then between 0 and 65535. After that, things made alot more sense and I could read out all the information in the blob.

Strange things these blobs. Alot of hard work to understand it for newbies :)

Thank you again! It was very important for me to read this data.

Best regards

engerberg