iOS third-party class library Fmdatabase

How to use Fmdatabase

-(nsstring*) GetPath {

nsarray* Paths =nssearchpathfordirectoriesindomains (Nsdocumentdirectory,nsuserdomainmask, YES);

return [[pathsobjectatindex:0]stringbyappendingpathcomponent:@ "MyTable"];

}

1. Create a database

-(void) createtable;

{

DataBase = [Fmdatabasedatabasewithpath:[selfgetpath]];

if (![ DataBase Open])

NSLog (@ "OPEN FAIL");

}

[databaseexecuteupdate:@ "CREATE TABLE IF not EXISTS MyTable (aa float,bb text,cc integer,dd integer,ee text)"];

[Databaseclose];

}

2. Querying data

-(void) Querydata

{

Get Data

Recordarray = [[Nsmutablearrayalloc]init];

db = [[Fmdatabasealloc]initwithpath:[selfgetpath]];

if ([db Open]) {

Fmresultset *rs = [dbexecutequery:@ "SELECT * from MyTable"];

while ([Rs next]) {

Onerecord = [[Onerecord alloc]init];

ONERECORD.AA = [Nsnumbernumberwithfloat:[rs doubleforcolumn:@ "AA"];

ONERECORD.BB = [rs stringforcolumn:@ "BB"];

onerecord.cc = [Nsnumbernumberwithint:[rs intforcolumn:@ "CC"];

ONERECORD.DD = [Nsnumbernumberwithint:[rs intforcolumn:@ "DD"];

onerecord.ee = [rs stringforcolumn:@ "ee"];

[Recordarrayaddobject:onerecord];

[Onerecordrelease];

}

[Rs Close];

[Dbclose];

}

3. Updating data

-(void) UpdateData

{

if ([dbopen]) {

[Dbbegintransaction];

[dbexecuteupdate:@] UPDATE MyTable SET AA =? WHERE date =? ", Aa1,aa2];

[dbexecuteupdate:@] UPDATE MyTable SET bb =? WHERE date =? ", Bb1,bb2];

[dbexecuteupdate:@] UPDATE MyTable SET cc =? WHERE date =? ", CC1,CC2];

[dbexecuteupdate:@] UPDATE MyTable SET dd =? WHERE date =? ", DD1,DD2];

[Dbcommit];

DB Close];

}

}

4. Inserting data

-(void) InsertData

{

Insert Database

[Dbbegintransaction];

[dbexecuteupdate:@ INSERT into MyTable (aa,bb,cc,dd,ee) VALUES (?,?,?,?,?) ",

NSNumber Numberwithfloat:aa],bb,cc,dd,ee];

DB commit];

DB Close];

}

5. The official use method is:

Usage

There is three main classes in FMDB:

1. Fmdatabase-represents a single SQLite database. Used for executing SQL statements.
2. Fmresultset-represents the results of executing a query on anfmdatabase.
3. Fmdatabasequeue-if You "re wanting to perform queries and updates the multiple threads, you'll want to the use of this class. It ' s described in the "Thread Safety" section below.

Database Creation

An fmdatabase are created with a path to a SQLite database file. This path can be one of these three:

1. A file system path. The file does not has to exist on disk. If It does not exist, it's created for you.
2. An empty string (@ ""). An empty database was created at a temporary location. This database was deleted with Thefmdatabase connection is closed.
3. Null. An In-memory database is created. This database would be destroyed with Thefmdatabase connection is closed.

