bug: executeSet does not support blobs

[wrzr123]

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

[robingenz]

Thanks for creating this issue. Feel free to submit a PR for this.

[wrzr123]

Alright, I created a PR: https://github.com/wrzr123/sqlite/compare/master...fix-issue%23642

This fixes half of the executeSet function for blobs. There are 2 ways how executeSet can be used:

const statement = `INSERT INTO ${tableName} (id, blob) VALUES (?, ?);`;
const values = [[1, myBlob1], [2, myBlob2]]

// 1st way:
const result = await this.db.executeSet([{statement, values}], true);

// 2nd way:
const result = await this.db.executeSet([{statement, values: values[0], [{statement, values: values[1]}], true);

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.