SQLite Forum

how to insert a structure data into blob?

how to insert a structure data into blob?

(1.3) Originally by hitbuyi with edits by Richard Hipp (drh) on 2020-05-21 12:23:13 from 1.2 [link] [source]

I am newer to  sqlite3, now i want insert a struct data into table,

char* sRadar1 = "CREATE TABLE IF NOT EXISTS radar_1 ("\
     "FrmNo         INT       PRIMARY KEY     NOT NULL," \
     "CapTime       DOUBLE                    NOT NULL," \
     "Data          BLOB," \
     "CamTime       DOUBLE," \
     "Mark          INT );";

typedef struct {
	int ID;           /* target's ID */
	float rx;         /* range of x  */ 
	float ry;         /* range of y  */
	float vx;         /* velocity of x  */ 
	float vy;         /* velocity of y  */
	float ax;         /* acceleration of x  */ 
	float ay;         /* acceleration of y  */
	float PoE;        /* target's probability of existence */ 
	float length;     /* target's length  */
	float width;      /* target's width   */
 	float height;     /* target's height  */
	int   type;       /* target's type    */
	double life;      /* target's life    */

hh_RadarTarget_t  mydata;
char sql[200];
char *errmsg = NULL;
sqlite3_stmt *stmt; 
int ret = 0;

mydata.id = 100;

sqlite3_exec(db, sRadar1, NULL, 0, &zErrMsg); // create table 

sprintf(sql, "INSERT INTO radar_1(FrmNo, CapTime,CamTime) VALUES(%d,%lf,%lf)", 1, 123.1,456.1);
sqlite3_exec(db,sql, NULL, NULL, &errmsg);
ret = sqlite3_prepare_v2(db, "INSERT INTO radar_1(Data) VALUES(?)", -1, &stmt, NULL);
if(SQLITE_OK!= ret || !stmt)
   printf("sqlite3_prepare_v2 failed,ret = %d\n",ret);

sqlite3_bind_blob(stmt,1, mydata, sizeof(hh_RadarTarget_t), NULL);
if(SQLITE_OK!= ret || !stmt)
   printf("sqlite3_bind_blob failed,ret = %d\n",ret);

if((SQLITE_DONE!= ret) && (SQLITE_ROW!= ret))
   printf("sqlite3_step failed,ret = %d \n",ret);


It can not work, why?
Any help will be apprecicated.

(2) By Stephan Beal (stephan) on 2020-05-21 06:24:43 in reply to 1.0 [link] [source]

It can not work, why?

Yes, it can work. sqlite3_bind_blob() doesn't accepts raw memory as-is, and that's what you've passed to it. You haven't shown us any error messages, nor any code where you test the result of the insert, so we can only speculate about what might have gone wrong in your case. You have failed to check any of the result codes for your various calls to sqlite3 functions, especially sqlite3_step().

Your CREATE TABLE has CapTime DOUBLE NOT NULL and your INSERT does not provide a value for that field. Perhaps that is where your problem is. Checking the result of the call to sqlite3_step(), and calling sqlite3_errmsg() if it fails, will probably tell you exactly what went wrong.

In any case: storing a binary image of a struct this way is completely non-portable and not generally a good idea. You may think that it's a quick and easy way to save and restore binary data, but it's a dark path which will cause you grief if you try to load that data on a machine with a different architecture. Such data will load in an incompatible environment, but may have a different binary signature there and thus effectively be corrupted.

(3) By hitbuyi on 2020-05-21 06:56:50 in reply to 2 [link] [source]

Sorry, now I have posted the whole source code. the other items except for blob can be written correctly. the problem is that blob can NOT be written

there is no error message, the code is complied ok

what's your meaning of raw memory? how shoud I do? can you show me a piece of code? or any similar example available on this forum?

(5) By Stephan Beal (stephan) on 2020-05-21 07:02:14 in reply to 3 [link] [source]

there is no error message, the code is complied ok

Sure, it compiles, but the code you have shown us does not check the results of any of the calls to sqlite functions. Please re-read my previous response: your INSERT does not comply with your schema, so cannot work for reasons having nothing to do with the blob.

(7.1) By hitbuyi on 2020-05-21 10:44:30 edited from 7.0 in reply to 5 [link] [source]


(9) By hitbuyi on 2020-05-21 10:47:27 in reply to 5 [link] [source]

This time, I remove NOT NULL constraint from primary key, and blob can be inserted, still there is another question:

FrmNo ,blob should be in the same row, but they are in different rows, FrmNo in the 1 row, the blob in the 2nd row, why? how should I modified my code?

(4.1) By hitbuyi on 2020-05-21 09:54:43 edited from 4.0 in reply to 2 [link] [source]

it's not an image data, but a struct data, I save it on disk, and read it from my PC

(6) By Stephan Beal (stephan) on 2020-05-21 07:27:11 in reply to 4.0 [link] [source]

what's your meaning of raw memory?

it not image data, but a struct data,

i mean no offense by this, but those two statements reveal that your understanding of working with memory in C could use some improvement before trying to work with "raw memory images" of data, i.e. blobs. In doing so, you will learn why storing data that way is only useful in very specialized cases. Unfortunately, it's been almost 30 years since i first learned C, so cannot recommend any reasonably modern introductory references on the topic, but googling "introduction to modern c programming" reveals several promising results. Perhaps other forum-goers can recommend specific reference works.

(8) By hitbuyi on 2020-05-21 10:31:36 in reply to 2 [link] [source]

sqlite3_step failed,errorCode = 19 errMsg = NOT NULL constraint failed: radar_1.FrmNo

what that mean?

(10) By Richard Hipp (drh) on 2020-05-21 12:25:15 in reply to 1.3 [source]

sqlite3_bind_blob(stmt,1, &mydata, sizeof(hh_RadarTarget_t), NULL);
                  /*      ^-----  I think you need to add this "&"  */

(11) By hitbuyi on 2020-05-22 01:09:50 in reply to 10 [link] [source]

ok, i hav done that.