| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • You already know Dokkio is an AI-powered assistant to organize & manage your digital files & messages. Very soon, Dokkio will support Outlook as well as One Drive. Check it out today!

View
 

Using the Objective-C Version of the SQLite Wrapper

Page history last edited by Lee Barney 13 years, 11 months ago

This tutorial is designed for those who wish to use the OC version of the SQLite wrapper found inside the framework but do not want to use the framework itself.  If you are using the full framework to create your JavaScript based application you do not need to understand this information.

 

 

The wrapper described here is available under the MIT license and exists inside the QCLib sub-project of the QuickConnectiPhone project.

 

In order to use the Objective-C version of the SQLite wrapper the application developer must include a .sqlite database file in the Resources group of their iPhone/Pad/touch application.  The developer must also include the source code for the two classes that makeup the wrapper.

 

This tutorial consists of three main portions in this order:

 

  1. The design of the wrapper tool set
  2. A discussion of the API for the wrapper tool set
  3. And examples using the API of the wrapper tool set.

 

 

The Design

The Objective-C version of the SQLite wrapper consists of two classes:

 

  1. SQLiteDataAccess - the primary interaction class used to modify the database and query it for information
  2. DataAccessResult - a container for the result of the database query or modification.

 

The design requirements for the SQLite Wrapper are that the wrapper must

 

  1. be very simple to use from the developers prospective. 
  2. require no knowledge of the SQLite c library 
  3. require no knowledge of how to interact with an SQLite database beyond a basic knowledge of SQL
  4. handle any SQL statement supported by SQLite
  5. handle prepared statements without any knowledge of what prepared statements are or how they are implemented in SQLite
  6. allow and fully support database transactions, rollbacks, etc. with minimal understanding by the developer.

 

The API

 

The SQLiteDataAccess Class

 

The SQLiteDataAccess class is the class you use to interact with a SQLite database and consists of seven public methods.

 

  1. - (SQLiteDataAccess*)initWithDatabase:(NSString*) dbName isWriteable:(BOOL) isWriteable;
    • The 'constructor' for the SQLiteDataAccess class.  Use this to start interacting with an SQLite database.  If no database file exists with the value of dbName nil is returned.  Parameters:
      1. dbName - a NSString that is the name of the SQLite file containing the database.  This string must end in .sqlite.  This string is the name of the file only not any path information.
      2. isWriteable - a Boolean flag indicating if you desire to modify the database structure or stored data.  This is generally pass a YES value. 
  2. - (DataAccessResult*)getData:(NSString*)SQL withParameters:(NSArray*)parameters;
    • This is the method used to query the database for information.  A query can be a prepared or a standard statement.  Parameters:
      1. SQL -  a NSString that is the SQL to be executed against the database.  This is usually some sort of SELECT statement.  It can also be a prepared statement type in which the WHERE values are replaced with the ? character place holder.
      2. parameters -  a NSArray of values to be bound to, associated with, the ? characters of a prepared statement SQL string or nil if the SQL is a standard statement.
  3. - (DataAccessResult*)setData:(NSString*)SQL withParameters:(NSArray*)parameters;
    • This is the method used to modify the database structure or the data in the database.  A modification can be a prepared or a standard statement.  Parameters: 
      1.  SQL -  a NSString that is the SQL to be executed against the database.  This is usually some sort of INSERT or CREATE statement.  It can also be a prepared statement type in which the SQL VALUES to be inserted are replaced with the ? character place holder.
      2. parameters -  a NSArray of values to be bound to, associated with, the ? characters of a prepared statement SQL string or nil if the SQL is a standard statement.
  4. - (DataAccessResult*)startTransaction;
    • This method is called prior to using a series of getData/setData calls to insure database integrity. 
  5. - (DataAccessResult*)endTransaction;
    • This method is called after all of the getData/setData calls made after starting a transaction complete successfully.  This causes the changes made to the database to be committed.
  6. (DataAccessResult*)rollback;
    • This method is called after a transaction has been started and one or more of the getData/setData calls has failed.  This undoes any changes that have been made during the transaction.
  7. - (void)close;
    • This method closes the connection to the database after the application is done using it.  This method is usually called only when the application is exiting.

 

The DataAccessResult Class

 

The DataAccessResult class is a data accumulator.  It consists only of information regarding a single getData or setData call.  As such it has no methods other than accessors for attributes.  Each of the six attribute of this class contains specific information regarding the database request made.

  1. NSArray *fieldNames; - every query in SQL requests specific fields.  This is an NSArray of NSStrings that are the names of the fields contained in the result of the getData call or nil if a setData call was made.
  2. NSArray *columnTypes; - the Objective-C type, NSString, NSNumber, etc., of each of the fields that are the result of a getData call or nil if a setData call was made.
  3. NSArray *results; - a two dimentional array, table, of the data where each row of the array is a requested record and each element of a row is value due to a getData call or nil if a setData call was made.  The order of the data in each row matches the order of the fieldNames and columnTypes attributes.
  4. NSString *errorDescription; - any database error generated by a getData or setData call or nil if no error occurred.
  5. NSInteger rowsAffected; - the number of rows in tables modified by a setData call or nil if a getData call was made.
  6. NSInteger insertedID; - the auto-generated ID value if a setData call was made to a table that uses auto-generated ids or nil.

 

 

 

