exportToJson not working (iOS)

[VesperiniL]

Describe the bug I'm trying to use the exportToJson function in "full" in a database with 60+ tables. When i call the function, the library starts exporting only to return this error: Error: exportToJson: Error get tables 'Full' failed : Error value is not (string, nsnull,int64,double

This happens ONLY on iOS, the project compiles as is for Android works just fine.

To Reproduce Steps to reproduce the behavior:

1. Fill a Database with data.
2. Call the exportToJson native functions with the args: exportToJson("full",false)

Expected behavior Return the exported JSON

Screenshots

Desktop (please complete the following information):

• OS: macOS Sonoma 14.2.1 | Mac Mini M2 16gb ram
• Browser Safari

Smartphone (please complete the following information):

• Device: iPhone Xr
• OS: iOS 17.2.1

Additional context Capacitor Sqlite on Ionic/React

[jepiqueau]

@VesperiniL can yo give exactely the way you create the connection to the database and then open it and then the call to the exportToJson command

[miguelchico]

I'm having the same issue with a new project. I've identified a possible reason related to the definition of my table properties. If I define a property of type NUMBER, then the export fail

[jepiqueau]

@miguelchico can you provide a small repro app showing the issue. As i do not have any news on my request to @VesperiniL i cannot solve an issue if i can"t reproduce it. Thanks in advance

[miguelchico]

hi @jepiqueau, I'm sorry but I can't provide a repo. The way to reproduce it is easy. I just followed https://jepiqueau.github.io/2023/08/26/Ionic7Angular-SQLite-CRUD-App.html and included a column of type number in the table creation. After that, trying to export the error will appear

[jepiqueau]

@miguelchico i cannot reproduce i give the list of modifications so you can follow-up and see what are the differences with your code. i took the Part1 ionic7-angular-sqlite-app and did the following

• modify the user.upgrade.statement.ts to include like you suggest a column of type NUMBER

export class UserUpgradeStatements {
  userUpgrades = [
      {
      toVersion: 1,
      statements: [
          `CREATE TABLE IF NOT EXISTS users(
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name TEXT NOT NULL,
          age NUMBER,
          active INTEGER DEFAULT 1
          );`
      ]
      },
      /* add new statements below for next database version when required*/
      /*
      {
      toVersion: 2,
      statements: [
          `ALTER TABLE users ADD COLUMN email TEXT;`,
      ]
      },
      */
  ]
}

• Modify the storage.service.ts to add a exportDBUser method

  
  import { SQLiteDBConnection } from '@capacitor-community/sqlite';
  import { Injectable} from '@angular/core';
  import { SQLiteService } from './sqlite.service';
  import { DbnameVersionService } from './dbname-version.service';
  import { UserUpgradeStatements } from '../upgrades/user.upgrade.statements';
  import { User } from '../models/user';
  import { BehaviorSubject, Observable } from 'rxjs';

