how to retrieve NSdata into NSdictionary from Sqlite in iOS

Question

I am creating iOS app which uses Sqlite DB. I have created Table As:

const char *sql_stmt = "CREATE TABLE IF NOT EXISTS ORDERTABLE (ID INTEGER PRIMARY KEY AUTOINCREMENT, ITEMDESC TEXT)";

and then I have to insert self.selectedItemsDictnory Dictionary into ItemDESC i am inserting as :

NSData *data = [NSJSONSerialization dataWithJSONObject:self.selectedItemsDictnory options:NSJSONWritingPrettyPrinted error:nil];
        NSString *insertSQL = [NSString stringWithFormat: @"INSERT INTO ORDERTABLE(ITEMDESC)VALUES( \"%@\");",data];

Upto this it is successfully done.

Now i have to retrieve this dictionary in same format

what i am doing is :

if (sqlite3_open(dbpath, &orderDB) == SQLITE_OK)
    {            
        const char* sqlStatement = [[NSString stringWithFormat:@"SELECT * FROM ORDERTABLE WHERE (ID = '%d')",self.orderSelected]cStringUsingEncoding:NSUTF8StringEncoding];
        sqlite3_stmt* statement;

            if (sqlite3_prepare_v2(orderDB, sqlStatement, -1, &statement, NULL) == SQLITE_OK) {


                if( sqlite3_step(statement) == SQLITE_ROW )
                {

               NSData *retData = (__bridge NSData*) sqlite3_column_value(statement, 1);
               NSDictionary *jsonObject=[NSJSONSerialization
                                              JSONObjectWithData:retData
                                              options:NSJSONReadingMutableLeaves
                                              error:nil];
                    NSLog(@"jsonObject is %@",jsonObject);
                    NSDictionary *dic = [NSDictionary dictionaryWithContentsOfData:retData];
                    NSLog(@"dic is %@",dic);

                }
            }

            sqlite3_finalize(statement);
            sqlite3_close(orderDB);
        }

But i am getting Bad Excess Exception. Please help me out in retrieval of data

Can you point out exact line where you are getting an exception? — Deepesh Gairola, Apr 24 '13 at 06:34
hi ,i am getting exception at NSDictionary *jsonObject=[NSJSONSerialization JSONObjectWithData:retData options:NSJSONReadingMutableLeaves error:nil]; — Sulabh, Apr 24 '13 at 06:36
i think it is not nil, when i PO its displaying as $1 = 0x090c71a8 [no Objective-C description available] — Sulabh, Apr 24 '13 at 06:44
since id is integer type , remove quotes in the id ,SELECT * FROM ORDERTABLE WHERE (ID = %d) and try — karthik, Apr 24 '13 at 06:49

score 1 · Answer 1 · answered Apr 24 '13 at 06:45

Is it possible if you can paste JSON here. Return type of NSJSONSerialization could be a Dictionary or an Array it depends on the root json object.

instead of

NSDictionary *jsonObject=[NSJSONSerialization
                                          JSONObjectWithData:retData
                                          options:NSJSONReadingMutableLeaves
                                          error:nil];

Try

NSArray *jsonObject=[NSJSONSerialization
                                          JSONObjectWithData:retData
                                          options:NSJSONReadingMutableLeaves
                                          error:nil];

And also its good habit to use error parameter to register for any exception so you can use

NSError *logError = nil;
NSArray *jsonObject=[NSJSONSerialization
                                          JSONObjectWithData:retData
                                          options:NSJSONReadingMutableLeaves
                                          error:&error];

actually in my selected item dictionary there are key/value pair like { Pasta = 220; "Spring Rolls" = 125; } it is a restrain waiter App in which while taking order i am saving items with price in dictionary and then inserting in sqlite order table with nsdata — Sulabh, Apr 24 '13 at 06:51
I think you should go through this tutorial, hope it might help http://www.raywenderlich.com/5492/working-with-json-in-ios-5 — Deepesh Gairola, Apr 24 '13 at 06:59

score 0 · Answer 2 · answered Apr 24 '13 at 07:03

0

Try below code to fetch data from sqlite

if (sqlite3_prepare_v2(_database, [query UTF8String], -1, &statement, nil) == SQLITE_OK)
 {
    while (sqlite3_step(statement) == SQLITE_ROW) {
        int uniqueId = sqlite3_column_int(statement, 0);
        char * temp = (char *) sqlite3_column_text(statement, 1);  
        NSString *item_desc = [[NSString alloc] initWithUTF8String:temp];                                  
    }
    sqlite3_finalize(statement);
}

answered Apr 24 '13 at 07:03

karthik

1,271
1
14
27

hi karthik i got result in item_desc as po item_desc $1 = 0x0715c6f0 <7b0a2020 224d4f4d 4f277322 203a2022 31323522 2c0a2020 22537072 696e6720 526f6c6c 7322203a 20223132 35220a7d> so how will i convert this into my actually dictionary??? – Sulabh Apr 24 '13 at 07:13
As you need to insert nsdata to the column ITEMDESC, declare type of ITEMDESC as BLOB, pls refer http://stackoverflow.com/questions/4724118/retrieve-nsdata-from-sqlite-stored-in-blob-data-type-for-iphone-app ,http://stackoverflow.com/questions/4215505/sqlite3-insert-and-read-blob-data-in-database – karthik Apr 24 '13 at 09:56
hi Karthik , i have declare data type of ITEM_DESC as blob, now how to convert string item_desc into nsditionary again – Sulabh Apr 25 '13 at 06:27
convert nsdictionary into nsdata and use sqlite3_bind_blob , for conversion refer http://stackoverflow.com/questions/3157356/converting-nsdictionary-object-to-nsdata-object-and-vice-versa – karthik Apr 26 '13 at 05:56
karthik , this process can only convert to NSdictionary , if it was archived. – Sulabh Apr 26 '13 at 06:01
have you tried this link http://stackoverflow.com/questions/4215505/sqlite3-insert-and-read-blob-data-in-database, to insert nsdata into sqlite. – karthik Apr 26 '13 at 06:24
now i have created table as . const char *sql_stmt = "CREATE TABLE IF NOT EXISTS ORDERTABLE (ID INTEGER PRIMARY KEY AUTOINCREMENT, ITEMDESC BLOB)"; – Sulabh Apr 26 '13 at 06:49

how to retrieve NSdata into NSdictionary from Sqlite in iOS

2 Answers2