capacitor-community / sqlite Goto Github PK
View Code? Open in Web Editor NEWCommunity plugin for native & electron SQLite databases
License: MIT License
Community plugin for native & electron SQLite databases
License: MIT License
Describe the bug
i have issues in listing all tables names from sqlite but it return only 1 tables name
To Reproduce
Steps to reproduce the behavior:
static func getTableInfo(){
do{
let statement :String = "select * from sqlite_master where type='table'"
prepareStatement(mdb?,sql)
}catch{
}
}
static func prepareStatement(mDB: OpaquePointer,statement : String) throws -> OpaquePointer? {
var statement: OpaquePointer?
guard sqlite3_prepare_v2(mDB, sql, -1, &statement, nil)
== SQLITE_OK else {
var message = "Error: querySQL prepare failed rc: (returnCodmessage:"
throw DatabaseHelperError.querySql(message: message)
}
return statement
}
When loading the plugin for electron, as described in the readme, CapacitorSQLPlugin.CapacitorSQLiteElectron
is undefined. Code Completion does not provide the suggestion for CapacitorSQLiteElectron
, hence the cast to "any".
Imports:
import { Plugins } from '@capacitor/core';
import * as CapacitorSQLPlugin from '@capacitor-community/sqlite';
const { CapacitorSQLite, Device } = Plugins;
Method to select the correct plugin
private async selectSqlitePlatform() {
console.warn('StorageService:selectSqlitePlatform')
if (this._sqlite !== undefined) {
console.warn('StorageService:selectSqlitePlatform -- Platform plugin seems to be loaded', this._sqlite)
}
// get platform name
const info = await Device.getInfo()
const platform = info.platform
if (platform === "ios") {
this._sqlite = CapacitorSQLite
}
else if (platform === "android") {
this._sqlite = CapacitorSQLite
try {
await this._sqlite.requestPermissions()
} catch (e) {
// permissions declined
}
}
else if (platform === "electron") {
console.log("electron")
this._sqlite = (CapacitorSQLPlugin as any).CapacitorSQLiteElectron // <------------ does not exist
}
else {
this._sqlite = CapacitorSQLPlugin.CapacitorSQLite
}
}
When I try the example service from the jeep starter project, there is no distinction between the platforms and so any action results in "Not implemented on Web Platform" (when running in electron).
Also, I can find no instance of "CapacitorSQLiteElectron" in the repo, thus questioning if this (still) exists?
I am working with Ionic 5 / Angular 10, access to the Project can be provided if needed.
Should it be an issue with versions, this is my package.json
{
"name": "",
"version": "4.0.0",
"author": "",
"homepage": "",
"scripts": {
"ng": "ng",
"start": "ng serve",
"build": "ng build",
"test": "ng test",
"lint": "ng lint",
"e2e": "ng e2e"
},
"private": true,
"dependencies": {
"@angular/common": "~10.0.0",
"@angular/core": "~10.0.0",
"@angular/forms": "~10.0.0",
"@angular/platform-browser": "~10.0.0",
"@angular/platform-browser-dynamic": "~10.0.0",
"@angular/router": "~10.0.0",
"@capacitor-community/electron": "^1.2.3",
"@capacitor-community/sqlite": "^2.4.2-2",
"@capacitor/android": "^2.4.2",
"@capacitor/core": "2.4.2",
"@capacitor/ios": "^2.4.2",
"@ionic-native/core": "^5.0.0",
"@ionic-native/splash-screen": "^5.0.0",
"@ionic-native/status-bar": "^5.0.0",
"@ionic/angular": "^5.0.0",
"ol": "^6.4.3",
"rxjs": "~6.5.5",
"tslib": "^2.0.0",
"zone.js": "~0.10.3"
},
"devDependencies": {
"@angular-devkit/build-angular": "~0.1000.0",
"@angular/cli": "~10.0.5",
"@angular/compiler": "~10.0.0",
"@angular/compiler-cli": "~10.0.0",
"@angular/language-service": "~10.0.0",
"@capacitor/cli": "2.4.2",
"@ionic/angular-toolkit": "^2.3.0",
"@types/jasmine": "~3.5.0",
"@types/jasminewd2": "~2.0.3",
"@types/node": "^12.11.1",
"codelyzer": "^6.0.0",
"jasmine-core": "~3.5.0",
"jasmine-spec-reporter": "~5.0.0",
"karma": "~5.0.0",
"karma-chrome-launcher": "~3.1.0",
"karma-coverage-istanbul-reporter": "~3.0.2",
"karma-jasmine": "~3.3.0",
"karma-jasmine-html-reporter": "^1.5.0",
"protractor": "~7.0.0",
"ts-node": "~8.3.0",
"tslint": "~6.1.0",
"typescript": "~3.9.5"
},
"description": ""
}
Describe the bug
await CapacitorSQLite.requestPermissions()
does not resume execution after storage permissions are granted on Android;
To Reproduce
Steps to reproduce the behavior:
await CapacitorSQLite.requestPermissions()
in your app initialization.Expected behavior
The app should halt the execution on requestPermissions()
and display a permission dialog, and resume the execution after the permissions have been granted.
Here is an extract of the Angular code I used:
if (info.platform === "android") {
try {
const sqlite = CapacitorSQLite as any;
console.log('REQUESTING PERMS');
await sqlite.requestPermissions();
console.log('GOT PERMS');
} 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();
}
}
I do get "REQUESTING PERMS", but never "GOT PERMS", after allowing storage, or if it was already allowed.
Smartphone:
Additional context
I'm using Angular, so I created a new issue despite the very similar case on #48
Also, unlike this issue, I don't have any other call to the database in my code.
I'm using version 2.4.2-9 of capacitor-community/sqlite.
I have run npx cap open android and able to run on simlator but i cant able to find sqlite DB location ,i want to open that sqlite db using sqlite Db Browser how its possible?
Hi, I am working on implementing custom plugin and in that plugin I want to insert records into sqlite using capacitor sqlite plugin…
Is there any example, where we can call method of one plugin from another plugin…
I have tried, but while calling a method need to pass PluginCall object as parameter… for that i need to pass message handler, PluginCall(this.handler,pluginId,callbackid,methodname,data); what will be the values for this parameters?
Any idea will be helpful for this implementation…
Thanks in advance…
https://forum.ionicframework.com/t/invoke-capacitor-sqlite-plugin-methods-from-custom-plugin/192713
Describe the bug
i have create plugin project and then use plugins and link plugin using npx cap link:bss after that in main projects install dependency using npp i plugin project file location
To Reproduce
Expected behavior
A clear and concise description of what you expected to happen.
Screenshots
If applicable, add screenshots to help explain your problem.
Desktop (please complete the following information):
Smartphone (please complete the following information):
Additional context
Add any other context about the problem here.
Describe the bug
On "larger" queries (e.g. a run
call with 20 Create Table statements or a executeSet
call with 30 Update queries) the android version will always throw an exception due to unfinalized statements or unfinished backups.
The exact Exceptions always thrown are:
E/Capacitor: Serious error executing plugin
java.lang.reflect.InvocationTargetException
at java.lang.reflect.Method.invoke(Native Method)
at com.getcapacitor.PluginHandle.invoke(PluginHandle.java:99)
at com.getcapacitor.Bridge$1.run(Bridge.java:521)
at android.os.Handler.handleCallback(Handler.java:883)
at android.os.Handler.dispatchMessage(Handler.java:100)
at android.os.Looper.loop(Looper.java:214)
at android.os.HandlerThread.run(HandlerThread.java:67)
Caused by: net.sqlcipher.database.SQLiteException: unable to close due to unfinalized statements or unfinished backups: sqlite3_close() failed
at net.sqlcipher.database.SQLiteDatabase.dbclose(Native Method)
at net.sqlcipher.database.SQLiteDatabase.onAllReferencesReleased(SQLiteDatabase.java:517)
at net.sqlcipher.database.SQLiteDatabase.close(SQLiteDatabase.java:1405)
at com.getcapacitor.community.database.sqlite.cdssUtils.SQLiteDatabaseHelper.execSet(SQLiteDatabaseHelper.java:271)
at com.getcapacitor.community.database.sqlite.CapacitorSQLite.executeSet(CapacitorSQLite.java:173)
at java.lang.reflect.Method.invoke(Native Method)
at com.getcapacitor.PluginHandle.invoke(PluginHandle.java:99)
at com.getcapacitor.Bridge$1.run(Bridge.java:521)
at android.os.Handler.handleCallback(Handler.java:883)
at android.os.Handler.dispatchMessage(Handler.java:100)
at android.os.Looper.loop(Looper.java:214)
at android.os.HandlerThread.run(HandlerThread.java:67)
E/AndroidRuntime: FATAL EXCEPTION: CapacitorPlugins
Process: io.ionic.starter, PID: 28875
java.lang.RuntimeException: java.lang.reflect.InvocationTargetException
at com.getcapacitor.Bridge$1.run(Bridge.java:530)
at android.os.Handler.handleCallback(Handler.java:883)
at android.os.Handler.dispatchMessage(Handler.java:100)
at android.os.Looper.loop(Looper.java:214)
at android.os.HandlerThread.run(HandlerThread.java:67)
Caused by: java.lang.reflect.InvocationTargetException
at java.lang.reflect.Method.invoke(Native Method)
at com.getcapacitor.PluginHandle.invoke(PluginHandle.java:99)
at com.getcapacitor.Bridge$1.run(Bridge.java:521)
at android.os.Handler.handleCallback(Handler.java:883)
at android.os.Handler.dispatchMessage(Handler.java:100)
at android.os.Looper.loop(Looper.java:214)
at android.os.HandlerThread.run(HandlerThread.java:67)
Caused by: net.sqlcipher.database.SQLiteException: unable to close due to unfinalized statements or unfinished backups: sqlite3_close() failed
at net.sqlcipher.database.SQLiteDatabase.dbclose(Native Method)
at net.sqlcipher.database.SQLiteDatabase.onAllReferencesReleased(SQLiteDatabase.java:517)
at net.sqlcipher.database.SQLiteDatabase.close(SQLiteDatabase.java:1405)
at com.getcapacitor.community.database.sqlite.cdssUtils.SQLiteDatabaseHelper.execSet(SQLiteDatabaseHelper.java:271)
at com.getcapacitor.community.database.sqlite.CapacitorSQLite.executeSet(CapacitorSQLite.java:173)
at java.lang.reflect.Method.invoke(Native Method)
at com.getcapacitor.PluginHandle.invoke(PluginHandle.java:99)
at com.getcapacitor.Bridge$1.run(Bridge.java:521)
at android.os.Handler.handleCallback(Handler.java:883)
at android.os.Handler.dispatchMessage(Handler.java:100)
at android.os.Looper.loop(Looper.java:214)
at android.os.HandlerThread.run(HandlerThread.java:67)
When i execute the statements in executeSet
one after the other with run
, everything finishes without errors.
Smartphone:
Any idea why this could happen?
Thanks in advance!
Just a quick performance question.
If I am accessing the db multiple times, should I just keep the db open or there is a good (security or performance) reason to be opening and closing it?
Hello,
I'm trying to execute a SELECT
sql statement as described in the readme.
But the result looks like this:
Select result:
{
"values":[],
"message":"Query command failed : Error: selectSQL prepare failed"
}
This is the relevant excerpt of my code:
const selectStatement = 'SELECT * FROM mytable';
const result = await this._sqlite.query({selectStatement, values: []});
console.log('Select result':, result);
I am trying this on my iPhone 10 device.
Am I doing something wrong or is there a bug?
Hi, im trying to use this plugins and i have had some problems on android, im going to list them.
1.- When query return no values, it also send a message: "Query command failed", its a lil confusing because its not an error.
2.- When there is an error on any kind of query (query, create, insert) its return 1 no -1. ill show my code and pics. (the code is almost the same as the examples)
i can guess that my error here is because of the index word so it wont create the table.
here is a simple print on execute and run methods
So the insert will fail too because there is not table with that name.
both responses are set as 1 not -1.
You can test it with ur example angular-sqlite-app-starter and forcing any error.
thx for ur help.
Would be really good to clean up the types because currently one would think we can also specify the database name on every call or run statements directly on database open call.
We should split up capSQLiteOptions capSQLiteResult in multiple interfaces.
Hi!
I am using Sqlite plugin v. 2.1.0-3 and have some difficulties exporting my DB in 'full' mode to Json.
The scheme of the DB is defined in normal way, as follows:
const sqlcmd = `
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS table1 (
table1id INTEGER PRIMARY KEY NOT NULL,
col1 TEXT UNIQUE NOT NULL
);
CREATE TABLE IF NOT EXISTS table2 (
table2id INTEGER PRIMARY KEY NOT NULL,
col1 TEXT UNIQUE NOT NULL
);
CREATE TABLE IF NOT EXISTS table3 (
table3id INTEGER PRIMARY KEY NOT NULL,
table1id INTEGER NOT NULL,
table2id INTEGER DEFAULT 1,
col1 TEXT NOT NULL,
col2 TEXT,
FOREIGN KEY(table1id) REFERENCES table1(table1id) ON DELETE CASCADE,
FOREIGN KEY(table2id) REFERENCES table2(table2id) ON DELETE SET DEFAULT
);
CREATE INDEX IF NOT EXISTS table3_index_table1_fk ON table3(table1id);
CREATE INDEX IF NOT EXISTS table3_index_table2_fk ON table3(table2id);
CREATE INDEX IF NOT EXISTS table3_index_col1 ON table3(col1);
CREATE INDEX IF NOT EXISTS table1_index_col1 ON table1(col1);
PRAGMA user_version = 1;
PRAGMA foreign_keys = ON;
COMMIT TRANSACTION;`;
Then I am using the following code to export the Db to json (similar to the code used in sqlite-starter project):
let result: any = await this.sqliteService.openDB(db_name);
if (result.result) {
let retExport = true;
const expResult: any = await this.sqliteService.exportToJson('full');
console.log('export result: ', expResult);
if (Object.keys(result.export).length === 0) { retExport = false; }
const jsObj: string = JSON.stringify(expResult.export);
const jsValidRes = await this.sqliteService.isJsonValid(jsObj);
if (!jsValidRes.result) { retExport = false; }
result = await this.sqliteService.close(environment.db_name);
...
However, by executing the exportToJson() method, I receive the following response:
{export: {}, message: "exportToJson: return Obj is not a JsonSQLite Obj"}
what could cause the problem? is there any restriction/prerequisite to use the exportToJson() function? is it ok to define the table scheme in normal way (i.e. not using Json import to create DB tables)? is it fine to have FOREIGN KEY constraints, DEFAULT and ON DELETE clauses etc. in the DB scheme, or they are not supported by exportToJson('full')?
Any advice would be greatly appreciated!
Hi @jepiqueau,
Thanks again for this plugin - we're using it extensively.
I'm having an issue in iOS only where the ImportFromJson method is failing due to the PRAGMA setup in the createDatabaseSchema method -> https://github.com/jepiqueau/capacitor-sqlite/blob/master/ios/Plugin/DatabaseSQLiteHelper.swift#L528
This fails on the execSQL step in the do block and throws the error "ImportFromJson command failed: Error: execSQL Error: execute failed."
The contents of the JSON I am attempting to import never get evaluated at all as the method exits before the tables are iterated over.
I've tried using both the "full" and "partial" modes and included/removed the table schemas as required in your documentation. They both produce the same result.
My app runs two imports to initialise the database tables and some data when the app is opened up which are working fine so it is only when the user is prompted to sync the local database with data from cloud storage that this occurs.
Is it necessary to run the pragmas on every import and would this call to ImportFromJson conflict with any other queries that may have run or are about to be called?
Let me know what other information might help.
Thanks!
Describe the bug
Property 'CapacitorSQLiteElectron' does not exist on type 'typeof import("../node_modules/@capacitor-community/sqlite/dist/esm/index")'
To Reproduce
on documentation:
async ngAfterViewInit()() {
const info = await Device.getInfo();
if (info.platform === "ios" || info.platform === "android") {
this._sqlite = CapacitorSQLite;
} else if(info.platform === "electron") {
this._sqlite = CapacitorSQLPlugin.CapacitorSQLiteElectron; <--------
} else {
this._sqlite = CapacitorSQLPlugin.CapacitorSQLite;
}
}
Describe the bug
When passing a null
value as a parameter on a INSERT statement, on Android the insert is performed with NULL values as expected. On iOS I get an error "Not a SQL type".
To Reproduce
Attempt to perform an INSERT like this (assume this._sqlite
is an open database with a table named Table and a column. The column is of type TEXT and nullable):
const statement = 'INSERT INTO Table(column) VALUES (?);';
const values = [null];
await this._sqlite.run({statement, values});
Expected behavior
An insert is performed and the value inserted is NULL, in any platform.
Actual behavior
On Android and Electron the INSERT succeeds. On iOS the insert fails with a "Not a SQL type" error ( see the line generating the error ).
Smartphone (please complete the following information):
capacitor/core: 2.4.2
capacitor/android: 2.4.2
capacitor/ios: 2.4.2
capacitor-community/sqlite: 2.4.2-8
Additional context
I've worked around the problem by doing a check if the value is null, then instead of using ?
I straight append NULL to the query, like this. It is an acceptable workaround while this issue is fixed:
const statement = 'INSERT INTO Table(column) VALUES (' + (param === null ? '?' : 'NULL') + ');';
// to ensure query safety we still use the parameter array for the non null values
const values = (param === null ? [] : [param]);
await this._sqlite.run({statement, values});
I have change the cordova plugin to this plugin when I update my app to capacitor, but I have a several problem with this plugin. When I do insert and this query have an error (e. duplicate primary key, invalid column type, ...) the return is {changes: -1, PrepareSQL step failed}... at this moment all the next querys returns the same error.
To works fine, I have dead the app and open back
Hi there! My name is Mike and I'm from the Ionic team. We've recently started up the capacitor-community org on github to bring together capacitor plugin authors and provide a semi-official place to maintain plugins.
I wanted to reach out and see if you'd be up for joining the org and having this sqlite plugin be part of that group.
https://github.com/capacitor-community
Let me know if you'd like to join and we can go from there! Cheers!
Currently the databases are stored under C:\Users\xxx\AppData\Local\Programs\xxx\Databases
.
This folder however gets cleared when updateing the application.
The DB should be stored under C:\Users\xxx\AppData\Roaming\xxx\databases
or somewhere similar so it does not get deleted.
Is your feature request related to a problem? Please describe.
I'm trying to work on a remote db file coming from our backend but i'm not sure if the library allows to work with external sources.
Describe the solution you'd like
I'd like a documented procedure to import external files as dbs on my app.
Describe alternatives you've considered
I've considered using .sql set of queries to recreate the db dynamically but this solution is not good for performance.
I store JavaScript milliseconds (e.g. Date.now()
) in a field defined as:
starttime INTEGER NOT NULL
Before the export the value is stored correctly:
1590952516107
== Sun May 31 2020 21:15:16 GMT+0200 (Central European Summer Time)
When exported, the value is incorrectly represented as:
1814616587
== Thu Jan 22 1970 01:03:36 GMT+0100 (Central European Standard Time)
Looking at the capacitor-sqlite
source code, it seems that Sqlite INTEGER
is stored in a Java Integer
, which results in an overflow.
The following test confirms this:
int i = (int) 1590952516107L;
System.out.println("i = " + i); // => i = 1814616587
Given that Sqlite's INTEGER
can store up to 8 bytes, the Java data type should be Long
, not Integer
.
Describe the bug
I try to create database using sqlite service from https://github.com/jepiqueau/angular-sqlite-app-starter
I have next code:
app.component.ts:
async ngAfterViewInit() {
await this.databaseInitService.initializeDatabase();
}
database.initialization.service.ts
public async initializeDatabase(): Promise<any> {
return this.sqliteService.initializePlugin().then(initResult => {
console.log('Initialize result:' + initResult);
this.sqliteService.getEcho('Hello!');
this.sqliteService.openDB(this.dbName).then(openDBResult => {
console.log('Database open result:' + JSON.stringify(openDBResult));
this.sqliteService.execute(this.initCmd);
this.sqliteService.execute(this.seedData);
});
});
}
But after launching my app I have next exception:
/io.ionic.starter E/AndroidRuntime: FATAL EXCEPTION: main
Process: io.ionic.starter, PID: 11955
java.lang.RuntimeException: Failure delivering result ResultInfo{who=@android:requestPermissions:, request=9538, result=-1, data=Intent { act=android.content.pm.action.REQUEST_PERMISSIONS (has extras) }} to activity {io.ionic.starter/io.ionic.starter.MainActivity}: java.lang.NullPointerException: Attempt to invoke virtual method 'void com.getcapacitor.PluginCall.resolve()' on a null object reference
at android.app.ActivityThread.deliverResults(ActivityThread.java:4845)
at android.app.ActivityThread.handleSendResult(ActivityThread.java:4886)
at android.app.servertransaction.ActivityResultItem.execute(ActivityResultItem.java:51)
at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:135)
at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:95)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2016)
at android.os.Handler.dispatchMessage(Handler.java:107)
at android.os.Looper.loop(Looper.java:214)
at android.app.ActivityThread.main(ActivityThread.java:7356)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:492)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:930)
Caused by: java.lang.NullPointerException: Attempt to invoke virtual method 'void com.getcapacitor.PluginCall.resolve()' on a null object reference
at com.getcapacitor.community.database.sqlite.CapacitorSQLite.handleRequestPermissionsResult(CapacitorSQLite.java:527)
at com.getcapacitor.Bridge.onRequestPermissionsResult(Bridge.java:747)
at com.getcapacitor.BridgeActivity.onRequestPermissionsResult(BridgeActivity.java:206)
at android.app.Activity.dispatchRequestPermissionsResult(Activity.java:8264)
at android.app.Activity.dispatchActivityResult(Activity.java:8114)
at android.app.ActivityThread.deliverResults(ActivityThread.java:4838)
at android.app.ActivityThread.handleSendResult(ActivityThread.java:4886)
at android.app.servertransaction.ActivityResultItem.execute(ActivityResultItem.java:51)
at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:135)
at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:95)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2016)
at android.os.Handler.dispatchMessage(Handler.java:107)
at android.os.Looper.loop(Looper.java:214)
at android.app.ActivityThread.main(ActivityThread.java:7356)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:492)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:930)
To Reproduce
Steps to reproduce the behavior:
I have created a repository with code where you can reproduce the bug:
https://github.com/eugenezhornyak/ionic-sqlite-example
Expected behavior
SQLite database initialized and my repository class can execute queries to it.
Desktop:
Android Studio 4.1.1
Build #AI-201.8743.12.41.6953283, built on November 5, 2020
Runtime version: 1.8.0_242-release-1644-b01 amd64
VM: OpenJDK 64-Bit Server VM by JetBrains s.r.o
Windows 10 10.0
Smartphone (please complete the following information):
Name: Pixel_2_API_29
CPU/ABI: Google Play Intel Atom (x86)
Target: google_apis_playstore [Google Play] (API level 29)
Skin: pixel_2
fastboot.chosenSnapshotFile:
runtime.network.speed: full
hw.device.name: pixel_2
hw.lcd.width: 1080
image.androidVersion.api: 29
tag.id: google_apis_playstore
hw.mainKeys: no
avd.ini.displayname: Pixel 2 API 29
hw.gpu.mode: auto
PlayStore.enabled: true
fastboot.forceColdBoot: no
hw.keyboard: yes
hw.sensors.proximity: yes
hw.dPad: no
hw.lcd.height: 1920
skin.dynamic: yes
hw.device.manufacturer: Google
image.sysdir.1: system-images\android-29\google_apis_playstore\x86
showDeviceFrame: yes
AvdId: Pixel_2_API_29
hw.lcd.density: 420
hw.arc: false
hw.device.hash2: MD5:55acbc835978f326788ed66a5cd4c9a7
fastboot.forceChosenSnapshotBoot: no
fastboot.forceFastBoot: yes
hw.trackBall: no
tag.display: Google Play
runtime.network.latency: none
disk.dataPartition.size: 6442450944
hw.sensors.orientation: yes
avd.ini.encoding: UTF-8
hw.gpu.enabled: yes
Hi again @jepiqueau,
I've just noticed that the exportToJson method is iOS is converting values that are a 0 or an empty string "" to "NULL" when exporting. As a result, when I re-import the exported JSON, the import fails due to NOT NULL CONSTRAINT failures.
An example of the exported JSON from iOS is:
"values":[
[1,"Access road","NULL","NULL"],
[2,"Accessway","NULL","NULL"],
[3,"Air handling system","NULL","NULL"],
[4,"Air lock","NULL","NULL"]
]
Where all the "NULL" strings should be zeros.
The Android exportToJson exports the values as zeros correctly:
"values": [
[1,"Access` road",0,0],
[2,"Accessway",0,0],
[3,"Air handling system",0,0],
[4,"Air lock",0,0]
]
The same applies when the value should be an empty string:
iOS:
[5, "position", ""NULL],
[6, "company", "NULL"]
vs. Android:
[5, "position", ""],
[6, "company", ""]
Let me know if I can be of any further assistance resolving this.
When i try to execute the executeSet command on iOS i receive the following error message (all other commands are fine):
To Native -> CapacitorSQLite executeSet 132074728
Error: Plugin CapacitorSQLite does not respond to method call "executeSet" using selector "executeSet:".
Ensure plugin method exists, uses @objc in its declaration, and arguments match selector without callbacks in CAP_PLUGIN_METHOD.
I used the example code to reproduce the error on iOS 13.1 in the Simulator.
I'm running CapacitorSQLite Version 2.2.1-3
Code:
import { Plugins } from "@capacitor/core";
const { CapacitorSQLite } = Plugins;
const set: Array<any> = [
{
statement:"INSERT INTO users (name,FirstName,email,age,MobileNumber) VALUES (?,?,?,?,?);",
values: [
"Blackberry","Peter","[email protected]",69,"4405060708"
],
},
{
statement:"INSERT INTO users (name,FirstName,email,age,MobileNumber) VALUES (?,?,?,?,?);",
values: [
"Jones","Helen","[email protected]",42,"4404030201"
],
},
{
statement:"INSERT INTO users (name,FirstName,email,age,MobileNumber) VALUES (?,?,?,?,?);",
values: [
"Davison","Bill","[email protected]",45,"4405162732"
],
},
{
statement:"INSERT INTO users (name,FirstName,email,age,MobileNumber) VALUES (?,?,?,?,?);",
values: [
"Brown","John","[email protected]",35,"4405243853"
],
},
{
statement:"UPDATE users SET age = ? , MobileNumber = ? WHERE id = ?;",
values: [
51,"4404030237",2
],
}
];
CapacitorSQLite.executeSet({ set: set });
Good day. I'm currently playing with this plugin and seems good so far. The problem I'm facing right now is I don't know the ID of the newly created record when I run an INSERT INTO
query. The run
function only returns changes
. The idea came from the cordova-sqlite
plugin I'm using before in a cordova app and is pretty helpful since I could now query again the newly created record to get the result including the auto-generated ones since I already have the ID.
One workaround is to query the record with the highest primary key or with the latest dateCreated
or other markers for being latest, but it could have an overhead than querying by ID.
Also, for some scenario, you could return the ID without querying again the record as you already have the copy of the details of the record except the ID, so you could just attached the returned ID to the copy.
It would be cool if we could have this feature. Thanks! And thank you for developing this awesome plugin! ;)
It seems this package relies on Device.getInfo (which also is not clear, and should not be a requirement?) being called first - but Device.getInfo does not work in web workers.
Hi everybody plugin is nice, but when I trying to add references columns or foreign keys plugin not works, I test on batch run statements(execute()) or single run statement(run()), thanks for any help.
Hi!
I have just updated the sqlite plugin from version 2.0.0-3 to 2.1.0-3. and now I receive the following error when built for Android: “ERROR Error: Uncaught (in promise): Not implemented” as I call the openDB() function.
Here is the code:
await this.sqliteService.initializePlugin();
if (this.sqliteService.isService) {
// open the database
let result: any = await this.sqliteService.openDB(environment.db_name);
if (result.result) {
...
I have even tried running the "Ionic/Angular SQLite Application Starter"(https://github.com/jepiqueau/angular-sqlite-app-starter) and faced the same issue.
Could you please help me with this issue?
Below is the full stack trace
****** entering run tests
default~tab2-tab2-module~tab3-tab3-module-es2015.js:3972 isDBExists {database: "test-sqlite"}
vendor-es2015.js:41341 ERROR Error: Uncaught (in promise): Not implemented
at resolvePromise (polyfills-es2015.js:3904)
at polyfills-es2015.js:3811
at rejected (vendor-es2015.js:123718)
at ZoneDelegate.invoke (polyfills-es2015.js:3470)
at Object.onInvoke (vendor-es2015.js:73048)
at ZoneDelegate.invoke (polyfills-es2015.js:3469)
at Zone.run (polyfills-es2015.js:3229)
at polyfills-es2015.js:3963
at ZoneDelegate.invokeTask (polyfills-es2015.js:3505)
at Object.onInvokeTask (vendor-es2015.js:73026)
And here is the output of ionic info
Ionic:
Ionic CLI : 6.5.0 (/usr/local/lib/node_modules/@ionic/cli)
Ionic Framework : @ionic/angular 5.0.7
@angular-devkit/build-angular : 0.803.26
@angular-devkit/schematics : 8.3.26
@angular/cli : 8.3.26
@ionic/angular-toolkit : 2.2.0
Capacitor:
Capacitor CLI : 2.0.0
@capacitor/core : 2.0.0
Utility:
cordova-res : not installed
native-run : not installed
System:
NodeJS : v13.5.0 (/usr/local/Cellar/node/13.5.0/bin/node)
npm : 6.13.4
OS : macOS Catalina
Hello,
Is there a way to open an existing .db file on android and ios?
I'm using this plugin for the first time and I want to open an existing .db file ( called "mydatabase.db) which in pre-populated.
I am trying to execute select query to read data from sqlite tables. but while reading data all the column names are coming in lower case in ios but its coming pascal case in android.
Please help me out to get same column name from sqlite table.
Thanks in advance
Describe the bug
await CapacitorSQLite.requestPermissions()
does not resume execution after storage permissions are granted on Android.
To Reproduce
Steps to reproduce the behavior:
await CapacitorSQLite.requestPermissions()
where execution of app starts.Expected behavior
The app should halt the execution on requestPermissions()
and display a permission dialog, and resume the execution after the permissions have been granted.
E.g. given the following code:
async created() {
try {
console.log('0')
await CapacitorSQLite.requestPermissions();
console.log('1')
}
catch(e) {
console.log(e);
}
}
I would expect the to see 0
and 1
logged after permission has been granted.
However, what happens is that only 0
is logged and the permission dialog is displayed, but nothing after requestPermissions()
is executed. See screenshot below.
Smartphone (please complete the following information):
As far as i see, the plugin only supports a single database, since it stores the database handle internally after opening the db. What about returning the handle and/or some kind of db instance with the query methods?
Hi!
Thank you for the caring support.
I am using version 2.1.0-4 of the plugin and have some problem importing the DB that I have already exported. Here is the steps that I have taken ( the scenario is based on the andular-sqlite-app-starter):
let result:any = await this._SQLiteService.openDB("test-sqlite");
if(result.result) {
result = await this._SQLiteService.createSyncTable();
// create tables
const sqlcmd: string = `
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY NOT NULL,
email TEXT UNIQUE NOT NULL,
name TEXT,
company TEXT,
size FLOAT,
age INTEGER
);
CREATE TABLE IF NOT EXISTS messages (
id INTEGER PRIMARY KEY NOT NULL,
userid INTEGER,
title TEXT NOT NULL,
body TEXT NOT NULL,
FOREIGN KEY (userid) REFERENCES users(id) ON DELETE SET DEFAULT
);
CREATE TABLE IF NOT EXISTS images (
id INTEGER PRIMARY KEY NOT NULL,
name TEXT UNIQUE NOT NULL,
type TEXT NOT NULL,
size INTEGER,
img BLOB
);
CREATE INDEX users_index_name ON users (name);
PRAGMA user_version = 1;
COMMIT TRANSACTION;
`;
result = await this._SQLiteService.execute(sqlcmd);
console.log('****** export ******');
let result:any = await this._SQLiteService.openDB("test-sqlite");
if(result.result) {
let ret:boolean = true;
let result:any = await this._SQLiteService.exportToJson("full");
console.log('---> result fullexportToJson ',result);
if (Object.keys(result.export).length === 0) ret = false;
const jsObj: string = JSON.stringify(result.export);
result = await this._SQLiteService.isJsonValid(jsObj);
if(!result.result) ret = false;
console.log('****** import ******');
let impresult:any = await this._SQLiteService.importFromJson(jsObj);
if(impresult.changes.changes === -1 ){ ret = false;}
the exported json:
jsonObject {"database":"test-sqlite","encrypted":false,"mode":"full","tables":[{"name":"users","schema":[{"column":"id","value":"INTEGER PRIMARY KEY NOT NULL"},{"column":"email","value":"TEXT UNIQUE NOT NULL"},{"column":"name","value":"TEXT"},{"column":"company","value":"TEXT"},{"column":"size","value":"FLOAT"},{"column":"age","value":"INTEGER"}],"indexes":[{"name":"users","column":"users_index_name"}]},{"name":"messages","schema":[{"column":"id","value":"INTEGER PRIMARY KEY NOT NULL"},{"column":"userid","value":"INTEGER"},{"column":"title","value":"TEXT NOT NULL"},{"column":"body","value":"TEXT NOT NULL"},{"value":"REFERENCES users(id) ON DELETE SET DEFAULT","foreignkey":"userid"}]},{"name":"images","schema":[{"column":"id","value":"INTEGER PRIMARY KEY NOT NULL"},{"column":"name","value":"TEXT UNIQUE NOT NULL"},{"column":"type","value":"TEXT NOT NULL"},{"column":"size","value":"INTEGER"},{"column":"img","value":"BLOB"}]}]}
and here is the output of importFromJson() method:
changes: {changes: -1}
message: "importFromJson command failed: import JsonObject not successful"
As a side note, I first thought, the definition of indexes in the json object, returned by the exportToJson() might not be correct and this is causing the issue.
which is:
"indexes":[{"name":"users","column":"users_index_name"}]
In my opinion it should be (could you please check this also) like this:
"indexes":[{"name":"users_index_name","column":"name"}]
Nevertheless, I also tried removing the CREATE INDEX definition from the sql statement, but the issue was still there and I had no success importing what I had just exported. Could you please check this?
I truly appreciate all your support!
Any plans for PouchDB wrapper?
Do you think it might help data avilabilty in browsers and PWA use cases?
Is your feature request related to a problem? Please describe.
Currently for electron we need to include a bunch of stuff in the index.html and call a different plugin.
Describe the solution you'd like
With https://github.com/capacitor-community/electron this is not needed anymore and we can inlcude it without these hacks.
See also https://capacitor-community-electron-docs-site.vercel.app/extra/create-plugin
An example for a plugin can be found here: https://github.com/digaus/community-capacitor-wifi
We should do this before the refactor: #1 (comment)
First thanks for your work JEPIQUEAU, I am following the documentation for trying to run the plugin in the web development environment and then build with capacitor to android but I have this error:
Error: Uncaught (in promise): Not implemented
after invoking the open method.
The plugins do not work on the web?
I have sqlite db location got from simulator ,i have changed secret in GlobelSqlite
public class GlobalSQLite {
public String secret = "visur@123";
public String newsecret = "visur@123";
}
after that run on simulator to generate test-encryptedSQLite.db ,when i am opening on SQliteStudio with database type SQlCipher and load test-encryptedSQLite.db location and passed Cipher password which i have given above its not able to login and see structure.how to open sqlite file for viewing strcuture of table in sqlite
Good day. In this link for batch execution, is there any way I could include values to query statements? Based on the method signature, it only accepts statements. If this function is not yet implemented, I think we could use something like this:
execute([
{ statement: statement1, values: ['value1'] },
{ statement: statement2, values: ['value2'] }
])
Also, in this signature,
execute({statements:"fooStatements"}) => Promise<{changes:{changes:number},message:string}>
is fooStatements
an array or a couple of strings combined?
Thanks!
Any plan for adding sqlite orm? something like expo-sqlite-orm
maybe.
Hello! Could someone help me with a functional example of the importFromJson function.
I get this error "importFromJson command failed: Stringify Json Object not Valid"
please help!!!
thanks!
Hi!
while using capacitor-sqlite 2.0.0-3 on iOS, I faced the following exception trying to SELECT some data from a table containing some NULL values. ( the same code works just fine on Android)
Fatal error: Unexpectedly found nil while unwrapping an Optional value:
In file UtilsSQLite.swift
It seems that iOS version is not able to handle NULL values.
Could you please advice?
Describe the bug
i am trying to query sqlite db table for that i have useed UtillSqlite Query Function ,but when i am calling and passing select query for table ,its not checking while sqlite3_step(handle) == SQLITE_ROW
To Reproduce
Steps to reproduce the behavior:
Expected behavior
should return column name
here is error point
https://github.com/capacitor-community/sqlite/blob/master/ios/Plugin/Utils/UtilsSQLite.swift#L178
Is there any plan or possibility that spatialite will be integrated?
We are interested in spatialite support on Android, iOS and Electron platform.
Is your feature request related to a problem? Please describe.
The immediate permission request on load must not be mandatory, it's really aggressive.
Describe the solution you'd like
I would like to be able to request Storage permission myself when needed
Describe alternatives you've considered
For ANDROID
I've replaced the pluginRequestPermissions()
in the plugin load()
function by isPermissionGranted = false;
then used CapacitorSQLite.requestPermissions()
when needed in my Javascript which work but doesn't exist on the Typescript declaration and does not allow to wait for the result nor provide a listener.
Is your feature request related to a problem? Please describe.
I store spatial data in my app and want to efficiently query it: I need spatialite.
Describe the solution you'd like
For starters, I'd just like to have documentation that shows what needs to be done to change the underlying sqlite implementation.
If my request seems a common use case, one could consider ways to make swapping sqlite-implementations easy through packages or similar mechanisms.
Yes I know that currently the binding makes use of sqlcipher-specific features, but that I can work with.
Hi,
as soon as i enable the encryption every single request works, but takes ~500ms on my real android device.
(without encryption it is ~30ms).
After a look at your plugin this may be due to connection open & close with every request.
The question is:
Is 500ms a normal/expected value because it seems very high to me? (Samsung A50, so the phone is not that old)
Thanks & have a good day!
There are some inconsistencies in how the values[] array type is handled between iOS and Android.
On iOS queries, values array must be string[]. In Plugin.swift line 204
guard let values = call.options["values"] as? Array<String> else {
retValues(call:call,ret:[],message:"Query command failed : Values should be an Array of string")
return
}
On Android, any[] is accepted and the plugin will seemingly happily convert to strings. In CapacitorSQLite.java line 229
ArrayList<String> vals = new ArrayList<String>();
for (int i = 0; i < values.length(); i++) {
vals.add(values.getString(i));
}
This feature implies that something that works perfectly on Android may fail on iOS. This is unfortunate.
When i use the plugin on iOS, the emulator crashes with the following error :
Thread 5: Fatal error: Not enough bits to represent the passed value
It happens in UtilsSQLite.swift
, line 100
sqlite3_bind_int(handle,Int32(idx), Int32(value))
OS X 10.14.6
XCode 11.3.1
Describe the bug
i am doing select query on Sqlite database and usign SqliteUtill getColumnValue but when i am debugging i am always getting column value null
To Reproduce
Steps to reproduce the behavior:
Screenshots
https://github.com/capacitor-community/sqlite/blob/master/ios/Plugin/Utils/UtilsSQLite.swift#L104
If applicable, add screenshots to help explain your problem.
Desktop (please complete the following information):
Smartphone (please complete the following information):
Additional context
Add any other context about the problem here.
Great work. Looking for an electron solution aswell. For a business application we need to store a lot of data. You mentioned that you forwarded the question to Capacitor team. Any solution yet?
Wouldnt this solution also work for us:
https://stackoverflow.com/a/57964289
Related issue:
jepiqueau/capacitor-data-storage-sqlite#10
Hi,
I have ON DELETE CASCADE
statements in my DDL that work when I run them on my Sqlite installation on my PC, but seem to not be triggered in the plugin.
I have also tried executing
await db.execute({statements:`
BEGIN TRANSACTION;
PRAGMA foreign_keys = ON;
COMMIT TRANSACTION;`});
right after opening the db, but it makes no difference.
Are there any known limitation with ON DELETE CASCADE
in the plugin?
If not, can you please show an example that you would expect to work?
Regardless, thank you for a great plugin!
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.