@Injectable() export class StorageService { public userList: BehaviorSubject<User[]> = new BehaviorSubject<User[]>([]); public databaseName: string = ""; private uUpdStmts: UserUpgradeStatements = new UserUpgradeStatements(); private versionUpgrades; private loadToVersion; private db!: SQLiteDBConnection; private isUserReady: BehaviorSubject = new BehaviorSubject(false);

constructor(private sqliteService: SQLiteService,
            private dbVerService: DbnameVersionService) {
    this.versionUpgrades = this.uUpdStmts.userUpgrades;
    this.loadToVersion = this.versionUpgrades[this.versionUpgrades.length-1].toVersion;
}
async initializeDatabase(dbName: string) {
    this.databaseName = dbName;
    // create upgrade statements
    await this.sqliteService
    .addUpgradeStatement({  database: this.databaseName,
                            upgrade: this.versionUpgrades});
    // create and/or open the database
    this.db = await this.sqliteService.openDatabase(
                                        this.databaseName,
                                        false,
                                        'no-encryption',
                                        this.loadToVersion,
                                        false
    );
    this.dbVerService.set(this.databaseName,this.loadToVersion);

    await this.getUsers();
}
// Current database state
userState() {
    return this.isUserReady.asObservable();
}
fetchUsers(): Observable<User[]> {
    return this.userList.asObservable();
}

async loadUsers() {
    const users: User[]= (await this.db.query('SELECT * FROM users;')).values as User[];
    this.userList.next(users);
}
// CRUD Operations
async getUsers() {
    await this.loadUsers();
    this.isUserReady.next(true);
}
async addUser(name: string) {
    const sql = `INSERT INTO users (name) VALUES (?);`;
    await this.db.run(sql,[name]);
    await this.getUsers();
}

async updateUserById(id: string, active: number) {
    const sql = `UPDATE users SET active=${active} WHERE id=${id}`;
    await this.db.run(sql);
    await this.getUsers();
}
async deleteUserById(id: string) {
    const sql = `DELETE FROM users WHERE id=${id}`;
    await this.db.run(sql);
    await this.getUsers();
}
async exportUserDB(): Promise<any> {
  this.userList.subscribe(users => {
    console.log('User List:', users);
  });
  if (this.sqliteService.platform === "web") {
    this.sqliteService.saveToStore(this.databaseName);
  }
  const jsonExport =  await this.db.exportToJson('full');
  console.log(`jsonExport: ${JSON.stringify(jsonExport)}`);
  return jsonExport;
}

}

 - Modify the home.page.ts and  add a handleOnClick() method

 ```ts
import { Component } from '@angular/core';
import { IonicModule } from '@ionic/angular';
import { UsersComponent } from '../components/users/users.component';
import { StorageService } from '../services/storage.service';
import { SQLiteService } from '../services/sqlite.service';

@Component({
  selector: 'app-home',
  templateUrl: 'home.page.html',
  styleUrls: ['home.page.scss'],
  standalone: true,
  imports: [IonicModule, UsersComponent ],
})
export class HomePage {
  constructor(private sqliteService: SQLiteService,
              private storage: StorageService) {}

  async handleOnClick () {
    const jsonUserDB = await this.storage.exportUserDB();
    console.log(`Users export: ${JSON.stringify(jsonUserDB)}`);
  }
}

• Modify the home.page.html to add a button in the header to export the database

<ion-header [translucent]="true">
  <ion-toolbar>
    <ion-buttons slot="end" >
      <ion-button (click)="handleOnClick()">
        <ion-icon name="download" slot="icon-only"></ion-icon>
        </ion-button>
      </ion-buttons>
    <ion-title>
    Managing Users
    </ion-title>
  </ion-toolbar>
</ion-header>

<ion-content [fullscreen]="true">
  <ion-header collapse="condense">
      <ion-toolbar>
      <ion-title size="large">Managing Users</ion-title>
      </ion-toolbar>
  </ion-header>

  <app-users></app-users>
</ion-content>

and i ran the app on ios

• i entered two users Sue Hellen and Dave Watson

• and i click on the button and got as result

  [log] - User List: [{"id":1,"name":"Sue Hellen","age":null,"active":1},{"id":2,"name":"Dave Watson","age":null,"active":1}]

[log] - jsonExport: {"export":{"mode":"full","tables":[{"name":"users","values":[[1,"Sue Hellen",null,1],[2,"Dave Watson",null,1]],"schema":[{"column":"id","value":"INTEGER PRIMARY KEY AUTOINCREMENT"},{"column":"name","value":"TEXT NOT NULL"},{"column":"age","value":"NUMBER"},{"column":"active","value":"INTEGER DEFAULT 1"}]}],"database":"myuserdb","version":1,"encrypted":false}}

• and then in the Home.page

[log] - Users export: {"export":{"mode":"full","tables":[{"name":"users","values":[[1,"Sue Hellen",null,1],[2,"Dave Watson",null,1]],"schema":[{"column":"id","value":"INTEGER PRIMARY KEY AUTOINCREMENT"},{"column":"name","value":"TEXT NOT NULL"},{"column":"age","value":"NUMBER"},{"column":"active","value":"INTEGER DEFAULT 1"}]}],"database":"myuserdb","version":1,"encrypted":false}}

which is correct

[jepiqueau]

@VesperiniL @miguelchico hey guys, if you do not follow-up or not prepare to share what your are doing , please do not open an issue i have tried and not able to reproduce the issue. If you do not reply in the coming days i will close the issue

[jepiqueau]

@VesperiniL What type of values are you using do you have blob or something else in your tables that you intend to export

[jepiqueau]

@VesperiniL @miguelchico fixed in v5.6.1-3