How to get data from Sqlite database?

Question

I have create sqlite db Table as below

NSString * sqlStmt =@"CREATE TABLE IF NOT EXISTS SONGS (ID INTEGER PRIMARY KEY AUTOINCREMENT, SONGNAME TEXT UNIQUE NOT NULL,ALBUMNAME TEXT ,ARTIST TEXT ,SIZE FLOAT ,IMAGE BLOB)";

when i am trying to get data from this table getting nothing

NSString *sqlQuery = [NSString stringWithFormat:
                @"SELECT ARTIST ,IMAGE FROM SONGS WHERE SONGNAME=kannulada"];

but iam getting data when iam trying to get data by id.

NSString *sqlQuery = [NSString stringWithFormat:
                @"SELECT ARTIST ,IMAGE FROM SONGS WHERE id=1"];

when i debugging it not excuting prepare loop

-(NSMutableArray*)retrieveDataArrayWHICHISequql
{
  [dataArray removeAllObjects];
 sqlite3_stmt  *statement;

const char *dbpath = [databasePath UTF8String];

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

    const char * selectQuery =[sqlQuery UTF8String];

    if (sqlite3_prepare_v2(_SQliteDB, selectQuery, -1, &statement, NULL)== SQLITE_OK)
    {
        while (sqlite3_step(statement)== SQLITE_ROW)
        {
            NSString * addressStr = [[NSString alloc]initWithUTF8String:(const char*)sqlite3_column_text(statement, 0)];

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

            //  NSString * nameStr = [[NSString alloc]initWithUTF8String:(const char*)sqlite3_column_text(statement, 2)];

            NSMutableDictionary * dataDict = [[NSMutableDictionary alloc]init];
            [dataDict setObject:addressStr forKey:@"getaddress"];
            [dataDict setObject:phNumStr forKey:@"getphone"];
            // [dataDict setObject:nameStr forKey:@"getname"];

            [dataArray addObject:dataDict];

        }
        sqlite3_finalize(statement);
        sqlite3_close(_SQliteDB);

    }

}
return dataArray;

i am using exact the same as song name NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO SONGS (SONGNAME ,ALBUMNAME ,ARTIST ,SIZE ,IMAGE) VALUES (\"%@\",\"%@\",\"%@\",\"%@\",\"%@\")",self.currentSongTitleName,self.currentSongAlbum,self.currentSongArtistName,display,self.imageDataFromMusic]; — Abhiram, Aug 22 '17 at 06:51
@Anbu.Karthik I HAVE string in sqlite db kick (2009) but i am getting kick(2009) namespaces are skipping but i need exact same in sqlite db? — Abhiram, Aug 22 '17 at 08:25
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/152505/discussion-between-savarala-and-anbu-karthik). — Abhiram, Aug 22 '17 at 08:28

score 1 · Accepted Answer · edited Aug 22 '17 at 07:20

1

NSString *sqlQuery = [NSString 

    stringWithFormat:
                    @"SELECT ARTIST ,IMAGE FROM SONGS WHERE SONGNAME='kannulada'"]

;

kannulada is string , so you must use single quote

edited Aug 22 '17 at 07:20

Caplin YT

269
1
5
16

answered Aug 22 '17 at 06:51

Sen-ming Young

26
4

I HAVE string in sqlite db kick (2009) but i am getting kick(2009) namespaces are skipping but i need exact same in sqlite db? – Abhiram Aug 22 '17 at 08:25

Rob · Answer 2 · 2017-08-22T07:02:50.533

-1

The problem is that if you have an error in sqlite3_prepare_v2 (it didn't return SQLITE_OK). So, if that happens, you should look at what sqlite3_errmsg returned, and it will tell you precisely why it failed. If you don't look at the meaningful error message that SQLite returns, you're flying blind.

if (sqlite3_prepare_v2(_SQliteDB, selectQuery, -1, &statement, NULL)== SQLITE_OK)
{
    ...
} else {
    NSLog(@"SQL Error: %s", sqlite3_errmsg(_SQliteDB));
}

Regarding why it's failing, it's because you have to either quote the string literal, 'kannulada', or, better, use a SQL placeholder, ?, and then use sqlite3_bind_text.

edited Aug 22 '17 at 07:02

answered Aug 22 '17 at 06:56

Rob

415,655
72
787
1,044

will u elobarate ur ans? – Abhiram Aug 22 '17 at 06:58
iam new to ios [ this one sqlite3_bind_text.] – Abhiram Aug 22 '17 at 07:02
See https://stackoverflow.com/a/27386276/1271826 or https://stackoverflow.com/a/25920119/1271826 or https://stackoverflow.com/a/21507483/1271826 – Rob Aug 22 '17 at 07:04
I HAVE string in sqlite db kick (2009) but i am getting kick(2009) namespaces are skipping but i need exact same in sqlite db? – Abhiram Aug 22 '17 at 08:25

How to get data from Sqlite database?

2 Answers2