SQLite for iOS development-C language interface specifications (iii) -- Binding Values To Prepared Statements, sqlite -- cbinding

SQLite for iOS development-C language interface specifications (iii) -- Binding Values To Prepared Statements, sqlite -- cbinding

The previous blog introduced how to connect to the SQLite database and query and traverse result sets. Sqlite3_stmt * stmt is used before, that is, the pre-compiled SQL statement. In this blog, you will learn about sqlite3_stmt and variable binding. Variable binding, simply put, refers to inputting the corresponding value into the pre-compiled SQL statement.

1. sqlite3_stmt Lifecycle

The instance of this object represents an SQL statement compiled into binary. Each SQL statement must be pre-compiled and converted to sqlite3_stmt for execution. In iOS development, Application or UIViewController all have their own lifecycles, and pre-compiled statements also have their own lifecycles. their lifecycles are as follows:

1. the lifecycle of the sqlite3_stmt object originated from sqlite3_prepare_v2 (). You can use sqlite3_prepare_v2 () to create a sqlite3_stmt object.

2. Use the sqlite3_bind _ * () interface to bind a variable to the sqlite3_stmt object.

3. Call sqlite3_step () to run the SQL statement once or multiple times.

4. Call sqlite3_reset () to return to the last executed SQL statement. You can call sqlite3_reset () multiple times. sqlite3_reset () is more like version rollback in version management.

5. Use the sqlite3_finalize () destructor to release the sqlite3_stmt object.

Sqlite3_stmt object constructor ("v2" interface is recommended): sqlite3_prepare (), sqlite3_prepare16 (), sqlite3_prepare16_v2 (), sqlite3_prepare_v2 ()

The destructor of the sqlite3_stmt object: sqlite3_finalize ()

The following is an interface for the sqlite3_stmt object. This blog introduces the sqlite3_bind _ * () method.

Ii. Value binding

Let's briefly introduce what is value binding. To put it bluntly, value binding is to use placeholders for some parameters during SQL statement pre-compilation (here? And then bind it to the variables in the program. Similar to string formatting. If you have previously engaged in Java JDBC or PHP, there is a concept similar to value binding when they operate on the database to execute SQL statements. Replace the placeholder value of the SQL statement with external variables. Value binding is often used in SELECT, INSERT, UPDATE, and other statements.

1. bind variables to pre-compiled SQL statements. Binding different types of variables requires different binding interfaces. Below are commonly used interfaces for variable binding.

2. in the text of the SQL statement entered by sqlite3_prepare_v2 (), the following parameters are replaced. In the following parameters, NNN indicates an integer (this integer represents the index of this parameter), and VVV indicates a letter identifier (the parameter name ). You can use the sqlite3_bind _ * () function to assign values to these placeholders.

Simply put, "?" The number is an anonymous parameter. The default value of the index after the first question mark is 1, and so on. And "? NNN "specifies an index for an anonymous parameter. You can write"? 1 ","? 2 ", and: VVV, @ VVV, $ VVV, these are famous parameters, and VVV is the parameter name. For example: ludashi, @ ludashi, $ ludashi.

The following example shows several manifestations of different parameters. The former is an anonymous parameter, and the latter has its own name.

3. sqlite3_bind _ * () parameter introduction (SQLITE_ OK is returned after these binding functions are successfully executed, and corresponding error code is returned if execution fails)

(1) The first parameter of sqlite3_bind _ * () is the statement pointer containing the pre-compiled placeholder above, that is, the object of sqlite3_stmt.

(2) The second parameter of sqlite3_bind _ * () is the index of the parameter in the SQL statement. For example, if the index value of the first parameter is 1, 1 is passed. The index of an anonymous parameter increases progressively from 1. If you have a parameter name, you can pass in the parameter name through the sqlite3_bind_parameter_index () interface to obtain the index of this parameter. sqlite3_bind_parameter_index () usage is as follows, the first parameter is the sqlite3_stmt object, and the following parameter is the SQL parameter name. The return value is the index of this parameter.

int index = sqlite3_bind_parameter_index (statement, "$ brandidMin");
(3) The third parameter is the value to be bound.

(4) sqlite3_bind_blob (), sqlite3_bind_text () and sqlite3_bind_text16 () There are also fourth and fifth parameters in the three interfaces. The fourth parameter represents the byte length of the third parameter "bound value". The fifth parameter is a pointer to the memory management callback function.

4. Usage scenarios of each binding function



(1) BLOB is a data type that stores big data in a database. It stores data in binary form.

1 SQLITE_API int sqlite3_bind_blob (sqlite3_stmt *, int, const void *, int n, void (*) (void *));
 

(2) As the name suggests, the following method is to bind double type data

