SQLite3 (iOS) query not executing

Question

I'm having some trouble getting this to work.

This is the code I want to execute:

[self openDB];
sqlite3_stmt *statement;
NSString *sql2 = [NSString stringWithFormat:@"INSERT INTO CheckList (CLName, DateAdd, Active, Costum, Percentage, UserId) VALUES ('ola232332332324', '2012-02-03', 1, 1, NULL, 1)"];
if(sqlite3_prepare_v2(db, [sql2 UTF8String], -1, &statement, NULL) == SQLITE_OK)
{
    alert1 = [[UIAlertView alloc]
              initWithTitle:@"Grats" message:@"The query was executed!" delegate:self cancelButtonTitle:@"Continue" otherButtonTitles: nil];
    [alert1 show];
    [alert1 release];
} 
else {
    NSAssert(0, @"Failed to execute");
}
sqlite3_finalize(statement);

And this is the code I use to call the db:

-(NSString *) filePath {
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDir = [paths objectAtIndex:0];

    return [documentsDir stringByAppendingPathComponent:@"prosegur.sqlite"];
}

-(void) openDB {
    sqlite3_open([[self filePath] UTF8String], &db);
    if (sqlite3_open([[self filePath] UTF8String], &db) != SQLITE_OK){
        NSAssert(0, @"Failed to open db");
    }
}

Edit: Trying to use the FMDB, here's the code:

FMDatabase *database = [FMDatabase databaseWithPath:@"prosegur.sqlite"];
if([database open]){
    NSLog(@"yes");
    [database executeUpdate:@"INSERT INTO CheckList (CLName, DateAdd, Active, Costum, UserId) VALUES ('cenas', '2012-02-0',  1, 1, 1)"];}
else
{
    NSLog(@"not");
}

Passes by the [database open] verification, but it doesn't execute the query.

EDIT 2: Already did what it was suggested, i'm starting to think that it creates a database and executes the query inside of it, but since it doesn't exist any table, it ignores that fact.

Here's the updated code:

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *docsPath = [[paths objectAtIndex:0] retain];
NSString *path = [[docsPath stringByAppendingPathComponent:@"prosegur.sqlite"] retain];

FMDatabase *database = [FMDatabase databaseWithPath:path];
[database open];
if([database open])
{
    NSLog(@"yes");
    [database executeUpdate:@"INSERT INTO CheckList (CLName) VALUES (?)", @"cenas"];
}
else 
{
    NSLog(@"no");
}
[database close];

Stephen Darlington · Accepted Answer · 2012-05-25 12:20:27Z

2

Maybe a silly question, but you are aware that sqlite3_prepare_v2 only prepares the statement and does not actually execute it? You also need to call sqlite3_step to actually do the insert,

You could use sqlite3_exec instead, which simply executes a statement without doing the prepare/execute two-step.

answered May 25, 2012 at 12:20

Stephen Darlington

52.8k12 gold badges110 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rodolfo Matos Over a year ago

Well my problem is that he doesn't get the sqlite3_prepare_v2 (..) ==SQLITE_OK verification right, he always goes to else. i can't see anything right with that sentence. Thanks

Victor Rudkov · Accepted Answer · 2012-05-25 12:35:55Z

2

I have had a lot of headache while using standart sqlite3 api in iOS development. Check out FMDB framework. It is very simple to use, and you can find lots of tutorials on the web.

If you do not want that framework, try using NSError for catching an error on query execution.

answered May 25, 2012 at 12:35

Victor Rudkov

3881 gold badge3 silver badges13 bronze badges

12 Comments

Rodolfo Matos Over a year ago

I will try that, if i can't make this working im really thinking to go with a coredata app. thanks msj! :)

Stephen Darlington Over a year ago

You can't use NSError as SQLite is a C-level API and NSError is Objective C.

Victor Rudkov Over a year ago

Yes. My fault. About FMDB - it is a wrapper around SQLite. So it supports real SQL queries, and is really easier to use than Core Data.

Rodolfo Matos Over a year ago

Edited the topic, i'd kill for some help, this is an academic work :(

Victor Rudkov Over a year ago

In your provided code you do not use actual path to your database. You should provide full path. This is how I get actual path for database:

+(NSString *) databasePath; {     NSString *databaseName = @"socialpad.sqlite";     NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);     NSString *documentsDir = [documentPaths objectAtIndex:0];     NSString *databasePath = [documentsDir stringByAppendingPathComponent:databaseName];     return databasePath; }

Change the path to your database in [FMDatabase databaseWithPath:@"prosegur.sqlite"];

|

Mani · Accepted Answer · 2012-05-25 13:02:14Z

1

Sample Example.

+(BOOL)InsertDataInPin:(NSString *)pin
    {
        NSString *databasePath =Your DatabasePath;

        NSString *SQL=@"INSERT INTO pincode Values(?)";
        sqlite3_stmt *dataset;


        if (sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK ) {

            if (sqlite3_prepare_v2(database, [SQL UTF8String], -1, &dataset, NULL) == SQLITE_OK) 
            {

                sqlite3_bind_text(dataset, 1, [pin UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_step(dataset);

            }       

            sqlite3_close(database);
        }


    }

answered May 25, 2012 at 13:02

Mani

1,84116 silver badges29 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 11:49:09Z

0

I found out the answer, if anyone is having troubles like this, check out the following link: IPhone Development - Failed to use SQLite

Thanks to all of you for the support!

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered May 29, 2012 at 8:35

Rodolfo Matos

3031 gold badge3 silver badges11 bronze badges

Collectives™ on Stack Overflow

SQLite3 (iOS) query not executing

4 Answers 4

1 Comment

12 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

12 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related