CODEWITHSUNDEEP
iosobjective-cdatabasexcodesqlite
Ben Heyderman
Ben Heyderman

Reputation: 11

Reading from a Database in an iPhone app

Below is my code for reading from a database:

if (sqlite3_open(dbpath, &database) == SQLITE_OK)
{
    const char *query_stmt = "SELECT QuestionID, question, RightAnswer,WrongAnswer1, WrongAnswer2, audio FROM questions WHERE done='no' AND Catagory='%@'", Catagory;

    int check = (sqlite3_prepare_v2(database, query_stmt, -1, &statement, NULL));
    NSLog(@"%i",check);


    if (sqlite3_prepare_v2(database, query_stmt, -1, &statement, NULL) == SQLITE_OK)
    {

        while(sqlite3_step(statement) == SQLITE_ROW)
        {
            questionID = [[NSString alloc]initWithUTF8String:(const char *) sqlite3_column_text(statement, 0)];
            questionString = [[NSString alloc]initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)];
            rightAnswerString = [[NSString alloc]initWithUTF8String:(const char *) sqlite3_column_text(statement, 2)];
            wrong1AnswerString = [[NSString alloc]initWithUTF8String:(const char *) sqlite3_column_text(statement, 3)];
            wrong2AnswerString = [[NSString alloc]initWithUTF8String:(const char *) sqlite3_column_text(statement, 4)];
            audioInteger = [[NSString alloc]initWithUTF8String:(const char *) sqlite3_column_text(statement, 5)];
            break;
        }

        sqlite3_finalize(statement);
    }
    sqlite3_close(database);
}

I want to read in the first row of my table where done='no' and the category matches the category stored in the variable catagory. I thought that I could do it using the while statement then breaking before it loops. however the condition of the while statement is never being satisfied. This suggest to me that the sql statement is returning no values.

SQL of the database is below:

DROP TABLE IF EXISTS "Questions";
CREATE TABLE "Questions" ("QuestionID" INTEGER PRIMARY KEY  NOT NULL , "Question" TEXT, "RightAnswer" TEXT, "WrongAnswer1" TEXT, "WrongAnswer2" TEXT, "Done" BOOL, "Catagory" TEXT, "Audio" INTEGER);
INSERT INTO "Questions" VALUES(1,'What is the BPM of this piece?','120 BPM','70 BPM','170 BPM','no','Rhythm',1);
INSERT INTO "Questions" VALUES(2,'How far apart are the 2 notes the bass guitar plays?','4 Semitones','1 Semitone','6 Tones','no','Pitch',1);
INSERT INTO "Questions" VALUES(3,'How is the organ Panned?','Left','Right','Centre','no','Pan',1);
INSERT INTO "Questions" VALUES(4,'What are the note values of the HiHat rhythms?','Semi quaver','Quaver','Crotchet','no','Rhythm',1);
INSERT INTO "Questions" VALUES(5,'The first chord played on the organ is...','Minor','Major','Atonal','no','Pitch',1);
INSERT INTO "Questions" VALUES(6,'How is the bass sound panned in this piece?','Right','Left','Center','no','Pan',2);
INSERT INTO "Questions" VALUES(7,'How is the lead synth panned in this piece?','Left','Right','Center','no','Pan',2);
INSERT INTO "Questions" VALUES(8,'The Kick Drum Rhythm In this can abe described as...','Four to the Floor','Off beat','Minor','no','Rhythm',2);
INSERT INTO "Questions" VALUES(9,'A clap sound can be heard on beat..','2 and 4','1 and 3','3','no','Rhythm',2);
DROP TABLE IF EXISTS "Results";
CREATE TABLE "Results" ("ResultID" INTEGER PRIMARY KEY  NOT NULL , "QuestionRight" INTEGER, "TotalQuestions" INTEGER, "Catagory" TEXT);

So what i'm asking is:

1) is there a better way to read in only the first row that satisfies my sql statement?

2) is there a reason my current method isn't working?

Thanks for your help,

Ben

************* UPDATE *************

Here is where category is defined:

CatagoryLoaded = [[NSUserDefaults standardUserDefaults] integerForKey:@"catagorysaved"];
switch (CatagoryLoaded) {
    case 1:
        CatagorySelected.text = [NSString stringWithFormat:@"Pan!"];
        Catagory = @"pan";
        break;
    case 2:
        CatagorySelected.text = [NSString stringWithFormat:@"Pitch!"];
         Catagory = @"pitch";
        break;
    case 3:
        CatagorySelected.text = [NSString stringWithFormat:@"Rhythm!"];
        Catagory = @"rhythm"; 
        break;
    default:
        break;
}

************* UPDATE 2*************

I changed it to Limit 1 as you suggested and am now getting thread1: signal SIGABRT

if (sqlite3_open(dbpath, &database) == SQLITE_OK)
{
    const char *query_stmt = "SELECT * FROM Questions WHERE Done='no' AND Catagory='%@' LIMIT 1", Catagory;

    int check = (sqlite3_prepare_v2(database, query_stmt, -1, &statement, NULL));
    NSLog(@"%i",check);


    if (sqlite3_prepare_v2(database, query_stmt, -1, &statement, NULL) == SQLITE_OK)
    {


            questionID = [[NSString alloc]initWithUTF8String:(const char *) sqlite3_column_text(statement, 0)];
            questionString = [[NSString alloc]initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)];
            rightAnswerString = [[NSString alloc]initWithUTF8String:(const char *) sqlite3_column_text(statement, 2)];
            wrong1AnswerString = [[NSString alloc]initWithUTF8String:(const char *) sqlite3_column_text(statement, 3)];
            wrong2AnswerString = [[NSString alloc]initWithUTF8String:(const char *) sqlite3_column_text(statement, 4)];
            audioInteger = [[NSString alloc]initWithUTF8String:(const char *) sqlite3_column_text(statement, 5)];


        sqlite3_finalize(statement);
    }
    sqlite3_close(database);
}

************* UPDATE ************* looks like the statement is leaving it as '%@' instead of what ever is stored in category, any idea why?

Below is a link to a screen shot showing variables just before SIGABRT crash. (won't let me post the image directly as i am new to the site)

http://puu.sh/hkOxf/8c05b9489b.png

Upvotes: 0

Views: 84

Answers (1)

Travis M.
Travis M.

Reputation: 11257

My only guess is that your "Category" variable is not a valid one. Other than that, the SQL statement looks good. I don't recall if SQLite is case sensitive but it wouldn't hurt to put Done and Questions and stuff in the proper case.

Also, for performance reasons, you can always add a "Limit 1" to the end of your SQL statement to get the first row. Or simply change it from a WHILE to an IF instead, so it only runs once, however, I recommend doing using the LIMIT syntax.

NSString *query_stmt = [NSString stringWithFormat:"SELECT * FROM Questions WHERE Done='no' AND Catagory='%@' LIMIT 1", Catagory];

Upvotes: 1

Related Questions