sqlite3_prepare_v2() == SQLITE_OK returns NO and existing table is not found

Question

I am developing an iphone application which using sqlite3 database file. and i am using following code to select from sqlite3 database ;

NSString* dbYolu = [NSString stringWithFormat:@"%@/emhdb3.sqlite3",NSHomeDirectory()];

sqlite3* db;

NSLog(@"%@ dbyolu : ", dbYolu);

if (sqlite3_open([dbYolu UTF8String], &db) == SQLITE_OK)
{
    NSString *query = [NSString stringWithFormat: @"SELECT username, mobile FROM peopleto WHERE namesurname=\"%@\"", name.text];
    NSLog(@"%@ : query", query);
    sqlite3_stmt *stmt;

    if (sqlite3_prepare_v2(db, [query UTF8String], -1, &stmt, nil) == SQLITE_OK) //  statement stmt returns null and returns SQLITE_OK = FALSE.
    {
        NSLog(@"stepped in SQLITE_OK is TRUE");
        while (sqlite3_step(stmt) == SQLITE_ROW)
        {
            NSString* ders_kodu = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 0)];
            double not = sqlite3_column_double(stmt, 1);

            NSLog(@"%@ : %f", ders_kodu, not);
        }
    }
    else
    {
        NSLog(@"%@ - but failed", stmt);
    }
    sqlite3_finalize(stmt);
}
else
    NSLog(@"db could not be opened");

it fails at the "sqlite3_prepare_v2(db, [query UTF8String], -1, &stmt, nil) == SQLITE_OK" point above. and the error is : "Error:No such table : peopleto. But it successfully returns the content of table when i run this query in sql browser. I mean this query is correct and working.

btw, I get the file with pathname with the following code on viewLoad

- (void)viewDidLoad
{
NSString *docsDir;
NSArray *dirPaths;

// Get the documents directory
dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

docsDir = [dirPaths objectAtIndex:0];

// Build the path to the database file
databasePath = [[NSString alloc] initWithString:[docsDir stringByAppendingPathComponent: @"emhdb3.sqlite3"]];

NSLog(@"dbpath : %@", databasePath);
NSFileManager *filemgr = [NSFileManager defaultManager];

if ([filemgr fileExistsAtPath: databasePath ] == NO)
{
    const char *dbpath = [databasePath UTF8String];
    NSLog(@"db not found");
    if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
    {
        NSLog(@"SQLITE_OK is passed");
        //.... some codes here, doesn't metter for me because SQLITE_OK = true
    } else {
        status.text = @"Failed to open/create database";
    }
}
else if ([filemgr fileExistsAtPath:databasePath] == YES)
{
    NSLog(@"i found the file here : %s",[databasePath UTF8String]);
}
NSLog(@"completed");
[super viewDidLoad];
}

I have the database file in my project folder and added to the project. What i am missing?

Thanks

///////

i am sorry for updating my question but stackoverflow does not allow me to add new ,

please find my update below ;

Here is the updated version of the code :

viewLoad part;

- (void)viewDidLoad
{
[self createEditableCopyOfDatabaseIfNeeded];

NSString *docsDir;
NSArray *dirPaths;
dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docsDir = [dirPaths objectAtIndex:0];
databasePath = [[NSString alloc] initWithString:[docsDir stringByAppendingPathComponent:@"emhdb3.sqlite3"]];

NSLog(@"dbpath : %@", databasePath);
NSFileManager *filemgr = [NSFileManager defaultManager];

if ([filemgr fileExistsAtPath: databasePath ] == NO)
{
const char *dbpath = [databasePath UTF8String];
    if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
    {
        char *errMsg;
        const char *sql_stmt = "CREATE TABLE IF NOT EXISTS peopleto (pId INTEGER PRIMARY KEY AUTOINCREMENT, namesurname TEXT, mobile TEXT)";
        if (sqlite3_exec(contactDB, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
        {
            status.text = @"Failed to create table";
        }
        sqlite3_close(contactDB);
    } else {
        status.text = @"Failed to open/create database";
    }
}
else if ([filemgr fileExistsAtPath:databasePath] == YES)
{
    NSLog(@"%s is filepath",[databasePath UTF8String]);
}
NSLog(@"checkpoint 4");
[super viewDidLoad];
}

buraya başka birşey yaz

- (void)createEditableCopyOfDatabaseIfNeeded {
NSLog(@"entered createEditableCopyOfDatabaseIfNeeded");
BOOL success;
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"emhdb3.sqlite3"];
success = [fileManager fileExistsAtPath:writableDBPath];
if (success)
{
    NSLog(@"success == YES returned");
    return;
}
NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"emhdb3.sqlite3"];
success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
if (!success) {
    NSLog( @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
}
else{
    NSLog(@"checkpoint 2");
}
}

and the part that select query is performed ;

-(IBAction)findcontact2:(id)sender
{
NSString *dbYolu = databasePath;
sqlite3* db;

NSLog(@"%@ dbyolu : ", dbYolu);

if (sqlite3_open([dbYolu UTF8String], &db) == SQLITE_OK)
{
    NSString *query = [NSString stringWithFormat: @"SELECT namesurname, mobile FROM peopleto"]; //any query

    NSLog(@"%@ : query", query);

    sqlite3_stmt *stmt;

    if (sqlite3_prepare_v2(db, [query UTF8String], -1, &stmt, nil) == SQLITE_OK)
    {
        NSLog(@"SQLITE is OK");
        while (sqlite3_step(stmt) == SQLITE_ROW)
        {
            NSString* ders_kodu = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 0)];
            double not = sqlite3_column_double(stmt, 1);

            NSLog(@"%@ : %f", namesurname, mobile);
        }
    }
    else
    {
        NSLog(@"Error=%s",sqlite3_errmsg(db));   // error message : Error=no such table: peopleto
        NSLog(@"%@ - is stmt", stmt); //stmt is null
    }
    sqlite3_finalize(stmt);
}
else
    NSLog(@"could not open the db");
}

As i pointed above, the error is Error=no such table: peopleto and stmt is returned null

DivineDesert DivineDesert · Accepted Answer · 2013-05-10T04:34:50

Are you copying your SQL file to documents, if not you need to do that first. While adding SQL file to project, it is added in your bundle and not in documents directory. YOu need to copy that first

- (void)createEditableCopyOfDatabaseIfNeeded {
    // First, test for existence.
    BOOL success;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"bookdb.sql"];
    success = [fileManager fileExistsAtPath:writableDBPath];
    if (success)
        return;
    // The writable database does not exist, so copy the default to the appropriate location.
    NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"bookdb.sql"];
    success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
    if (!success) {
        NSLog( @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
    }
}

sqlite3_prepare_v2() == SQLITE_OK returns NO and existing table is not found

2 Answers