Export To JSON Full or Partial

[jjgl]

Hi,

I am trying to use getDatabaseExport but getting the error below.
I've tried both passing 'full' and 'partial' as parameters.

For partial I made sure to have attribute 'last_modified' on all tables and called createSyncTable and setSyncDate methods.

The response I get invariably is:

{"message":"ExportToJson command failed : exportToJson(message: "Error get tables \'Partial\' failed : Query result not well formatted")","export":{}}

or

response: {"message":"ExportToJson command failed : exportToJson(message: "Error get tables \'Full\' failed : Query result not well formatted")","export":{}}

I tried looking up the errors above, but couldn't find an explanation.

This is a database schema that is working. I can select, insert, delete, update...
My schema does have foreign keys, which I found out used to be an issue on version 2.0.XX, so I'm discarding that as a potential source of the problem, but still worth mentioning.

To reproduce
1 - import a JSON db schema as outlined on your docs
2 - createSyncTable CapacitorSQLite.createSyncTable();
3 - insert values into (a) table, including last_modified i.e.:1608124489426
4 - setSyncDate CapacitorSQLite.setSyncDate({syncdate:date}); where date is a string i.e.: 1608125030631
5 - call getDatabaseExport CapacitorSQLite.exportToJson({ jsonexportmode: mode }); where mode is a string with either values 'full' or 'partial'.

The expected behaviour would be to get a JSON of the database changes "@returns Promise"

Smartphone (please complete the following information):

• Device: iphone XS
• OS: 14.2
• Browser stock browser, safari
• Version 14

Additional info:
Running app with command:
"ionic cap run ios -l --external" from VS code.
The project is built with ionic, angular and capacitor.

I did have to assume certain things such as setSyncDate parameter.
The documentation says:

Prop	Type	Description
syncdate	string	Set the synchronization date

but couldn't find additional examples or explanations.

Can syncdate be:

• "ms since epoch" (seems to work, returns true)
• or a timestamp in format YYYY-mm-dd HH:mm:ss (no, it returns false)

but its an empiric process.

I hope the documentation contained examples or explanations for the input and output parameters.

I really appreciate all your work and hope I am just making a silly mistake here.

While trying to find a solution I ran into the following tutorial that also has an issue with the same export method so I'm inclined to thinking its not just me:
https://devdactic.com/sqlite-ionic-app-with-capacitor/
https://youtu.be/2kTT3k8ztL8

Looking forward to some feedback.
Regards,
J

[jepiqueau]

@jjgl first thanks for using the plugin. On iOS the syncdate is Int(date.timeIntervalSince1970) ie(1608195139) so you have to divide by 1000. Did you look at https://github.com/jepiqueau/angular-sqlite-app-starter and also at the documentation https://github.com/capacitor-community/sqlite/blob/master/docs/API.md and https://github.com/capacitor-community/sqlite/blob/master/docs/ImportExportJson.md. This should help you to fix your issue if not come back to me

[jjgl]

Hi Jepiqueau, thank you very much for you reply, I really appreciate it.

Thanks for this:
"On iOS the syncdate is Int(date.timeIntervalSince1970) ie(1608195139) so you have to divide by 1000."

I had already checked API.md and ImportExportJson.md. I did copy paste content from API.md on my initial post as well.

Full mode should still work independently of last_modified being divided by 1000 or not.
CapacitorSQLite.exportToJson({ jsonexportmode: 'full' });

and I'm getting the same response independently of full or partial mode and with multiple json schemas as simple as one table:

{"message":"ExportToJson command failed : exportToJson(message: "Error get tables \'Full\' failed : Query result not well formatted")","export":{}}

I'm searching for "Query result not well formatted" and "Error get tables" on the repository and nothing comes up.

I haven't implemented https://github.com/jepiqueau/angular-sqlite-app-starter but I had a look and I couldn't see things being done differently from my implementation.

I'll try to clone it and see how I go. I wouldn't mind finding on the documentation what the errors "Query result not well formatted" and "Error get tables" are caused by.

Thanks.

[jepiqueau]

@jjgl Error get table's names failed means that at the beginning of the process no tables list are returned. Did you open the database before and what is the database name in your open statement. So for testing just make a quick select on a table and see the result before to to exportToJson("full") .
For me the database is not opened or is empty that the only reason i can see for getting this error
Are you using an Angular service? if yes did you inject the service into your page. The service which is given as an example is not a singleton service so it has to be initialized this.initPlugin = this._SQLiteService.initializePlugin(); in the ngAfterViewInit() method of each component page. Otherwise if you only want to initialize it in the app.component you have to make it 'singleton' by replacing in the service

@Injectable({
  providedIn: 'root'
})

by

@Injectable()

and modify the app.component.ts

import { Component } from '@angular/core';

import { Platform } from '@ionic/angular';
import { SplashScreen } from '@ionic-native/splash-screen/ngx';
import { StatusBar } from '@ionic-native/status-bar/ngx';
import { SQLiteService } from './services/sqlite.service';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.scss']
})
export class AppComponent {
  private initPlugin: boolean;
  constructor(
    private platform: Platform,
    private splashScreen: SplashScreen,
    private statusBar: StatusBar,
    private _sqlite: SQLiteService,
  ) {
    this.initializeApp();
  }

  initializeApp() {
    this.platform.ready().then(async () => {
      this.statusBar.styleDefault();
      this.splashScreen.hide();
      this._detail.set(false);
      this._sqlite.initializePlugin().then(ret => {
        this.initPlugin = ret;
        console.log(">>>> in App  this.initPlugin " + this.initPlugin)
      });
    });
  }
}

and modify the app.module.ts as

import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { RouteReuseStrategy } from '@angular/router';

import { IonicModule, IonicRouteStrategy } from '@ionic/angular';
import { SplashScreen } from '@ionic-native/splash-screen/ngx';
import { StatusBar } from '@ionic-native/status-bar/ngx';

import { AppComponent } from './app.component';
import { AppRoutingModule } from './app-routing.module';
import { SQLiteService } from './services/sqlite.service';

@NgModule({
  declarations: [AppComponent],
  entryComponents: [],
  imports: [BrowserModule, IonicModule.forRoot(), AppRoutingModule],
  providers: [
    StatusBar,
    SplashScreen,
    SQLiteService,
    { provide: RouteReuseStrategy, useClass: IonicRouteStrategy }
  ],
  bootstrap: [AppComponent]
})
export class AppModule {}

hope this could help you finding the reason why your app is failing

[jepiqueau]

@jjgl Any news?

[jjgl]

Thanks again Jean Pierre, I can make inserts and selects and other operations on the database. For example I have a table countries that I download from the server and insert into the database

*INSERT INTO countries (last_modified,id,name,code,language,phone_code) VALUES (1608216034,"3","Afghanistan","AF","fa","93"),(1608216034,"6","Albania","AL","sq","355"),(1608216034,"56","Algeria","DZ","ar","213"),....and so on*

, the result is: *response: {"changes":{"changes":230}}* same with dummy customers: *INSERT INTO customers (last_modified,id,first_name,last_name,gender,email,phone,national_id,date_of_birth,created_at,created_by,last_edited,organisation,comment_id,country_id) VALUES (1608216040,"3","William","Jones","1","peterjones@mail.com <peterjones@mail.com>","420305202","1234567","1983-01-04","2020-11-12 12:39:02","3","2020-11-19 05:10:10","1",null,"1"),....and so on* *response: {"changes":{"changes":120}}* where country_id is foreign key of table countries. I can do selects and show lists of items on my app pages etc... I call the database initialize method in my database service from the app.component.ts as well as you suggest: initializeApp() {

this.platform.ready().then(async () => { const loading = await this.loadingCtrl.create(); await loading.present(); this.databaseService.init(); this.databaseService.dbReady.subscribe(isReady => { if (isReady) { loading.dismiss(); this.statusBar.styleDefault(); this.splashScreen.hide(); } }); }); }

I also have an init on my database service: async init(): Promise<void> {

console.log('init db called') const info = await Device.getInfo(); if (info.platform === 'android') { try { const sqlite = CapacitorSQLite as any; await sqlite.requestPermissions(); this.setupDatabase(); } catch (e) { const alert = await this.alertCtrl.create({ header: 'No DB access', message: 'This app can\'t work without Database access.', buttons: ['OK'] }); await alert.present(); } } else { this.setupDatabase(); } }

The database works marvelously, I really like it. It's just the exportToJSON method I cannot get to work. The only difference I found in my implementation from yours is that I didn't have SQLiteService as a provider, still all of the above works. I added the service as a provider now and still get the same result *{"export":{},"message":"ExportToJson command failed : exportToJson(message: \"Error get tables \\'Full\\' failed : Query result not well formatted\")"}* I think my only option is to clone your https://github.com/jepiqueau/angular-sqlite-app-starter If that doens't work then I'll get back to you. I really appreciate your messaging back, I hope to come back with good news soon.

…

On Thu, 17 Dec 2020 at 23:41, QUEAU Jean Pierre ***@***.***> wrote: @jjgl <https://github.com/jjgl> Error get table's names failed means that at the beginning of the process no tables list are returned. Did you open the database before and what is the database name in your open statement. So for testing just make a quick select on a table and see the result before to to exportToJson("full") . For me the database is not opened or is empty that the only reason i can see for getting this error Are you using an Angular service? if yes did you inject the service into your page. The service which is given as an example is not a singleton service so it has to be initialized this.initPlugin = this._SQLiteService.initializePlugin(); in the ngAfterViewInit() method of each component page. Otherwise if you only want to initialize it in the app.component you have to make it 'singleton' by replacing in the service @Injectable({ providedIn: 'root'}) by @Injectable() and modify the app.component.ts import { Component } from ***@***.***/core'; import { Platform } from ***@***.***/angular';import { SplashScreen } from ***@***.***/splash-screen/ngx';import { StatusBar } from ***@***.***/status-bar/ngx';import { SQLiteService } from './services/sqlite.service'; @component({ selector: 'app-root', templateUrl: 'app.component.html', styleUrls: ['app.component.scss']})export class AppComponent { private initPlugin: boolean; constructor( private platform: Platform, private splashScreen: SplashScreen, private statusBar: StatusBar, private _sqlite: SQLiteService, ) { this.initializeApp(); } initializeApp() { this.platform.ready().then(async () => { this.statusBar.styleDefault(); this.splashScreen.hide(); this._detail.set(false); this._sqlite.initializePlugin().then(ret => { this.initPlugin = ret; console.log(">>>> in App this.initPlugin " + this.initPlugin) }); }); }} and modify the app.module.ts as import { NgModule } from ***@***.***/core';import { BrowserModule } from ***@***.***/platform-browser';import { RouteReuseStrategy } from ***@***.***/router'; import { IonicModule, IonicRouteStrategy } from ***@***.***/angular';import { SplashScreen } from ***@***.***/splash-screen/ngx';import { StatusBar } from ***@***.***/status-bar/ngx'; import { AppComponent } from './app.component';import { AppRoutingModule } from './app-routing.module';import { SQLiteService } from './services/sqlite.service'; @NgModule({ declarations: [AppComponent], entryComponents: [], imports: [BrowserModule, IonicModule.forRoot(), AppRoutingModule], providers: [ StatusBar, SplashScreen, SQLiteService, { provide: RouteReuseStrategy, useClass: IonicRouteStrategy } ], bootstrap: [AppComponent]})export class AppModule {} hope this could help you finding the reason why your app is failing — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#59 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AEFCVYLJDQYHKLJ7Y3KTUGDSVH4AJANCNFSM4U6APZJA> .

-- Kind Regards, *Jerónimo González* Mobile: (+61) 423 479 692

[jepiqueau]

@jjgl Have you try to debug in Xcode? It seems related to your database May be something that i have missed. It is happening in the getTablesFull method in the ExportToJson.swift. Can you share the schema of your database? so i could have a look to reproduce it. Would be nice to identify if it is in the ExportToJson.getSchemaIndexes method or in the ExportToJson.getValues Hope we will identify the reason

[jepiqueau]

@jjgl Is your countries id , customers id and country_id in customers TEXT?

[jepiqueau]

@jjgl Is your id the first field in the schema of the countries & customers tables as mentioned in the API doc?

[jepiqueau]

@jjgl OK i create a full test component like this

import { Component, AfterViewInit } from '@angular/core';
import { SQLiteService } from '../services/sqlite.service';

@Component({
  selector: 'app-issue59',
  templateUrl: './issue59.component.html',
  styleUrls: ['./issue59.component.scss'],
})
export class Issue59Component implements AfterViewInit {
  platform: string;
  issue59: boolean = false;
  initTest:boolean = false;
  initPlugin: boolean = false;
  constructor(private _SQLiteService: SQLiteService) { }
  /*******************************
   * Component Lifecycle Methods *
   *******************************/

  async ngAfterViewInit() {
    // Initialize the CapacitorSQLite plugin
    this.initPlugin = this._SQLiteService.initializePlugin();
    console.log(`in ngAfterViewInit this.initPlugin: ${this.initPlugin}`);
    console.log(`isService ${this._SQLiteService.isService}`);
  }
  ngOnDestroy() {
    console.log("ngOnDestroy");
    this._SQLiteService.handlerPermissions.remove();
  }
  /*******************************
  * Component Methods           *
  *******************************/

  async runTests(): Promise<void> {
    console.log("****** entering run tests");
    // In case of multiple test runs
    this.resetDOM();
    // Start Running the Set of Tests
    const cardSQLite = document.querySelector('.card-sqlite');
    if(cardSQLite && cardSQLite.classList.contains("hidden")) 
              cardSQLite.classList.remove('hidden');
    if(this.initPlugin) {
      this.initTest = await this.testInitialization();
      if (this.initTest) {
        // Create a Database with No-Encryption
        this.issue59 = await this.testIssue59();
        if(!this.issue59) {     
          document.querySelector('.sql-failure1').classList
                  .remove('display');
        } else {
          console.log("***** End testDatabase *****");
          document.querySelector('.sql-success1').classList
                  .remove('display');
        }
      }
    }
  }
  /**
  * Reset the DOM
  */
  async resetDOM(): Promise<void> {
    const cardSQLite = document.querySelector('.card-sqlite');
    if(cardSQLite) {
      if (!cardSQLite.classList.contains("hidden")) cardSQLite.classList.add('hidden');
      for (let i:number=0;i< cardSQLite.childElementCount;i++) {
        if(!cardSQLite.children[i].classList.contains('display')) cardSQLite.children[i].classList.add('display');
      }
    }
  }

  /**
   * testInitialization
   */
  async testInitialization(): Promise<boolean> {
    return new Promise(async (resolve) => {
      const echo = await this._SQLiteService.getEcho("Hello from JEEP");
      console.log("*** echo ",echo);
      if(echo.value === "Hello from JEEP") {
        resolve(true);
      } else {
        resolve(false);
      }
    });
  }
  /**
  * Test a non-encrypted database
  */
  async testIssue59(): Promise<boolean> {
    return new Promise(async (resolve) => {
      // check if the database test-json-db59 exists 
      let result: any = await this._SQLiteService.isDBExists("test-json-db59");
      console.log("isDBExists 'test-json-db59' " + JSON.stringify(result));
      if(!result.result) {
        console.log("*** database test-json-db59 does not exist");
      }
      
      // open the database
      result = await this._SQLiteService.openDB("test-json-db59"); 
      if(result.result) {
        console.log("*** Database test-json-db59 Opened");
        document.querySelector('.openDB').classList.remove('display');
        result = await this._SQLiteService.createSyncTable();
        console.log("*** After createSyncTable " + result.changes.changes);
        // create tables
        const sqlcmd: string = `
        BEGIN TRANSACTION;
        CREATE TABLE IF NOT EXISTS countries (
            id TEXT PRIMARY KEY NOT NULL,
            name TEXT UNIQUE NOT NULL,
            code TEXT,
            language TEXT,
            phone_code TEXT,
            last_modified INTEGER DEFAULT (strftime('%s', 'now'))
        );
        CREATE TABLE IF NOT EXISTS customers (
            id TEXT PRIMARY KEY NOT NULL,
            first_name TEXT NOT NULL,
            last_name TEXT NOT NULL,
            gender TEXT NOT NULL,
            email TEXT NOT NULL,
            phone TEXT,
            national_id TEXT NOT NULL,
            date_of_birth TEXT,
            created_at TEXT,
            created_by TEXT,
            last_edited TEXT,
            organization TEXT,
            comment_id TEXT,
            country_id TEXT,
            last_modified INTEGER DEFAULT (strftime('%s', 'now')),
            FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE SET DEFAULT
        );
        CREATE INDEX IF NOT EXISTS countries_index_name ON countries (name);
        CREATE INDEX IF NOT EXISTS countries_index_last_modified ON countries (last_modified);
        CREATE INDEX IF NOT EXISTS customers_index_last_name ON customers (last_name);
        CREATE INDEX IF NOT EXISTS customers_index_last_modified ON customers (last_modified);
        CREATE TRIGGER IF NOT EXISTS countries_trigger_last_modified
        AFTER UPDATE ON countries
        FOR EACH ROW WHEN NEW.last_modified <= OLD.last_modified
        BEGIN
            UPDATE countries SET last_modified= (strftime('%s', 'now')) WHERE id=OLD.id;
        END;
        CREATE TRIGGER IF NOT EXISTS customers_trigger_last_modified
        AFTER UPDATE ON customers
        FOR EACH ROW WHEN NEW.last_modified <= OLD.last_modified
        BEGIN
            UPDATE customers SET last_modified= (strftime('%s', 'now')) WHERE id=OLD.id;
        END;
        PRAGMA user_version = 1;
        COMMIT TRANSACTION;
        `;
        result = await this._SQLiteService.execute(sqlcmd);
        console.log("*** After createTables " + result.changes.changes);
        if(result.changes.changes === 0 || result.changes.changes === 1) {
          document.querySelector('.execute1').classList.remove('display'); 
          // Insert some Countries
            let sqlcmd: string = `
            BEGIN TRANSACTION;
            DELETE FROM countries;
            INSERT INTO countries (id,name,code,language,phone_code) VALUES ("3","Afghanistan","AF","fa","93");
            INSERT INTO countries (id,name,code,language,phone_code) VALUES ("6","Albania","AL","sq","355");
            INSERT INTO countries (id,name,code,language,phone_code) VALUES ("56","Algeria","DZ","ar","213");
            COMMIT TRANSACTION;
            `;
            result = await this._SQLiteService.execute(sqlcmd);
            console.log("*** After Insert countries " + result.changes.changes);
            const retEx2 = result.changes.changes === 3 ? true : false;
            if(retEx2) document.querySelector('.execute2').classList.remove('display'); 
            sqlcmd = `
            BEGIN TRANSACTION;
            DELETE FROM customers;
            INSERT INTO customers (id,first_name,last_name,gender,email,
              phone,national_id,comment_id,country_id) VALUES (
              "3","William","Jones","1","peterjones@mail.com<peterjones@mail.com>","420305202","1234567",null,"3");
            INSERT INTO customers (id,first_name,last_name,gender,email,
              phone,national_id,comment_id,country_id) VALUES (
              "1","Alexander","Brown","1","alexanderbrown@mail.com<alexanderbrown@mail.com>","420305203","1234572",null,"6");
            COMMIT TRANSACTION;
            `;
            result = await this._SQLiteService.execute(sqlcmd);
            console.log("*** After Insert customers " + result.changes.changes);
            const retEx3 = result.changes.changes === 2 ? true : false;
            if(retEx3) document.querySelector('.execute3').classList.remove('display');

            // Export to Json
            let retExportFull:boolean = true;
            result = await this._SQLiteService.exportToJson("full");
            console.log('result fullexportToJson ',result);
            if (Object.keys(result.export).length === 0)  retExportFull = false;
            const jsObj: string = JSON.stringify(result.export);
            console.log("*** jsObject " + jsObj);
            result = await this._SQLiteService.isJsonValid(jsObj);
            if(!result.result) retExportFull = false;  
            if(retExportFull) document.querySelector('.export').classList.remove('display');
    

            // Close the Database
            result = await this._SQLiteService.close("test-json-db59");
            const retClose = result.result

            if (retClose) document.querySelector('.close').classList.remove('display'); 
            let ret = false;
            if(retEx2 && retEx3 && retClose && retExportFull) ret = true;
            console.log(`$$$ before ending test issue#59 '${ret}'`);
            resolve(ret);
  
        } else {
          resolve(false);
        }
      } else {
        console.log("*** Error: Database test-json-db59 not opened");
        resolve(false);
      }
    });
  }

}

and i am able to reproduce the error so i will now work on finding the solution for it

[jepiqueau]

@jjgl this is fixed in release 2.4.3. Test it and tell me if it is working now on your side

[jjgl]

@jjgl this is fixed in release 2.4.3. Test it and tell me if it is working now on your side

Hi @jepiqueau thanks again for running these tests:

I updated my dependencies and now confirm getting:
All dependencies match the latest package versions :)

