Sqlite3 inserting BLOBs instead of text

Question

I have an ACTIVITY entity that has been created this way:

CREATE TABLE ACTIVITY (ID INTEGER PRIMARY KEY, TYPE TEXT, DESCRIPTION TEXT);

I'm trying to save an Activity object in the Sqlite3 database this way:

func save(database:COpaquePointer) -> Int
{
    let id = getMaxActivityId(database) + 1;
    let insertQuery = "INSERT INTO ACTIVITY (TYPE, ID, DESCRIPTION) VALUES (?,?,?);"
    var statement:COpaquePointer = nil
    if sqlite3_prepare_v2(database, insertQuery, -1, &statement, nil) == SQLITE_OK
    {
        sqlite3_bind_text(statement, 1,type, -1, nil)
        sqlite3_bind_int(statement, 2, Int32(id))

        sqlite3_bind_text(statement, 3, description ?? "", -1, nil)
    }

    if sqlite3_step(statement) != SQLITE_DONE
    {
        let e = NSException(name: "Failed to insert Activity", reason: nil, userInfo: nil)
        e.raise()
    }
    sqlite3_finalize(statement)
    return id
}

The values are:

activity.type = "Walking"
activity.id = 12
activity.description = Optional(nil)

When retrieving the data it seems to be inconsistent, and if I try downloading the app container and checking the database, I can see that in the type field, a BLOB type is being inserted instead of a string:

If I try changing the type to "text" it shows this message:

The app I'm using is "Sqlite Browser" 3.8.0 for Os X.

The order of the keys in the INSERT statement is different from the order of the keys in the CREATE TABLE statement, but I don't assume that that matters. – It looks as if the first 4 records were created correctly, did you change something in between? — Martin R
– Martin R, Commented Jun 22, 2016 at 13:59
@MartinR Yes, the DESCRIPTION field was created afterwards (ALTER TABLE ACTIVITY ADD COLUMN DESCRIPTION TEXT). — Ramy Al Zuhouri
– Ramy Al Zuhouri, Commented Jun 22, 2016 at 16:38
Strange fact: if I don't bind the 3rd value (description), the type field gets inserted correctly. — Ramy Al Zuhouri
– Ramy Al Zuhouri, Commented Jun 22, 2016 at 16:59

Community · Accepted Answer · 2017-05-23 11:53:17Z

8

Later on I discovered that I should to save the string as UTF8, and not just a plain Swift string, otherwise I may get unwanted behaviours:

sqlite3_bind_int(statement, 1, Int32(id))
sqlite3_bind_text(statement, 2,(type as NSString).UTF8String, -1, nil)

let desc = (description ?? "") as NSString
sqlite3_bind_text(statement, 3, desc.UTF8String, -1, nil)

I've found the solution from this question.

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Jun 22, 2016 at 22:14

Ramy Al Zuhouri

22.1k27 gold badges113 silver badges198 bronze badges

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

1 Comment

Rob Over a year ago

BTW, you should use SQLITE_TRANSIENT for that last parameter to sqlite3_bind_text.

Collectives™ on Stack Overflow

Sqlite3 inserting BLOBs instead of text

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