iOS use of SQLite3

Source: Internet
Author: User
Tags sql error

iOS use of SQLite3

First, in Firefox open sqlite3 (if not, select Tools, add-ons, added) new Sqlite3 database, Contacts,

Create a members table, field Id,integer, primary key, self-increment, name,varchar;email,varchar,null;birthday,datetime,null.

Add some data to the table:

Second, create a new empty appliation, add a homeviewcontroller, and a component libsqlite3.dylib, to support the sqlite3 of the connection, closing, adding and deleting changes and other operations.

1. HomeViewController.h Code:

#import <UIKit/UIKit.h> #import "sqlite3.h" @interface homeviewcontroller:uiviewcontroller{sqlite3 *db;//Declare a Sqlite3 Database}-(NSString *) filepath;//the path to the database file. Usually in the sandbox documents inside the Operation @end

2. HOMEVIEWCONTROLLER.M Code:

#import "HomeViewController.h"

@interface Homeviewcontroller () @end @implementation Homeviewcontroller

This method is used to return the full path information of the database in the Documents folder

-(NSString *) filepath{Nsarray *paths = Nssearchpathfordirectoriesindomains (NSDocumentDirectory, NSUserDomainMask, YE    S);    NSString *documentsdir = [Paths objectatindex:0]; return [Documentsdir stringbyappendingpathcomponent:@ "Contacts.sqlite"];}

Ways to open a database

-(void) opendb{     if (Sqlite3_open ([[Self FilePath] utf8string], &db)! = SQLITE_OK) {    &N Bsp   Sqlite3_close (db);        Nsassert (0, @ "Database open failed. ");   }} //Insert Data Method-(void) Insertrecordintotablename: (NSString *) tablename        & nbsp             &NBSP;WITHFIELD1: (NSString *) field1 field1value: (NSString *) field1value                        ANDFIELD2: (NSString *) field2 field2value: (NSS Tring *) field2value                        ANDFIELD3: (nsstring *) field3 Field3value: (NSString *) field3value{/* Method 1: Classic method     NSString *sql = [NSString stringwithformat:@] INSERT into '%@ ' ('%@ ', '%@ ', '%@ ') VALUES ('%@ ', '%@ ', '%@ ') ', TableName, field1, Field2, field3, Field1value, Field2value , field3value];    Char *err;    if (sqlite3_exec (DB,[SQL Utf8string], NULL, NULL, &AMP;ERR)! = SQLITE_OK) {        sqlite3_close (db);        Nsassert (0, @ "Insert data Error! ");   }    * *

Method 2: Binding Methods for variables

NSString *sql = [NSString stringwithformat:@ "INSERT into '%@ ' ('%@ ', '%@ ', '%@ ') VALUES (?,?,?)", TableName, Field1, Fiel        D2, field3];        Sqlite3_stmt *statement; if (SQLITE3_PREPARE_V2 (DB, [SQL Utf8string],-1, &statement, nil) = = Sqlite_ok) {sqlite3_bind_text (statement,        1, [Field1value utf8string], -1,null);        Sqlite3_bind_text (statement, 2, [Field2value utf8string], -1,null);    Sqlite3_bind_text (statement, 3, [Field3value utf8string], -1,null); } if (Sqlite3_step (statement)! = Sqlite_done) {nsassert (0, @ "Insert data failed!        ");    Sqlite3_finalize (statement); }                                 }

 

Query data-(void) getallcontacts{     NSString *sql = @ "SELECT * FROM members";    sqlite3_stmt *stat ement;        if (SQLITE3_PREPARE_V2 (DB, [SQL Utf8string], 1, &statement, nil) = = Sqlite_ok ) {        while (sqlite3_step (statement) = = Sqlite_row) {          &NBSP;&NB sp;            char *name = (char *) sqlite3_column_text (statement, 0);      & nbsp     NSString *namestr = [[NSString alloc] initwithutf8string:name];          &NBSP;&NBS p;            char *email = (char *) sqlite3_column_text (statement, 1);      & nbsp     NSString *emailstr = [[NSString alloc] initwithutf8string:email];          &NBSP;&N bsp;            char *birthday = (char *) sqlite3_column_text (statement, 2);    &NB Sp   &NBsp   NSString *birthdaystr = [[NSString alloc] initwithutf8string:birthday];          &NBSP;&N bsp;            NSString *info = [[NSString alloc] initwithformat:@ "%@-%@-%@",  &NB Sp                           NAMESTR, EMAILSTR, Birthdaystr] ;                        NSLOG (info);                        [nameStr release];            [emailstr release];            [birthdaystr release];        &NBS P   [Info release];       }        sqlite3_finalize (statement);   } } -(void) viewdidload{    [self opendb];       [self insertrecordintotablename:@ "Members"withfield1:@ "Name" field1value:@ "Lee 1" andfield2:@ "email" field2value:@ "[email protected]" andfield3:@ "birthday "field3value:@" 12-45-78 "];        [self insertrecordintotablename:@] members" withfield1:@ " Name "field1value:@" Lee 2 "andfield2:@" email "field2value:@" [email protected] "andfield3:@" Birthday "Field3value: @ "12-45-78"];        [self insertrecordintotablename:@ ' members ' withfield1:@ ' name ' field1value:@ "Lee 3" andfield2:@ "email" field2value:@ "[email protected]" andfield3:@ "Birthday" field3value:@ " 12-45-78 "];                [self getallcontacts];        Sqlite3_close (db);        [Super Viewdidload];}   @end  

Effects after inserting data:

The effect of the query:

Third, Summary:

1. Data query: The SQLITE3_EXEC () function executes the SQL statement, which is useful when there are no return values (such as creating a table, inserting a record, deleting a record, and so on).

The SQLITE3_STAT structure, the SQLITE3_PREPARE_V2 () function, the Sqlte3_step () function, and the sqlite3_finalize () function are also used.

The query is divided into three stages: preparation stage: Sqlite3_stat, SQLITE3_PREPARE_V2 ()

Execution Stage: Sqlte3_step ()

Termination phase: Sqlite3_finalize ()

2. Schedule:

Sqlite3 The return value of the database when it is opened and what it means

return value

Describe

return value

Describe

Sqlite_ok=0

Return success

Sqlite_full=13

Database full, insert failed

Sqlite_error=1

SQL error or wrong database

Sqlite_cantopen=14

Cannot open database file

sqlite_internal=2

SQLite's internal logic error

Sqlite_protocol=15

Database Lockout protocol error

Sqlite_perm=3

Access Denied

Sqlite_empty=16

database table is empty

Sqlite_abort=4

callback function Request Interrupt

Sqlite_schema=17

Database schema Changes

Sqlite_busy=5

Database file is locked

Sqlite_toobig=18

Too many rows for one table data

Sqlite_locked=6

A table in the database is locked

Sqlite_constraint=19

aborted because of a constraint violation

Sqlite_nomen=7

Memory allocation failure

Sqlite_mismatch=20

Data types do not match

Sqlite_readonly=8

Attempt to write to a read-only database

Sqlite_misuse=21

Database error usage

Sqlite_interrupt=9

End operation by Sqlite_interrupt ()

Sqlite_nolfs=22

Using features not supported by the host operating system

sqlite_ioerr=10

Disk I/O error occurred

Sqlite_auth=23

Illegal authorization

sqlite_corrupt=11

Database disk mirroring deformity

Sqlite_format=24

Secondary database format error

Sqlite_notfound=12

(Internal only) table or record does not exist

Sqlite_notadb=26

Not a database file is open

iOS use of SQLite3

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.