capacitor-community
i am using latest 5.6.2
i have migrating old corodova plugin to this capacitor plugin.
I am importing api records in to my phone db (sql lite) . The plugin wors perfectly in web edition.
But not working in real device as well as emulator
my code
async importAccounts(accounts: IAccountResponse[]) {
let statements: any=[];
let lists=[];
const query1= 'DELETE FROM accounts';
const query2 = 'INSERT INTO accounts (ain,cin,.......,closingBalanceDate,lastTransactionDate) VALUES (?, ?, ?, ? ,? , ?, ?, ?, ? , ?, ?, ?, ? ,? ,?, ?, ? ,? ,?,?,?,?,?)';
const query3 = 'DELETE from logs WHERE id = ?';
statements.push({statement: query1, values: []});
for(let i=0;i<accounts.length;i++){
lists.push([accounts[i].ain,accounts[i].cin,,,,,,,,,,,accounts[i].closingBalanceDate,accounts[i].lastTransactionDate]);
}
statements.push({statement: query2, values: lists});
statements.push({statement: query3, values: [1]}); //1- Download
await this.db.executeSet(statements,true);
await this.loadAccounts();
await this.loadLogs();
The obove code works perfecly in browser
But i am getting below error
2024-03-11 16:49:23.973 6374-6374 Capacitor/Plugin io.ionic.starter V To native (Capacitor plugin): callbackId: 68811029, pluginId: CapacitorSQLite, methodName: query 2024-03-11 16:49:23.973 6374-6374 Capacitor io.ionic.starter V callback: 68811029, pluginId: CapacitorSQLite, methodName: query, methodData: {"database":"corepigmy","statement":"SELECT id,lastUpdatedDate FROM logs;","values":[],"readonly":false,"isSQL92":true} 2024-03-11 16:49:24.066 6374-6374 Capacitor/Console io.ionic.starter I File: https://localhost/main.65ba506cbb0b69b3.js - Line 1 - Msg: >>> Log: [{"id":1,"lastUpdatedDate":"2024-03-11T11:19:23.636Z"}] 2024-03-11 16:49:24.582 6374-6403 JavaBinder io.ionic.starter E !!! FAILED BINDER TRANSACTION !!! (parcel size = 532) 2024-03-11 16:49:24.583 6374-6403 GmsClient io.ionic.starter W IGmsServiceBroker.getService failed android.os.DeadObjectException: Transaction failed on small parcel; remote process probably died, but this could also be caused by running out of binder buffe at android.os.BinderProxy.transactNative(Native Method) at android.os.BinderProxy.transact(BinderProxy.java:584) at FH.b(chromium-TrichromeWebViewGoogle6432.apk-stable-506007137:288) at com.google.android.gms.common.internal.BaseGmsClient.p(chromium-TrichromeWebViewGoogle6432.apk-stable-506007137:53) at lE.run(chromium-TrichromeWebViewGoogle6432.apk-stable-506007137:1852) at android.os.Handler.handleCallback(Handler.java:942) at android.os.Handler.dispatchMessage(Handler.java:99) at Wv0.dispatchMessage(chromium-TrichromeWebViewGoogle6432.apk-stable-506007137:223) at android.os.Looper.loopOnce(Looper.java:201) at android.os.Looper.loop(Looper.java:288) at android.os.HandlerThread.run(HandlerThread.java:67) 2024-03-11 16:49:24.684 6374-6394 EGL_emulation io.ionic.starter D app_time_stats: avg=11.25ms min=6.94ms max=41.40ms count=36 2024-03-11 16:49:25.704 6374-6394 EGL_emulation io.ionic.starter D app_time_stats: avg=10.37ms min=7.67ms max=22.45ms count=36 2024-03-11 16:49:26.436 6374-6374 Capacitor/Plugin io.ionic.starter V To native (Capacitor plugin): callbackId: 68811030, pluginId: CapacitorSQLite, methodName: executeSet 2024-03-11 16:49:26.440 6374-6374 Capacitor io.ionic.starter V callback: 68811030, pluginId: CapacitorSQLite, methodName: executeSet, methodData: {"database":"corepigmy","set":[{"statement":"DELETE FROM accounts;","values":[]},{"statement":"INSERT INTO accounts (ain,cin,openingDate,balance,customerName,schemeCode,categoryCode,phoneNo,email,bankName,bankCode,branchName,branchCode,bankRecieptName,branchRecieptName,lin,loanAmount,accountName,isLoan,nain,groupNo,closingBalanceDate,lastTransactionDate) VALUES (?, ?, ?, ? ,? , ?, ?, ?, ? , ?, ?, ?, ? ,? ,?, ?, ? ,? ,?,?,?,?,?);","values":[["000000000000338","01401000338","2023-04-05T18:30:00Z",28030,"SAYAD SAHIL","12","","eeeee",null,"Manipal","Demo","Demo Society",null,"Samaja Seva S S","Demo",null,0,null,0,null,null,"2024-02-26T18:30:00Z","2024-02-25T18:30:00Z"],["000000000000340","01401000340","2023-04-05T18:30:00Z",3900,"RANJITH R MOOLYA","12","","9036986909",null,"Manipal","Demo","Demo Society",null,"Samaja Seva S
After checking log i am removed Not null constrant in table ( but actyally data coming to in api ) to test i have removed not null constrint in table schema and again i have run on emulator then only 1 record insert to account table
Please let me author please let me know the issue .. in browser working perfecly...i am using exact same as your demo application create tg eschema.. Is looping I have missed any thing …
@lajith111 what is the length of accounts and how do you execute the statements
As of now less than 100 but in producttion may be 3k to 5 And columns may be 20
Sory missed main thing..
await this.db.executeSet(statements,true);
await this.loadAccounts();
await this.loadLogs();
i need to do transaction for executeset is am doing correct ?
I have to execute in a order query1 query 2 query 3 within single transaction ( in query 2 has multiple records to insert account table)
earlier plugin we use transaction extenstion function which is quite eazy
I think issue with plugin itself or please let me know what I am doing or any alternative solution
EDIT : just now checked exept id column all columns stored as null in (1 row Created)
Table:
CREATE TABLE IF NOT EXISTS accounts(id INTEGER PRIMARY KEY AUTOINCREMENT,ain TEXT , ...
@lajith111 in v5.6.3 i have added a SQLite Transactions Documentation SQLiteTransaction.md and make few fixes. Can you share the schema of the table accounts
@lajith111 in v5.6.3 i have added a SQLite Transactions Documentation SQLiteTransaction.md and make few fixes. Can you share the schema of the table accounts
Please find the schema ..
CREATE TABLE IF NOT EXISTS accounts(id INTEGER PRIMARY KEY AUTOINCREMENT,ain TEXT NOT NULL,customerName TEXT, openingDate TEXT, balance NUMERIC NOT NULL, cin TEXT ,schemeCode TEXT,categoryCode TEXT,phoneNo TEXT,email TEXT,bankName TEXT,bankCode TEXT,bankRecieptName TEXT,branchName TEXT,branchCode,branchRecieptName TEXT,bin TEXT,lin TEXT,loanAmount NUMERIC,accountName TEXT,isLoan INT NOT NULL,nain TEXT,groupNo TEXT,closingBalanceDate TEXT,lastTransactionDate TEXT);
`CREATE TABLE IF NOT EXISTS logs(id INTEGER PRIMARY KEY, lastUpdatedDate TEXT NOT NULL);
const query2 = 'INSERT INTO accounts (ain,cin,openingDate,balance,customerName,schemeCode,categoryCode,phoneNo,email,bankName,bankCode,branchName,branchCode,bankRecieptName,branchRecieptName,lin,loanAmount,accountName,isLoan,nain,groupNo,closingBalanceDate,lastTransactionDate) VALUES (?, ?, ?, ? ,? , ?, ?, ?, ? , ?, ?, ?, ? ,? ,?, ?, ? ,? ,?,?,?,?,?)';
Edit: Same issue exist in 5.6.3.
@lajith111 i take your tables schema and did this
const testIssue523 = async(db: SQLiteDBConnection) => {
setLog(prevLog => prevLog + '### Start Test Transaction Issue 523 ###\n');
if (db !== null) {
const txn: any[] = [];
txn.push({statement:'DELETE FROM accounts;'});
const stmt523 = `INSERT INTO accounts
(ain,cin,openingDate,balance,customerName,schemeCode,categoryCode,phoneNo,
email,bankName,bankCode,branchName,branchCode,bankRecieptName,branchRecieptName,bin,
lin,loanAmount,accountName,isLoan,nain,groupNo,closingBalanceDate,lastTransactionDate)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);`;
const values =
["000000000000338","01401000338","2023-04-05T18:30:00Z",28030,"SAYAD SAHIL","12","","9036986909",
null,"Manipal","Demo","Demo Society",null,"Samaja Seva S S","Demo",null,
null,0,null,0,null,null,"2024-02-26T18:30:00Z","2024-02-25T18:30:00Z"];
txn.push({statement: stmt523, values: values});
for (let i=1; i<100; i++ ) {
const val = getRandomNumber(100, 999);
values[0] = `000000000000${val}`;
values[1] = `01401000${val}`;
values[3] = getRandomNumber(100, 100000);
values[4] = generateRandomName("XXXXX XXXXX");
values[7] = (getRandomNumber(9000000000, 9999999999)).toString();
txn.push({statement: stmt523, values: values});
}
try {
const ret = await db.executeTransaction(txn);
console.log(`Test Issue523 Transaction ret: ${JSON.stringify(ret)}`);
setLog(prevLog => prevLog + '### Test Issue523 successfull###\n');
} catch(err:any) {
const msg = err.message ? err.message : err;
console.log(`Test Issue523 Transaction msg: ${msg}`)
setLog(prevLog => prevLog + `### Test Issue523 failed : ${msg} ###\n`);
} finally {
let selectQuery = "SELECT * FROM accounts;";
const retQuery = await db.query(selectQuery);
console.log(`>>> query All retQuery4: ${JSON.stringify(retQuery)}`)
setLog(prevLog => prevLog + '### End Test Issue523 Transaction ###\n');
}
}
}
with
const getRandomNumber = (min: number, max: number): number => {
return Math.floor(Math.random() * (max - min + 1)) + min;
}
const generateRandomName = (pattern: string) => {
return pattern.replace(/X/g, () => String.fromCharCode(65 + Math.floor(Math.random() * 26)));
}
and it works fine on web, iOS and Android
@lajith111 your set of Data is too big if you want to use executeSet instead of executeTransaction you have to split in several {statement, values) where values can contain 10 rows of values. this is what the error means transaction failed on small parcel; remote process probably died, but this could also be caused by running out of binder buffer. You can make several trials to adjust the split by 10 ,15, 20 ,... till you get the error
@jepiqueau thanks for your support i have one doubt what is the diff b/w execute set and execute transaction?
I have tested ur code in my app ..its working...
i observed that in second query i am passing array of array but in ur example each row you adding txn
May be that is the issue..I will change my implenetation will update you thanks
EDit: sory disturb you in web i geting below error on catch .. . But data saved on db some ststement missing execute loadAccount(); but some time executed propery with no error
RollbackTransaction: RollbackTransaction: cannot rollback - no transaction is active
Edit 2
checked in emulator data saving perfectly But web only I am getting transaction error Any way thanks for the support
Finally I said earlier I passsig double array (!some where in this repo itself I saw array of array will work) to txn but I changed to same as yours now able to data saved on device thanks
@lajith111 i add this to the test like you did
const newValues = [];
for (let i=0; i<2000; i++ ) {
const val = getRandomNumber(400, 1500);
values[0] = `000000000000${val}`;
values[1] = `01401000${val}`;
values[3] = getRandomNumber(100, 100000);
values[4] = generateRandomName("XXXXX XXXXX");
values[7] = (getRandomNumber(9000000000, 9999999999)).toString();
newValues.push(values);
}
const stmtSet = [{statement: stmt523, values: newValues}];
const retSet = await db.executeSet(stmtSet,true);
console.log(`Test Issue523 executeSet retSet: ${JSON.stringify(retSet)}`);
setLog(prevLog => prevLog + '### Test Issue523 successfull###\n');
i tested from 100 to 2000 by step 100. and it works. i do not know what is the accounts.length in your case. They will be certainly a limit for a statement. Would be interesting to know why you get that error in Web Have you check your statements can you share your app on github and explain me when it failed.
@lajith111 sorry when i test to 2000 the code is
...
for (let i=0; i<2000; i++ ) {
const val = getRandomNumber(400, 1500);
....
@jepiqueau i tested again restart pc & tested. First time i click to import from ui then no error but second time i got error like below in web
Error: Error: RollbackTransaction: RollbackTransaction: cannot rollback - no transaction is active
at web.js:165:19
at Generator.throw (<anonymous>)
at asyncGeneratorStep (asyncToGenerator.js:3:1)
at _throw (asyncToGenerator.js:25:1)
at _ZoneDelegate.invoke (zone.js:368:26)
at Object.onInvoke (core.mjs:14223:33)
at _ZoneDelegate.invoke (zone.js:367:52)
at Zone.run (zone.js:130:43)
at zone.js:1260:36
at _ZoneDelegate.invokeTask (zone.js:403:31)
This is process i am doing in component:
await this.dbService.initiateLog(1,dt); //1- Log insert
const results = this.accountService.downloadAccountsByAgent(); // get the data from API
let result= await lastValueFrom(results);
await Promise.all( [this.storage.store(AUTH_CONSTANTS.COLLECTION_START_KEY, result.collectionStart),
this.storage.store(AUTH_CONSTANTS.COLLECTION_END_KEY, result.collectionEnd)
]); //this is not SQL Lite
await this.dbService.importAccountsback(accounts); // this will call to db serviece with 3 sql statement like delete from
account, insert list to account table and 3 sql remove log entry insert from intiate log
Intiated Log method
async initiateLog(id: number,dt: string) {
try{
const query = 'INSERT INTO logs (id,lastUpdatedDate) VALUES (?,?) ON CONFLICT(id) DO UPDATE SET lastUpdatedDate=?;';
await this.db.run(query,[id,dt,dt]);
this.alertService.presentAlert("Log","Log insert");
await this.loadLogs();
}
catch(err)
{
this.toastService.presentErrorToast(err);
console.log(err)
}
Edit: i replaced with your code i get no error but in my code only differece id i dowload accounts from API which has 93 records.
Can you share the whole code on github so i can test it and identify the issue
I have keep neccessary things publish to https://github.com/lajith111/sqllitedemo/tree/master
I am new to github..Please check app & offline componet is used for sql lite
@jepiqueau can u please let me I thery any wrong in my code please let me know till now I am not migrated.. if any thing wrong difficult to get the data in the device .. pls advice
@jepiqueau Is it possible to do like in SQL lite where nama_product regexp '(^| )aya';
I asked question in https://stackoverflow.com/questions/78083418/sql-like-with-each-word-starting-charcter
But its works in Mysql not in SQL lite becuse regexp not in Sql lite Can Pls help on this if possible?
@lajith111 i forked your code as there is no readme can you explain me how to use the application and how to reproduce the error
@lajith111 on which operating system (MacOs, Windows, Linux) and which brower are you testing your app
@lajith111 on which operating system (MacOs, Windows, Linux) and which brower are you testing your app
Hi I Have removed all unnessery things
- ionic s
- Click Login Button
- It redirect to offline Page
- Click to Download button, It will Download dummy records to Sql lite Db
- After download completes delete button will enable, you can delete records of Accounts.
- Then Click to Download button Exception message in web ( console) but records will be saved
Thanks
@lajith111 i do not understand 5 what do you mean by You can delete in the Same place how you do this. I assume the error will come in 6 when i Click to Download the second time
I dont see your Previous message.
i Am using windows 11& MS Edge Latest version
After download delete button will enable & after delete download button will enable
Yes.. error come in Step 6.
@lajith111 When you said delete button is in fact the same than the download button i could not see a specific delete button
Sory for my bad english, for 2nd time download you have to delete the previous records downloaded (step 5)
Step 6 for 2nd time donwload
error Some time come in step 4 & step 6
@lajith111 When you said delete button is in fact the same than the download button i could not see a specific delete button
I think some bug in my app, but if Log table has rows (it will saved on initiateLog ) then delete button will show (tranCount not using & also not required)
<ion-fab vertical="center" horizontal="center" translucent="true" slot="fixed" *ngIf="currentSegment==1 && !tranCount && ( isDownload || !accLogExist)">
<ion-fab-button color="primary" (click)="!isDownload && downloadAccounts()">
<ion-spinner *ngIf="isDownload"></ion-spinner>
<ion-icon name="cloud-download" *ngIf="!isDownload"></ion-icon>
</ion-fab-button>
</ion-fab>
<ion-fab vertical="center" horizontal="center" translucent slot="fixed" *ngIf="currentSegment==1 && !tranCount && (accLogExist || accCount) && !isDownload">
<ion-fab-button color="danger" (click)="!isClear && clearAccounts()">
<ion-spinner *ngIf="isClear"></ion-spinner>
<ion-icon name="trash" *ngIf="!isClear"></ion-icon>
</ion-fab-button>
</ion-fab>
@lajith111 Sorry i forgot to publish the write jeep-sqlite so do
npm uninstall --save @capacitor-community/sqlite jeep-sqlite
npm i --save @capacitor-community/sqlite@latest
check that in node_modules/jeep-sqlite the version is 2.6.2
Now it should work
Thanks @jepiqueau i think issue now resolved...
One more help i need is regexp can be used in this plugin...I dont get cli edition..Pls le me knw
@jepiqueau some time time when refresh the page in browser account table data auto deleted...
I am getting Error in transaction method run when i call below method in web
const id=2; //2- Transaction Sync
const query1 = DELETE FROM transactions;;
const query2 = DELETE FROM accounts;;
const query3 = DELETE from logs WHERE id = ?;;
const transaction: any = [ {statement: query1,values: []}, {statement: query2,values: []}, {statement: query3,values: [id]} ]
try{ let res= await this.db.executeTransaction(transaction); await this.loadAccounts(); await this.loadTransactions(); await this.loadLogs(); } catch(err:any) { console.log(err.message ? err.message : err); this.toastService.presentErrorToast(err.message ? err.message : err); } finally {
}
@lajith111 you must saveToStore before the refresh. For the others questions look at your code it may be something wrong.
For the regexp give me an example i am not sure i can implement it as the regexp is not handle the same in differents plaforms
create table person(
name varchar(20)
);
insert into person values
('ayam bakar'),
('daging ayam'),
('bayam hijau'),
('daun bayam');
i have table person. In that name details are stored in single column( firstname + " " + lastname)
i need to search first/last name starts with 'aya'
Expected Result:
**_name_**
ayam bakar
daging ayam
In my sql Same can be achieve using below thing
select name
from person
where name regexp '(^| )aya';
I checked in sql lite but it seems not working.Thanks