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
iOS data Persistence-sqlite common functions