[Database] sqlite3 usage Summary 4

(2) An auxiliary data type is required for operating binary SQLite to operate binary data: sqlite3_stmt *.
-J: d $ S + | $ N/W7 D & M
2 C3 x $ X % s) z "N ;~! X-^ 'x records an "SQL statement ". Why do I use double quotation marks for "SQL statements? Because you can regard the content represented by sqlite3_stmt * as an SQL statement, but in fact it is not a well-known SQL statement. It is an internal data structure that has resolved SQL statements and marked records by SQLite itself.
3 F, D7 L $ M4 T7 v
. X0 i7 A6 U "Y -?) K/\ 'n' because this structure has been parsed, you can insert binary data into this statement. Of course, inserting binary data into the sqlite3_stmt structure cannot directly use memcpy or use a + number like STD: string. The function provided by SQLite must be used for insertion. 8 E (Q3 J! B % J F
1 O1 G: V & O & B8 S % _ S. H -'
: Y' z5 Y & S; H! W

(X7 _ & G (I, S * H7 ni.1 write binary
2 Q; R! J2 K1 @
6 B5 M % P4 I0 y;} (| The following describes the steps for writing binary data. 'L5 d $ V/Z' h'e: W
+ N 'A' R9 E * T3 I $? 0 P' |]
To insert binary data, make sure that the field type of this table is blob. I suppose there is such a table:

Copy content to clipboard

Code:

create table Tbl_2( ID integer, file_content   blob )

First declare
: B-S, C6 C, C9 N #[2 m. s! [(N; @ 1 V
Sqlite3_stmt * Stat;
* B, q + Y; B-s! C & I d "B-u7 Z # h # B h % P0 @ 6 E2 V; B
Then, resolve an SQL statement to the stat structure:

Copy content to clipboard

Code:

sqlite3_prepare( db, “insert into Tbl_2( ID, file_content) values( 10, ? )”, -1, &stat, 0 );

The above function completes SQL statement parsing. The first parameter, like the previous one, is a sqlite3 * type variable, and the second parameter is an SQL statement.
# D-l/y $ L & Q (O7g (V
6 V * L #~ 2 P5 O9 h what's special about this SQL statement is that there is? . In the sqlite3_prepare function ,? Number indicates an undefined value, which is inserted only when its value is equal.
% I2 W. D1 H.]
2 _ "Y + Z) V2 B The third parameter I wrote is-1, which indicates the length of the preceding SQL statement. If it is less than 0, SQLite automatically calculates its length (the SQL statement is treated as a string ending with \ 0 ). 8 w # | 5 U0 ~ 8}. z'e,
* U/F5 C "V: L7 X4 N.} 2 H3 K
The fourth parameter is the pointer of sqlite3_stmt. The parsed SQL statement is placed in this structure. + F6 n! B; D &?, N
-{# E * E: O7 F # T
I do not know what the fifth parameter is. It can be set to 0.
-M. E % Q1 ''' y/^ # D (K
) '"| (O8 @" C $ C4 o % Y' H + L5 I p if this function is successfully executed (the returned value is sqlite_ OK and stat is not null ), then you can start to insert binary data.

Copy content to clipboard

Code:

Sqlite3_bind_blob (stat, 1, pdata, (INT) (length_of_data_in_bytes), null); // pdata is the data buffer, length_of_data_in_bytes is the data size, in bytes

This function has a total of five parameters.
-'(U & R-T * C, C3 B $ Z
, H7 C. Q *~. ^ $ T * C 1st parameters: sqlite3_stmt * type variable obtained by prepare.
/C ({. Y (@ M #} (W "d + y6 U, y
! W9 J % U6 F "F/T 2nd parameters :? Number index. In the preceding prepare SQL statement, is there? Number. If there are multiple? How can I insert a number? The method is to change the bind_blob function's 2nd parameters. I write 1 for this parameter, indicating that the value inserted here should replace the first? (Here, the index starts counting from 1, not from 0 ). If you have multiple? Number, write multiple bind_blob statements, and change their 2nd parameters to replace them with different? . If yes? No. SQLite is null. 4 | + Q5 L) K, ;_

2 J i8 T2 x0 p-W $] 7 R "B 3rd parameters: Starting pointer of binary data. "W, N4 I: Q, L; h $ o, ^; x2 ^
5 W * I * ^ 6 I4 Z P7}
4th parameters: the length of binary data, in bytes.
2 \ 6 D! C; C, u d; I @: S0 C + F & y6} 'f0 V0 G5 K/h
5th parameters: it is a callback function that tells SQLite to call this function after processing data to analyze your data. I have not used this parameter, so I cannot understand it deeply. However, it is generally null, and the memory to be released is released using code.
. P ('2 \! M2 F; N3 r-g; @ 2G! Y .[
.! After E-'-V3 P9 {BIND is complete, the binary data enters your "SQL statement. Now you can save it to the database:

Copy content to clipboard

Code:

int result = sqlite3_step( stat );

With this statement, the SQL statement indicated by stat is written to the database.
/Q: C/[6 I! L5 s "N0 X. u'a. Z" @ 3 U0 x * H2 R: S + E3
Finally, release the sqlite3_stmt structure:

Copy content to clipboard

Code:

Sqlite3_finalize (STAT); // analyze the content just allocated

I .2 Read Binary & q + {% F9 i8 J8 N + S G4 L: S. Q

(? % U! E8 z * P "W/m (V6 A describes the steps for reading binary data.
/@ 9 N-V7 y' | 0 I4 C, i8 [5}: D5 o
As before, declare the sqlite3_stmt * type variable first:

Copy content to clipboard

Code:

sqlite3_stmt * stat;

Then, resolve an SQL statement to the stat structure:

Copy content to clipboard

Code:

sqlite3_prepare( db, “select * from Tbl_2”, -1, &stat, 0 );

After the prepare is successful (the returned value is sqlite_ OK), query the data.

Copy content to clipboard

Code:

int result = sqlite3_step( stat );

The return value of this statement is sqlite_row, indicating success (not sqlite_ OK ).
1 l0 I0 T5 C # p $ |, s # R1 \"'
$]. D1 \ + '+ F & {You Can cyclically execute the sqlite3_step function to query a record at a time. If the returned value is not sqlite_row, the query is complete.
1 M7 O) g, N + M-J * n
. G7 H "t0 |; [4] then start to get the value of the first field: ID. ID is an integer. Use the following statement to obtain its value:

Copy content to clipboard

Code:

Int id = sqlite3_column_int (stat, 0); // The first field content is obtained. The value starts from 0 because the ID field of my table is the first field, so here I enter 0

Next we will get the value of file_content, because file_content is binary, so I need to get its pointer and its length:

Copy content to clipboard

Code:

      const void * pFileContent = sqlite3_column_blob( stat, 1 );

      int len = sqlite3_column_bytes( stat, 1 );

In this way, the binary value is obtained. &? 6 ^,} 3 P2 h # O4 [! M5 A2 x

& Y4 G "E9 r$ ^ 6 K, K, X after saving the content of pfilecontent, do not forget to release the sqlite3_stmt structure:

Copy content to clipboard

Code:

Sqlite3_finalize (STAT); // analyze the content just allocated

I .3 reuse sqlite3_stmt Structure
; X) '1 M/D: T
$ D3? % '7 h # B/n if you need to repeat the sqlite3_stmt structure parsed by sqlite3_prepare, you need to use the function: sqlite3_reset.

Copy content to clipboard

Code:

result = sqlite3_reset(stat);

In this way, the stat structure becomes the status when sqlite3_prepare is completed, and you can re-bind the content for it.