iOS Problem with sqlite3

[teamstar]

Hi all,
I'm trying to use sqlite3 for db implementation but was confronted with 'SIGABRT' during runtime.

Database1ViewController.h:

#import <UIKit/UIKit.h>
#import "sqlite3.h"

@interface Database1ViewController : UIViewController
{
    sqlite3 *db;
}

-(NSString *)filepath;
-(void) openDB;
-(void) createTableNamed:(NSString *)tableName
              withField1:(NSString *)field1
              withField2:(NSString *)field2;

-(void)insertRecordIntoTableNamed:(NSString *)tableName
                       withField1:(NSString *)field1
                      field1Value:(NSString *)field1Value
                        andField2:(NSString *)field2
                      field2Value:(NSString *)field2Value;

-(void)getAllRowsFromTableName: (NSString *)tableName;
@end

Database1ViewController:

#import "Database1ViewController.h"

@implementation Database1ViewController

- (void)didReceiveMemoryWarning
{
    [super didReceiveMemoryWarning];
    // Release any cached data, images, etc that aren't in use.
}

#pragma mark - View lifecycle

-(NSString *)filepath{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDir = [paths objectAtIndex:0];
    return [documentsDir stringByAppendingPathComponent:@"database.sql"];
}



//function to opening db
-(void) openDB{
    
    if (sqlite3_open([[self filepath] UTF8String], &db)!= SQLITE_OK) {
        sqlite3_close(db);
        NSAssert(0, @"Database failed to open.");
    }
}



//function to creating table after opening db
-(void)createTableNamed:(NSString *)
    tableName withField1:(NSString *)field1 
             withField2:(NSString *)field2{
    char *err;
    NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@'('%@' ""TEXT PRIMARY KEY, '%@' TEXT);", tableName, field1, field2];
    
    if(sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK){
        sqlite3_close(db);
        NSAssert(0, @"Table failed to be created.");
    }
}




//Function to insert record
-(void) insertRecordIntoTableNamed:(NSString *)tableName 
                        withField1:(NSString *)field1 field1Value:(NSString *)field1Value 
                         andField2:(NSString *)field2 field2Value:(NSString *)field2Value{
    NSString *sql = [NSString stringWithFormat:@"INSERT OR REPLACE INTO '%@'('%@', '%@')" "VALUES('%@', '%@')", tableName, field1, field2, field1Value, field2Value];
    
    char *err;
    if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK) {
        sqlite3_close(db);
        NSAssert(0, @"Error updating database.");
    }
}

- (void)viewDidLoad
{
    [self openDB];
    [self createTableNamed:@"Contact" 
                withField1:@"email" 
                withField2:@"name"];
    
    //Merging values and functions together
    for (int i=0; i<=2; i++) {
        NSString *email = [[NSString alloc] initWithFormat:@"user%d@learn2develop.net", i];
        NSString *name = [[NSString alloc] initWithFormat:@"user %d", i];
        
        [self insertRecordIntoTableNamed:@"Contacts" 
                              withField1:@"email" field1Value:email 
                               andField2:@"name" field2Value:name];
        [email release];
        [name release];
    }
    [super viewDidLoad];
	// Do any additional setup after loading the view, typically from a nib.
}

This is the error during runtime, main.m:

#import <UIKit/UIKit.h>

#import "Database1AppDelegate.h"

int main(int argc, char *argv[])
{
    @autoreleasepool {
        return UIApplicationMain(argc, argv, nil, NSStringFromClass([Database1AppDelegate class]));[B][COLOR="Red"]Thread 1: Program received signal: "SIGABRT".[/COLOR][/B]
    }
}

The error in my console:

2012-03-28 10:38:04.577 Database1[332:f803] *** Assertion failure in -[Database1ViewController insertRecordIntoTableNamed:withField1:field1Value:andField2:field2Value:], .../Database1ViewController.m:65
2012-03-28 10:38:04.596 Database1[332:f803] *** Terminating app due to uncaught exception 'NSInternalInconsistencyException', reason: 'Error updating database.'
*** First throw call stack:
(0x1584052 0x1715d0a 0x152ca78 0xa5b2db 0x2ca7 0x2e48 0x19864e 0xf8a73 0xf8ce2 0xf8ea8 0xffd9a 0x251f 0xd09d6 0xd18a6 0xe0743 0xe11f8 0xd4aa9 0x146efa9 0x15581c5 0x14bd022 0x14bb90a 0x14badb4 0x14baccb 0xd12a7 0xd2a9b 0x2232 0x21a5)
terminate called throwing an exceptionsharedlibrary apply-load-rules all

