C Programming Interface of SQlite database (5) Convenience Functions -- Reading Notes of Using SQlite

C Programming Interface of SQlite database (5)
Convenience Functions by Drizzle QQ: 253786989

SQlite has many convenient functions left behind in the early days. These functions have many disadvantages. Of course, they still exist for a reason-ease of use.

Their advantages are only convenient to use, rather than good performance. On the contrary, they have worse performance than calling public api functions directly. There is nothing special about these convenient functions, but they call APIs such as sqlite3_prepare_xxx, sqlite3_step, and sqlite3_finalize internally to complete one-stop functions. There are often many extra types of conversions in such functions, so these functions may be executed more slowly than calling sqlite3_prepare_xxx, sqlite3_step, sqlite3_finalize, and other APIs.

Second, these Apis do not support parameter binding. As described in the previous article, such programs are more vulnerable to "SQL injection attacks" and have worse security.

In short, the authors Jay A. Kreibich do not recommend these convenient functions. As he said: "If you're just trying to throw together a quick and dirty snippet of code, these functions provide an easy means of doing that. I stronugly recommend using the normal prepare, step, and finalize routines. you'll end up with safer code and better performance."

(1)

int sqlite3_exec(  sqlite3*,                                  /* An open database */  const char *sql,                           /* SQL to be evaluated */  int (*callback)(void*,int,char**,char**),  /* Callback function */  void *,                                    /* 1st argument to callback */  char **errmsg                              /* Error msg written here */);

Prepare and execute one or more SQL statements to call the callback function pointed to by the 3rd parameters for each row of the result set. The 1st parameters point to a valid database connection. The 2nd arguments are UTF-8-encoded SQL statements that can contain one or more: separated by semicolons ). The first parameter is a pointer to the callback function. If you do not need a callback function, you can pass NULL to this parameter. The 4th parameters are also a pointer used to pass user data to the callback function. Note that, for example, if the pointer is obtained by sqlite3_malloc, you must call the sqlite3_free function to release it when it is no longer in use. The first parameter is a pointer to the pointer, through which the error code is returned. If no error is encountered during sqlite3_exec execution, the function returns SQLITE_ OK.

For example, sqlite3_exec is performing the SELECT query operation and wants to process the returned results. You need to pass a callback function to sqlite3_exec. In this way, the sqlite3_exec function calls this callback function every time it obtains a row of data. If the operation being executed by sqlite3_exec does not return data, you can directly pass NULL to the 3rd parameters of the sqlite3_exec function.

Callback function prototype:

int user_defined_exec_callback( void *userData, int numCol, char **colData, char **colName )

The 1st parameters correspond to the 4th parameters of the sqlite3_exec function and are used to transmit user data. The first parameter indicates the total number of columns in the result set. 3rd parameters Save the data of the current data row, and 4th parameters Save the column names of all columns of the current data row. Both the 3rd and 4th parameters are returned as strings.

Normally, the callback function should return 0. If it returns a non-0 value, the sqlite3_exec function will terminate the execution and return the SQLITE_ABORT error code.

(2)

int sqlite3_get_table(  sqlite3 *db,          /* An open database */  const char *zSql,     /* SQL to be evaluated */  char ***pazResult,    /* Results of the query */  int *pnRow,           /* Number of result rows written here */  int *pnColumn,        /* Number of result columns written here */  char **pzErrmsg       /* Error msg written here */);

This function is different from the sqlite3_exec function. It returns all result data only after the SQL command string is executed. This function is specially designed for SELECT statements.

The first parameter is a pointer to the database connection. The 2nd arguments are UTF-8-encoded SQL command strings. The 3rd parameters seem to be a bit complex and return the SQL statement execution result through this parameter. First, this parameter is a pointer pointing to a one-dimensional array. The element type stored in this one-dimensional array is a string pointer. The number of rows and columns in the result set returned by 4th and 5th parameters. The last parameter returns an error code.

The returned result set after the function is executed, including (nColumn * (nRow + 1) data items. The multiple columns are column names, and the remaining nColumn * nRom items are data.

If the sqlite3_get_table function does not encounter any errors during execution, SQLITE_ OK is returned.

The following is an example of Access to the result set to obtain the data of row R and column C in the result set:

/* offset to access column C of row R of **result */int  offset  = ((R + 1) * numCol) + C;char *value  = result[offset];

The SQL command string passed to the sqlite3_get_table function can contain multiple SELECT statements. However, the returned results of these SELECT statements are all stored in the one-dimensional array pointed to by pazResult. There is no way to determine which rows of data are retrieved by the SELECT statement in a one-dimensional array. All SELECT statements must return the same number of columns; otherwise, the sqlite3_get_table function fails to be executed. In addition, only the first statement returns the column name, which is stored in the first row of the one-dimensional array in the result set. For the above reason, it is best to use a separate SQL command each time you call the sqlite3_get_table function.

(3)

void sqlite3_free_table(char **result);

The result set returned by the sqlite3_get_table function. The memory occupied is released by the sqlite3_free_table function.

C Programming Interface of SQLite database (5)
Convenience functions by Drizzle QQ: 253786989