The Examples 

 

Usually an instance of the SQLiteDataAccess class is stored as an attribute of a widely visible class so that it can be reused many times after it has been instantiated.  This is generally done at application startup.  In this example, for the sake of clarity of the calls, it will be created as a local variable in some method.  Creating a connection to an SQLite database is computationally intensive.  Do not create a new SQLiteDataAccess object each time you need to use one.

 

Creating an SQLiteDataAccess Instance

the following line of code demonstrates how to create a connection to an SQLite database file included in the resources of an application.  The name of the database file is demo.sqlite.  Since we will be modifying the database, either its structure or the data it contains, the isWriteable parameter YES is also passed.  If you wanted to only read from a preformed database you would pass NO.

 

NameSQLiteDataAccess *theDatabase = [[SQLiteDataAccess alloc]

                                                                      initWithDatabase:@"demo.sqlite" isWriteable:YES];

 

The .sqlite file can be a preformed database, one with existing tables, or it can have no tables.  If you want to add a table to a database you make a setData call similar to the one below.  Here we are adding a simple contact table consisting of:

 

  • a unique auto incrementing primary key as an integer
  • a name as a string
  • an email as a string
  • and a company name as a string.

 

Usually you would create a table as part of a transaction.  For the sake of clarity transaction handling is covered later. 

 

 

 

Using the setData Method 

 

DataAccessResult* syncTempCreateResult = [theDatabase setData:@"CREATE TABLE IF NOT EXISTS contact 

                                                                           (id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL

                                                                           , name VARCHAR

                                                                           , email VARCHAR

                                                                           , company VARCHAR)"

                                                       withParameters:nil];

 

Since the SQL used is a standard statement, a prepared statement example can be found below, no parameters are needed and so a nil is passed as the second parameter.

 

To add data to this table the setData method is used again and the result of our request is tracked.  Since we will be adding more than one record all of the setData calls will be executed within a transaction.  

 

Also, since more than one contact is to be inserted a prepared statement SQL string is created and reused.  The first set of values to be inserted are:

name - bob

email - bob@nowhere.com

company - Nowhere, Inc.

 

 

//put together the SQL string

NSString insertContactSQL = @"INSERT INTO contact ('name', 'email', 'company') VALUES(?, ?, ?)"

[theDatabase startTransaction];

//place the values to be inserted into an NSArray

NSArray *params = [NSArray arrayWithObjects:@"bob", @"bob@nowhere.com", @"Nowhere, Inc."];

//call setData

DataAccessResult* theResult = [theDatabase setData:insertContactSQL parameters:params];

if(theResult.errorMessage != nil){//some error must have happened

     [theDatabase rollback];

     [theResult release];

     return;//return early so no more database calls are made.

}

//reuse the params array for the next set of data

params = [NSArray arrayWithObjects:@"sue", @"sue@BigCompany.com", @"BigCompany, Inc."];

//make the second call to setData

DataAccessResult* anotherResult = [theDatabase setData:insertContactSQL parameters:params];

if(theResult.errorMessage != nil){//some error must have happened

     [theDatabase rollback];

     [anotherResult release];

     return;//return early so no more database calls are made.

}

 

//end the transaction and commit the changes

[theDatabase endTransaction];

 

//use the results as you desire.  The auto-incremented ids for each insert are found in each result.

[theResult release];

[anotherResult release];

 

 

Using the getData Method

 

Calling getData is very similar to calling setData.  First an example of a standard SQL statement will be shown.  Parameters are not used for standard SQL statements and so nil is passed.

 

DataAccessResult *allContactsResult = [theDatabase getData:@"SELECT * FROM contacts" parameters:nil];

 

In the next example a prepared statement is used since the value of the WHERE restriction may have been retrieved from a server, the user, or some other unsecured location.  The NSString variable contactName would be created in some other portion of the code.

 

//create a parameters array

params = [NSArray arrayWithObject:contactName];

//call getData

DataAccessResult *someContactsResult = [theDatabase getData:@"SELECT * FROM contacts WHERE name = ?" parameters:params];

 

Now that results for the query exist use them to print out information or display it to the user in some custom format.  This example simply prints out the results.

 

//check for an error

if(someContactsResult.errorMessage != nil){

     NSLog(@"ERROR: %@", someContactsResult.errorMessage);

     return;

}

 

//print out the results data from the query

NSLog(@"someContacts results %@",someContactsResult.results);

 

 

 

 

 

 

Comments (0)

You don't have permission to comment on this page.