Is it syntax error?

 

[PhoneyDeveloper]

Mr. teamstar, may I introduce you to Mr. SIGABRT? You will be having a long, productive relationship together.

Your SIGABRT was generated at Database1ViewController.m: line 65. The printout says

reason: 'Error updating database.'

I think that is this line

        NSAssert(0, @"Error updating database.");

SIGABRT is a runtime exception, also called an assertion failure. You should step to that line in the debugger to see what the problem is. You should also examine the exact error that is returned from the

sqlite3_exec()

call.

On a side note, in most cases you shouldn't be generating sql statements in the way that this code does by string manipulations. It makes your code vulnerable to "SQL injection" attacks. Instead you should be using binding to bind variables.

 

[teamstar]

Mr. teamstar, may I introduce you to Mr. SIGABRT? You will be having a long, productive relationship together.

Your SIGABRT was generated at Database1ViewController.m: line 65. The printout says



I think that is this line

        NSAssert(0, @"Error updating database.");

SIGABRT is a runtime exception, also called an assertion failure. You should step to that line in the debugger to see what the problem is. You should also examine the exact error that is returned from the

sqlite3_exec()

call.

On a side note, in most cases you shouldn't be generating sql statements in the way that this code does by string manipulations. It makes your code vulnerable to "SQL injection" attacks. Instead you should be using binding to bind variables.

Debugger is console right? I double checked the book that i get the code from and everything is exactly as it is (i am really hoping its typo error).

 

[teamstar]

Mr. teamstar, may I introduce you to Mr. SIGABRT? You will be having a long, productive relationship together.

Your SIGABRT was generated at Database1ViewController.m: line 65. The printout says



I think that is this line

        NSAssert(0, @"Error updating database.");

SIGABRT is a runtime exception, also called an assertion failure. You should step to that line in the debugger to see what the problem is. You should also examine the exact error that is returned from the

sqlite3_exec()

call.

On a side note, in most cases you shouldn't be generating sql statements in the way that this code does by string manipulations. It makes your code vulnerable to "SQL injection" attacks. Instead you should be using binding to bind variables.

With regards to using binding, is this the correct procedure:

//Function to insert record
-(void) insertRecordIntoTableNamed:(NSString *) tableName
                        withField1:(NSString *) field1
                       field1Value:(NSString *) field1Value
                         andField2:(NSString *) field2
                       field2Value:(NSString *) field2Value {
    
    NSString *sqlStr = [NSString stringWithFormat:
                        @"INSERT OR REPLACE INTO '%@' ('%@', '%@') "
                        "VALUES (?,?)", tableName, field1, field2];
    const char *sql = [sqlStr UTF8String];
    
    
    sqlite3_stmt *statement;
    if (sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK) {
        sqlite3_bind_text(statement, 1, [field1Value UTF8String],
                          -1, NULL);
        sqlite3_bind_text(statement, 2, [field2Value UTF8String],
                          -1, NULL);
    }
    
    
    if (sqlite3_step(statement) != SQLITE_DONE)
        NSAssert(0, @"Error updating table.");
    sqlite3_finalize(statement);    
    
}

 

[PhoneyDeveloper]

The console is where you see the debugger's printed output. Xcode puts a GUI on top of the debugger so you can set breakpoints, step, view variables and other things using the GUI instead of the command line controls for the debugger.

I use the FMDB wrapper for sqlite rather than the C-language sqlite APIs that you're using, so I'm not an expert on the C-language APIs. But your binding code looks like it might be correct. However, my SQL statements are almost always constant strings that aren't built at runtime. In that case I know the table name and column names that I'm interested in.

 

[TheWatchfulOne]

Hey, teamstar, I'm also developing an app that uses the sqlite3 database.

Here's what I would do if/when I get an error like what you got: "Error updating database."

Step through the code to a point after your SQL statement is put together but before it gets executed and print it to the console.

