SQLite binary field (ZZ)

Sometimes we use the database to store files, we need to use binary fields, the following columns are commonly used methods.

1. Write binary data

Sqlite3 * DB;

int result;

Char **errmsg =null;

result = Sqlite3_open ("test.db", &db);

if (Result! = SQLITE_OK)

{return-1;}

result = sqlite3_exec (db, "CREATE Table TB (ID integer, Content blob)", NULL, NULL, ERRMSG);

if (Result! = SQLITE_OK) {printf ("Erro");}

Char *buffer;//the binary content to be written, or it can be read from a file

Buffer=new char[1024*1024];

for (int i=0;i<1024*1024;i++) buffer[i]= ' a ';

SQLITE3_STMT *stat;//The structure to use when writing binary data

Sqlite3_prepare (db, "INSERT into TB (ID, content)",-1, &stat, 0);//Prepare to insert data

Sqlite3_bind_blob (stat, 1, buffer, strlen (buffer), NULL); Bind content and fields

Result=sqlite3_step (stat);//execution

Sqlite3_finalize (stat); free memory

Sqlite3_close (DB);

2. Read binary data

Here is an introduction to the Sqlite3_blob_open function

This function is used to open binary field data

The first parameter is a database handle

The second parameter is the name of the database

The third parameter is a table name

The fourth parameter is a binary data field (column) name

The fifth parameter is the number of rows, which is the field that opens the first few rows.

Sixth one

The seventh parameter is a binary file handle

Using this function to make a binary field is as simple as opening a file.

The following is an example code:

Sqlite3 * DB;

int result;

Char **errmsg =null;

result = Sqlite3_open ("test.db", &db);

if (Result! = SQLITE_OK)

{return-1;}

int rf= Sqlite3_blob_open (db,null, "tbl_2", "File_content", 1,1,&sqlite3_blob);

if (rf!= sqlite_ok) return–1;

int len=sqlite3_blob_bytes (SQLITE3_BLOB);//Get Binary data length

Sqlite3_blob_read (sqlite3_blob,buffer,len,0); Read the binary data, the last parameter is the starting position

If you do not want to read the entire content, you can set this parameter

Sqlite3_blob_close (SQLITE3_BLOB);//Close

Sqlite3_close (DB);

3. Several questions

A. The fifth parameter of the Sqlite3_blob_open function reads the data of the first row, does not know what function can get the absolute row number of the current row, and the Sqlite3_total_changes function can only get the relative number of rows.

B. The Sqlite3_blob_write function is used to write binary data, but this function can only be used to modify binary data, not to insert binary data, and the length of the write data cannot exceed the length of the first time the data is inserted.

I want to store a single binary data field may have a few grams of capacity, and can be modified at any time, the length will constantly change, with sqlite3_stmt obviously not, and the Sqlite3_blob_write function can not change the length of the binary data field, Is that I do not know much about SQLite, or the database itself has this limitation, I hope the Master can guide.