select query not getting value on latest android ->

[austin92jus]

Describe the bug On Android 12 version I tired you get db data by selecting query its throw error

Error: CapacitorSQLitePlugin: Protocol message contained an invalid tag (zero). at Object.cap.fromNative (home:414:32) at :1:18 but same commands working on lower version without problem

query SELECT * From task_list WHERE status != 'closed' order by updateAt desc limit 10 offset 0

[austin92jus]

(https://user-images.githubusercontent.com/55691899/159871297-83a67cf7-5c46-43e4-868b-250c92e7f251.png)

[jepiqueau]

@austin92jus can you share your table schema and some data Was it working on previous releases

[austin92jus]

{ name: "task_list", schema: [ { column: "id", value: "INTEGER PRIMARY KEY AUTOINCREMENT" }, { column: "_id", value: "TEXT UNIQUE NOT NULL" }, { column: "assignee", value: "INTEGER" }, { column: "approved", value: "INTEGER" }, { column: "cid", value: "TEXT" }, { column: "createAt", value: "TEXT" }, { column: "updateAt", value: "TEXT" }, { column: "desc", value: "TEXT" }, { column: "oid", value: "INTEGER" }, { column: "files", value: "TEXT" }, { column: "status", value: "TEXT NOT NULL" }, { column: "title", value: "TEXT" }, { column: "type", value: "TEXT" }, { column: "isBusiness", value: "INTEGER" }, { column: "unreadCount", value: "INTEGER" }, { column: "category", value: "TEXT" }, { column: "aCustomer", value: "TEXT" }, { column: "fCustomer", value: "TEXT" }, { column: "du", value: "TEXT" }, { column: "aWorkType", value: "TEXT" }, { column: "fWorkType", value: "TEXT" }, { column: "last", value: "TEXT" }, { column: "delivers", value: "TEXT" }, { column: "reads", value: "TEXT" }, { column: "cInfo", value: "TEXT" }, { column: "last_modified",value: "INTEGER DEFAULT (strftime('%s', 'now'))" }

        ],
        indexes: [
            { name: "index_id", value: "cid,oid" },
            { name: "index_last_modified", value: "updateAt DESC" },
            { name: "index_cust", value: "aCustomer,fCustomer" }
        ]
    },

[austin92jus]

select query is working on most of phone but in Samsung latest android version 12 phone not getting work its throw error

[austin92jus]

return new Promise((resolve, reject) => { const a = this.sqlitePlugin.query({ database: this.dbName, statement: stat, values: params }).then(res => { console.log('result of execution', res) resolve(res.values) }).catch(err => { console.log(err, ' error on fetching data') reject('Error On fetching data'); }) });

this is my function I used statement I passed :=> "select * from task_list"

[austin92jus]

any updates on any side?

[jepiqueau]

@austin92jus Can you provide some data the way you did enter them

[austin92jus]

"INSERT INTO task_list (delivers,reads,status,files,_id,title,du,cid,assignee,oid,type,createAt,updateAt,cInfo,last,category ) VALUES ('[]','[]','progress','[]','60857f2bd04e5800ce4720b7','test group updat','2021-04-25T14:39:20.220Z','60616972bf1ab71fef2926eb',91XXXX3,91XXXXX08,'task','2021-04-25T14:39:39.779Z','2021-10-19T11:08:50.271Z','{"91XXX8":0,"919XXX6":9,"91XXXXX33":0}','{"sender":91XXXX73,"msg":"here"}','Work') ON CONFLICT (_id) DO UPDATE SET delivers='[]',reads='[]',status='progress',files='[]',_id='60857f2bd04e5800ce4720b7',title='test group updat',du='2021-04-25T14:39:20.220Z',cid='60616972bf1ab71fef2926eb',assignee=91XXXXX73,oid=918056598408,type='task',createAt='2021-04-25T14:39:39.779Z',updateAt='2021-10-19T11:08:50.271Z',cInfo='{"91XXXXX80":0,"91XXXXXX16":9,"918012430333":0}',last='{"sender":91XXXXX73,"msg":"here"}',category='Work' "

this query i used to get the result SELECT * From task_list WHERE status != 'closed' order by updateAt desc limit 10 offset 0

[jepiqueau]

@austin92jus i am going to look at it now to see if i can reproduce it thanks

[jepiqueau]

@austin92jus seems you have put some XXX to hide something but this does not work for integer

[austin92jus]

its integer i hide some value its privacy number of my application so I hide it

[austin92jus]

actual query does'nt have XXX its replaced of integer

[jepiqueau]

@austin92jus i have a Samsung S20FE 5G Android 12 and it is working fine. Have you completely deleted the folder android and run npx cap add android and all the other commands

[jepiqueau]

@austin92jus Can you share the db.service.ts file

[austin92jus]

@austin92jus i have a Samsung S20FE 5G Android 12 and it is working fine. Have you completely deleted the folder android and run npx cap add android and all the other commands

yes I did ,.. still have a issues

[austin92jus]

@austin92jus Can you share the db.service.ts file

import { Injectable } from "@angular/core"; import { CapacitorSQLite, CapacitorSQLitePlugin, SQLiteConnection, SQLiteDBConnection } from "@capacitor-community/sqlite"; import { BehaviorSubject } from "rxjs"; import { environment } from "src/environments/environment"; import { getPlatform } from "../helper/utility-helper"; import { dataToImport } from "./db_import"; //import "rxjs/add/operator/map";

/* Generated class for the DbProvider provider.

See https://angular.io/docs/ts/latest/guide/dependency-injection.html for more info on providers and Angular 2 DI. */ export type TABLES = keyof { conv_list?: string, task_list: string, message: string, config: string, task_history: string } const win: any = window;

@Injectable({ providedIn: 'root' }) export class DbService {

private _dbPromise: Promise;

sqlite: SQLiteConnection; isService: boolean = false; platform: string; sqlitePlugin: CapacitorSQLitePlugin; native: boolean = false; dbReady = new BehaviorSubject(false); dbName = environment.dbName; db: SQLiteDBConnection = {} as SQLiteDBConnection; constructor() { }

initializePlugin(): Promise { return new Promise(async resolve => { this.platform = getPlatform(); if (this.platform === 'ios' || this.platform === 'android') this.native = true; this.sqlitePlugin = CapacitorSQLite; //this.sqlitePlugin.requestPermissions().then(res => { this.sqlite = new SQLiteConnection(this.sqlitePlugin); //await this.deleteOldDatabases(); //return; const res = await this.sqlite.checkConnectionsConsistency(); console.log('database is present', res) if (res.result) { this.dbReady.next(true); } else { this.setupDatabase(); } this.isService = true; console.log('database permissions') resolve(true); }); }

private async setupDatabase() { this.sqlitePlugin.createConnection({ database: this.dbName, encrypted: false, version: 2 }).then(res => { console.log('database are ready', res); this.sqlitePlugin.open({ database: this.dbName }) //this.db = res; //this.db.open(); this.importInitalDB(); }) }

private importInitalDB() { this.sqlitePlugin.isTableExists({ database: this.dbName, table: 'config' }).then(async res => { console.log('is table result', res) if (!res.result) { console.log('import database',) const result = await this.sqlite.isJsonValid(JSON.stringify(dataToImport)); if (!result.result) { console.log('error database', res) return Promise.reject(new Error("IsJsonValid failed")); } this.sqlite.importFromJson(JSON.stringify(dataToImport)).then(res => { console.log('result for impoty', res) this.dbReady.next(true); }); } else { this.dbReady.next(true); } }); } async deleteOldDatabases(folderPath?: string): Promise { if (!this.native) { return Promise.reject(new Error(Not implemented for ${this.platform} platform)); } if (this.sqlite != null) { try { const path: string = folderPath ? folderPath : "default"; const db = await this.sqlitePlugin.deleteDatabase({ database: this.dbName }) console.log('deleted database', db) return Promise.resolve(db); } catch (err) { return Promise.reject(new Error(err)); } } else { return Promise.reject(new Error(no connection open)); } }

// Initialize the DB with our required tables _tryInit(drop = false) { if (drop) { // this.dropTable(TABLES.comments); // this.dropTable(TABLES.conversation); // this.dropTable(TABLES.task); } }

insert(table: TABLES, newObject): Promise { return new Promise((resolve, reject) => { // this.excuteQuery('SELECT * FROM task_list'); console.log(newObject); const val = this.getFieldValuesStr(newObject); const nameField = this.getFieldNamesStr(newObject); const query = INSERT INTO ${table} (${nameField} ) VALUES (${val}) ON CONFLICT (_id) DO UPDATE SET ${this.getFieldSetNamesStr(newObject)};

  console.log('call insert', query)
  this.excuteQuery(query, []).then(res => {
    console.log('task , res upda', res)
    resolve(res);
  }).catch(err => {
    console.log(err, 'error')
    reject(err);
  });
});

//return this.db.query('REPLACE INTO ' + table + ' (' + this.getFieldNamesStr(newObject)
//+ ') VALUES (' + this.getFieldValuesStr(newObject) + ")");

}

update(table: TABLES, object, where: string): Promise { const query = 'UPDATE ' + table + ' SET ' + this.getFieldSetNamesStr(object) + ' WHERE ' + where return new Promise((resolve, reject) => { this.excuteQuery(query, []).then(res => { console.log('result of update', res) resolve(res); }).catch(err => { console.log(err, 'error') reject(err); }); }); }

delete(table: TABLES, where: string): Promise { const query = 'DELETE FROM ' + table + ' WHERE ' + where return new Promise((resolve, reject) => { this.excuteQuery(query, []).then(res => { console.log('result of delete', res) resolve(res); }).catch(err => { console.log(err, 'error') reject(err); }); }); }

private dropTable(table: TABLES) { this.query("DROP TABLE " + table ).catch(err => { console.error('Storage: Unable to create initial storage User table', err.tx, err.err); }); }

async excuteQuery(stat: string, params: any = []): Promise { return new Promise((resolve, reject) => { const a = this.sqlitePlugin.query({ database: this.dbName, statement: stat, values: params }).then(res => { console.log('result of execution', res) resolve(res.values) }).catch(err => { console.log(err, ' error on fetching data') reject('Error On fetching data'); }) }); }

getList(table: TABLES, where: string): Promise { const query = 'SELECT * FROM ' + table + ' WHERE ' + where return new Promise((resolve, reject) => { this.excuteQuery(query, []).then(res => { console.log('result of select', res) resolve(res); }).catch(err => { console.log(err, ' db error') reject(err); }); });

//return this.query('SELECT * FROM ' + TABLES[table]).then(data => {
// if (data.res.rows.length > 0) {
//   console.log('Rows found.');
//   if (this.platform.is('cordova') && win.sqlitePlugin) {
//     let result = [];

//     for (let i = 0; i < data.res.rows.length; i++) {
//       const row = data.res.rows.item(i);
//       result.push(row);
//     }
//     return result;
//   }
//   else {
//     return data.res.rows;
//   }
// }
// });

}

private getFieldNamesStr(newObject) { const dat: Array = Object.keys(newObject); const index = dat.indexOf('__v') if (index > -1) { dat.splice(index, 1); } return dat.join(','); }

private getFieldValuesStr(object) { const dat: Array = []; Object.keys(object).forEach(a => { if (a != 'v') { const val = typeof object[a] == 'object' || Array.isArray(object[a]) ? '${JSON.stringify(object[a])}' : typeof object[a] == 'string' ? '${object[a]}' : object[a]; dat.push(val); } }) // const index = dat.indexOf('v') // if (index > -1) { // dat.splice(index, 1); // } return dat.join(','); let fields: any = '';

for (let f in object) {
  const val = typeof object[f] == 'object' ? JSON.stringify(object[f]) : object[f];
  fields += '\"' + val + '\",';
}
fields = fields.substr(0, fields.length - 1);
return fields;

}

// updateByValue(object, table: TABLES): Promise { // return this.query('UPDATE ' + TABLES[table] + ' SET ' + this.getFieldSetNamesStr(object) + ' WHERE id=?', // this.getFieldValuesArray(object)); // }

private getFieldSetNamesStr(object) { const skipKey = ["__v"]; let fields: Array = []; Object.keys(object).forEach((a: any) => { if (!skipKey.includes(a)) { const val = typeof object[a] == 'object' || Array.isArray(object[a]) ? '${JSON.stringify(object[a])}' : typeof object[a] == 'string' ? '${object[a]}' : object[a]; fields.push(a + "=" + val); } }) //fields = fields.slice(0, - 1); return fields.join(','); }

private getFieldValuesArray(object) { return Object.values(object); }

// delete(table: TABLES, object): Promise { // let query = "DELETE FROM " + TABLES[table] + " WHERE id=?"; // return this.query(query, object.id); // }

query(query: string, params: any[] = []): Promise { return new Promise((resolve, reject) => { try { this._dbPromise.then(db => { db.transaction((tx: any) => { tx.executeSql(query, params, (tx: any, res: any) => resolve({ tx: tx, res: res }), (tx: any, err: any) => reject({ tx: tx, err: err })); }, (err: any) => reject({ err: err })); }); } catch (err) { reject({ err: err }); } }); } }

[jepiqueau]

@austin92jus is your database encrypted? Are you using biometrics?

[austin92jus]

not encrypted not using biometrics

[jepiqueau]

@austin92jus Do you have Android Studio if yes can you try debug app with a break point

• in line 887 of CapacitorSQLitePlugin.java
• in line 764 of CapacitorSQLite.java
• in line 488 of Database.java in the capacitor-community/sqlite
• and move down line by line to see where the error is

[austin92jus]

thank you let me try to get

[jepiqueau]

@austin92jus Add also a break point in line 89 of RetHandler.java

[jepiqueau]

@austin92jus Any progress on your side?

[austin92jus]

yes i'm trying to reproduce the issues and debug

[jepiqueau]

@austin92jus Any progress on this otherwise i will close the issue

[austin92jus]

@jepiqueau thank you for your reply.. now its working fine