Here you might be able to spot anything wrong with the statement (if you're very familiar with SQL.)

If nothing is obviously wrong, you can copy an paste from the console into your favorite sqlite editor (I have used Base and currently use Navicat Essentials for SQLite both available in the Mac App Store and both excellent sqlite editors.) Run your SQL statement there (or even in Terminal.) If it's an incorrect statement, the sqlite editor will tell you, althought I admit sometimes the error messages it gives are kinda vague. Check out www.sqlite.org for more detailed info on sqlite.

In a nutshell, you want to make sure your problem is not being caused by an illegitamte SQL statement. It's possible something else is causing the problem but that's where I would start.

 

[TheWatchfulOne]

Hey, teamster, I just noticed something.

Regarding your question about sqlite3 bindings, here is an excerpt from the code you showed:

sqlite3_bind_text(statement, 1, [field1Value UTF8String], -1, NULL);

I was just trying to get bindings working myself, and I finally did in my own app. Here's what is now working for me:

sqlite3_bind_text(dbPreparedStatement, 1, [newValue UTF8String], -1, SQLITE_TRANSIENT);

Every sample of the sqlite3_bind_text call that I could find has "SQLITE_TRANSIENT" where you have "NULL" Maybe that could make a difference? I'm looking forward to any status updates from you on whether you solved your problem or not. I think I figured out tonight that you can only bind values and not table names or column names.

I'm familiar with the SQL "language" from my years as a database developer (which I have not done full time for several years now.) But I recently got The Definitive Guide to SQLite 2nd Edition by Grant Allen and Mike Owens and I've learned much from what little I've read so far.

And there is just one more thing...

You can log the actual error message returned by sqlite3 to the console:

NSLog(@"Error: %s", sqlite3_errmsg(_markerDatabase));

 

[teamstar]

The console is where you see the debugger's printed output. Xcode puts a GUI on top of the debugger so you can set breakpoints, step, view variables and other things using the GUI instead of the command line controls for the debugger.

I use the FMDB wrapper for sqlite rather than the C-language sqlite APIs that you're using, so I'm not an expert on the C-language APIs. But your binding code looks like it might be correct. However, my SQL statements are almost always constant strings that aren't built at runtime. In that case I know the table name and column names that I'm interested in.

Is there any tutorial about using debugger for a newbie like me?

----------

Hey, teamstar, I'm also developing an app that uses the sqlite3 database.

Here's what I would do if/when I get an error like what you got: "Error updating database."

Step through the code to a point after your SQL statement is put together but before it gets executed and print it to the console.

Here you might be able to spot anything wrong with the statement (if you're very familiar with SQL.)

If nothing is obviously wrong, you can copy an paste from the console into your favorite sqlite editor (I have used Base and currently use Navicat Essentials for SQLite both available in the Mac App Store and both excellent sqlite editors.) Run your SQL statement there (or even in Terminal.) If it's an incorrect statement, the sqlite editor will tell you, althought I admit sometimes the error messages it gives are kinda vague. Check out www.sqlite.org for more detailed info on sqlite.

In a nutshell, you want to make sure your problem is not being caused by an illegitamte SQL statement. It's possible something else is causing the problem but that's where I would start.

I'm from PHP->MySQL->PHP environment. I am totally a newbie in iOS (Xcode + Objective-C + Cocoa)
Only found that there's DB implementation in Xcode called sqlite. I'm thrilled to pick on this. I really do appreciate your help in suggesting a better syntax.
Thanx again TheWatchfulOne...

 

[chown33]

Is there any tutorial about using debugger for a newbie like me?

See this post:
https://forums.macrumors.com/showthread.php?p=14613588#post14613588

If you expect to master the debugger quickly, you won't. It takes time and practice. Lots of practice. Even practice on programs that you know work. In fact, practicing on known-good code is a better way to learn the debugger than having to debug broken code, with a tool (the debugger) you barely understand.

In the early days it may even seem like learning the debugger is more trouble than it's worth. It's not. Practice early, practice often.

Every program's bugs are different, and finding them takes careful obversation and thought. Often even more careful thought than you spent writing the code in the first place. Which makes sense: if you'd put more thought into the original code, you wouldn't have a bug. Finding a bug means you have to forget what you believe about your code, and believe what the debugger shows you. You won't see it until you believe it.