also double checking pakage.json has: "@capacitor-community/sqlite": "^2.4.3",

My schema just for testing this feature as simply as possible:

{
    "database": "product-db",
    "version": 1,
    "encrypted": false,
    "mode": "full",
    "tables": [
      {  
        "name": "countries",
        "schema": [
          { "column": "id", "value": "INTEGER PRIMARY KEY NOT NULL" },
          { "column": "name", "value": "TEXT NOT NULL" },
          { "column": "code", "value": "TEXT NOT NULL" },
          { "column": "language", "value": "TEXT NOT NULL" },
          { "column": "phone_code", "value": "TEXT" },
          { "column": "last_modified", "value": "INTEGER DEFAULT (strftime('%s', 'now'))" }
        ]
      }
    ]
  }

Again inserting 230 countries into de DB:

[log] - sql: INSERT INTO countries (last_modified,id,name,code,language,phone_code) VALUES (1608420940,"3","Afghanistan","AF","fa","93"),(1608420940,"6","Albania","AL","sq","355"),(1608420940,"56","Algeria","DZ","ar","213"),(1608420940,"1","Andorra","AD","ca","376"),(1608420940,"9","Angola","AO","pt","244"),(1608420940,"5","Anguilla","AI","en","264"),(1608420940,"4","Antigua and Barbuda","AG","en","1268")

