lundi 20 avril 2015

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

Aucun commentaire:

Enregistrer un commentaire