Ionic - Problems with SQLite


#1

Hello,

I have updated to Ionic 3 and since then I have this error when I compile. It worked very well under Ionic 2 :

Property ‘openDatabase’ does not exist on type ‘SQLite’.

I have also

“Property ‘executeSql’ does not exist on type ‘SQLite’.”

I can’t find the source of the problem, so if anyone knows the solution or can help me. Thanks :slight_smile:

My code :

import { Injectable } from '@angular/core';
import { Http } from '@angular/http'; 
import { SQLite, SQLiteObject  } from '@ionic-native/sqlite';
import { Platform } from 'ionic-angular';

@Injectable()
export class Database {
 
    public storage: SQLite;
    private options = { name: "data.sqlite3", location: 'default'};
    private queryNames = "SELECT * FROM place";
    public names: String[] = [];
 
     public constructor(private platform: Platform) {
        this.storage = new SQLite();
        this.storage.openDatabase(this.options).then((success) => {
          console.info("Opened data.db with success");
          this.storage.executeSql(this.queryNames, {}).then((data) => {
            let rows = data.rows;
            for (let i = 0; i < rows.length; i++) 
              this.names.push(rows.item(i).name);
            console.log("Number of names on database = " + this.names.length);
          });
        }, (err) => {
          console.log("Error opening database: " + err);
        });
      }
global packages:

    @ionic/cli-utils : 1.2.0
    Cordova CLI      :  7.0.1 
    Ionic CLI        : 3.2.0

local packages:

    @ionic/app-scripts              : 1.3.7
    @ionic/cli-plugin-cordova       : 1.2.1
    @ionic/cli-plugin-ionic-angular : 1.2.0
    Cordova Platforms               : android 6.2.3
    Ionic Framework                 : ionic-angular 3.3.0


#2

Ionic Native SQLite is supposed to be used differently. Have a look at the example in the documentation: https://ionicframework.com/docs/native/sqlite/


#3

I have already tried this way, but I have another error message :

Cant Resolve all parameters for SQLiteObject (?)

import { Injectable } from '@angular/core';
import { SQLite, SQLiteObject  } from '@ionic-native/sqlite';

@Injectable()
export class Database {

