Primary use of SQLite database

Use steps

1. Introduce the header file: Add the frameworks and libraries that will be used under the project Build phases. I've added the sqlite3 I'm going to use in the project.

2. Specify the database storage path: The database is generally stored in the sandbox root directory under the Documents folder, in the specified path, the name of the database to be written

NSString *sandbox =*filepath = [SandBox stringbyappendingpathcomponent:@ "documents/db_ Student.sqlite"];  // the database name created is Db_student.sqlite

3. Open the Database

3.1 First string to convert OC's path string to C

Const char * path = [FilePath utf8string];

3.2 Opening a database

sqlite3_open (path, &_db);

What you need to know is that this code has two functions:

1) If the database does not exist, create the database and open it;

2) If the database already exists, open the database directly.

int Sqlite3_open (  constchar *filename,/   ** /   **ppdb          */* *);

This is the function declaration of this code: from which you can see

The first parameter requires the file name of the database and is a UTF-8-converted C string;

The second parameter requires a double pointer of type sqlite3, so passing in is an address pointer;

Last point: If the database is successfully opened, a value is returned SQLITE_OK

#define SQLITE_OK 0/* Successful result * /

4. Create a table

4.1 Creating the SQL statement for the table: note that it is a C-language string instead of a string of OC type

Const char *sql = "CREATE table if not exists tb_student (ID integer primary key,sex bo Olean,name text)";

4.2 Execute DDL statement, call the EXEC function only if it is a DDL statement

 int  sqlite3_exec (sqlite3*, const  Span style= "color: #0000ff;" >char  *sql, void  *,int , char  * *, char  * *), void  *, char  **errmsg);  /*   first parameter: Database variable, one already open I database the second parameter: The statement that creates the table the third argument: the callback function, which is the function that the statement executes to execute, usually fills in the nil fourth parameter: the first of the callback function parameters, usually nil fifth parameter: Error message, you can fill nil, note is a double pointer  */  //  So the statement that creates the table can be written like this  This code also has a return value, which returns SQLITE_OK indicates that the table was created successfully. 

5. The operation of adding, deleting, changing and checking the data sheet

5.1 SQL statements with placeholders

Add insert into tb_student (name, Sex) VALUES (?,?);

1) Please do not be in the statement? It's scary, here. Its role is the placeholder, which means that a value is required here, and this value is not given but an indeterminate value, for example, can be obtained from a text box.

Of course, if you use this statement to manipulate the data table, it is absolutely impossible, why ya, people do not know you bai, perhaps you said, then I directly write a definite value, so always can! The answer is still no, this I also feel very pit where ([email protected][email protected]=)

So what do we do--convert SQL statements into SQLITE3_STMT types (now that you're a family, you can't still see me)

sqlite3_stmt * stmt =-1// Do not underestimate these two lines of code: Convert the string type of SQL statements to the stmt type; only after conversion can you put the question mark (? is replaced with the corresponding data, note that regardless of whether there is a question mark in the SQL statement, it is converted to this stmt  type

The following C function is the prototype of the second sentence of the code above.

/*     First parameter: Database variable    second parameter: SQL statement    The third argument: the length of the string, 1 is the length of the system calculation, and the    fourth parameter: A pointer to the storage location after the statement is converted;    The fifth parameter: the location of the data beyond the specified length; normally filled with null;*/int sqlite3_prepare_v2 (sqlite3 *db,constChar  intconstchar **pztail);

2) The next total is the statement inside? Replaced: This will depend on your field type to decide which method to use, and if it is a string type (that is, the text type), use the function

/*   First parameter: stmt structure  The second argument: which question mark to replace; starting from 1;  third parameter: Replace the contents of the  fourth parameter: the length of the string, 1 means the system calculates the length of the  fifth parameter: callback function Usually write null*/intintconstcharintvoid(* ) (void*))

Has a return value of SQLITE_OK, indicating a successful conversion

When the call is called

Sqlite3_bind_text (stmt, 1, Name,-1, NULL); // name is what you get through a text box

It's much simpler if it's an int type or a Boolean type.

Sqlite3_bind_int (stmt, 2, sex); // sex is what you get with the same text box

3) Executive Sqlite3_step (stmt)

Returns Sqlite_done when execution succeeds, so you can determine whether the statement executed successfully by returning a value

4) do not forget to destroy the stmt when the inserted action is completed

sqlite3_finalize (stmt);

5) Close the database

When the insert is complete and no further action is made, close the database

Sqlite3_close (_db);

5.2 Query statements without placeholders

Convert the SQLL statement to the stmt type even if there are no placeholders in the SQL statement

5.3 Query Statements

There are two different places in a query statement

1) The return value of the query succeeds differently Sqlite_row

2) The mechanism of the operation is also different: before all the data in the table is traversed, the query operation is performed, and each query to a qualifying record returns a Sqlite_row, so you can use this feature to extract the eligible data from the table.

 while(Sqlite3_step (stmt) = =Sqlite_row) {     /*first parameter: stmt the second parameter: the value of which column to take; starting from 0*/     intID = Sqlite3_column_int (stmt,0); intSex = Sqlite3_column_int (stmt,2); ConstUnsignedChar*cname = Sqlite3_column_text (stmt,1); NSString*name =Nil; if(CName! =NULL) {Name= [NSString stringwithutf8string: (Const Char*) CName]; } NSLog (@"_____id:%d ____name:%@ _____ Sex:%d", ID, name, sex);}

Initial use of the SQLite database