Use copyFromAssets

[ncham]

I'm new to ionic and sqlite, my requirement is to copy prepopulated .db file to android device. I did read the documentation but bit confused using this library, about the connection and copyFromAssets() function.

Could anybody please give me a brief idea about this.

[jepiqueau]

@ncham you should look at the application starter referenced in the Readme to understand how to use this plugin before starting your development. The copyFromAssets allow you to set in your app prepopulated dbs. you store them into YOUR_APPLICATION/src/assets/databases folder. Then the first thing you do in your app is to copy the database(s) into the right location by using

await this._sqlite.copyFromAssets();

and then you create a connection to one of the db

let db = await this._sqlite
                  .createConnection("YOUR_DB_NAME", false, "no-encryption", 1);

and the open the db

      await db.open();

and then do whatever methods

      let res: any = await db.query("SELECT * FROM ONE_OF_YOUR_TABLES");

That is it. Hope this will help you to start

[ncham]

I implemented it according to the documentation and the return value of await this._sqlite.copyFromAssets(); was Msg: {"result":false,"message":"Not implemented on Web Platform"} But I'm running it on Android Studio emulator.

Return value of let dbS = await this._sqlite.createConnection("sample", false, "no-encryption", 1); was Msg: ERROR Error: Uncaught (in promise): Error: no db returned is null

After further investigation this.platform = Capacitor.getPlatform(); is android But const sqlitePlugin: any = CapacitorSQLite; value's platform is web Msg: SqlitePlugin{"config":{"name":"CapacitorSQLite","platforms":["web"]},"loaded":false,"listeners":{},"windowListeners":{}}

[jepiqueau]

@ncham Which version of the plugin are you using? If it is 2.9.13 or lower and you are on Android platform, register the plugin in your main activity:

import com.getcapacitor.community.database.sqlite.CapacitorSQLite;

public class MainActivity extends BridgeActivity {

  @Override
  public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);

    // Initializes the Bridge
    this.init(
        savedInstanceState,
        new ArrayList<Class<? extends Plugin>>() {
          {
            // Additional plugins you've installed go here
            // Ex: add(TotallyAwesomePlugin.class);
            add(CapacitorSQLite.class);
          }
        }
      );
  }
}

and it should work

[ncham]

Registered the plugin but still the same result.

The version I'm using is 2.9.13 and developing with ionic and angular. The app should run on both Android and iOS but still testing with Android.

I guess CapacitorSQLite is taken from sqlite/dist/esm/web.d.ts always.

[jepiqueau]

@ncham that not possible so show me your package.json file and the MainActivity.java file. make sure your MainActivity.jave file is the same than the one i gave above

Did you run this

npx cap sync
npm run build
npx cap copy
npx cap open android

[ncham]

Yes, I did run those commands.

package.json

