iOS in SQLite3 basic operation

iOS about sqlite3 operations

The iphone supports access to the iphone's local database via sqlite3 .

Detailed usage such as the following

1: Join the development package Libsqlite3.0.dylib

The first is to set up the project file. Add the iphone version of the sqlite3 Database Development package to the project. Right-click on the frameworks under project. Then select the libsqlite3.0.dylib file.

Libsqlite3.0.dylib file Address:
/developer/platforms/iphoneos.platform/developer/sdks/iphoneos2.2.sdk/usr/lib/libsqlite3.0.dylib

2, the operation in the code:

So the next step is the code.

1 first get the address of the sqlite3 database file on iphone

Nsarray *paths = Nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask, YES);
NSString *documentsdirectory = [Paths objectatindex:0];
NSString *path = [documentsdirectory stringbyappendingpathcomponent:@ "database_name"];

2 Open the sqlite3 database file on the iphone

Sqlite3 *database;
Sqlite3_open ([path utf8string], &database);

3 Preparing SQL text---SQL statements

Sqlite3_stmt *stmt;
const char *sql = " SELECT * FROM table_name WHERE pk=? and name=?";
SQLITE3_PREPARE_V2 (Database, SQL,-1, &stmt, NULL);

4 State-determined number of references

The first int of the bonding
Sqlite3_bind_int (stmt, 1, 1);
Bonding a second string parameter
Sqlite3_bind_text (stmt, 2, [title Utf8string],-1, sqlite_transient);

5 run the SQL text

Sqlite3_step (stmt);

6 Releasing the SQL text resource

Sqlite3_finalize (stmt);

7 Closethe sqlite3 database on the i Phone

Sqlite3_close (database);


http://hi.baidu.com/clickto/blog/item/0c6904f787c34125720eec87.html

Here's a walkthrough of using SQLite to create a database and then query the content. 2 important structures and 5 main functions:

Sqlite3 *pdb, database handle, very similar to file handle files

sqlite3_stmt *stmt, an ODBC-equivalent command object that holds compiled SQL statements



Sqlite3_open (), open the database

Sqlite3_exec (), run a non-query SQL statement

Sqlite3_prepare (), prepare the SQL statement, run the SELECT statement, or use parameter bind, using this function (encapsulated with sqlite3_exec).

Sqlite3_step (), use this function to move through the recordset after calling Sqlite3_prepare.

Sqlite3_close (), close the database file



Another series of functions. Used to get data from a recordset field, such as

Sqlite3_column_text (), take the text type of data.

Sqlite3_column_blob (), fetching blob-type data

Sqlite3_column_int (), take int type of data

The process of handling SQL requests in a preparedstatement manner
Features: Ability to bind parameters, generate process. It's like ADO when it's running. Returns a row of results each time.

1. First establish the statement object:
int Sqlite3_prepare (
Sqlite3 *db,/* Database handle */
const char *zsql,/* SQL statement, UTF-8 encoded */
int nbytes,/* Length of Zsql in bytes. */
Sqlite3_stmt **ppstmt,/* out:statement handle */
const char **pztail/* out:pointer to unused portion of Zsql */
);

2. The number of references in the binding process (assuming there are no definite parameters)
int sqlite3_bind_xxxx (sqlite3_stmt*, int, ...);
The second int type parameter-Indicates the ordinal of the parameter in SQL (starting from 1).

The third parameter is the value to bind the parameter to.

Additional references for BLOB and text values:
The four-parameter is the length of the string (Unicode 8or16) and does not contain the end ' \ s '.

The fifth number of parameters. The type is void (*) (void*), which represents the function used to clean up the reference string after SQLite processing ends.
An unknown parameter that is not bound will be considered null.

