IOS sqlite basics and iossqlite Basics

IOS sqlite basics and iossqlite Basics

1. Import the libsqlite3.0.dylib Library

File: # import "sqlite3.h"

2. Create a database

# Define kDocDir [NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex: 0]

# Define dbPath [kDocDir stringByAppendingPathComponent: @ "test. db"]

Sqlite3 * db;

If (sqlite3_open ([dbPath UTF8String], & db )! = SQLITE_ OK ){

Sqlite3_close (db );

NSAssert (0, @ "failed to open the database. ");

Return;

}

3. Create Table 1. Three different types of field IDs are integer, name is string, and image is binary.

Char * sqlStr = "create table table1 (id integer, name text, image blob )";

Sqlite3_stmt * statement;

If (sqlite3_prepare_v2 (db, sqlStr,-1, & statement, nil )! = SQLITE_ OK ){

NSLog (@ "Error: failed to prepare statement: create table1 ");

Return;

}

Int success = sqlite3_step (statement );

Sqlite3_finalize (statement );

If (success! = SQLITE_DONE ){

NSLog (@ "Error: failed to dehydrate: CREATE TABLEtable1 ");

Return;

}

4. insert data. Note that the number of question marks must match the parameter.

Int idvalue;

NSString * namevalue ";

NSData * image;

SqlStr = "insert into table1 VALUES (?,?,?) ";

Int success = sqlite3_prepare_v2 (db, sqlStr,-1, & statement, NULL );

If (success! = SQLITE_ OK ){

NSLog (@ "Error: failed to insert into table1 ");

}

Sqlite3_bind_int (statement, 1, [idvalue integerValue]); // The second parameter starts from 1, which is different from the subsequent data query.

Sqlite3_bind_text (statement, 2, [namevalue UTF8String],-1, SQLITE_TRANSIENT );

Sqlite3_bind_blob (statement, 3, [image bytes], (int) [image length], SQLITE_TRANSIENT );

Success = sqlite3_step (statement );

Sqlite3_finalize (statement );

If (success = SQLITE_ERROR ){

NSLog (@ "Error: failed to insert into the database with message .");

Return;

}

5. query data

SqlStr = "SELECT * FROM table1 ";

If (sqlite3_prepare_v2 (db, sqlStr,-1, & statement, NULL )! = SQLITE_ OK ){

NSLog (@ "Error: failed to prepare statement with message: gettable1 .");

Return;

}

// In the query result set, all records are traversed one by one. The number corresponds to the column value.

While (sqlite3_step (statement) = SQLITE_ROW ){

Int tempint = sqlite3_column_int (statement, 0); // the sequence number starts from 0. Note the difference between the sequence number and the inserted sequence number.

Char * temp = (char *) sqlite3_column_text (statement, 1 );

NSString * tempstr = temp? [NSString stringwithuf8string: temp]: @ "";

Int length = sqlite3_column_bytes (statement, 2); // obtain the length of binary data

Byte * bytes = (Byte *) sqlite3_column_blob (statement, 2 );

NSData * data = [NSData dataWithBytes: byteslength: length];

}

 

6. Shut down the database.

Sqlite3_close (db );