Creating Data Access Component using sqlite3

[LordMooch]

I am trying to create a class that I can use as a generic Data Access Component (DAC), with sqlite3 as the storage mechanism. I am doing this so as to centralise the data access in my app(s). Before starting the DAC I had successfully managed to get data into and out of a database using sqlite3. However, I am having problems getting the same functionality to work within a central class.

For example, I have a Mood class that stores 5 integers relating to a person's mood for a given date. I have created a Mood class to hold this information. Within the Mood class I have - amongst others - two methods; load and save. 'load' retrieves data for a given date, while save writes the data to the database.

In my original - pre-DAC - code I had calls to sqlite methods within the load method of my Mood class. Now I am trying to put that code into a generic 'runSQLNoRecords' method on my DAC class, i.e. all my future classes can then use the DAC rather than me having to re-write sqlite statements over and over.

In order to do this, I figured my DAC class would need to have two global variables (not sure if that's the right terminology here); namely the database and the statement. By being Globals I could repeatedly access the records within the statement and therefore be able to traverse a recordset.

The DAC def looks as follows:

@interface DAC : NSObject
{
sqlite3_stmt *statement;
sqlite3 *database;
}

// Init the database
- (void) initDatabase;

// Open the database
- (void) openDatabase;

// Close the database
- (void) closeDatabase;

// Run a query that DOES NOT return records
- (void) runSQLNoRecords: (NSString*) theSQL;

// Run a query that DOES return records
- (BOOL) runSQLReturnRecords: (NSString*) theSQL;

// Step through to next record, return TRUE if ok, FALSE otherwise
- (BOOL) isNextRecord;

// Return an Int from the recordset
- (int) returnInt: (int) column;

// Return a String from the recordset
- (NSString*) returnString: (int) column;


@end


The definition for the problematic method is:

// Run a Query that DOES return records
- (BOOL) runSQLReturnRecords: (NSString*) theSQL
{

// Check if the DB is available
if (sqlite3_open([[self dbFilePath] UTF8String], &database) != SQLITE_OK)
{
NSAssert(0, @"Failed to open db.");
sqlite3_close(database);
return FALSE;
}

// Get the row
if (sqlite3_prepare_v2(database, [theSQL UTF8String], -1, &statement, nil) != SQLITE_OK)
{
return FALSE;
}

// Must be ok if we get this far
return TRUE;
}


My logic in the above is to open the database and the statement and, in theory, that statement should then be available to future calls to methods on my DAC, e.g. returnInt, returnString etc - up until the point I release the DAC from memory.

The problem is that the above method always fails on the prepare statement with an error code of SQLITE_ERROR. According to the docs that's either a SQL error - I'm using the exact same SQL as what previously worked when running code outside of the DAC class - or a database not open error. As I am opening the database successfully prior to the prepare I'm not sure that's an issue either.

I am new to this development environment - C, Objective-C, XCode - and as such am having a hard time learning how to debug etc with this tech. I'd really appreciate any help with this problem.

EDIT: Code added to show how my Mood class calls the DAC

Below is the load method from my Mood class. Note, this method used to contain all the relevant sqlite commands, e.g. opening the database, preparing the statement, stepping the statement etc and that code worked correctly for me.

// Load the object with data from the Date passed in
- (void) load: (NSDate*) theDate
{

// Get today's date in the format it will be stored in the database
NSString* date;
date = GETS DATE FROM DATE PICKER;

NSString *select = [[NSString alloc] initWithFormat:@"Select CalendarDate, Work, Mood, Home, Exercise, Food From Mood Where CalendarDate = '%@'", date];

// Get a DAC object
DAC *myDAC = [DAC alloc];

// Open the database
[myDAC openDatabase];

// Open the Recordset based on 'select'
// This will stay open until we close the Database later
[myDAC runSQLReturnRecords:(select)];

// NOTE TO READERS: The above call ALWAYS fails


/*
CODE HERE TO READ IN THE DATA, BUT CODE NEVER GETS THIS FAR
CODE REMOVED FOR CLARITY
*/

}