How to write the query for retrieving id from sqlite database table in iphone apps.

Question

Hi,

I am new to using the SQLite database in iphone apps. I have created a database with login table in it and added the database to the project. Login table contains 3 fields "ID", "Username" and "Password" fields. I am entering the username and password in the textfields of the view. Now i am looking of how to retrieve the id from the login table when username and password entered are correct by checking the table. Can anyone help out in this.

My Code:

-(void)checkInDatabase {

if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK)
{
    //uid = "select count(*) from logins where Username==txtusername.text and Password==txtpassword.text";
     NSString* sql =[[NSString alloc] initWithFormat:@"select id from login where Username =%s and Password=%s;",txtusername.text,txtpassword.text];

    [sql UTF8String];
    sqlite3_stmt *statement;
    if(sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK )
    {
        if(sqlite3_step(statement) == SQLITE_ROW);
        {
            uid =[NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 1)]; 
        }
    }
    else
    {
        uid =0;
    }
    sqlite3_finalize(statement);
}
sqlite3_close(database);

}

Thank you

Answer 1

You need to have your SQL statement in a format usable by sqlite3. You might do something like this:

NSString *select = [[NSString alloc] initWithFormat:@"select id from login where Username=%s and Password=%s;", txtusername.text, txtpassword.text];

Since sqlite is a C library you will need to get the UTF8 version of the select string and pass that to the sqlite lib:

if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, NULL) == SQLITE_OK)

Answer 2

I got it working by writing the code like this.

NSString* sql = [[NSString alloc] initWithFormat:@"select id from login where Username='%@' and Password='%@'",txtusername.text,txtpassword.text];

Its working good for me now.