3. Operation Process
int Sqlite3_step (sqlite3_stmt*);
Possible return values:
*sqlite_busy: The database is locked and needs to wait until it succeeds.
*sqlite_done: Successful run process (need to run again to restore Database state)
*sqlite_row: Returns a row of results (using sqlite3_column_xxx (sqlite3_stmt*, int icol) to get the results of each column.
Calling again will return the result of the next row.
*sqlite_error: Execution error, procedure cannot be called again (Error content reference sqlite3_errmsg function return value)
*sqlite_misuse: Incorrect use of this function (usually the procedure is not initialized correctly)

4. Clean up statement objects at the end
int sqlite3_finalize (sqlite3_stmt *pstmt);
Resources that should be consumed during the cleanup process before shutting down the database.

5. Operation of the Reset process
int Sqlite3_reset (sqlite3_stmt *pstmt);
The process will go back to the state that was not running before, and the bound parameters will not change.

Examples:
Create a database

NSString *docsdir;
Nsarray *dirpaths;

Dirpaths = Nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask, YES);
Docsdir = [Dirpaths objectatindex:0];
DatabasePath = [[NSString alloc]initwithstring:[docsdir stringbyappendingpathcomponent:@ "info.db"]];
Nsfilemanager *filemanager = [Nsfilemanager Defaultmanager];
if ([FileManager fileexistsatpath:databasepath]==no) {
const char *dbpath = [DatabasePath utf8string];
if (Sqlite3_open (DBPath, &database) ==SQLITE_OK) {
Char *errmsg;
const char *createsql = "CREATE TABLE IF not EXISTS INFO (ID INTEGER PRIMARY KEY autoincrement,num text,classname Text,nam E TEXT) ";
if (Sqlite3_exec (dataBase, createsql, NULL, NULL, &ERRMSG)!=SQLITE_OK) {
Status.text = @ "CREATE TABLE failed";

}
}
else{
Status.text = @ "Create/open failled";
}
}

Save :
Sqlite3_stmt *statement;

const char *dbpath = [DatabasePath utf8string];

if (Sqlite3_open (DBPath, &database) ==SQLITE_OK) {
if ([Num.text isequaltostring:@ ""]) {
Uialertview *alert = [[Uialertview alloc] initwithtitle:@ "sorry!" message:@ "number cannot be nil!" Delegate:self cancelBu ttontitle:@ "OK" otherbuttontitles:nil];
[Alert show];
}
else {

NSString *insertsql = [NSString stringwithformat:@ "INSERT into INFO (num,classname,name) VALUES (\"%@\ ", \"%@\ ", \"%@\ ")" , Num.text,classname.text,name.text];
const char *insertsatement = [Insertsql utf8string];
SQLITE3_PREPARE_V2 (DataBase, Insertsatement,-1, &statement, NULL);
if (sqlite3_step (statement) ==sqlite_done) {
Status.text = @ "Save to DB.";
Num.text = @ "";
Classname.text = @ "";
Name.text = @ "";
}
else {
Status.text = @ "Save failed!";
}
Sqlite3_finalize (statement);
Sqlite3_close (dataBase);
}
}

clear:

Num.text = @ "";
Classname.text = @ "";
Name.text = @ "";
Status.text = @ "";


Enquiry:
const char *dbpath = [DatabasePath utf8string];

Sqlite3_stmt *statement;