1 SQLITE_API int sqlite3_bind_double (sqlite3_stmt *, int, double);
 

(3) Bind a 32-bit integer value

1 SQLITE_API int sqlite3_bind_int (sqlite3_stmt *, int, int);
 

(4) Bind a 64-bit integer value

1 SQLITE_API int sqlite3_bind_int64 (sqlite3_stmt *, int, sqlite3_int64);
 

(5) Bind a NULL value (can be NULL in the database)

1 SQLITE_API int sqlite3_bind_null (sqlite3_stmt *, int);
 

(6) Bind a UTF-8 encoded string. The fourth parameter is also mentioned above. It is the length of the bound string. If it is a negative value, it is bound as much as it is passed.

1 SQLITE_API int sqlite3_bind_text (sqlite3_stmt *, int, const char *, int n, void (*) (void *));
 

(7) Bind a UTF-16 encoded string. The fourth parameter is also mentioned above. It is the length of the bound string. If it is negative, it is bound as much as it is passed.

1 SQLITE_API int sqlite3_bind_text16 (sqlite3_stmt *, int, const void *, int, void (*) (void *));
 

(8) Bind the value of the sqlite3_value structure type. The sqlite3_value structure can store data in any format.

        SQLITE_API int sqlite3_bind_value (sqlite3_stmt *, int, const sqlite3_value *);
 

(9) Bind a binary data of BLOB type of arbitrary length, and set every byte of it to 0. The third parameter is the byte length. The special use of this function is to create a large BLOB object, which can be updated later through the BLOB interface function.

1 SQLITE_API int sqlite3_bind_zeroblob (sqlite3_stmt *, int, int n);
 

5. Value binding commonly used tool functions

(1) The function below returns the number of parameters in the pre-compiled SQL statement. These parameters can be anonymous parameters or named parameters.



The specific usage of is as follows:

1 int count = sqlite3_bind_parameter_count (statement);
2 NSLog (@ "% d", count);
 

(2) Get the name of the corresponding parameter by index



The specific usage of is as follows:

1 const char * name = sqlite3_bind_parameter_name (statement, 1);
2 NSLog (@ "% s", name);
 

(3) In one, the index is obtained by name, which is the opposite of the above method.



The calling method is as follows:

1 int index = sqlite3_bind_parameter_index (statement, ": brandidMax");
2 NSLog (@ ": brandidMax——index =% d", index);
 

Three, value binding examples

The following example is to use value binding in the query statement. After binding the value, call the method of querying the database, and then output the value. Because there is enough above, the following code does not need to be commented.

 1-(void) qureyInfoWithDataBase2: (sqlite3 *) database {
 2     
 3 NSString * qureyInfo = @ "SELECT * FROM CARBRAND WHERE BRANDID>: brandidMin AND BRANDID <: brandidMax";
 4
 5 sqlite3_stmt * statement;
 6
 7 const char * zSql = [qureyInfo UTF8String];
 8     
 9 int result = sqlite3_prepare_v2 (database, zSql, -1, & statement, nil);
10
11 int count = sqlite3_bind_parameter_count (statement);
12 NSLog (@ "count =% d", count);
13
14 const char * name = sqlite3_bind_parameter_name (statement, 1);
15 NSLog (@ "name =% s", name);
16
17 if (result == SQLITE_OK) {
18
19 int index = sqlite3_bind_parameter_index (statement, ": brandidMax");
20 NSLog (@ ": brandidMax_index =% d", index);
twenty one         
22 // Value binding
23 sqlite3_bind_int (statement, 1, 180);
24 sqlite3_bind_int (statement, 2, 200);
25
26 [self queryUserInfoWith: database WithStatement: statement];
27}
28
29}


How to query the database

 1 // Query the database
 2-(void) queryUserInfoWith: (sqlite3 *) database WithStatement: (sqlite3_stmt *) statement {
 3
 4 while (sqlite3_step (statement) == SQLITE_ROW) {
 5
 6 int rowNum = sqlite3_column_int (statement, 0);
 7
 8 char * rowDataOne = (char *) sqlite3_column_text (statement, 1);
 9         
10 char * rowDataTow = (char *) sqlite3_column_text (statement, 2);
11
12 NSString * nameString = [NSString stringWithUTF8String: rowDataOne];
13
14 NSString * firstLetterString = [NSString stringWithUTF8String: rowDataTow];
15
16 NSLog (@ "BrandId =% d, Name =% @, FirstLetter =% @", rowNum, nameString, firstLetterString);
17
18}
19 sqlite3_finalize (statement);
20
twenty one }
The input results are as follows:



 The blog will be here first today, and things about the SQLite database will continue to be updated.