Bug: Unable to insert multiple records at once (Bulk insertion)

[Sampath-Lokuge]

Hi,

I need to add bulk records at once. I have tried it like so:

createExpenseTemplates(expenseTemplates: ExpenseTemplateModel[]): Promise<capSQLiteChanges> {
    const sqlQuery = `INSERT INTO ExpenseTemplates (
      ExpenseTemplateId,
      TemplateName,
      ExpenseTemplateIconId,
      IsNameEnabled,
      IsAmountEnabled,
      IsDateEnabled,
      IsExpenseTypeEnabled,
      IsVatEnabled,
      IsPhotoEnabled,
      CreatedDate,
      UserId) 
      VALUES (?,?,?,?,?,?,?,?,?,?,?)`;

    const expenseTemplateList: any = [];

    forEach(expenseTemplates, (e) => {
      expenseTemplateList.push([
        e?.id,
        e?.templateName,
        e?.expenseTemplateIconId,
        e?.isNameEnabled,
        e?.isAmountEnabled,
        e?.isDateEnabled,
        e?.isExpenseTypeEnabled,
        e?.isVatEnabled,
        e?.isPhotoEnabled,
        e?.createdDate,
       e?.userId,
      ]);
    });

    const sqlValues = [expenseTemplateList];

    return this.sqliteDbConnection.run(sqlQuery, sqlValues, false);
  }

But it gives exceptions like so:

core.mjs:6495 ERROR Error: Uncaught (in promise): Error: Run: Cannot bind [["cca4a94c-3957-552f-90bd-6c7077aa7a44","tmp","152f86c0-39de-4094-8001-6227bbcc0393",true,true,true,true,true,true,1650178438,"2xiSELKS9RSYUBkgf4OziOapoTG3"],["d4fcb17d-cb1f-c2aa-4bbc-e2143ee364c1","expt4","8919c329-aa5c-4630-b2b3-32345c4753b9",true,true,true,true,true,true,1650176901,"2xiSELKS9RSYUBkgf4OziOapoTG3"],["0e90e7c6-88fa-da90-db4d-f935d399cfb7","extmp3","8919c329-aa5c-4630-b2b3-32345c4753b9",true,true,true,false,true,true,1650173777,"2xiSELKS9RSYUBkgf4OziOapoTG3"],["9c5b0b2f-1754-c706-882a-21fbf7d524a4","exp tmp1","8919c329-aa5c-4630-b2b3-32345c4753b9",true,true,true,true,true,true,1650091148,"2xiSELKS9RSYUBkgf4OziOapoTG3"]] at index 1 Supported types: null, byte[], float, double, long, int, short, byte, string
Error: Run: Cannot bind [["cca4a94c-3957-552f-90bd-6c7077aa7a44","tmp","152f86c0-39de-4094-8001-6227bbcc0393",true,true,true,true,true,true,1650178438,"2xiSELKS9RSYUBkgf4OziOapoTG3"],["d4fcb17d-cb1f-c2aa-4bbc-e2143ee364c1","expt4","8919c329-aa5c-4630-b2b3-32345c4753b9",true,true,true,true,true,true,1650176901,"2xiSELKS9RSYUBkgf4OziOapoTG3"],["0e90e7c6-88fa-da90-db4d-f935d399cfb7","extmp3","8919c329-aa5c-4630-b2b3-32345c4753b9",true,true,true,false,true,true,1650173777,"2xiSELKS9RSYUBkgf4OziOapoTG3"],["9c5b0b2f-1754-c706-882a-21fbf7d524a4","exp tmp1","8919c329-aa5c-4630-b2b3-32345c4753b9",true,true,true,true,true,true,1650091148,"2xiSELKS9RSYUBkgf4OziOapoTG3"]] at index 1 Supported types: null, byte[], float, double, long, int, short, byte, string
    at Object.cap.fromNative (VM3:414:32)
    at <anonymous>:1:18
    at resolvePromise (zone.js:1255:1)
    at zone.js:1162:1
    at rejected (tslib.es6.js:74:89)
    at ZoneDelegate.invoke (zone.js:400:1)
    at Object.onInvoke (core.mjs:25853:33)
    at ZoneDelegate.invoke (zone.js:399:1)
    at Zone.run (zone.js:160:1)
    at zone.js:1318:1
    at ZoneDelegate.invokeTask (zone.js:434:1)
    at Object.onInvokeTask (core.mjs:25840:33)