if (Sqlite3_open (DBPath, &database) ==SQLITE_OK) {
NSString *querysql = [NSString stringwithformat:@ "select Classname,name from info where num=\"%@\ "", Num.text];
const char *querystatement = [Querysql utf8string];
if (Sqlite3_prepare_v2 (DataBase, Querystatement,-1, &statement, NULL) ==SQLITE_OK) {
if (sqlite3_step (statement) ==sqlite_row) {
NSString *classnamefield = [[NSString alloc] initwithutf8string: (const char *) Sqlite3_column_text (statement, 0)];
Classname.text = Classnamefield;
NSString *namefield = [[NSString alloc] initwithutf8string: (const char *) Sqlite3_column_text (statement, 1)];
Name.text = NameField;

Status.text = @ "find~~~";
}
else {
Status.text = @ "did not do not find need.";
}
Sqlite3_finalize (statement);
}
Sqlite3_close (dataBase);
}




Other tool functions
1. Total number of rows to get results
int Sqlite3_column_count (sqlite3_stmt *pstmt);
Assuming that the procedure has no return value, such as update, 0 is returned

2. Get the number of data included in the current row
int Sqlite3_data_count (sqlite3_stmt *pstmt);
Assuming Sqlite3_step returns Sqlite_row, the number of columns can be obtained. Otherwise, it is zero.

3.  get data for a column in the data row
Sqlite3_column_xxx (sqlite3_stmt*, int icol);
After Sqlite3_step returns Sqlite_row, use it to get the data for column Icol. The
XXX represents:
Blob: A pointer to the memory that holds the data
bytes, bytes16:  gets the size of the BLOB type data, or the string length of text converted to UTF8/UTF16.
double, int, int64:  Value
text,text16: string pointer
Type: The data type of the column (Sqlite_integer,sqlite_float. Sqlite_text,sqlite_blob,sqlite_null)
Note: Assuming that the column uses a different data-reading method that is appropriate for the column itself, the resulting value will be the converted result.

4.  Gets the type of data for a column in the data row
Int sqlite3_column_type (sqlite3_stmt*, int icol);
return value: Sqlite_integer. Sqlite_float. Sqlite_text,sqlite_blob,sqlite_null
uses a method similar to the SQLITE3_COLUMN_XXX () function.

The//////////////////////////////////////sqlite  of the library file
mac  has many applications that can be used to produce it, with  ui   interface is very convenient.

But suppose you don't want to install another software. The MAC system also has built-in sqlite3 components that can be built by the console. First of all, let's start with a text editing software. It is created in SQL, and is saved as a data.sql.

1begintransaction;2createtable ' Info ' (_id integerprimarykey, ' Name ' text, ' Tel ' text, ' Address ' text); 3INSERTINTO ' info ' VALUES (1, ' Richie ', ' 1234567 ', ' Taizhong '); 4INSERTINTO ' Info ' VALUES (2, ' Eric ', ' 7654321 ', ' Taipei City '); 5INSERTINTO ' Info ' VALUES ( 3, ' Andy ', ' 1234321 ', ' Kaohsiung '); 6COMMIT;

Then, under console, the following instruction is produced to produce DATA.RDB this SQLite file

1sqlite3 Data.rdb < Data.sql

Use the Sqlite repository for IOS
The first to increase the number of resources in the case, and then in the case of increased libsqlite3.0.dylib.

The next step is to write the code, and xxxAppDelegate.h must import sqlite3.h and declare a SQLITE3 structure.

1#import "Sqlite3.h" 2 [email protected]: nsobject<uiapplicationdelegate>4{5 sqlite3* database;6}

The Didfinishlaunchingwithoptions function in XXXAPPDELEGATE.M begins to increase the relative code

1-  (BOOL) Application: (uiapplication*) Application didfinishlaunchingwithoptions: (NSDictionary*) launchoptions2{3    //  Check the existence of the repository, there is no time  copy4    nsstring* Path = [nssearchpathfordirectoriesindomains (Nsdocumentdirectory, nsuserdomainmask, yes)  lastobject];5    nsstring*file = [path stringbyappendingpathcomponent : @ "Data.rdb"];6    if ([[nsfilemanagerdefaultmanager] fileexistsatpath:file] ==  false) 7    {8        nsstring*fromfile  = [[nsbundlemainbundle] pathforresource:@ "Data.rdb" oftype:nil];9         [[nsfilemanagerdefaultmanager] copyitematpath:fromfile topath:file error:nil];10     }11    // open12    if (Sqlite3_open ([File  utf8string], &database) &NBSP;!=&NBSP;SQLITE_OK) 13        nsassert1 (0, @ " failed to open database with message  '%s '. ",  sqlite3_errmsg (Database)); 14   15    self.window.rootViewController = self.viewController;16     [self.window makekeyandvisible];17    returnyes;18}19  20 -  (void) dealloc21{22    sqlite3_close (database); 23    [ SUPERDEALLOC];24}

Simply stated, after adding Data.rdb to the case, the repository will appear in the app suite. But each app has only a proprietary Documents to read and write. Therefore, it is necessary to determine whether the file is present in the Documents catalogue. If it does not exist, then the copy goes through the record and then the Open repository. So why do we have to judge? Why not copy it every time? Because suppose you don't want the repository to be overwritten every time the app version number is updated, you have to make a file presence and no judgment.

Read the information repository
After the successful open repositories. Can begin to read and write.

The method of reading the library is also very simple, just be familiar with the SQL method, should have no problem.

1nsstring*sql = [nsstringstringwithformat:@ "SELECT * from Event"];2sqlite3_stmt *statement;3if (SQLITE3_PREPARE_V2 ( Database, 1,-1, &statement, NULL) = = SQLITE_OK) 4{5 while (sqlite3_step (statement) = = Sqlite_row) 6 {7 NSS Tring*strvalue = [nsstringstringwithutf8string: (char*) sqlite3_column_text (statement, 0)];8 Intintvalue = Sqlite3_co Lumn_int (statement, 1); 9}10}11sqlite3_finalize (statement);

It must be noted that Sqlite3_column_text, Sqlite3_column_int is responsible for obtaining the information, it is necessary to specify which column index.

Execute SQL command
This is the use of SELECT, but assume that you need to do INSERT, DELETE, UPDATE, and so on. It is simple, just need the following instructions.

1char*errmsg;2nsstring*sql = [nsstringstringwithformat:@ "CREATE TABLE ' Info ' (_id INTEGER PRIMARY KEY, ' Name ' TEXT, ' Tel ') Text, ' Address ' text) "];3sqlite3_exec (database, 1, nil, nil, &errmsg);

iOS in SQLite3 basic operation