i am using latest 5.6.2

[lajith111]

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 …

[jepiqueau]

@lajith111 what is the length of accounts and how do you execute the statements

[lajith111]

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 , ...

[jepiqueau]

@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]

@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.

[jepiqueau]

@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

[jepiqueau]

@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

[lajith111]

@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

[jepiqueau]

@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.

[jepiqueau]

@lajith111 sorry when i test to 2000 the code is

      ...

      for (let i=0; i<2000; i++ ) {
        const val = getRandomNumber(400, 1500);

      ....

[lajith111]

@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.

[jepiqueau]

Can you share the whole code on github so i can test it and identify the issue

[lajith111]

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

[lajith111]

@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]

@lajith111 i will have a look may be during the week end be patient

[lajith111]

@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?

[jepiqueau]

@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

[jepiqueau]

@lajith111 on which operating system (MacOs, Windows, Linux) and which brower are you testing your app

[lajith111]

@lajith111 on which operating system (MacOs, Windows, Linux) and which brower are you testing your app

Hi I Have removed all unnessery things 1) ionic s 2) Click Login Button 3) It redirect to offline Page 4) Click to Download button, It will Download dummy records to Sql lite Db 5) After download completes delete button will enable, you can delete records of Accounts. 6) Then Click to Download button Exception message in web ( console) but records will be saved

Thanks

[jepiqueau]

@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

[lajith111]

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.

[jepiqueau]

@lajith111 When you said delete button is in fact the same than the download button i could not see a specific delete button

[lajith111]

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]

@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>

[jepiqueau]

@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

[lajith111]

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

[lajith111]

@jepiqueau some time time when refresh the page in browser account table data auto deleted...

[lajith111]

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 {

}

[jepiqueau]

@lajith111 you must saveToStore before the refresh. For the others questions look at your code it may be something wrong.

[jepiqueau]

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

[lajith111]

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

[jepiqueau]

@lajith111 Sorry the REGEXP calls a user defined function, and i cannot defined a user function which will be called by sqlite web, iOS or Android. So there is nothing i can do. i already answer earlier on user defined function . So i close the issue

[lajith111]

Thanks .. I am little confused sql lite supports regexp. Can I customize works only with android like that.. pls let me know

[jepiqueau]

@lajith111 response from ChatGPT

Yes, you can use regular expressions (regex) in SQLite's WHERE statement using the REGEXP operator. However, SQLite doesn't natively support regex out of the box like some other database systems do. Instead, you can use the REGEXP operator if you compile SQLite with the REGEXP extension enabled.

Here's how you can use the REGEXP operator in SQLite:

First, you need to ensure that SQLite is compiled with the REGEXP extension enabled. By default, SQLite does not have this enabled. You may need to compile SQLite from source with the appropriate flags, or use a version of SQLite that has this extension enabled.

Once you have SQLite with REGEXP enabled, you can use the REGEXP operator in your SELECT statements like this:

sql
Copy code
SELECT * FROM your_table WHERE your_column REGEXP 'your_regex_pattern';
Replace your_table with the name of your table and your_column with the name of the column you want to apply the regex pattern to. Replace 'your_regex_pattern' with your actual regular expression pattern.

Keep in mind that the specific syntax and functionality of regular expressions in SQLite may vary depending on the version of SQLite you're using and how it was compiled. So, always refer to the documentation or specific resources related to the version you're working with.

User

Is this statement will work also in sqlite android and iOS as the regexp expression write-up may vary from one platform to other?

ChatGPT

Using the REGEXP operator in SQLite for Android and iOS would depend on whether the version of SQLite used in those platforms has the REGEXP extension enabled.

By default, SQLite on Android and iOS does not have the REGEXP extension enabled. Therefore, you would need to compile SQLite with REGEXP support enabled or use a version of SQLite that has this extension already enabled.

For Android, if you're using the SQLite database directly through the Android SDK, you would typically use the built-in SQLite version provided by Android. In this case, you might need to compile SQLite from source with REGEXP support enabled and include it in your Android project.

For iOS, if you're using SQLite directly or through libraries like FMDB, you would again need to ensure that SQLite is compiled with REGEXP support enabled. This might involve compiling SQLite from source with the appropriate flags.

In summary, the statement SELECT * FROM your_table WHERE your_column REGEXP 'your_regex_pattern'; will work on Android and iOS SQLite databases only if they are compiled with the REGEXP extension enabled. Otherwise, you'll need to use alternative methods to achieve similar functionality, such as filtering data in your application code after retrieving it from the SQLite database.

Hope this is clear enough