Common functions and statements of SQLite

SQLITE3.0 uses the C function interface, which is commonly used as follows:

Sqlite3_open ()//Open Database

Sqlite3_close ()//Close Database

Sqlite3_exec ()//Execute SQL statements, such as creating tables

SQLITE3_PREPARE_V2 ()//Compile SQL statements

Sqlite3_step ()//EXECUTE Query SQL statement

Sqlite3_finalize ()//END SQL statement

Sqlite3_bind_text ()//binding parameters

Sqlite3_column_text ()//Query the data on the field

Create a database table

Sqlite3 *sqlite = nil;

NSString *filepath = [Nshomedirectory () stringbyappendingformat:@ "/documents/%@", @ "Data.sqlite"];

int result = Sqlite3_open ([FilePath utf8string],&sqlite);

if (result = SQLITE_OK) {

NSLog (@ "Failed to open data");

}

To create a table's SQL statement

NSString *sql = @ "CREATE TABLE usertable (userId text not NULL PRIMARY KEY unique,username text, age integer)";

Char *error;

Execute SQL statement

result = Sqlite3_exec (Sqlite,[sql utf8string],null, NULL, &error);

if (Result! = SQLITE_OK) {

NSLog (@ "Failed to create database,%s", Erorr);

}

Sqlite_close (SQLite);

Inserting data

Sqlite3 *sqlite = nil;

sqlite3_stmt = *stmt = nil;

NSString *filepath = [Nshomedirectory () stringbyappendingformat:@ "/documents/%@", @ "Data.sqlite"];

int result = Sqlite3_open ([FilePath utf8string],&sqlite);

if (result = SQLITE_OK) {

NSLog (@ "Failed to open data");

}

NSString *sql = @ "INSERT into usertable (userid,username,age) VALUES (?,?,?)";

SQLITE3_PREPARE_V2 (SQLite, [SQL Utf8string],-1, &stmt, NULL);

NSString *userid = @ "1002";

NSString *username = @ "Zhang San";

int age = 3;

populating data in SQL

Sqlite3_bind_text (stmt, 1, [UserId utf8string], 1 NULL);

Sqite3_bind_text (stmt, 2, [UserName utf8string], -1,null);

Sqite3_bind_int (stmt, 3, age);

result = Sqlite3_step (stmt);

if (result = = Sqlite_erorr | | result = = sqlite_misuse) {

NSLog (@ "failed to execute SQL statement");

return NO;

}

Sqlite3_finalize (stmt);

Sqlite3_close (SQLite);

Querying data

Sqlite3 *sqlite = nil;

sqlite3_stmt = *stmt = nil;

NSString *filepath = [Nshomedirectory () stringbyappendingformat:@ "/documents/%@", @ "Data.sqlite"];

int result = Sqlite3_open ([FilePath utf8string],&sqlite);

if (result = SQLITE_OK) {

NSLog (@ "Failed to open data");

}

NSString *sql = @ "Select UserId, username,age from usertable WHERE age;?";

SQLITE3_PREPARE_V2 (SQLite, [SQL Utf8string], -1,&stmt, NULL);

int age = 1;

Sqlie3_bind_int (stmt, 1,age);

result = Sqlite3_step (stmt);

A list of data after iterating through a query

while (result = = Sqlite_row) {

Char *userid = (char*) sqlite3_column_text (stmt,0);

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

int age = Sqlite3_column_int (stmt,2);

　　

NSString *userid = [NSString stringwithcstring:userid encoding:nsutf8stringencoding];

NSString *username = [NSString stringwithcstring:username encoding:nsutf8stringencoding];

NSLog (@ "-----user name:%@, User id:%@, Age:%d---", username,userid,age);

result = Sqlite3_step (stmt);

}

Sqlite3_finalize (stmt);

Sqlite3_close (SQLite);

Common functions and statements of SQLite