SQLite Forum

How to use BLOB in C program
Login

How to use BLOB in C program

(1) By Scott Doctor (scottdoctor) on 2020-04-12 21:27:50 [source]

A project I am working on involves automatically logging data from a test setup by reading some instrumentation during very long tests. That part works fine. I am adding sqlite to the data collection program (written in C) to use sqlite as "the file format" for the massive amount of data collected. Currently the system simply writes a massive csv file that is then imported into excel. ugh. Literally hours spent after each test manually processing data. So I wrote a C program that talks to the equipment, snags the data, then organizes it into a sqlite database. Much easier to find and grab data of interest after the test saving hours of work.

Handling numbers and text is no problem, works fine. During the post processing, my program creates and displays some graphs of the data. The current process involves literal cut and paste of the graphs. Screen shot, print out, cut out graph, tape into log book. So I am trying to make this program into a digital log book where all the data and graphs are in a single sqlite file.

I can programmatically convert the graphs in the post processing into a PNG or JPEG image file. I want to save that image as a BLOB in the table with the test data.

I never used BLOB before and confused how to insert and extract data using a BLOB. The syntax is confusing from the few examples I found on the internet which seem to use BIND to do the task.

Assume I have the image in memory as a char array of bytes.

I need an example in C of an INSERT statement how I put a binary block of memory into a BLOB, and conversely extracting the bytes from the BLOB into a memory array.

(2) By Larry Brasfield (LarryBrasfield) on 2020-04-13 01:10:16 in reply to 1 [link] [source]

I think you want to look at the extension in ext/misc/fileio.c. That same code is ensconced into recent sqlite3 shell tools, without need of loading an extension DLL.

It does write/read files, converting from/to BLOB objects you can use with regular SQL.

If you want to cutout the lives-in-a-file step, you might peruse the

sqlite3_blob_{open,read,write,close}

streaming interface. I've used it to good effect.

(3) By Larry Brasfield (LarryBrasfield) on 2020-04-13 01:26:34 in reply to 1 [link] [source]

Here is some streaming code. Sorry it's not yet documented. It should be somewhat obvious, as it helps implement shell functions described thusly:

.blobextract TABLE COL ROW FILE ?DB?    Extract DB blob into named file.
                         Parameters must specify a blob selected by:
                           SELECT COL FROM DB.table WHERE rowid = ROW
                         FILE may be '-' for extraction to stdout.

.blobreplace TABLE COL ROW FILE ?DB?    Replace DB blob with file content.
                         Otherwise like .blobextract except that FILE must
                         name a readable file. ('-' is not stdin.)

(4.1) By Keith Medcalf (kmedcalf) on 2020-04-13 11:31:35 edited from 4.0 in reply to 1 [link] [source]

You send a blob as a bound parameter using sqlite3_bind_blob just as you would use any of the sqlite3_bind_<datatype> functions. Similarly you would retrieve a blob column using sqlite3_column_blob just as you would use any of the sqlite3_column_<datatype> functions. The interfaces are quite similar to the functions for handling text, except that you of course need to set or get the data size since blobs are not 0 terminated.

https://sqlite.org/c3ref/bind_blob.html
https://sqlite.org/c3ref/column_blob.html