iOS insert/select on db, one questione

[skunkio]

Hi All,
reading my book i'm making some tests to write/read over a db.
So, i thought to create a common class to execute queries (write/read) without write every time the same code to access and manage the "connection". Inside the same class i have also some methods to set/get formats before write or after read to/from a db.
So i created a class with two methods inside, one for insert and one for select and then i created another class with a method inside to execute a statement sql.
For all the operations i have back only a code looks nice (insert, delete, update...) but i have some problems managing query returns rows.
Infact, a select statement will give me different values based on the query i'm executing and which table i'm interrogating.
This is my code:

Class with insert/select methods

- (BOOL) insert
{
	BOOL result;
	Utils_DataBase *dbUtil = [[Utils_DataBase alloc] init];
	
	NSString *insertSQL = [[NSString alloc] initWithFormat:@"INSERT INTO TEMP (CD, DS) VALUES (%i, %@)", cd, [dbUtil setString:name]];
	
	result = [self executeQuery:insertSQL returnValueToCheck:SQLITE_DONE];
	
	[insertEventSQL release];
	[dbUtil dealloc];
	
	return result;
}

- (BOOL) select
{
	BOOL result;
	int endOfFile;
	sqlite3_stmt *sqliteStmt;
		
	Utils_DataBase *dbUtil = [[Utils_DataBase alloc] init];
	
	NSString *selectSQL = [[NSString alloc] initWithFormat:@"SELECT CD, DS FROM TEMP"];
	
	result = [self executeQuery:selectSQL returnValueToCheck:SQLITE_ROW STMT:sqliteStmt];
	
	if (result) {
		while ((endOfFile = sqlite3_step(sqliteStmt)) == SQLITE_ROW) {
			NSLog(@"loop");
		}
	}
	
	[retrieveSavedEvent release];
	[dbUtil dealloc];
	
	return result;
}

- (BOOL) executeQuery:(NSString *) sql returnValueToCheck:(int) returnValue
{
	return [self executeQuery: (NSString *) sql returnValueToCheck:(int) returnValue STMT:(sqlite3_stmt **) nil];
}

- (BOOL) executeQuery:(NSString *) sql returnValueToCheck:(int) returnValue STMT:(sqlite3_stmt **) stmt;
{
	BOOL result;
	Utils_DataBase *dbUtil = [[Utils_DataBase alloc] init];
	
	NSString *connectionString = [[NSString alloc] initWithFormat:@"%@", @"/Developer/_My Code/test.db"];	
	
	result = [dbUtil executeQueryOnDB:connectionString StatementSQL:sql returnValueToCheck:returnValue STMT:stmt];
	
	[connectionString release];
	[dbUtil dealloc];
	
	return result;	
}

and this is the common class i'm using to excute the sql:

- (BOOL) executeQueryOnDB:(NSString *) database StatementSQL:(NSString *) statementSQL returnValueToCheck:(int) returnValue STMT:(sqlite3_stmt **) stmt
{
	sqlite3 *db;
	int dbrc;
	BOOL result = FALSE;
	
	const char *dbName = [database UTF8String];
	dbrc = sqlite3_open(dbName, &db);
	
	if (dbrc) {
		NSLog (@"couldn't open db: %@", database);
		return FALSE;
	}
	
	sqlite3_stmt *dbps;
	
	NSLog(@"%@", statementSQL);
	
	const char *statement = [statementSQL UTF8String];
	
	dbrc = sqlite3_prepare_v2(db, statement, -1, &dbps, NULL);	
	dbrc = sqlite3_step (dbps);
	
	if (dbrc == (int) returnValue) {
		result = TRUE;
		
		if ((int) returnValue == SQLITE_ROW) {
			stmt = dbps;
		}
	} else {
		result = FALSE;
	}
	
	sqlite3_finalize (dbps);
	sqlite3_close(db);
	
	return result;
}

executeQueryOnDB has in input a string with my db path, a statement SQL, which code has been checked check in case of success and a reference to sqlite3_stmt.
In my idea, passing this object as reference i supposed to manage its content outside the common class writing something like that in my select method:

while ((endOfFile = sqlite3_step(sqliteStmt)) == SQLITE_ROW) {
	NSLog(@"loop");
}

Running this cose the insert statement works fine, the select works but not properly as in my idea.
Infact, running the select method the SQL is ok, no erros code, then i set the referenced object to the internal stmt object as per line below:

stmt = dbps;

and inside my select method i receive on the while line:

while ((endOfFile = sqlite3_step(sqliteStmt)) == SQLITE_ROW) {

the error:

Program received signal: “EXC_BAD_ACCESS”.

Based on the error looks like sqliteStmt is not allocated outside the executeQueryOnDB. I tryed also commenting the lines below:

//sqlite3_finalize (dbps);
//sqlite3_close(db);

I don't know about this error and i have also another question. How you manage statements sql in your apps?

Ciao,
stè

 

[PhoneyDeveloper]

I use FMDB. You should too. It already supplies a working Objective-C wrapper for sqlite3 that works on iPhone. There's no point in duplicating it.