Can't save blobs with db.update_rows(...)

[mrimvo]

Using db.update_rows(...), saving blobs does not work.

Output is:

UPDATE users SET myblob=[PoolByteArray] WHERE uid = 4026253021;
SQL error: no such column: PoolByteArray

But I think it should be something like:

UPDATE users SET myblob=x'789c....' WHERE uid = 4026253021;

I can save blobs with db.insert_row(...) though. But updating blobs doesn't work.

---

As a side note, I also tried

db.query_with_bindings(
    "UPDATE users SET ?=? WHERE uid = ?;",
    ["myblob", PoolByteArray([1,2]), uid])

But in the log it says:

UPDATE users SET ?=? WHERE uid = ?;
SQL error: near "?": syntax error

So there seems to be another issue with query_with_bindings

[2shady4u]

The first issue is known and is a duplicate of #32
This will hopefully be fixed in the next update (whenever I have some free time 😱 )

Your side note is actually a lot more interesting as this is unexpected behaviour.... will test ASAP and get back to you

[2shady4u]

So I tested it somewhat and get the same error.
When not putting a "?" in for the data-column everything works perfectly.

I'll have to check, but I actually think you can't bind columns in this way.
So you'll have to change your query to this:

db.query_with_bindings("UPDATE users SET myblob=? WHERE uid = ?;", [PoolByteArray([1,2]), uid])

or if you still want the "myblob" value to be variable:

db.query_with_bindings("UPDATE users SET " + "myblob" +"=? WHERE uid = ?;", [PoolByteArray([1,2]), uid])

I'll have to check if there's anything in the documentation about this...

[2shady4u]

After a little bit of searching I found this:
http://sqlite.1065341.n5.nabble.com/Binding-a-column-name-td1045.html

So it's a SQLite restriction that applies here and not an issue of the plugin itself! 😅

[mrimvo]

Thank you so much, I wasn't aware of this at all. Using query_with_bindings, I should be able to update blobs, which makes the original issue less urgent. Thank you for investigating on that.

[2shady4u]

Can I close this issue then? Since this is a duplicate from #32 and the secondary issue is due to an SQLite restriction and doesn't directly relate to the Godot-SQLite plugin itself?

[mrimvo]

Yes you can close it, thank you :) Maybe add a hint to the docu about the secondary issue, for the next person stumbling over it 👍 Am 18. März 2021 23:43:09 schrieb Piet Bronders ***@***.***>:

…

Can I close this issue then? Since this is a duplicate from #32 and the secondary issue is due to an SQLite restriction and doesn't directly relate to the Godot-SQLite plugin itself? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or unsubscribe.

[2shady4u]

will do!