Sqlite3 0 Basic Primer

Sqlite3 Introduction to use

One. The process used

To use SQLite, you need to import libsqlite3.0.dylib into the project's configuration file.

The functions used in the process are broadly divided into the following:

> Sqlite3_open ()

> Sqlite3_prepare ()

> Sqlite3_step ()

> Sqlite3_column ()

> Sqlite3_finalize ()

> Sqlite3_colse ()

These processes are conceptually stated, not entirely program-running processes, such as Sqlite3_column (), which represent the various operations of a column that queries the data within a row.

says that this function is not actually present in SQLite.

1.sqlite3_open () Open database

Before you manipulate the database, you first open the database, which opens a link to the SQLite database file and returns a database link object. This operation is the first in the program

A call to the SQLite function, but also a prerequisite for other SQLite APIs. Many SQLite interface functions require a pointer to a database link object as their first parameter.

The definition of the function-----(for different encodings and parameters, the official document gives three definitions)

int Sqlite3_open (

const char *filename,/* UTF8 */

Sqlite3 **ppdb/* SQLite DB handle */

);

int Sqlite3_open16 (

const void *filename,/* UTF16 */

Sqlite3 **ppdb/* SQLite DB handle */

);

int Sqlite3_open_v2 (

const char *filename,/* UTF8 */

Sqlite3 **ppdb,/* SQLite DB handle */

int flags,/* flags */

const char * ZVFS/* Name of VFS module to use */

);

Additional notes:

If the database file to be opened does not exist, a database file with the same name will be created.

return value:

If the SQLite database is opened (or created) successfully, SQLITE_OK will be returned, otherwise an error code will be returned. Sqlite3_errmsg () or SQLITE3_ERRMSG16 ()

Can be used to obtain the English description of the database open error code, these two functions are defined as:

const char * SQLITE3_ERRMSG (sqlite3*);

const void * SQLITE3_ERRMSG16 (sqlite3*);

Parameter description:

FileName: The file name of the database file that needs to be opened, this parameter is UTF-8 encoded in Sqlite3_open and SQLITE3_OPEN_V2, and in Sqlite3_open16

, the UTF-16 encoding method is used.

PPDB: A database link handle will return to this parameter, and if an error occurs, PPDB will return a null value

Flags: Additional control parameters as database links, which can be sqlite_open_readonly,sqlite_open_readwrite and sqlite_open_readwrite| One of the sqlite_open_create, used to control how the database is opened, can be Sqlite_open_nomutex,sqlite_open_fullmutex, Sqlite_open_sharedcache, As well as Sqlite_open_privatecache, detailed information can be consulted in the documentation

2.sqlite3_prepare ()

This function converts the SQL text into a prepared statement (prepared statement) object and returns a pointer to the object. This interface requires a database connection pointer and a text to be prepared that contains the SQL statement. It does not actually execute (evaluate) This SQL statement, it simply prepares the SQL statement for execution

function definition (listed only for UTF-8)

int Sqlite3_prepare (

Sqlite3 *db,/* Database handle */

const char *zsql,/* SQL statement, UTF-8 encoded */

int Nbyte,/* Maximum length of zsql in bytes. */

Sqlite3_stmt **ppstmt,/* out:statement handle */

const char **pztail/* out:pointer to unused portion of Zsql */

);

int Sqlite3_prepare_v2 (

Sqlite3 *db,/* Database handle */

const char *zsql,/* SQL statement, UTF-8 encoded */

int Nbyte,/* Maximum length of zsql in bytes. */

Sqlite3_stmt **ppstmt,/* out:statement handle */

const char **pztail/* out:pointer to unused portion of Zsql */

);

　　

Parameters:

DB: Data pointer

Zsql:sql statements, using UTF-8 encoding

Nbyte: If Nbyte is less than 0, the function takes out the contents of the Zsql from the beginning to the first 0 terminator, and if the nbyte is not negative, it is the maximum number of bytes that the function can read from the Zsql. If nbytes is not negative, zsql terminates at the first meeting of '/000/or ' u000 '

Pztail: The above mentioned Zsql end after meeting the Terminator or the nbyte to achieve the set, if there is any remaining content, then the remaining content is stored in Pztail, not including The Terminator

PPSTMT: A pointer to a compiled prepared statement that can be executed using sqlite3_step (), if an error occurs, it is set to NULL, such as if the input text does not include an SQL statement. The calling procedure must be responsible for removing the compiled SQL statement after it is finished using Sqlite3_finalize ().

Description

If execution succeeds, returns SQLITE_OK, otherwise an error code is returned. It is recommended to use the SQLITE3_PREPARE_V2 function in any program now, Sqlite3_prepare only for forward compatibility

