CODEWITHSUNDEEP
objective-ciosxcodesqlite
Lloydworth
Lloydworth

Reputation: 763

Unable to retrieve values from SQLite Database

I am developing an iPad application.
I have an SQLite database in my resource folder.
When my application launches, these codes will copy the database to my NSDocumentDirectory.

- (void) copyDatabaseIfNeeded {
  NSFileManager *fileManager = [NSFileManager defaultManager];
  NSError *error;
  NSString *dbPath = [self getDBPath];
  BOOL success = [fileManager fileExistsAtPath:dbPath];

  if(!success) {

    NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"Mydata.sqlite"];
    success = [fileManager copyItemAtPath:defaultDBPath toPath:dbPath error:&error];

    if (!success)
        NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
  }
}

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

In another view of my application, i have these codes to open the database and retrieve some values.

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDir = [paths objectAtIndex:0];
if(sqlite3_open([[documentsDir stringByAppendingPathComponent:@"Mydata.sqlite"] UTF8String], &db) != SQLITE_OK){
    sqlite3_close(db);
    NSAssert(0, @"Failed to open database.");
}
NSString *sql = [[NSString alloc] initWithFormat:@"SELECT HOMEONE, HOMETWO, HOMETHREE, HOMEFOUR FROM STUDENTS WHERE NAME='%@'",Name];
sqlite3_stmt *statement;
if(sqlite3_prepare_v2(db, [sql UTF8String], -1, &statement, nil)== SQLITE_OK)
{
    while(sqlite3_step(statement) == SQLITE_ROW){
        char *one = (char *) sqlite3_column_text(statement,0);
        ActionOne = [[NSString alloc] initWithUTF8String:one];
        char *two = (char *) sqlite3_column_text(statement,1);
        ActionTwo = [[NSString alloc] initWithUTF8String:two];
        char *three = (char *) sqlite3_column_text(statement,2);
        ActionThree = [[NSString alloc] initWithUTF8String:three];
        char *four = (char *) sqlite3_column_text(statement,3);
        ActionFour = [[NSString alloc] initWithUTF8String:four];
    }
    sqlite3_finalize(statement);
}

Yes, with these codes, i'm able to retrieve the values from the database.
But now i need to retrieve more values so i edited my codes.

NSString *sql = [[NSString alloc] initWithFormat:@"SELECT HOMEONE, HOMETWO, HOMETHREE, HOMEFOUR, VOICEONE, VOICETWO, VOICETHREE, VOICEFOUR FROM STUDENTS WHERE NAME='%@'",Name];
sqlite3_stmt *statement;
if(sqlite3_prepare_v2(db, [sql UTF8String], -1, &statement, nil)== SQLITE_OK)
{
    while(sqlite3_step(statement) == SQLITE_ROW){
        char *one = (char *) sqlite3_column_text(statement,0);
        ActionOne = [[NSString alloc] initWithUTF8String:one];
        char *two = (char *) sqlite3_column_text(statement,1);
        ActionTwo = [[NSString alloc] initWithUTF8String:two];
        char *three = (char *) sqlite3_column_text(statement,2);
        ActionThree = [[NSString alloc] initWithUTF8String:three];
        char *four = (char *) sqlite3_column_text(statement,3);
        ActionFour = [[NSString alloc] initWithUTF8String:four];

        char *vone = (char *) sqlite3_column_text(statement,4);
        VoiceOne = [[NSString alloc] initWithUTF8String:vone];
        char *vtwo = (char *) sqlite3_column_text(statement,5);
        VoiceTwo = [[NSString alloc] initWithUTF8String:vtwo];
        char *vthree = (char *) sqlite3_column_text(statement,6);
        VoiceThree = [[NSString alloc] initWithUTF8String:vthree];
        char *vfour = (char *) sqlite3_column_text(statement,7);
        VoiceFour = [[NSString alloc] initWithUTF8String:vfour];
    }
    sqlite3_finalize(statement);
}

Now it doesn't even retrieve the HOMEONE, HOMETWO, HOMETHREE, HOMEFOUR values. I tried changing my SQL statement to retrieve just the VOICEONE, VOICETWO, VOICETHREE, VOICEFOUR values, it also doesn't give me any values.

I did some checking and found out that the application didn't even step into this line:

    if(sqlite3_prepare_v2(db, [sql UTF8String], -1, &statement, nil)== SQLITE_OK)

Which means the sqlite3_prepare_v2 isn't successful? I have checked the database field types.
I tried executing the SQL statement in 'SQLite Database Browser', and it was able to return the values.

May i know what is the problem with my codes?

Upvotes: 0

Views: 706

Answers (1)

saadnib
saadnib

Reputation: 11145

This problem may occur because you may changed your database and copy the new one and forgot to remove app from simulator or device and trying to run the project. What i suggest if you are running it in simulator then plz copy the database path and open it in SQLite Database Browser from this path and try to run your sql statement here and you will find that its not running also here. Please delete app from simulator or device if you copy new database into project.

Upvotes: 1

Related Questions