     constructor (private sqlite: SQLite) {
      this.sqlite.create({
        name: 'data.sqlite3',
        location: 'default'
      }) .then((db: SQLiteObject) => {
        db.executeSql('SELECT * FROM place', {})
        .then(() => console.log('Executed SQL'))
        .catch(e => console.log(e));
      })
      .catch(e => console.log(e));
    }
}

I don’t know where to look and I don(t understand why my code worked perfectly and a simple update breaks everything.


#4

Are you certain that you did everything in the page @Sujan12 linked to? Specifically the bit about adding providers to your app module?


#5

I tested with new project and still the same error: Cant Resolve all parameters for SQLiteObject (?)

My app module file :

import { BrowserModule } from '@angular/platform-browser';
import { ErrorHandler, NgModule } from '@angular/core';
import { IonicApp, IonicErrorHandler, IonicModule } from 'ionic-angular';
import { HttpModule } from '@angular/http';

import { MyApp } from './app.component';
import { HomePage } from '../pages/home/home';
import { ListPage } from '../pages/list/list';

import { StatusBar } from '@ionic-native/status-bar';
import { SplashScreen } from '@ionic-native/splash-screen';
import { DatabaseProvider } from '../providers/database/database';
import { SQLite, SQLiteObject } from '@ionic-native/sqlite';

@NgModule({
  declarations: [
    MyApp,
    HomePage,
    ListPage  
  ],
  imports: [
    BrowserModule,
    HttpModule,
    IonicModule.forRoot(MyApp)
  ],
  bootstrap: [IonicApp],
  entryComponents: [
    MyApp,
    HomePage,
    ListPage
  ],
  providers: [
    StatusBar,
    SplashScreen,
    SQLite,
    SQLiteObject,
    {provide: ErrorHandler, useClass: IonicErrorHandler},
    DatabaseProvider
  ]
})

export class AppModule {}


#6

What happens if you take SQLiteObject out of the providers, leaving only SQLite?


#7

That’s work without SQLiteObject, thank you.

Now it seems not to be able to find the pre-populated database. Before that worked by installing cordova-sqlite-ext but no more now.


#8

I too am having issues opening a Database.

In the current documentation you have to create the db then assign the object on creation.

How do you open an existing one?


#9

Hi! Almazard, show you give more details how you solved?


#10

Not solved. I still have the same problem. I tried different things without success. And there’s nothing about it in the doc. How such a simple thing is so complicated to do?


#11

‘create’ method used to create and open database if it exists


#12

Yes. Logs tell me that the database is ok. But it can’t return a simple request to this database (“sqlite3_prepare_v2 failure: no such table”). So I deduce that it creates a new database and doesn’t open mine.


#13

Unless you’re doing something funky it should certainly open your existing database.

Where are you creating the table?


#14

This is a pre-populated database. The query (select * from place) returns something normally.


#15

Or maybe the “location: ‘default’” has changed ? No more in www folder?


#16

So, i did this:

i have installed two plugins: cordova-sqlite-storage and cordova-plugin-dbcopy.

First time i have to copy the database, and after open it:

window.plugins.sqlDB.remove(“mydb.db”,0, function deleteSuccess(){
console.log(‘deleted’)
}, function deleteError(error){
});

let self = this;
window.plugins.sqlDB.copy(“mydb.db”, 0, function copySuccess(){
console.log(‘copiedd’)
self.sqlite.create({
name: ‘hymnal.db’,
location: ‘default’
}).then((db: SQLiteObject) => {
db.executeSql(“select * from table”, {}).then((res) => {
//console.log(JSON.stringify(res))
}).catch((e) => {
console.log(JSON.stringify(e))
});
})
})

look this:

https://github.com/an-rahulpandey/cordova-plugin-dbcopy
https://github.com/litehelpers/Cordova-sqlite-storage#opening-a-database


#17

It works ! Thanks !
With this new way of doing it I don’t really know how to use it in a function.

I try to do that :

export class DatabaseProvider {

  database: SQLiteObject;
  public names: String[] = [];

  constructor(public http: Http, private sqlite: SQLite) {

    (<any>window).plugins.sqlDB.remove("db.sqlite3",0, function deleteSuccess(){
      console.log('deleted')
    }, function deleteError(error){
      console.log('not deleted')
    });

    let self = this;
    (<any>window).plugins.sqlDB.copy("db.sqlite3", 0, function copySuccess(){
      console.log('copiedd')
      self.sqlite.create({
        name: 'db.sqlite3',
        location: 'default'
      }).then((db: SQLiteObject) => {
        this.database = db;
        db.executeSql("select * from table", {}).then((data) => {
        console.log(JSON.stringify(data))
        }).catch((e) => {
          console.log(JSON.stringify(e))
        });
      })
    })
  }

  getAll() {
    return this.database.executeSql("SELECT * FROM table", []).then((data) => {
      let names = [];
      if (data.rows.length > 0) {
        for (var i = 0; i < data.rows.length; i++) {
          names.push({ name: data.rows.item(i).name });
        }
      }
      return names;
    }, err => {
      console.log('Error: ', err);
      return [];
    });
  }

}

But by this way I have an error :
Unhandled Promise rejection: Cannot set property 'database' of null ; Zone: <root> ; Task: Promise.then ; Value: TypeError: Cannot set property 'database' of null

I don’t see how to pass the database to my function.


#18

You should never type function inside of a function.

Remove the
let self = this; stuff and replace function with fat arrows.

e.g.
function copySuccess()
becomes
() =>


#19

I hope I will help you with this example, use Platform.ready(), for you sqlite plugin load after than the platform is ready:

import { Injectable } from '@angular/core';
import {SQLite, SQLiteObject} from '@ionic-native/sqlite';
import { Platform } from 'ionic-angular';
@Injectable()
export class SqliteProvider {
  private db: SQLiteObject = null; //storage the SQLiteObject return by create method

  constructor(
    private platform: Platform,
    private sqlite: SQLite
  ) {
    this.platform.ready().then(() => {
      //call openDB method
      this.openDB().then(() => {
        //call createTable method 
        this.createTable();
      });
    }); 
  }

  public openDB(): Promise<void>{
    return this.sqlite.create({
      name: 'test.db',
      location: 'default'
    })
    .then( (db: SQLiteObject) => {
      //storage object to property
      this.db = db; 
    });
  }

  //creamos tabla
  public createTable(): Promise<void>{
    //you can access to method executeSql now
    return this.db.executeSql('CREATE TABLE IF NOT EXISTS table (column VARCHAR(1))', []);
  }

}

#20

can u give example of how to insert data into tabale and get the data from table