SQLLite - reading the databse

Ok, now that we've implemented opening the database, the next step is actually reading it. We only have a couple of tables in the db, and we're not concerned with one of them yet. The main table is a list of all the words needed to be studied for the JLPT test.

So, let's take a look at the find method from yesterday's tutorial:

-(void) findContact
{
const char *dbpath = [databasePath UTF8String];
sqlite3_stmt *statement;

if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
{
NSString *querySQL = [NSString stringWithFormat: @"SELECT address, phone FROM contacts WHERE name=\"%@\"", name.text];

const char *query_stmt = [querySQL UTF8String];

if (sqlite3_prepare_v2(contactDB, query_stmt, -1, &statement, NULL) == SQLITE_OK)
{
if (sqlite3_step(statement) == SQLITE_ROW)
{
NSString *addressField = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 0)];
address.text = addressField;

NSString *phoneField = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)];
phone.text = phoneField;

status.text = @"Match found";

[addressField release];
[phoneField release];
} else {
status.text = @"Match not found";
address.text = @"";
phone.text = @"";
}
sqlite3_finalize(statement);
}
sqlite3_close(contactDB);
}
}

We can change the file name and select statement, but the problem is it's only selecting a single row. We'll go with that for now, just to get the structure in place, and then switch it off to a loop which reads all the rows.


Ok, here's the revised method:

-(void) selectAll
{
const char *dbpath = [databasePath UTF8String];
sqlite3_stmt *statement;

if (sqlite3_open(dbpath, &jlptDB) == SQLITE_OK)
{
//NSString *querySQL = [NSString stringWithFormat: @"SELECT address, phone FROM contacts WHERE name=\"%@\"", name.text];


NSString *querySQL = [NSString stringWithFormat: @"Select v_all_words_by_level_freq._id, level, number, kanji, hiragana, english, freq, rating, last_review_date, next_review_date from v_all_words_by_level_freq where _id = 1"];

const char *query_stmt = [querySQL UTF8String];

if (sqlite3_prepare_v2(jlptDB, query_stmt, -1, &statement, NULL) == SQLITE_OK)
{
if (sqlite3_step(statement) == SQLITE_ROW)
{
NSString *kanji = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 3)];

NSString *hiragana = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 4)];

NSString *english = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 5)];

NSLog(@"kanji: %@, hiragana: %@, english: %@", kanji, hiragana, english);

[kanji release];
[hiragana release];
[english release];
} else {
NSLog(@"match not found");
}
sqlite3_finalize(statement);
}
sqlite3_close(jlptDB);
}
}

And add a call after the init in the app delegate:

MyDatabaseReader *myDatabaseReader = [[MyDatabaseReader alloc] init];

[myDatabaseReader selectAll];

Well, I'm still not getting the results I'm looking for. Here's the code with some debugging added:

-(void) selectAll
{
const char *dbpath = [databasePath UTF8String];
sqlite3_stmt *statement;

if (sqlite3_open(dbpath, &jlptDB) == SQLITE_OK)
{

NSLog(@"select all, open ok");

//NSString *querySQL = [NSString stringWithFormat: @"SELECT address, phone FROM contacts WHERE name=\"%@\"", name.text];


NSString *querySQL = [NSString stringWithFormat: @"Select v_all_words_by_level_freq._id, level, number, kanji, hiragana, english, freq, rating, last_review_date, next_review_date from v_all_words_by_level_freq whre _id = 1"];

const char *query_stmt = [querySQL UTF8String];

if (sqlite3_prepare_v2(jlptDB, query_stmt, -1, &statement, NULL) == SQLITE_OK)
{
NSLog(@"select all, prepare ok");

if (sqlite3_step(statement) == SQLITE_ROW)
{
NSLog(@"select all, row found");

NSString *kanji = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 3)];

NSString *hiragana = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 4)];

NSString *english = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 5)];

NSLog(@"kanji: %@, hiragana: %@, english: %@", kanji, hiragana, english);

[kanji release];
[hiragana release];
[english release];
} else {
NSLog(@"match not found");
}
sqlite3_finalize(statement);
}
else {
NSLog(@"select all, prepare unssuccessful");
}
sqlite3_close(jlptDB);
}
else {
NSLog(@"select all, open failed");

}
}

And when I run it, it shows "prepare unsuccessful" - so perhaps I've munged the syntax. If I copy and paste into RazorSQL - oh, yeah, I typo-ed the "where". Next error - there is no rating column. How did that old sql get in there?

Ok, let's just have RazorSQL generate a select statement:

SELECT
_id,
level,
number,
kanji,
hiragana,
english,
freq,
freq_sequence,
level_freq_sequence
FROM
all_words
where _id = 1;

Which, tailored for the test, looks like this:

NSString *querySQL = [NSString stringWithFormat: @"SELECT kanji, hiragana, english FROM all_words where _id = 1;"];

and there we go:

2011-08-31 16:06:08.822 JlptQuizApp[5516:207] select all, prepare ok
2011-08-31 16:06:08.823 JlptQuizApp[5516:207] select all, row found
2011-08-31 16:06:08.823 JlptQuizApp[5516:207] kanji: 会う, hiragana: あう, english: to meet

Ok, in our next post, we'll probably just make the changes for this class replace the XMLReader. Onward ho!