0

I have a pre-populated test sqlite db with a table with blob column where png image is saved. I'm trying to query it in an ionic/angular/capacitor with @capacitor-community/sqlite platform running in Android phone.

private getConnection = () => {
    return new SQLiteConnection(CapacitorSQLite);
};

private getDb = async (conn: SQLiteConnection, dbName: string): Promise<SQLiteDBConnection> => {
    const db: SQLiteDBConnection = await conn.createConnection(
      dbName, false, 'no-encryption', 1, false
    );
    await db.open();
    return db;
};

private readTable = async (db: SQLiteDBConnection): Promise<DBSQLiteValues> => {
    console.log('home: readTable: entered');

    // returns "[B@e44e523" for img column instead of "iVBORw0..." or "data:image/png;base64,iVBORw0..." or somthing like that
    const stmt = `SELECT id, name, img FROM teach;`;
    console.log('home: readTable: stmt=', stmt);

    const ret: DBSQLiteValues = await db.query(stmt, []);
    console.log('home: readTable: ret=', ret);

    return ret;
};

async ngOnInit() {
    this.conn = this.getConnection();
    console.log('home: ngOnInit: conn=', this.conn);

    this.db = await this.getDb(this.conn, 'testdb');
    console.log('home: ngOnInit: db=', this.db);

    const rows: DBSQLiteValues = await this.readTable(this.db);
    console.log('home: ngOnInit: rows=', rows);
}

I also tried changing the query a bit:

    const stmt = `SELECT id, name, BASE64(img) AS img FROM teach;`;

but it returns error:

Query: in selectSQL cursor no such function: BASE64: , while compiling: SELECT id, name, BASE64(img) AS img FROM teach;
  • BTW, the pre-populated sqlite db was NOT created using @capacitor-community/sqlite.
  • It's copied manually using Android Studio to /data/data/io.ionic.starter/databases
  • Also, right now I'm exclusive testing on an Android 12 phone and no other platform/version.
  • Ref sample project to reproduce the issue is here, including the env setup.

UPDATE

I also added debug messages in java layer of the plugin and found that it receives the blob correctly (in logcat) from Android API.

com.getcapacitor.community.database.sqlite.SQLite.Database#selectSQL():

case FIELD_TYPE_BLOB:
    row.put(colName, c.getBlob(index));
    byte[] bArray = c.getBlob(index);
    Log.v(TAG, "=====>> BLOB: index=" + index + ", blob=" + bArray + ", len=" + bArray.length);
    Log.v(TAG, "=====>> BLOB: [0]=" + bArray[0]);
    Log.v(TAG, "=====>> BLOB: [1]=" + bArray[1]);
    Log.v(TAG, "=====>> BLOB: [2]=" + bArray[2]);
    Log.v(TAG, "=====>> BLOB: [3]=" + bArray[3]);
    Log.v(TAG, "=====>> BLOB: [4]=" + bArray[4]);
    Log.v(TAG, "=====>> BLOB: [5]=" + bArray[5]);
    Log.v(TAG, "=====>> BLOB: [6]=" + bArray[6]);
    Log.v(TAG, "=====>> BLOB: [7]=" + bArray[7]);
    break;

