1. Open the Database
Function
int Sqlite3_open (
const char *filename,//file path for database
Sqlite3 **ppdb//DB instance
);
Example
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 any SQL statement
Function
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
);
Example
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:
① open transaction: BEGIN TRANSACTION;
② ROLLBACK TRANSACTION: rollback;
③ COMMIT TRANSACTION: Commit;
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
Example 1//inserting data
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
① 1th parameter is sqlite3_stmt * type
② the 2nd parameter refers to the position of the placeholder, the position of the first placeholder is 1, not a 0
③ the 3rd parameter refers to the value to which the placeholder is bound
④ the 4th parameter refers to the length of the data passed in the 3rd argument, and for the C string, you can pass 1 instead of the length of the string
⑤ 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
Example 2//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
Sqlite3 data store
