Error while executing Sqlite transaction

Ionic Framework ionic-v3
1

I have a local db service which takes care of opening the database and creating tables if they aren’t already there. Below is the code that I have

import {Injectable} from '@angular/core';
import {SQLite} from "ionic-native";

@Injectable()
export class LocalDbService {

    sqlDb: SQLite;

    constructor() {
        this.sqlDb = new SQLite();
    }

    openDatabase() {
        return this.sqlDb.openDatabase({
            name: 'app.db',
            location: 'default'
        }).then(() => {
            return this.sqlDb.transaction((tx) => {
                tx.executeSql(`create table if not exists groups (
                  id nvarchar(50) primary key not null,
                  name nvarchar(50) unique not null,
                  createdOn datetime not null default current_timestamp,
                  updatedOn datetime not null default current_timestamp,
                  deleted boolean not null default 0
                )`, [])
            })
        }).catch((err) => {
          console.error('Error while creating tables', err);
        })
    }
}

The database opens fine. The problem is that the transaction always produces an error which is undefined when I log it. If I execute sql without the transaction it runs correctly. What am I doing wrong here?

1 Like
2

Sorry for the late reply, but I would recommend using a Storage-wrapper for the SQLite:

@Injectable()
export class CustomerService {
    private store: Storage;

    constructor() {
        this.store = new Storage(SqlStorage);
        this.store.query(`
            CREATE TABLE IF NOT EXISTS customers(
                UUID             TEXT    PRIMARY KEY    NOT NULL,
                NAME             TEXT    NOT NULL,
                CREATEDAT        TEXT    NOT NULL,
                DESCRIPTION      TEXT    NOT NULL
            );
        `)
        .then(() => {
               console.log('DB is up and running!');
           },
           error => {
               console.log(error);
           }
        );
    }
}

Questions? Just ask.

3

Thanks for replying. I’ve tried using SqlStorage, but I couldn’t find anything that could allow me to use a transaction to insert and update multiple statements at once in a particular table. How to do that using SqlStorage?

4

Can you please provide a code snippet of what you were trying to archive exactly. I will have a look at it.

5

Here’s the usecase I’m aiming for.

I have these two tables, groups and members which I save locally using sqlite on the users phone. These contain data that can be added, edited, and deleted by different users on different mobile devices, each having their own local copy of data. So I sync the data to my online server. When the sync happens, the server may reply with newly added / edited data and this data needs to be updated locally in such a way that either all the updates happen locally or if in case of an error a rollback occurs. This is why I want to know how to achieve something similar to transactions in SqlStorage.

Based on your suggestion I’ve started using SqlStorage. I hope I can find a way around transactions and batch operations for my usecase.

6

I’d first simply the flow by removing the transaction (in this case it’s not essential), to something

open() {
      return this.storage
        .openDatabase({
          name: 'mydb',
          location: 'default'
        })
        .then(
          rs => {
            return this.storage.executeSql(
              `CREATE TABLE IF NOT EXISTS  ...`,
              []
            )
            .then(
              () => Log.debug('cache table created'),
              err => Log.error('table creation failed', err)
            );
          },
          err => console.log('failed to open database')
        );
    }
}

You should be able to see something with that.

Also new Storage(SqlStorage) and SQLite are two different things, Storage(SqlStorage) is the browser Web SQL abstraction, while SQLite is the native API. Former has various limitations that latter doesn’t share.

7

Not sure if thats a way to do it, but i solved it by running all my queries in a Promise.all[...]
Like

  this.storage.execute(...),
  this.storage.execute(...),
  this.storage.execute(...)
])
.then(...)
.catch(...)```
8

While the code snippet creates just one table inside the transaction, the app I’m working on creates 5 tables inside it. There are also functions in the provider that update the data by wrapping the executeSql statement inside a transaction, which should rollback on errors.

9

Here’s the approach I use for doing something similar.

1 Like
10

Thank you @rapropos. That approach does solve my problem. However, I’m wondering if a bug report should be filed in ionic-native repo so that people trying to use SQlite from ionic-native do not face the same problem.