Repository navigation
bug: executeSet does not support blobs #642
Description
Plugin version:
"@capacitor-community/sqlite": "^7.0.0"
Platform(s):
Just tested on Android, don't know about iOS and Electron.
Current behavior:
Let's assume the following code:
const statement = `INSERT INTO ${tableName} (id, blob) VALUES (?, ?);`;
const values = [[1, myBlob1], [2, myBlob2]]
const result = await this.db.executeSet([{statement, values}], true);
This results in a parsing error ("unexpected character '{').
My blobs are of type Buffer. Those work fine with the run command, but if I want to mass insert data with executeSet, they don't work.
Expected behavior:
Blobs can be used in executeSet as well.
Steps to reproduce:
See above
Related code:
I debugged the plugin, and I found out that in the code of the "run" statement the method "objectJSArrayToArrayList" from the file "UtilsSQLite.java" is called. This method is recognizing objects of type "Buffer" and is parsing the data from it.
This method is not called during "executeSet".
Other information:
Are blobs of type Buffer intentionally not supported or did so far just nobody need this yet? Or should I use executeSet somehow differently to make it work?
Capacitor doctor:
Capacitor Doctor
Latest Dependencies:
@capacitor/cli: 7.2.0
@capacitor/core: 7.2.0
@capacitor/android: 7.2.0
@capacitor/ios: 7.2.0
Installed Dependencies:
@capacitor/cli: 7.0.1
@capacitor/android: 7.0.1
@capacitor/ios: 7.0.1
@capacitor/core: 7.0.1
[success] Android looking great! 👌
[error] Xcode is not installed
Activity
robingenz commentedon May 21, 2025
Thanks for creating this issue. Feel free to submit a PR for this.
wrzr123 commentedon May 22, 2025
Alright, I created a PR:
wrzr123/sqlite@master...fix-issue#642
This fixes half of the executeSet function for blobs. There are 2 ways how executeSet can be used:
The 2nd way is now handling blobs correctly. This was easy to implement, because in the 2nd way executeSet is binding values the normal way and there was just some logic needed to transform the Buffer object into a byte array.
For the 1st way a bigger refactoring would be needed. Here the values are turned into string literals and attached to the SQL statement. This then needs to be parsed, and it seems like there is no way to parse blobs into valid SQL syntax.
Therefore some logic would be needed to detect blobs in advance. Then way 1 could be somehow transferred into way 2.
For me it's fine with the fix I implemented. But somewhere this behavior should be documented.
folsze commentedon Jun 5, 2026
@wrzr123
Congrats on finding a fix. I will create an active PR to implement the "2nd way", I tested your suggested fix and it works.
Btw also tested: on iOS it all worked before already, this issue is only present on android
For anyone else reading this, for buffers I found this page useful for usage of blobs:
https://github.com/capacitor-community/sqlite/blob/master/docs/SQLiteBlob.md
About the "1st way":
Imo the "1st way" you described is not valid sql syntax, since you are providing more values than there are question marks in the statement. I think it hence should not work. But this is not the reason why it fails, cause in the end the behavior of the plugin is that it replaces the single (?, ?) with 4 values and hence correctly handles the valid statement.
After debugging I realized if we add
(?, ?), (?, ?)to the end of the statement, this actually leads to the second pair of ? not being replaced, so the "wrong" usage is actually "correct".So my first question would be:
how did you find out how to use executeSet in the way you did, without adding another pair of (?, ?) to the end and it still working?
I think we should just make users use the "2nd way" of executeSet, since that's what is more logical based on the method signature, where set is an array for a reason:
So for blobs, we simply do not support "multipleRowsStatements", since the "1st way" could as well be done with
db.run(stmt, values)as far as I know and have done in my app as well.The "1st way" you described is weird in both its usage (not having to provide two pairs of question marks) and its implementation is even weirder and fails.
Btw your "2nd way" had two typos. This is how it should be spelled:
const result = await this.db.executeSet([ {statement, values: values[0]}, {statement, values: values[1]} ], true);Note: I tried to have this be my first contribution to open source, so let me know if I missed sth.
folsze commentedon Jun 5, 2026
PR here:
https://github.com/capacitor-community/sqlite/pull/692/changes
capacitor-community#642: only apply fix to Buffer objects
capacitor-community#642: run prettier