I can select and show on screen values but when trying to export

TO JS {"export":{},"message":"ExportToJson command failed : exportToJson(message: "Error get tables \'Full\' failed : Query result not well formatted")"}

I'll try running the following commands and will come back with feedback soon:
getTablesFull method in the ExportToJson.swift.
ExportToJson.getSchemaIndexes
ExportToJson.getValues

[jepiqueau]

@jjgl in your app folder go to

cd ./ios/App
pod install --repo-update
cd ../..
npx cap sync
npm run build
npx cap copy
npx cap open ios

[jepiqueau]

@jjgl this is working fine with release 2.4.3

      // test with Import
      console.log("$$$ Start Import a JSON Object");
      const dataToImport59: any = {
        database : "product-db",
        version : 1,
        encrypted : false,
        mode : "full",
        tables :[
          {
            "name": "countries",
            "schema": [
              { "column": "id", "value": "INTEGER PRIMARY KEY NOT NULL" },
              { "column": "name", "value": "TEXT NOT NULL" },
              { "column": "code", "value": "TEXT NOT NULL" },
              { "column": "language", "value": "TEXT NOT NULL" },
              { "column": "phone_code", "value": "TEXT" },
              { "column": "last_modified", "value": "INTEGER DEFAULT (strftime('%s', 'now'))" }
            ],
            indexes: [
              {name: "index_country_on_name",column: "name"},
              {name: "index_country_on_last_modified",column: "last_modified"}
            ],
            values: [
                [3,"Afghanistan","AF","fa","93",1608216034],
                [6,"Albania","AL","sq","355",1608216034],
                [56,"Algeria","DZ","ar","213",1608216034],
            ]

          }
        ]
      };
      result = await this._SQLiteService
                .importFromJson(JSON.stringify(dataToImport59));    
      console.log('import result ',result)
      this.retImport = result.changes.changes >0 ? true : false;

if the id is an Integer it must be given in the INSERT as an Integer

i am updating the angular-sqlite-app-starter to demonstrate this Should be there soon

[jepiqueau]

@jjgl Did you succeed to make it working ?

[jjgl]

@jjgl Did you succeed to make it working ?

Hi @jepiqueau,

I now have the export function working.

I deleted the iOS folder re added it and it worked.

I'm sure it was working when you updated the library and I'm sorry I didn't try this before.

Thank you very much for your help, it really encourages me to use and recommend this library with the level of support and involvement you have shown.

I will close this issue now.