IPhone/iOS development-SQLite database usage

Source: Internet
Author: User
IPhone development-SQLite database usage

I want to use sqlite3.0 to create a database and then create a table in the database.

First, introduce the Lib library of sqlite3.0. Then include the header file # import <sqlite3.h> [1] Open the database. If not, create
Sqlite3 * database _;
-(Bool) Open {
Nsarray * paths = nssearchpathfordirectoriesindomains (nsdocumentdirectory, nsuserdomainmask, yes );
Nsstring * documentsdirectory = [paths objectatindex: 0 ];
Nsstring * Path = [documentsdirectory stringbyappendingpathcomponent: @" Mydb. SQL " ];
Nsfilemanager * filemanager = [nsfilemanager defaultmanager];
Bool find = [filemanager fileexistsatpath: path];
// Find the database file mydb. SQL
If (FIND ){
Nslog ( @" Database file have already existed. " );
If (Sqlite3_open ([path utf8string], & database _)! = Sqlite_ OK ){
Sqlite3_close (Database _);
Nslog (@" Error: Open Database File. " );
Return No;
}
Return Yes;
}
If (Sqlite3_open ([path utf8string], & database _) = sqlite_ OK ){
Bfirstcreate _ = yes;
[Self createchannelstable: Database _]; // Implement the createchannelstable function later.
Return Yes;
} Else {
Sqlite3_close (Database _);
Nslog ( @" Error: Open Database File. " );
Return No;
}
Return No;
}
 


[2] Create a table
 1   // Create a table with five fields (ID, CID, title, imagedata, imagelen)
2 // Note that ID is the primary key of the table, which must exist.
3 // CID, and title are both strings, imagedata is binary data, and imagelen is the length of the binary data.
4
5 -(Bool) createchannelstable :( sqlite3 *) dB
6 {
7 Char * SQL =" Create Table channels (ID integer primary key ,\
8 CID text ,\
9 Title text ,\
10 Imagedata blob ,\
11 Imagelen integer) " ;
12 Sqlite3_stmt * statement;
13 If (Sqlite3_prepare_v2 (dB, SQL ,- 1 , & Statement, nil )! = Sqlite_ OK ){
14 Nslog ( @" Error: failed to prepare statement: Create channels table " );
15 Return No;
16 }
17 Int Success = sqlite3_step (statement );
18 Sqlite3_finalize (statement );
19 If (Success! = Sqlite_done ){
20 Nslog ( @" Error: failed to dehydrate: Create Table Channels " );
21 Return No;
22 }
23 Nslog ( @" Create Table 'channels' successed. " );
24 Return Yes;
25 }
 

 

[3] Insert a record to the table Assume that channle is a data structure that stores the content of a record.
-(Bool) insertonechannel :( channel *) Channel
{
Nsdata * imagedata = uiimagepngrepresentation (Channel. Image _);
Nsinteger imagelen = [imagedata length];
Sqlite3_stmt * statement;
Static Char * SQL = " Insert into channels (CID, title, imagedata, imagelen )\
Values (?,?,?,?) " ;
// The number of question marks must match the number of fields in the fields (CID, title, imagedata, imagelen), representing unknown values. The values and fields are associated below.
Int Success = sqlite3_prepare_v2 (Database _, SQL ,-1 , & Statement, null );
If (Success! = Sqlite_ OK)
{
Nslog ( @" Error: failed to insert: Channels " );
Return No;
}

// The numbers 1, 2, 3, and 4 represent the question mark
Sqlite3_bind_text (statement, 1 , [Channel. ID _ utf8string],-1 , Sqlite_transient );
Sqlite3_bind_text (statement, 2 , [Channel. Title _ utf8string],- 1 , Sqlite_transient );
Sqlite3_bind_blob (statement, 3 , [Imagedata bytes], imagelen, sqlite_transient );
Sqlite3_bind_int (statement, 4 , Imagelen );

Success = sqlite3_step (statement );
Sqlite3_finalize (statement );

If (Success = sqlite_error ){
Nslog ( @" Error: failed to insert into the database with message. " );
Return No;
}

Nslog ( @" Insert one channel ##############: Id = % @ " , Channel. ID _);
Return Yes;
}

 

[4] database query Obtain all the records in the table and put them in the fchannels array.
-(Void ) Getchannels :( nsmutablearray *) fchannels
{
Sqlite3_stmt * Statement = nil;
Char * SQL = " Select * From Channels " ;
If (Sqlite3_prepare_v2 (Database _, SQL ,- 1 , & Statement, null )! = Sqlite_ OK)
{
Nslog ( @" Error: failed to prepare statement with message: Get channels. " );
}
// 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)
{
Char * Cid = ( Char *) Sqlite3_column_text (statement, 1 );
Char * Title = ( Char *) Sqlite3_column_text (statement, 2 );
Byte * imagedata = (byte *) sqlite3_column_blob (statement, 3 );
Int Imagelen = sqlite3_column_int (statement, 4 );
Channel * channel = [[channel alloc] init];
If (CID)
Channel. ID _ = [nsstring stringwithuf8string: Cid];
If (Title)
Channel. Title _ = [nsstring stringwithuf8string: title];
If (Imagedata)
{
Uiimage * image = [uiimage imagewithdata: [nsdata datawithbytes: imagedata length: imagelen];
Channel. Image _ = image;
}
[Fchannels addobject: Channel];
[Channel release];
}
Sqlite3_finalize (statement );
}

 

Related Article

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.