{
  "name": "xxxxxx",
  "version": "0.0.1",
  "author": "Ionic Framework",
  "homepage": "https://ionicframework.com/",
  "scripts": {
    "ng": "ng",
    "start": "ng serve",
    "build": "ng build",
    "test": "ng test",
    "lint": "ng lint",
    "e2e": "ng e2e"
  },
  "private": true,
  "dependencies": {
    "@angular/common": "~11.2.0",
    "@angular/core": "~11.2.0",
    "@angular/forms": "~11.2.0",
    "@angular/platform-browser": "~11.2.0",
    "@angular/platform-browser-dynamic": "~11.2.0",
    "@angular/router": "~11.2.0",
    "@capacitor-community/sqlite": "^2.9.13",
    "@capacitor/android": "^2.4.7",
    "@capacitor/core": "2.4.7",
    "@ionic/angular": "^5.5.2",
    "rxjs": "~6.6.0",
    "tslib": "^2.0.0",
    "zone.js": "~0.10.2"
  },
  "devDependencies": {
    "@angular-devkit/build-angular": "~0.1101.4",
    "@angular/cli": "~11.1.4",
    "@angular/compiler": "~11.2.0",
    "@angular/compiler-cli": "~11.2.0",
    "@angular/language-service": "~11.2.0",
    "@capacitor/cli": "2.4.7",
    "@ionic/angular-toolkit": "^3.1.0",
    "@types/jasmine": "~3.6.0",
    "@types/jasminewd2": "~2.0.3",
    "@types/node": "^12.11.1",
    "codelyzer": "^6.0.0",
    "jasmine-core": "~3.6.0",
    "jasmine-spec-reporter": "~5.0.0",
    "karma": "~5.2.0",
    "karma-chrome-launcher": "~3.1.0",
    "karma-coverage": "~2.0.3",
    "karma-coverage-istanbul-reporter": "~3.0.2",
    "karma-jasmine": "~4.0.0",
    "karma-jasmine-html-reporter": "^1.5.0",
    "protractor": "~7.0.0",
    "ts-node": "~8.3.0",
    "tslint": "~6.1.0",
    "typescript": "~4.0.2"
  },
  "description": "An Ionic project"
}

MainActivity.java file

package io.ionic.starter;

import android.os.Bundle;
import com.getcapacitor.BridgeActivity;
import com.getcapacitor.Plugin;
import java.util.ArrayList;
import com.getcapacitor.community.database.sqlite.CapacitorSQLite;

public class MainActivity extends BridgeActivity {
  @Override
  public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);

    // Initializes the Bridge
    this.init(savedInstanceState, new ArrayList<Class<? extends Plugin>>() {{
      // Additional plugins you've installed go here
      // Ex: add(TotallyAwesomePlugin.class);
      add(CapacitorSQLite.class);
    }});
  }
}

[jepiqueau]

@ncham It should work if you did run all the commands. i started from scratch a new application this morning did all the steps and it works. see https://github.com/jepiqueau/testissue93 Hopes this clarify and ends this issue. I will let you close it when it successful on your site

[ncham]

Perfect, I tested your code and it's working. Then I compared with my coding which I copied from the doumentation

Original

import { Capacitor } from '@capacitor/core';
import { CapacitorSQLite, SQLiteDBConnection, SQLiteConnection, capSQLiteSet,
         capSQLiteChanges, capEchoResult, capSQLiteResult 
        } from '@capacitor-community/sqlite';
....
async closeAllConnections(): Promise<void>
....
async copyFromAssets(): Promise<void> 

Changed to

import { Plugins, Capacitor } from '@capacitor/core';
import { SQLiteDBConnection, SQLiteConnection, capSQLiteSet,
      capSQLiteChanges, capEchoResult, capSQLiteResult
      } from '@capacitor-community/sqlite';
const { CapacitorSQLite } = Plugins;
.....
async closeAllConnections(): Promise<capSQLiteResult> 
......
async copyFromAssets(): Promise<capSQLiteResult>

Then it worked. Thanks for the support.

Once the .db file is copied to the device, is it ok to delete the file? How should I deploy the app to the real world with .db?

[jepiqueau]

@ncham if the db is part of your asset and is required as initial db to start your app you have to keep it and when the app starts you check if the db exists, if not you copy it from the asset and the open it otherwise you open it Voilà. I close the issue and i will remove the example app from github

[ncham]

Thanks.

[binaryking979]

I downloaded sqlite database from server. I am testing on android emulator too. It saved at "/storage/emulated/0/download." But I cannot connect this database. How can I use this database? Please help me

[jepiqueau]

@binaryking979 you must use the downloadFromHttp method of the plugin it will download the database in the right location (database folder of your app ) which is not /storage/emulated/0/download and you could use it in your app

[binaryking979]

Thank you for you help. Do you mean downloadFromHttp method of capacitor plugin? But I cannot fine this method.

[jepiqueau]

@binaryking979 sorry i mean getFromHTTPRequest