iOS Development database Chapter-sqlite Common functions

iOS Development database Chapter-sqlite Common functions

A simple explanation

1. Open the Database

int Sqlite3_open (

const char *filename,//file path for database

Sqlite3 **ppdb//DB instance

);

2. Execute any SQL statement

int Sqlite3_exec (

sqlite3*,//an open DB instance

const char *sql,//SQL statement to execute

Int (*callback) (void*,int,char**,char**),//callback after the SQL statement is executed

void *,//The 1th parameter of the callback function

Char **errmsg//error message

);

3. Check the legality of the SQL statement (pre-query preparation)

int Sqlite3_prepare_v2 (

Sqlite3 *db,//DB instance

const char *zsql,//SQL statement to check

int Nbyte,//maximum byte length for SQL statements

Sqlite3_stmt **ppstmt,//sqlite3_stmt instance, used to obtain database data

const CHAR **pztail

);

4. Querying a row of data

int Sqlite3_step (sqlite3_stmt*); If a row of data is queried, the Sqlite_row is returned

5. Use stmt to get the value of a field (the subscript of a field starts with 0)

Double sqlite3_column_double (sqlite3_stmt*, int icol); Floating point data

int Sqlite3_column_int (sqlite3_stmt*, int icol); Integral type data

Sqlite3_int64 Sqlite3_column_int64 (sqlite3_stmt*, int icol); Long-integer data

const void *sqlite3_column_blob (sqlite3_stmt*, int icol); Binary Text data

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

Second, SQLite code

1. Create, open, and close the database

Create or open a database

Path is the location where the database files are stored

sqlite3 *db = NULL;

int result = Sqlite3_open([path utf8string], &db);

Code parsing:

Sqlite3_open () will open the database based on the file path, and if it does not, a new database will be created. If result equals constant SQLITE_OK, the database is opened successfully

Sqlite3 *db: An open DB instance

The path to the database file must be passed in the C string (not NSString)

Close database:sqlite3_close(db);

2. Execute SQL statements that do not return data

Execute a statement of the record

char *errormsg = NULL; Used to store error messages

Char *sql = "CREATE table if not exists T_person (ID integer primary key autoincrement, name text, age integer);";

int result = sqlite3_exec(db, SQL, NULL, NULL, &ERRORMSG);

Code parsing:

Sqlite3_exec () can execute any SQL statements, such as Create tables, update, insert, and delete operations. However, it is generally not necessary to execute a query statement because it does not return the queried data

Sqlite3_exec () can also execute the statement:

(1) Open transaction: BEGIN TRANSACTION;

(2) ROLLBACK TRANSACTION: rollback;

(3) Commit a transaction: commit;

3. Inserting data with placeholders

Char *sql = "INSERT into T_person (name, age) VALUES (?,?);";

sqlite3_stmt *stmt;

if (sqlite3_prepare_v2(DB, SQL,-1, &stmt, NULL) = = Sqlite_ok) {

Sqlite3_bind_text (stmt, 1, "Hen",-1, NULL);

Sqlite3_bind_int (stmt, 2, 27);

}

if (sqlite3_step(stmt)! = Sqlite_done) {

NSLog (@ "Insert data error");

}

sqlite3_finalize (stmt);

Code parsing:

SQLITE3_PREPARE_V2 () The return value equals SQLITE_OK, stating that the SQL statement is ready for success with no syntax issues

Sqlite3_bind_text (): Most of the binding functions have only 3 parameters

(1) The 1th parameter is a sqlite3_stmt * type

(2) The 2nd parameter refers to the position of the placeholder, the position of the first placeholder is 1, not 0

(3) The 3rd parameter refers to the value to which the placeholder is bound

(4) The 4th parameter refers to the length of the data passed in the 3rd parameter, and for the C string, it can be passed-1 instead of the length of the string

(5) The 5th parameter is an optional function callback that is typically used to complete the memory cleanup work after the statement is executed

Sqlite_step (): Execute SQL statement, return Sqlite_done representative successful execution completed

Sqlite_finalize (): Destroy Sqlite3_stmt * Object

4. Querying data

Char *sql = "Select Id,name,age from T_person;";

sqlite3_stmt *stmt;

if (sqlite3_prepare_v2(DB, SQL,-1, &stmt, NULL) = = Sqlite_ok) {

while (sqlite3_step(stmt) = = Sqlite_row) {

int _id = sqlite3_column_int(stmt, 0);

Char *_name = (char *)sqlite3_column_text(stmt, 1);

NSString *name = [NSString stringwithutf8string: _name];

int _age = sqlite3_column_int(stmt, 2);

NSLog (@ "id=%i, name=%@, age=%i", _id, Name, _age);

}

}

sqlite3_finalize (stmt);

Code parsing:

Sqlite3_step () returns Sqlite_row represents a new record traversal

Sqlite3_column_* () is used to get the corresponding value for each field, and the 2nd parameter is the index of the field, starting with 0