de

I use a special SQL class which takes all the heavy lifting out of programming a database in my iPhone and iPad applications. The library is called sqlite.h/m by Matteo Bertozzi. Its great, because a query can be executed in the following manner:

[sqlite executeQuery:@"SELECT * FROM test;"];

Here is a list of some of my commonly used SQL queries. (This will be updated over time)

//Select All rows from database ‘test’ where key=7. Limiting to 2 results
query2 = [NSString stringWithFormat:@"select * FROM test where key='%@' LIMIT 2",[tempA objectForKey:@"key"]];

//Search for Duplicates in database test where key is reused
select key, count (key) as NumOccurrences FROM test
GROUP by key
HAVING ( COUNT(key)>1)

//Update database ‘test’ setting key to a string where ClientProject = a string
query4 = [NSString stringWithFormat:@"UPDATE test set key= %d where ClientProject='%@'",generated,results4];

//Drop Table
[sqlite executeNonQuery:@"DROP TABLE test"];
//Create Table
[sqlite executeNonQuery:@"CREATE TABLE test (key TEXT NOT NULL, num INTEGER, value TEXT);"];
//Insert row into database
[sqlite executeNonQuery:@"INSERT INTO test VALUES (?, ?, ?);", @"hey", [NSNumber numberWithInt:42], @”"];