(For more information on temporary and in-memory databases, read the SQLite documentation on the Subject:http://www.sqlite . org/inmemorydb.html)

Fmdatabase *db = [fmdatabase databasewithpath:@ "/tmp/tmp.db"];

Opening

Before you can interact with the database, it must is opened. Opening fails if there is insufficient resources or permissions to open and/or create the database.

if (![ DB Open]) {

[DB release];

Return

}

Executing Updates

Any sort of SQL statement which are not a SELECT statement qualifies as an update. This includes create,pragma,update, INSERT, Alter,commit, BEGIN, Detach,delete, DROP, End,explain, VACUUM, and REPLACE STA Tements (plus many more). Basically, if your SQL statement does not begin Withselect, it's an UPDATE statement.

Executing updates returns a single value, a BOOL. A return value of YES means the update was successfully executed, and a return value of NO means this some error was Encou Ntered. If you use the-[fmdatabase executeUpdate:error:withArgumentsInArray:orVAList:] method to execute a update, you may suppl Y annserror * * That'll be filled in if execution fails. Otherwise The-lasterrormessage And-lasterrorcode Methods to retrieve more information.

Executing Queries

A SELECT statement is a query and is executed via one of the-executequery ... methods.

Executing queries returns an Fmresultset object if successful, and nil upon failure. Like executing updates, there are a VARIANT that accepts ANNSERROR * * parameter. Otherwise should use The-lasterrormessage And-lasterrorcode methods to determine why a query failed.

In order to iterate through the results of your query, you use a while () loop. You also need to ' step ' from one record to the other. With FMDB, the easiest.

Fmresultset *s = [db executequery:@ "select * from myTable"];

while ([s next]) {

Retrieve values for each record

}

You must always invoke-[fmresultset next] before attempting to access the values returned in a query, even if your ' re only Expecting one:

Fmresultset *s = [db executequery:@ "select COUNT (*) from myTable"];

if ([s next]) {

int totalcount = [s intforcolumnindex:0];

}

Fmresultset have many methods to retrieve data in an appropriate format:

• Intforcolumn:
• Longforcolumn:
• Longlongintforcolumn:
• Boolforcolumn:
• Doubleforcolumn:
• Stringforcolumn:
• Dateforcolumn:
• Dataforcolumn:
• Datanocopyforcolumn:
• Utf8stringforcolumnindex:
• Objectforcolumn:

Each of these methods also have a {type}forcolumnindex:variant that's used to retrieve the data based on the position of The column in the results, as opposed to the column ' s name.

Typically, there ' s no need to-close an fmresultset yourself, since that happens when either the result set is deallocated , or the parent database is closed.

Closing

When you are finished executing queries and updates on the database, you should-close the fmdatabaseconnection so that S Qlite would relinquish any resources it had acquired during the course of its operation.

[DB close];

Transactions

Fmdatabase can begin and commit a transaction by invoking one of the appropriate methods or executing a begin/end transact Ion statement.

Data sanitization

When providing a SQL statement to FMDB, you should not attempt to "sanitize" any values before insertion. Instead, you should with the standard SQLite binding syntax:

INSERT into MyTable VALUES (?,?,?)

The? Character is recognized by SQLite as a placeholder for a value to be inserted. The execution methods all accept a variable number of arguments (or a representation of those arguments, such as Annsarray , Nsdictionary, or ava_list), which is properly escaped for you.

Alternatively, named parameters Syntax:

INSERT into MyTable VALUES (: ID,: Name,: Value)

The parameters must start with a colon. SQLite itself supports other characters, but internally the Dictionary keys is prefixed with a colon, does not incl Ude the colon in your dictionary keys.

Nsdictionary *argsdict = [nsdictionary dictionarywithobjectsandkeys:@ "My name", @ "name", Nil];

[DB executeupdate:@ INSERT into myTable (name) VALUES (: Name) "Withparameterdictionary:argsdict";

Thus, you should does this (or anything):

[DB executeupdate:[nsstring stringwithformat:@ "INSERT into myTable VALUES (%@)", @ "this has \" lots of ' bizarre \ "quotes ‘"]];

Instead, should do:

[DB executeupdate:@ "INSERT into MyTable VALUES (?)", @ "this have \" lots of ' bizarre \ "Quotes '"];

All arguments provided to The-executeupdate:method (or any of the variants so accept a va_list as a parameter) must be Objects. The following won't work (and would result in a crash):

[DB executeupdate:@ INSERT into MyTable VALUES (?) ", 42];

The proper-to-insert a number is-to-box it in an NSNumber object:

[DB executeupdate:@ INSERT into MyTable VALUES (?) ", [NSNumber numberwithint:42]];

Alternatively, you can use The-execute*withformat:variant to use nsstring-style substitution:

[DB executeupdatewithformat:@ INSERT into myTable VALUES (%d) ", 42];

Internally, The-execute*withformat:methods is properly boxing things for you. The following percent modifiers is recognized:%@,%c,%s,%d,%d,%i,%u,%u,%hi,%hu,%qi,%qu,%f,%g,%ld,%lu,%lld, and %llu. Using a modifier other than those would have unpredictable results. If, for some reason, you need the% character to appear in your SQL statement, you should use%%.

Using Fmdatabasequeue and Thread Safety.

Using a single instance of Fmdatabase from multiple threads at once are a bad idea. It has always been OK and make a Fmdatabase objectper thread. Just don ' t share a single instance across threads, and definitely not across multiple threads at the same time. Bad things'll eventually happen and you'll eventually get something to crash, or maybe get an exception, or maybe Meteor Ites'll fall out of the sky and hits your Mac Pro. This would suck.

So don ' t instantiate a single Fmdatabase object with use it across multiple threads.

Instead, use Fmdatabasequeue. It ' s your friend and it's here to help. Here's how to use it:

First, make your queue.

Fmdatabasequeue *queue = [Fmdatabasequeue Databasequeuewithpath:apath];

Then use it like so:

[Queue indatabase:^ (Fmdatabase *db) {

[DB executeupdate:@ INSERT into MyTable VALUES (?) ", [NSNumber numberwithint:1]];

[DB executeupdate:@ INSERT into MyTable VALUES (?) ", [NSNumber Numberwithint:2]];

[DB executeupdate:@ INSERT into MyTable VALUES (?) ", [NSNumber Numberwithint:3]];

Fmresultset *rs = [db executequery:@ "select * from foo"];

while ([Rs next]) {

...

}

}];

An easy-to-wrap things up in a transaction can is do like this:

[Queue intransaction:^ (Fmdatabase *db, BOOL *rollback) {

[DB executeupdate:@ INSERT into MyTable VALUES (?) ", [NSNumber numberwithint:1]];

[DB executeupdate:@ INSERT into MyTable VALUES (?) ", [NSNumber Numberwithint:2]];

[DB executeupdate:@ INSERT into MyTable VALUES (?) ", [NSNumber Numberwithint:3]];

if (whoopssomethingwronghappened) {

*rollback = YES;

Return

}

etc...

[DB executeupdate:@ INSERT into MyTable VALUES (?) ", [NSNumber Numberwithint:4]];

}];

Fmdatabasequeue would make a serialized GCD queue with the background and execute the blocks you pass to the GCD queue. This means if your fmdatabasequeue's methods from multiple threads at the same time GDC would execute them in the Order they is received. This means queries and updates won ' t-step on each other's toes, and every one is happy.