inserting html string into sqlite db using objective c

Question

I am using the below snippet to insert html string into sqlite db, my code works fine but when i retrieve the same html string and display in web view it doesn't render, some data is getting modified, can anyone please help how to insert long html strings into db.

const char *sql = "insert into articles (art_id,sub_cat_id,art_title,art_details) Values(?, ?, ?, ?)";

                    if(sqlite3_prepare_v2(database, sql, -1, &addStmt, NULL) != SQLITE_OK)
                        NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(database));

                    sqlite3_bind_text(addStmt, 1, [[tuser objectForKey:@"art_id"] UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(addStmt, 2, [[tuser objectForKey:@"sub_cat_id"] UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(addStmt, 3, [[tuser objectForKey:@"art_title"] UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(addStmt, 4, [[tuser objectForKey:@"art_details"] UTF8String], -1, SQLITE_TRANSIENT);


                    if(SQLITE_DONE != sqlite3_step(addStmt))
                        NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));
                    else
                        //SQLite provides a method to get the last primary key inserted by using sqlite3_last_insert_rowid
                        //coffeeID = sqlite3_last_insert_rowid(database);

                        //Reset the add statement.
                        sqlite3_reset(addStmt);

Could you please be more specific about "some data" and its "getting modified"? — Sergey Kalinichenko
– Sergey Kalinichenko, Commented Aug 23, 2012 at 14:38
Hi some double quotes and single quotes are getting truncated, thanks deepesh — Max
– Max, Commented Aug 23, 2012 at 14:40
It is quite possible that the problem has nothing to do with saving/retrieving the data from sqlite. You may want to provide more info on how the HTML gets handled on its way to and from the database. — Sergey Kalinichenko
– Sergey Kalinichenko, Commented Aug 23, 2012 at 14:47
before inserting when i copied the string it was showing up in browser, later when i insert and retrieve it, nothing works, so there is something getting modified, can you help me knowing what is the best process to follow to insert long html string in sqlite db using objective c — Max
– Max, Commented Aug 23, 2012 at 14:54
Add logging to see when the changeover happens. If necessary, try retrieving the string from the database immediately after inserting it. Other than a possibility of the ending segment being truncated because of line sizing issues, I see nothing wrong with the code from your post. — Sergey Kalinichenko
– Sergey Kalinichenko, Commented Aug 23, 2012 at 15:00

Mike M · Accepted Answer · 2012-08-24 16:22:54Z

3

The single quotes are likely causing the issue. Since it's HTML you can replace the single quotes with an & apos; or you can "double" the single quote like shown here so that sqlite is okay with it:

    NSString *artdetails = [tuser objectForKey:@"art_details"];
    NSString *arttitle = [tuser objectForKey:@"art_title"];

    NSString *ad = [artdetails stringByReplacingOccurrencesOfString:@"'" withString:@"''"];    
    NSString *at = [arttitle stringByReplacingOccurrencesOfString:@"'" withString:@"''"];

...

    sqlite3_bind_text(addStmt, 3, [at UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(addStmt, 4, [ad UTF8String], -1, SQLITE_TRANSIENT);

etc

edited Aug 24, 2012 at 16:22

answered Aug 23, 2012 at 17:36

Mike M

4,4561 gold badge31 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Augie Over a year ago

on a similar approach, look into encoding your string before writing it to sqlite and then decoding it upon retrieval to ensure special characters don't conflict with insert statement stackoverflow.com/questions/1666717/…

Collectives™ on Stack Overflow

inserting html string into sqlite db using objective c

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related