sqlite drop table command

[whitedragon101]

I have a function which is supposed to drop the table I'm working on so I can start again. It looks like this :

- (void) dropData
{
    
    sqlite3_stmt    *statement;
    char *errMsg;
    
    NSString *sql_stmt = @"DROP TABLE THOUGHT_RECORD";
    
    const char *drop_stmt = [sql_stmt UTF8String];
    sqlite3_prepare_v2(thoughtDB, drop_stmt,
                       -1, &statement, NULL);
    
    
    if (sqlite3_step(statement) == SQLITE_DONE)
    {
        status = @"Didn't Drop table";
        NSLog(@"Didn't Drop table");
    } else
    {
        NSLog(@"Dropped table");
        
    }
    sqlite3_finalize(statement);
    sqlite3_close(thoughtDB);

    
}

The console outputs - " Dropped table"
However, if i then print out the table it is still there and so is all the data.

Any ideas? Is the above code not correct?

[chown33]

The logic of this code seems odd:

    if (sqlite3_step(statement) == SQLITE_DONE)
    {
        status = @"Didn't Drop table";
        NSLog(@"Didn't Drop table");
    } else
    {
        NSLog(@"Dropped table");
        
    }

If the call indicates "Done", then the table WASN'T dropped, but if the call returns any other value, then it says the table WAS dropped. That seems either incomplete, backwards, or both.

What does the debugger say the value returned from sqlite3_step() really is? That may be a clue as to what's really happening.

If you don't know how to use the debugger, this would be a good time to learn it. Being able to step through statements one at a time is a powerful tool for seeing what really happens in code.

[waterskier2007]

Originally Posted by whitedragon101

I have a function which is supposed to drop the table I'm working on so I can start again. It looks like this :

- (void) dropData
{
    
    sqlite3_stmt    *statement;
    char *errMsg;
    
    NSString *sql_stmt = @"DROP TABLE THOUGHT_RECORD";
    
    const char *drop_stmt = [sql_stmt UTF8String];
    sqlite3_prepare_v2(thoughtDB, drop_stmt,
                       -1, &statement, NULL);
    
    
    if (sqlite3_step(statement) == SQLITE_DONE)
    {
        status = @"Didn't Drop table";
        NSLog(@"Didn't Drop table");
    } else
    {
        NSLog(@"Dropped table");
        
    }
    sqlite3_finalize(statement);
    sqlite3_close(thoughtDB);

    
}

The console outputs - " Dropped table"
However, if i then print out the table it is still there and so is all the data.

Any ideas? Is the above code not correct?

I believe that you need to use a different check than SQLITE_DONE. I can't remember off the top of my head but maybe SQLITE_OK instead. Done just returns whether or not it finished, so even if it finished with a bad result, it still might pop done

edit: see status codes here http://www.sqlite.org/c3ref/c_abort.html

[whitedragon101]

Originally Posted by chown33

The logic of this code seems odd:

    if (sqlite3_step(statement) == SQLITE_DONE)
    {
        status = @"Didn't Drop table";
        NSLog(@"Didn't Drop table");
    } else
    {
        NSLog(@"Dropped table");
        
    }

If the call indicates "Done", then the table WASN'T dropped, but if the call returns any other value, then it says the table WAS dropped. That seems either incomplete, backwards, or both.

What does the debugger say the value returned from sqlite3_step() really is? That may be a clue as to what's really happening.

If you don't know how to use the debugger, this would be a good time to learn it. Being able to step through statements one at a time is a powerful tool for seeing what really happens in code.

Whoops yep the NSlog commands are backwards.

The return value of

sqlite3_step(statement)

is 21 which is

#define SQLITE_MISUSE      21   /* Library used incorrectly */

So I think there is something wrong with this code. That its not the right way to drop a table. When I google I can't find anything other than just the sqlite command.

"DROP TABLE yourTableNameHere"

Any help appreciated

[whitedragon101]

Figured it out. It was missing :

    if (sqlite3_open(dbpath, &thoughtDB) == SQLITE_OK)

to open the db for editing

It should read

- (void) dropData{
    
  
    sqlite3_stmt    *statement;
    const char *dbpath = [databasePath UTF8String];
    
    if (sqlite3_open(dbpath, &thoughtDB) == SQLITE_OK)
    {
    
    NSString *sql_stmt = @"DROP TABLE THOUGHT_RECORD";
    
    const char *drop_stmt = [sql_stmt UTF8String];
    sqlite3_prepare_v2(thoughtDB, drop_stmt,
                       -1, &statement, NULL);
        
    if (sqlite3_step(statement) == SQLITE_DONE)
    {
        NSLog(@"Dropped table");

    } else
    {
        status = @"Didn't Drop table";
        NSLog(@"Didn't Drop table");
        
    }
    sqlite3_finalize(statement);
    sqlite3_close(thoughtDB);

    }
}