defaultErrorLogger @ core.mjs:6495
handleError @ core.mjs:6543
next @ core.mjs:26463
__tryOrUnsub @ Subscriber.js:183
next @ Subscriber.js:122
_next @ Subscriber.js:72
next @ Subscriber.js:49
next @ Subject.js:39
emit @ core.mjs:22752
(anonymous) @ core.mjs:25879
ZoneDelegate.invoke @ zone.js:400
Zone.run @ zone.js:160
runOutsideAngular @ core.mjs:25752
onHandleError @ core.mjs:25879
ZoneDelegate.handleError @ zone.js:404
Zone.runGuarded @ zone.js:174
_loop_2 @ zone.js:1113
api.microtaskDrainDone @ zone.js:1125
drainMicroTaskQueue @ zone.js:627
Promise.then (async)
scheduleMicroTask @ zone.js:603
ZoneDelegate.scheduleTask @ zone.js:424
onScheduleTask @ zone.js:311
ZoneDelegate.scheduleTask @ zone.js:414
Zone.scheduleTask @ zone.js:248
Zone.scheduleMicroTask @ zone.js:268
scheduleResolveOrReject @ zone.js:1308
resolvePromise @ zone.js:1246
(anonymous) @ zone.js:1162
cap.fromNative @ VM3:431
(anonymous) @ VM852:1

This is an Ionic/Angular native app. Please let me know what is the issue here. Thanks!

[tobiasmuecksch]

@Sampath-Lokuge You are using run() wrong. It is for single datasets only.
You have to call it once per dataset. OR you could use the executeSet() method which is described here.

The correct implementation with run() would look more like this:

async createExpenseTemplates(expenseTemplates: ExpenseTemplateModel[]): Promise<capSQLiteChanges[]> {
    const sqlQuery = `INSERT INTO ExpenseTemplates (
      ExpenseTemplateId,
      TemplateName,
      ExpenseTemplateIconId,
      IsNameEnabled,
      IsAmountEnabled,
      IsDateEnabled,
      IsExpenseTypeEnabled,
      IsVatEnabled,
      IsPhotoEnabled,
      CreatedDate,
      UserId) 
      VALUES (?,?,?,?,?,?,?,?,?,?,?)`;

   const results: capSQLiteChanges[] = [];

    for (const expenseTemplate of expenseTemplates) {
       const values = [
        expenseTemplate.id,
        expenseTemplate.templateName,
        expenseTemplate.expenseTemplateIconId,
        expenseTemplate.isNameEnabled,
        expenseTemplate.isAmountEnabled,
        expenseTemplate.isDateEnabled,
        expenseTemplate.isExpenseTypeEnabled,
        expenseTemplate.isVatEnabled,
        expenseTemplate.isPhotoEnabled,
        expenseTemplate.createdDate,
      ];

      const currentResult = await this.sqliteDbConnection.run(sqlQuery, values, false);

     results.push(currentResult);
   }

    return results;
  }

[Sampath-Lokuge]

Hi,

I have tried like so. But it is not working. Please let me know this issue here.

createExpenseTemplates(expenseTemplates: ExpenseTemplateModel[]): Promise<capSQLiteChanges> {
    const statement = `INSERT INTO ExpenseTemplates (
      ExpenseTemplateId,
      TemplateName,
      ExpenseTemplateIconId,
      IsNameEnabled,
      IsAmountEnabled,
      IsDateEnabled,
      IsExpenseTypeEnabled,
      IsVatEnabled,
      IsPhotoEnabled,
      CreatedDate,
      UserId) 
      VALUES (?,?,?,?,?,?,?,?,?,?,?)`;

    const sqlValues: any = [];

    forEach(expenseTemplates, (e) => {
      sqlValues.push([
        e?.id,
        e?.templateName,
        e?.expenseTemplateIconId,
        e?.isNameEnabled,
        e?.isAmountEnabled,
        e?.isDateEnabled,
        e?.isExpenseTypeEnabled,
        e?.isVatEnabled,
        e?.isPhotoEnabled,
        e?.createdDate,
        e?.userId,
      ]);
    });


    const results: capSQLiteSet[] = [
      {
        statement,
        values: [sqlValues],
      },
    ];

 

    return this.sqliteDbConnection.executeSet(results, true);
  }

[tobiasmuecksch]

The issue is, that you are still using it wrong. Please have a close look at the documentation for executeSet() and capSQLiteSet.

The correct code should work like this:

createExpenseTemplates(expenseTemplates: ExpenseTemplateModel[]): Promise<capSQLiteChanges> {
    const statement = `INSERT INTO ExpenseTemplates (
      ExpenseTemplateId,
      TemplateName,
      ExpenseTemplateIconId,
      IsNameEnabled,
      IsAmountEnabled,
      IsDateEnabled,
      IsExpenseTypeEnabled,
      IsVatEnabled,
      IsPhotoEnabled,
      CreatedDate,
      UserId) 
      VALUES (?,?,?,?,?,?,?,?,?,?,?)`;

    const executionSet: capSQLiteSet[] = [];

    forEach(expenseTemplates, (e) => {
      const values = [
        e?.id,
        e?.templateName,
        e?.expenseTemplateIconId,
        e?.isNameEnabled,
        e?.isAmountEnabled,
        e?.isDateEnabled,
        e?.isExpenseTypeEnabled,
        e?.isVatEnabled,
        e?.isPhotoEnabled,
        e?.createdDate,
        e?.userId,
      ];

     executionSet.push({
        statement,
        values,
      });
    });


    return this.sqliteDbConnection.executeSet(executionSet, true);
  }

[Sampath-Lokuge]

Hi @tobiasmuecksch

Ok sure. All are working fine now. Thanks a lot for the support.