Note

<1> Prepare statement (prepared statement) object

typedef struct SQLITE3_STMT sqlite3_stmt;

The Prepare statement (prepared statement) object represents an instance of a simple SQL statement object, which is often referred to as a "prepared statement" or "compiled SQL statement" or directly called a "statement."

The life cycle of a statement object undergoes such a process:

L use SQLITE3_PREPARE_V2 or related functions to create this object

L use sqlite3_bind_* () to bind values to host parameters (host parameters)

L execute this SQL by calling Sqlite3_step one or more times

L use Sqlite3--reset () to reset this statement, and then go back to the 2nd step, this process done 0 or more times

L destroy this object using Sqlite3_finalize ()

In SQLite, there is no specific content of the SQLITE3_STMT structure, it is just an abstract type, which is usually manipulated by its pointers during use, while the pointer to the SQLITE3_STMT type is actually a pointer to the VDBE structure

<2> host parameters (host parameters)

In the SQL statement text passed to SQLITE3_PREPARE_V2 () or its variables, the text that satisfies the following template is replaced with a parameter:

L?

L? NNN,NNN representative Numbers

L:VVV,VVV representative character

L @VVV

L $VVV

In the above templates, nnn represents a number, VVV represents an alphanumeric marker (for example: 222 for a marker with a name of 222), and the arguments (variables) in the SQL statement are specified by several templates above, such as

"Select?" From? "This statement specifies two parameters, and the index value of the first parameter in the SQLite statement is 1, knowing that the two parameters in the statement are indexed 1 and 2, using"? " will be automatically given the index value, and use "? NNN "You can specify the index value of the parameter yourself, which indicates that the index value of this parameter is NNN. ": VVV" means a parameter named "VVV", which also has an index value, which is automatically specified.

You can use sqlite3_bind_* () to bind values to these parameters

　　

3. SQLITE3_SETP ()

This procedure is used to execute a prepared statement with the previous sqlite3_prepare created. This statement executes to the location where the first line of the result is available. To move on to the second line of the result, simply call SQLITE3_SETP () again. Continue calling SQLITE3_SETP () to know that the statement is complete, those statements that do not return a result (such as: insert,update, or delete), Sqlite3_step () is only executed once to return

function definition

int Sqlite3_step (sqlite3_stmt*);

return value

The return value of the function is based on the function used to create the sqlite3_stmt parameter, and if the old version of the interface Sqlite3_prepare () and Sqlite3_prepare16 () is used, the return value is Sqlite_busy, Sqlite_done, Sqlite_row, Sqlite_error, or Sqlite_misuse, and the V2 version of the interface Sqlite3_prepare_v2 () and SQLITE3_PREPARE16_V2 () return both these result codes and extended result codes.

For all V3.6.23.1 and all versions preceding it, it is necessary to call Sqlite3_reset () after Sqlite3_step (), before the subsequent sqlite3_ step. Failure to call Sqlite3_reset to reset the prepare statement will cause sqlite3_ step to return Sqlite_misuse, but in V3. After 6.23.1, Sqlite3_step () will automatically call Sqlite3_reset.

int Sqlite3_reset (sqlite3_stmt *pstmt);

Sqlite3_reset is used to reset a prepared statement object to its initial state, and then prepares to be re-executed. All SQL statement variables use the sqlite3_bind* bound value, and the bindings are reset using Sqlite3_clear_bindings. The Sqlite3_reset interface resets the prepared statement to the beginning of its code. Sqlite3_reset does not change any of the binding values on the prepared statement, then it is assumed that the statement may have undergone other changes during execution, and then the statement resets it to the state of the bound value.

4. Sqlite3_column ()

This process returns a column from the current row of the result set that executes the sqlite3_step () execution of a prepared statement. Each time Sqlite3_step gets a column for a result set, the process can be called multiple times to query the values of the columns of the row. There are multiple functions for column operations, all prefixed with sqlite3_column

const void *sqlite3_column_blob (sqlite3_stmt*, int icol);

int sqlite3_column_bytes (sqlite3_stmt*, int icol);

int SQLITE3_COLUMN_BYTES16 (sqlite3_stmt*, int icol);

Double sqlite3_column_double (sqlite3_stmt*, int icol);

int Sqlite3_column_int (sqlite3_stmt*, int icol);

Sqlite3_int64 Sqlite3_column_int64 (sqlite3_stmt*, int icol);

Const unsigned char *sqlite3_column_text (sqlite3_stmt*, int icol);

const void *sqlite3_column_text16 (sqlite3_stmt*, int icol);

int Sqlite3_column_type (sqlite3_stmt*, int icol);