06-05 18:13:07.200 26234 26234 I Capacitor/Console: File: https://localhost/6997.a86a1992d81b27be.js - Line 1 - Msg: home: readTable: stmt= SELECT id, name, img FROM teach;
06-05 18:13:07.201 26234 26234 V Capacitor/Plugin: To native (Capacitor plugin): callbackId: 69203466, pluginId: CapacitorSQLite, methodName: query
06-05 18:13:07.201 26234 26234 V Capacitor: callback: 69203466, pluginId: CapacitorSQLite, methodName: query, methodData: {"database":"testdb","statement":"SELECT id, name, img FROM teach;","values":[],"readonly":false}
06-05 18:13:07.203 26234 26322 V com.getcapacitor.community.database.sqlite.SQLite.Database: =====>> INT: index=0, int=1
06-05 18:13:07.203 26234 26322 V com.getcapacitor.community.database.sqlite.SQLite.Database: =====>> STR: index=1, str=11:41:10 AM
06-05 18:13:07.203 26234 26322 V com.getcapacitor.community.database.sqlite.SQLite.Database: =====>> BLOB: index=2, blob=[B@61b1fd9, len=1741
06-05 18:13:07.203 26234 26322 V com.getcapacitor.community.database.sqlite.SQLite.Database: =====>> BLOB: [0]=-119
06-05 18:13:07.203 26234 26322 V com.getcapacitor.community.database.sqlite.SQLite.Database: =====>> BLOB: [1]=80
06-05 18:13:07.203 26234 26322 V com.getcapacitor.community.database.sqlite.SQLite.Database: =====>> BLOB: [2]=78
06-05 18:13:07.203 26234 26322 V com.getcapacitor.community.database.sqlite.SQLite.Database: =====>> BLOB: [3]=71
06-05 18:13:07.203 26234 26322 V com.getcapacitor.community.database.sqlite.SQLite.Database: =====>> BLOB: [4]=13
06-05 18:13:07.203 26234 26322 V com.getcapacitor.community.database.sqlite.SQLite.Database: =====>> BLOB: [5]=10
06-05 18:13:07.203 26234 26322 V com.getcapacitor.community.database.sqlite.SQLite.Database: =====>> BLOB: [6]=26
06-05 18:13:07.203 26234 26322 V com.getcapacitor.community.database.sqlite.SQLite.Database: =====>> BLOB: [7]=10

I also added debug messages in com.getcapacitor.community.database.sqlite.CapacitorSQLite#query() but for some reason this doesn't appear in logcat.

ArrayList<Object> arrValues = uSqlite.objectJSArrayToArrayList(values);
res = db.selectSQL(statement, arrValues);
Log.v(TAG, "----->> res=" + res.toString());

// no debug msg from CapacitorSQLite.java in logcat

I also added debug messages in com.getcapacitor.community.database.sqlite.CapacitorSQLitePlugin#query(). Here, I could see the values of INTEGER and TEXT columns correctly but not the BLOB column in logcat.

JSArray res = implementation.query(dbName, statement, values, readOnly);
Log.v(TAG, ".....>> res=" + res.get(0));
Log.v(TAG, ".....>> class =" + row.getClass().getName());
Log.v(TAG, ".....>> id=" + row.getInteger("id")); // returns correct value
Log.v(TAG, ".....>> name=" + row.getString("name")); // returns correct value
// Log.v(TAG, ".....>> name=" + row.getBlob("img")); // compiler error, no such method
JSObject bArray = row.getJSObject("img"); // returns null
Log.v(TAG, ".....>> img=" + bArray);


06-05 18:13:07.203 26234 26322 V com.getcapacitor.community.database.sqlite.CapacitorSQLitePlugin: .....>> res={"id":1,"name":"11:41:10 AM","img":"[B@816dd9e"}
06-05 18:13:07.204 26234 26322 V com.getcapacitor.community.database.sqlite.CapacitorSQLitePlugin: .....>> res={"id":1,"name":"11:41:10 AM","img":"[B@816dd9e"}
06-05 18:13:07.204 26234 26322 V com.getcapacitor.community.database.sqlite.CapacitorSQLitePlugin: .....>> class =com.getcapacitor.JSObject
06-05 18:13:07.204 26234 26322 V com.getcapacitor.community.database.sqlite.CapacitorSQLitePlugin: .....>> id=1
06-05 18:13:07.204 26234 26322 V com.getcapacitor.community.database.sqlite.CapacitorSQLitePlugin: .....>> name=11:41:10 AM
06-05 18:13:07.204 26234 26322 V com.getcapacitor.community.database.sqlite.CapacitorSQLitePlugin: .....>> img=null

expected "img":"iVBORw0..." (or something like that depending on the png image)

actual "img":"[B@816dd9e"

vd1
  • 11
  • 3

1 Answers1

0

I'll suggest to save img as encoded base64 string to retrieve data from sqlite.

But if you want to CRU(d) blob - try to use this plugin version - cordova-sqlite-ext (it's mentioned in docs).

As I understand blob is not supported by capacitor-community/sqlite.

Other option - save img as base64.

I'm using it too(capacitor-community/sqlite) - and I figured out by saving not base64 file but array buffer.

Hope it will help you ;)

Eugene_Kr
  • 109
  • 3
  • Thanks @Eugene_Kr. I already tried `cordova-sqlite-ext` plugin and it's working. Unfortunately, it doesn't support sqlcipher. Another cordova-sqlite plugin supports sqlcipher but not blob!. So, I had to switch to capacitor-community/sqlite plugin. Also, I get db from somewhere else and I don't have any choice but to query the blob that's in byte array and NOT in base64. I made some progress by making a change in capacitor-community/sqlite ([ref](https://github.com/capacitor-community/sqlite/issues/426)), but trying to figure out if there is a better way. – vd1 Jun 06 '23 at 22:59
  • https://stackoverflow.com/questions/16245767/creating-a-blob-from-a-base64-string-in-javascript#:~:text=The%20atob%20function%20will%20decode%20a%20Base64%2Dencoded%20string%20into%20a%20new%20string%20with%20a%20character%20for%20each%20byte%20of%20the%20binary%20data. Try this approach – Eugene_Kr Jun 07 '23 at 08:24