Sqlite3_value *sqlite3_column_value (sqlite3_stmt*, int icol);

Description

The first parameter is a pointer to the prepared statement object returned from Sqlite3_prepare, and the second parameter specifies the index of the column in this row that you want to return. The leftmost column has an index number of 0, and the number of columns in the row can be obtained using Sqlite3_colum_count ().

These processes convert the type of the numeric value according to the situation, and SQLite uses sqlite3_snprintf () to automate the conversion, and here is a detailed table of conversions:

Note: BLOB data type refers to binary data blocks, such as to store a picture in the database, this picture will be stored in binary form, the corresponding data type in SQLite is the blob

　　

int sqlite3_column_bytes (sqlite3_stmt*, int icol) int sqlite3_column_bytes16 (sqlite3_stmt*, int icol) Two functions returns the number of bytes for the contents of the corresponding column , the number of bytes does not include the 0 terminator that was added to the subsequent type conversion process.

Here are a few of the safest and simplest usage strategies

> First Sqlite3_column_text (), then Sqlite3_column_bytes ()

> First Sqlite3_column_blob (), then Sqlite3_column_bytes ()

> First sqlite3_column_text16 (), then SQLITE3_COLUMN_BYTES16 ()

　　

5. Sqlite3_finalize

int sqlite3_finalize (sqlite3_stmt *pstmt);

This process destroys the prepared statements that were previously created by Sqlite3_prepare, and each prepared statement must use this function to destroy to prevent memory leaks.

Calling this function on a null pointer has no effect, and can be prepared at any point in the life cycle of the statement to invoke the function: After the statement is executed, after calling Sqlite_reset one or more times, or after sqlite3_step any call, regardless of whether the statement completes execution

6. Sqlite3_close

This process closes the database connection that was previously opened using Sqlite3_open, and any prepared statement related to the connection must be released before the shutdown function is called

　　

Two. Examples of Use

Saved in document recently saved in document Apple not allowed to upload
Save in cache inside
NSString *path = Nssearchpathfordirectoriesindomains (Nscachesdirectory, Nsuserdomainmask, YES). FirstObject;
Stitching file name
Path = [path stringbyappendingpathcomponent:@ "Student.sqlite"];
NSLog (@ "%@", Path);

Open a database, create a database file
FileName to save the full path file name of the database
PPDB DB Instance
Sqlite3 *db = nil;
if (Sqlite3_open ([path utf8string], &_db) = = SQLITE_OK) {
NSLog (@ "open successfully");
}else{
NSLog (@ "Open failed");
}
Create a table
First parameter: DB instance
Second parameter: Execute that database statement
NSString *sql = @ "CREATE table if not exists t_student (ID integer primary key autoincrement, name text, age integer);";
[Self execsqlwithstring:sql];

NSString *sql = @ "Select *from t_student;";
Prepare query
Database statement Bytes-1 indicates the number of bytes automatically calculated
PPSTMT handle: The statement used to manipulate the query
Sqlite3_stmt *stmt;
if (Sqlite3_prepare (_db, SQL. Utf8string,-1, &stmt, NULL) ==SQLITE_OK) {
Ready to succeed
Execution handle
while (Sqlite3_step (stmt) = = Sqlite_row) {
int ID = sqlite3_column_int (stmt, 0);
NSString *name = [NSString stringwithutf8string: (const char *) Sqlite3_column_text (stmt, 1)];
NSLog (@ "%d-%@", id,name);
}
}

　　

　　Sqlite3_exec is sqlite3_prepare_v2,sqlite3_step () and sqlite3_finalize () packages , allowing the program to execute multiple times SQL statements instead of writing many duplicate code.

The Sqlite3_exec interface executes 0 or more UTF-8 encoded, semicolon-delimited SQL statements that are passed to the second parameter. If Sqlite3_exec's third parameter callback function pointer is not empty, it will be called for each result row from the executed SQL statement (that is, the callback function will be called multiple times, the above example will return 2 result rows, and thus will be executed 2 times), the 4 parameter is the first parameter passed to the callback function, and if the callback function pointer is empty, then the callback does not occur and the resulting row is ignored.

If an error occurs in the execution of the SQL statement, the execution of the current statement is stopped and subsequent statements are skipped. When the fifth parameter is not empty, it is allocated memory and writes an error message, so you need to call Sqlite3_free to release the object after sqlite3_exec to prevent memory leaks

callback function:

Int (*callback) (void*,int,char**,char**),/* Callback function */

The first parameter is passed in by the fourth parameter of the sqlite3_exec.

The second parameter is the number of columns in the result row

The third parameter is a pointer to the column data in a row

The fourth parameter is a pointer to the column name in the row

Sqlite3 0 Basic Primer