SQL driving me mental

borconi · June 8, 2017, 3:08pm

I need some advice because I’m really starting to get desperate!
I’m on ionic 3 by the way.

This is my database service:

import { Injectable } from '@angular/core';
import {SQLite} from 'ionic-native';
import 'rxjs/add/operator/map';

declare var window: any;

@Injectable()
export class MyDatabase {
	
	public db: any;
	public dbname: string = 'Eclean';

	constructor() { }

	/**
	 * Init - init database etc. PS! Have to wait for Platform.ready
	 */
	init(): Promise<any> {
		return new Promise(resolve => {
		/*	if (typeof window.sqlitePlugin !== 'undefined') {
				this.db = window.sqlitePlugin.openDatabase({ name: this.dbname, location: 'default',androidDatabaseImplementation: 2, androidLockWorkaround: 1});
				 console.log("--> running on device: ", this.db);
				resolve();
			} else {*/
				this.db = window.openDatabase(this.dbname, '1.0', 'Eclean', 2*1024*1024);
				 console.log("--> running in browser: ", this.db);
				resolve();
			//};
		});
	}

	/**
	 * query - executes sql
	 */
	 
	 	 q2 (q: string, params?: any) {							
		 params = params || [];
			this.db.transaction((tx) => {
				tx.executeSql(q, params, function(tx, results) {
					console.log("Success: "+q+" Result: "+JSON.stringify(results));
					return [results,null];
						
				}, function(tx, error) {
					console.log("Error: " + JSON.stringify(error));
					return [null,error];
				});
			});
		 
	 }
	 
	 
	query(q: string, params?: any): Promise<any> {
		return new Promise((resolve, reject) => {
			params = params || [];
			this.db.transaction((tx) => {
				tx.executeSql(q, params, (tx, res) => {
					resolve(res);
				}, (tx, err) => {
					reject(err);
				});
			});
		});
	}
}

This is the call on platform ready:


  platform.ready().then(() => {
		db.init().then(res=>{
		db.query('CREATE TABLE IF NOT EXISTS USERS (phone unique, firstn, lastn)');  
		db.query('CREATE TABLE IF NOT EXISTS WHOSIGNED (storeid, firstn, lastn, UNIQUE (storeid, firstn, lastn) ON CONFLICT REPLACE)'); 
		db.query('CREATE TABLE IF NOT EXISTS WHOCANSIGN (last_modified unique, client INTEGER, rep_pos)'); 	
		db.query('CREATE TABLE IF NOT EXISTS RISKDEF (id unique, title, pdf INTEGER, last_modified INTEGER)'); 	
		db.query('CREATE TABLE IF NOT EXISTS ASSIGNEDREP (id unique, company INTEGER, who_can_id INTEGER, stat INTEGER, last_modified INTEGER)'); 
		db.query('CREATE TABLE IF NOT EXISTS MYWORK (scheduleid unique, status INTEGER, schedule_date INTEGER, location_id INTEGER, company_id INTEGER, Comp_Name, Short_add, Long_add, Latitude NUMERIC, Longitude NUMERIC , risk INTEGER, question INTEGER, autopupluate INTEGER, override, phone, job_description INTEGER, last_modified INTEGER)'); 
		db.query('CREATE TABLE IF NOT EXISTS COMPLETEDJOBS (scheduleid unique, completition_date INTEGER, Latitude NUMERIC, Longitude NUMERIC, acc, rep_pos INTEGER, rep_name, picture TEXT, signature TEXT,satisfied INTEGER,comment)'); 
		db.query('CREATE TABLE IF NOT EXISTS JOBDESC (id INTEGER unique, owner INTEGER, short_desc, long_desc, last_modified INTEGER)'); 
		db.query('CREATE TABLE IF NOT EXISTS MANUALJOB (company,address,job,reppos,repname,picture,signature,lat,lng,acc,sigdate)'); 
		//db.query('DELETE from USERS',[],function(tx,results){console.log("Deleted users ok");});	
		//db.query('DELETE from MYWORK',[],function(tx,results){console.log("Deleted work ok");});	
		console.log(db.q2('INSERT INTO USERS (firstn, lastn, phone) values ("Emil","Bor","0000000000")', []));
		console.log(db.q2('SELECT * FROM USERS', []));
		
		db.query('INSERT INTO USERS (firstn, lastn, phone) values ("Emil","Bor","0000000000")', []).then(res=>{
					console.log("Successfully inserted");
						 //aux.navController.pop();
				}).catch(err=>  {
					console.log(err);
				});
		db.query('SELECT * FROM USERS', []).then(res=>{
					console.log("Result" + JSON.stringify(res));
						 //aux.navController.pop();
				}).catch(err=>  {
					console.log("Result" + JSON.stringify(err));
				});
						
				});
				
		
		
	  });

I’m expecting my select query to return a result, which it does in browser and on one of my phones but on the second phone it just returns an error, here are the logcats from the phones after a fresh install. As you can see phone 1, everything is OK, phone 2, not so much!

Any clue?
Thank you!

Phone 1:

06-08 15:59:22.180 8656-8656/com.ionicframework.eclean372823 D/CordovaWebViewImpl: onPageFinished(file:///android_asset/www/index.html)
06-08 15:59:22.210 8656-8656/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62170 : Success: CREATE TABLE IF NOT EXISTS USERS (phone unique, firstn, lastn) Result: {}
06-08 15:59:22.210 8656-8656/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62170)] "Success: CREATE TABLE IF NOT EXISTS USERS (phone unique, firstn, lastn) Result: {}", source: file:///android_asset/www/build/main.js (62170)
06-08 15:59:22.220 8656-8656/com.ionicframework.eclean372823 E/cr_LocationProvider: Caught security exception while registering for location updates from the system. The application does not have sufficient geolocation permissions.
06-08 15:59:22.270 8656-8656/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62170 : Success: CREATE TABLE IF NOT EXISTS WHOSIGNED (storeid, firstn, lastn, UNIQUE (storeid, firstn, lastn) ON CONFLICT REPLACE) Result: {}
06-08 15:59:22.270 8656-8656/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62170)] "Success: CREATE TABLE IF NOT EXISTS WHOSIGNED (storeid, firstn, lastn, UNIQUE (storeid, firstn, lastn) ON CONFLICT REPLACE) Result: {}", source: file:///android_asset/www/build/main.js (62170)
06-08 15:59:22.290 8656-8656/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62170 : Success: CREATE TABLE IF NOT EXISTS WHOCANSIGN (last_modified unique, client INTEGER, rep_pos) Result: {}
06-08 15:59:22.290 8656-8656/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62170)] "Success: CREATE TABLE IF NOT EXISTS WHOCANSIGN (last_modified unique, client INTEGER, rep_pos) Result: {}", source: file:///android_asset/www/build/main.js (62170)
06-08 15:59:22.310 8656-8656/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62170 : Success: CREATE TABLE IF NOT EXISTS RISKDEF (id unique, title, pdf INTEGER, last_modified INTEGER) Result: {}
06-08 15:59:22.310 8656-8656/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62170)] "Success: CREATE TABLE IF NOT EXISTS RISKDEF (id unique, title, pdf INTEGER, last_modified INTEGER) Result: {}", source: file:///android_asset/www/build/main.js (62170)
06-08 15:59:22.330 8656-8656/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62170 : Success: CREATE TABLE IF NOT EXISTS ASSIGNEDREP (id unique, company INTEGER, who_can_id INTEGER, stat INTEGER, last_modified INTEGER) Result: {}
06-08 15:59:22.330 8656-8656/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62170)] "Success: CREATE TABLE IF NOT EXISTS ASSIGNEDREP (id unique, company INTEGER, who_can_id INTEGER, stat INTEGER, last_modified INTEGER) Result: {}", source: file:///android_asset/www/build/main.js (62170)
06-08 15:59:22.350 8656-8656/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62170 : Success: CREATE TABLE IF NOT EXISTS MYWORK (scheduleid unique, status INTEGER, schedule_date INTEGER, location_id INTEGER, company_id INTEGER, Comp_Name, Short_add, Long_add, Latitude NUMERIC, Longitude NUMERIC , risk INTEGER, question INTEGER, autopupluate INTEGER, override, phone, job_description INTEGER, last_modified INTEGER) Result: {}
06-08 15:59:22.350 8656-8656/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62170)] "Success: CREATE TABLE IF NOT EXISTS MYWORK (scheduleid unique, status INTEGER, schedule_date INTEGER, location_id INTEGER, company_id INTEGER, Comp_Name, Short_add, Long_add, Latitude NUMERIC, Longitude NUMERIC , risk INTEGER, question INTEGER, autopupluate INTEGER, override, phone, job_description INTEGER, last_modified INTEGER) Result: {}", source: file:///android_asset/www/build/main.js (62170)
06-08 15:59:22.370 8656-8656/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62170 : Success: CREATE TABLE IF NOT EXISTS COMPLETEDJOBS (scheduleid unique, completition_date INTEGER, Latitude NUMERIC, Longitude NUMERIC, acc, rep_pos INTEGER, rep_name, picture TEXT, signature TEXT,satisfied INTEGER,comment) Result: {}
06-08 15:59:22.370 8656-8656/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62170)] "Success: CREATE TABLE IF NOT EXISTS COMPLETEDJOBS (scheduleid unique, completition_date INTEGER, Latitude NUMERIC, Longitude NUMERIC, acc, rep_pos INTEGER, rep_name, picture TEXT, signature TEXT,satisfied INTEGER,comment) Result: {}", source: file:///android_asset/www/build/main.js (62170)
06-08 15:59:22.400 8656-8656/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62170 : Success: CREATE TABLE IF NOT EXISTS JOBDESC (id INTEGER unique, owner INTEGER, short_desc, long_desc, last_modified INTEGER) Result: {}
06-08 15:59:22.400 8656-8656/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62170)] "Success: CREATE TABLE IF NOT EXISTS JOBDESC (id INTEGER unique, owner INTEGER, short_desc, long_desc, last_modified INTEGER) Result: {}", source: file:///android_asset/www/build/main.js (62170)
06-08 15:59:22.420 8656-8656/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62170 : Success: CREATE TABLE IF NOT EXISTS MANUALJOB (company,address,job,reppos,repname,picture,signature,lat,lng,acc,sigdate) Result: {}
06-08 15:59:22.420 8656-8656/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62170)] "Success: CREATE TABLE IF NOT EXISTS MANUALJOB (company,address,job,reppos,repname,picture,signature,lat,lng,acc,sigdate) Result: {}", source: file:///android_asset/www/build/main.js (62170)
06-08 15:59:22.440 8656-8656/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62156 : Success: INSERT INTO USERS (firstn, lastn, phone) values ("Emil","Bor","0000000000") Result: {}
06-08 15:59:22.440 8656-8656/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62156)] "Success: INSERT INTO USERS (firstn, lastn, phone) values ("Emil","Bor","0000000000") Result: {}", source: file:///android_asset/www/build/main.js (62156)
06-08 15:59:22.460 8656-8656/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62156 : Success: SELECT * FROM USERS Result: {}
06-08 15:59:22.460 8656-8656/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62156)] "Success: SELECT * FROM USERS Result: {}", source: file:///android_asset/www/build/main.js (62156)
06-08 15:59:22.460 8656-8656/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62173 : [object SQLError]
06-08 15:59:22.460 8656-8656/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62173)] "[object SQLError]", source: file:///android_asset/www/build/main.js (62173)
06-08 15:59:22.460 8656-8656/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 61617 : [object SQLError]
06-08 15:59:22.460 8656-8656/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(61617)] "[object SQLError]", source: file:///android_asset/www/build/main.js (61617)
06-08 15:59:22.470 8656-8656/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62170 : Success: SELECT * FROM USERS Result: {}
06-08 15:59:22.470 8656-8656/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62170)] "Success: SELECT * FROM USERS Result: {}", source: file:///android_asset/www/build/main.js (62170)
06-08 15:59:22.470 8656-8656/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 61620 : Result{}
06-08 15:59:22.470 8656-8656/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(61620)] "Result{}", source: file:///android_asset/www/build/main.js (61620)
06-08 15:59:30.140 8656-8656/com.ionicframework.eclean372823 D/CordovaActivity: Paused the activity.
06-08 15:59:30.180 8656-8656/com.ionicframework.eclean372823 D/CordovaActivity: Stopped the activity.


Phone 2:

06-08 16:01:08.649 6749-6749/com.ionicframework.eclean372823 D/CordovaWebViewImpl: onPageFinished(file:///android_asset/www/index.html)
06-08 16:01:08.672 6749-6749/com.ionicframework.eclean372823 E/LocationProvider: Caught security exception registering for location updates from system. This should only happen in DumpRenderTree.
06-08 16:01:08.674 6749-6749/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62170 : Success: CREATE TABLE IF NOT EXISTS USERS (phone unique, firstn, lastn) Result: {"rowsAffected":0,"insertId":1,"rows":{"length":0}}
06-08 16:01:08.674 6749-6749/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62170)] "Success: CREATE TABLE IF NOT EXISTS USERS (phone unique, firstn, lastn) Result: {"rowsAffected":0,"insertId":1,"rows":{"length":0}}", source: file:///android_asset/www/build/main.js (62170)
06-08 16:01:08.708 6749-6749/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62170 : Success: CREATE TABLE IF NOT EXISTS WHOSIGNED (storeid, firstn, lastn, UNIQUE (storeid, firstn, lastn) ON CONFLICT REPLACE) Result: {"rowsAffected":0,"insertId":1,"rows":{"length":0}}
06-08 16:01:08.708 6749-6749/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62170)] "Success: CREATE TABLE IF NOT EXISTS WHOSIGNED (storeid, firstn, lastn, UNIQUE (storeid, firstn, lastn) ON CONFLICT REPLACE) Result: {"rowsAffected":0,"insertId":1,"rows":{"length":0}}", source: file:///android_asset/www/build/main.js (62170)
06-08 16:01:08.727 6749-6749/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62170 : Success: CREATE TABLE IF NOT EXISTS WHOCANSIGN (last_modified unique, client INTEGER, rep_pos) Result: {"rowsAffected":0,"insertId":1,"rows":{"length":0}}
06-08 16:01:08.727 6749-6749/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62170)] "Success: CREATE TABLE IF NOT EXISTS WHOCANSIGN (last_modified unique, client INTEGER, rep_pos) Result: {"rowsAffected":0,"insertId":1,"rows":{"length":0}}", source: file:///android_asset/www/build/main.js (62170)
06-08 16:01:08.749 6749-6749/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62170 : Success: CREATE TABLE IF NOT EXISTS RISKDEF (id unique, title, pdf INTEGER, last_modified INTEGER) Result: {"rowsAffected":0,"insertId":1,"rows":{"length":0}}
06-08 16:01:08.750 6749-6749/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62170)] "Success: CREATE TABLE IF NOT EXISTS RISKDEF (id unique, title, pdf INTEGER, last_modified INTEGER) Result: {"rowsAffected":0,"insertId":1,"rows":{"length":0}}", source: file:///android_asset/www/build/main.js (62170)
06-08 16:01:08.771 6749-6749/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62170 : Success: CREATE TABLE IF NOT EXISTS ASSIGNEDREP (id unique, company INTEGER, who_can_id INTEGER, stat INTEGER, last_modified INTEGER) Result: {"rowsAffected":0,"insertId":1,"rows":{"length":0}}
06-08 16:01:08.771 6749-6749/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62170)] "Success: CREATE TABLE IF NOT EXISTS ASSIGNEDREP (id unique, company INTEGER, who_can_id INTEGER, stat INTEGER, last_modified INTEGER) Result: {"rowsAffected":0,"insertId":1,"rows":{"length":0}}", source: file:///android_asset/www/build/main.js (62170)
06-08 16:01:08.789 6749-6749/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62170 : Success: CREATE TABLE IF NOT EXISTS MYWORK (scheduleid unique, status INTEGER, schedule_date INTEGER, location_id INTEGER, company_id INTEGER, Comp_Name, Short_add, Long_add, Latitude NUMERIC, Longitude NUMERIC , risk INTEGER, question INTEGER, autopupluate INTEGER, override, phone, job_description INTEGER, last_modified INTEGER) Result: {"rowsAffected":0,"insertId":1,"rows":{"length":0}}
06-08 16:01:08.789 6749-6749/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62170)] "Success: CREATE TABLE IF NOT EXISTS MYWORK (scheduleid unique, status INTEGER, schedule_date INTEGER, location_id INTEGER, company_id INTEGER, Comp_Name, Short_add, Long_add, Latitude NUMERIC, Longitude NUMERIC , risk INTEGER, question INTEGER, autopupluate INTEGER, override, phone, job_description INTEGER, last_modified INTEGER) Result: {"rowsAffected":0,"insertId":1,"rows":{"length":0}}", source: file:///android_asset/www/build/main.js (62170)
06-08 16:01:08.814 6749-6749/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62170 : Success: CREATE TABLE IF NOT EXISTS COMPLETEDJOBS (scheduleid unique, completition_date INTEGER, Latitude NUMERIC, Longitude NUMERIC, acc, rep_pos INTEGER, rep_name, picture TEXT, signature TEXT,satisfied INTEGER,comment) Result: {"rowsAffected":0,"insertId":1,"rows":{"length":0}}
06-08 16:01:08.814 6749-6749/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62170)] "Success: CREATE TABLE IF NOT EXISTS COMPLETEDJOBS (scheduleid unique, completition_date INTEGER, Latitude NUMERIC, Longitude NUMERIC, acc, rep_pos INTEGER, rep_name, picture TEXT, signature TEXT,satisfied INTEGER,comment) Result: {"rowsAffected":0,"insertId":1,"rows":{"length":0}}", source: file:///android_asset/www/build/main.js (62170)
06-08 16:01:08.833 6749-6749/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62170 : Success: CREATE TABLE IF NOT EXISTS JOBDESC (id INTEGER unique, owner INTEGER, short_desc, long_desc, last_modified INTEGER) Result: {"rowsAffected":0,"insertId":1,"rows":{"length":0}}
06-08 16:01:08.833 6749-6749/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62170)] "Success: CREATE TABLE IF NOT EXISTS JOBDESC (id INTEGER unique, owner INTEGER, short_desc, long_desc, last_modified INTEGER) Result: {"rowsAffected":0,"insertId":1,"rows":{"length":0}}", source: file:///android_asset/www/build/main.js (62170)
06-08 16:01:08.859 6749-6749/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62170 : Success: CREATE TABLE IF NOT EXISTS MANUALJOB (company,address,job,reppos,repname,picture,signature,lat,lng,acc,sigdate) Result: {"rowsAffected":0,"insertId":1,"rows":{"length":0}}
06-08 16:01:08.859 6749-6749/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62170)] "Success: CREATE TABLE IF NOT EXISTS MANUALJOB (company,address,job,reppos,repname,picture,signature,lat,lng,acc,sigdate) Result: {"rowsAffected":0,"insertId":1,"rows":{"length":0}}", source: file:///android_asset/www/build/main.js (62170)
06-08 16:01:08.879 6749-6749/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62156 : Success: INSERT INTO USERS (firstn, lastn, phone) values ("Emil","Bor","0000000000") Result: {"rowsAffected":1,"insertId":1,"rows":{"length":0}}
06-08 16:01:08.879 6749-6749/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62156)] "Success: INSERT INTO USERS (firstn, lastn, phone) values ("Emil","Bor","0000000000") Result: {"rowsAffected":1,"insertId":1,"rows":{"length":0}}", source: file:///android_asset/www/build/main.js (62156)
06-08 16:01:08.906 6749-6749/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/polyfills.js: Line 1 : Uncaught InvalidAccessError: Failed to read the 'insertId' property from 'SQLResultSet': The query didn't result in any rows being added.
06-08 16:01:08.906 6749-6749/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(1)] "Uncaught InvalidAccessError: Failed to read the 'insertId' property from 'SQLResultSet': The query didn't result in any rows being added.", source: file:///android_asset/www/build/polyfills.js (1)
06-08 16:01:08.912 6749-6749/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62173 : [object SQLError]
06-08 16:01:08.912 6749-6749/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62173)] "[object SQLError]", source: file:///android_asset/www/build/main.js (62173)
06-08 16:01:08.917 6749-6749/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 61617 : [object SQLError]
06-08 16:01:08.917 6749-6749/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(61617)] "[object SQLError]", source: file:///android_asset/www/build/main.js (61617)
06-08 16:01:08.929 6749-6749/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/polyfills.js: Line 1 : Uncaught InvalidAccessError: Failed to read the 'insertId' property from 'SQLResultSet': The query didn't result in any rows being added.
06-08 16:01:08.929 6749-6749/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(1)] "Uncaught InvalidAccessError: Failed to read the 'insertId' property from 'SQLResultSet': The query didn't result in any rows being added.", source: file:///android_asset/www/build/polyfills.js (1)

rapropos · June 8, 2017, 7:47pm

Your query() method returns a Promise, which you completely ignore. Thus, you have no way of ensuring that the tables have been created by the time you try to insert data into them.

Solution:

db.query('create table')
  .then(db.query('insert into table'))

borconi · June 8, 2017, 7:51pm

Hi.

I have tried all possible combinations, this is just a copy&paste of the latest one. If you look at the posted logs, the insert in successful, but the select fails.

I will redo a small test keeping the promises but it’s the same result on one device it works on the other it doesn’t…

rapropos · June 8, 2017, 7:54pm

On Tuesday it will work, but if it’s raining, it won’t. If the moon is full, it will work, but if you try it in the kitchen, it won’t.

You have a race condition. You have no control of your execution flow. This results in unpredictable behavior.

borconi · June 8, 2017, 7:55pm

Give me 5 I’m redoing it as per your indications, ill post back the logs.

borconi · June 8, 2017, 8:01pm

Ok, as per your request updated code:

 platform.ready().then(() => {
		db.init().then(res=>{
		db.query('CREATE TABLE IF NOT EXISTS USERS (phone unique, firstn, lastn)').then(res=>{
		db.query('INSERT INTO USERS (firstn, lastn, phone) values ("Emil","Bor","0000000000")', []).then(res=>{
					db.query('SELECT * FROM USERS', []).then(res=>{
								console.log("Result" + JSON.stringify(res));
									 //aux.navController.pop();
							}).catch(err=>  {
								console.log("Result" + JSON.stringify(err));
							});
				}).catch(err=>  {
					console.log(err);
				});

		});
		});
});

Uninstalled app from phone so it’s a fresh start.

Phone 1:

06-08 20:59:52.770 13943-13943/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62186 : Success: CREATE TABLE IF NOT EXISTS USERS (phone unique, firstn, lastn) Result: {}
06-08 20:59:52.770 13943-13943/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62186)] "Success: CREATE TABLE IF NOT EXISTS USERS (phone unique, firstn, lastn) Result: {}", source: file:///android_asset/www/build/main.js (62186)
06-08 20:59:52.800 13943-13943/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62186 : Success: INSERT INTO USERS (firstn, lastn, phone) values ("Emil","Bor","0000000000") Result: {}
06-08 20:59:52.800 13943-13943/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62186)] "Success: INSERT INTO USERS (firstn, lastn, phone) values ("Emil","Bor","0000000000") Result: {}", source: file:///android_asset/www/build/main.js (62186)
06-08 20:59:52.830 13943-13943/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62186 : Success: SELECT * FROM USERS Result: {}
06-08 20:59:52.830 13943-13943/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62186)] "Success: SELECT * FROM USERS Result: {}", source: file:///android_asset/www/build/main.js (62186)
06-08 20:59:52.830 13943-13943/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 61603 : Result{}
06-08 20:59:52.830 13943-13943/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(61603)] "Result{}", source: file:///android_asset/www/build/main.js (61603)

Phone 2:

06-08 20:57:38.090 29034-29034/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62186 : Success: CREATE TABLE IF NOT EXISTS USERS (phone unique, firstn, lastn) Result: {"rowsAffected":0,"insertId":1,"rows":{"length":0}}
06-08 20:57:38.091 29034-29034/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62186)] "Success: CREATE TABLE IF NOT EXISTS USERS (phone unique, firstn, lastn) Result: {"rowsAffected":0,"insertId":1,"rows":{"length":0}}", source: file:///android_asset/www/build/main.js (62186)
06-08 20:57:38.139 29034-29034/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 62186 : Success: INSERT INTO USERS (firstn, lastn, phone) values ("Emil","Bor","0000000000") Result: {"rowsAffected":1,"insertId":1,"rows":{"length":0}}
06-08 20:57:38.140 29034-29034/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62186)] "Success: INSERT INTO USERS (firstn, lastn, phone) values ("Emil","Bor","0000000000") Result: {"rowsAffected":1,"insertId":1,"rows":{"length":0}}", source: file:///android_asset/www/build/main.js (62186)
06-08 20:57:38.165 29034-29034/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/polyfills.js: Line 1 : Uncaught InvalidAccessError: Failed to read the 'insertId' property from 'SQLResultSet': The query didn't result in any rows being added.
06-08 20:57:38.165 29034-29034/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(1)] "Uncaught InvalidAccessError: Failed to read the 'insertId' property from 'SQLResultSet': The query didn't result in any rows being added.", source: file:///android_asset/www/build/polyfills.js (1)

And now there is no race condition.

Just as a note I ended up with that ugly code because I was breaking everything taking it step by step back trying and failing to understand why my app was working on one device but not on the other… and I cannot understand what is wrong… You will see that the database service has ugly code in it as well, for same reason trying to pinpoint the root cause, hence using only WebSQL and not Sqlite plugin to eliminate any potential additional cause…

Both devices are Android devices, both with stock rom no tweaks no extra things one is running Lollipop with System.WebView 39 and the other is Marshmallow (not sure about the WebView version)

rapropos · June 8, 2017, 8:22pm

I wonder if this is causing more problems than it’s solving. Can you try with the code that chains then and also use ionic-native’s SQLite plugin?

borconi · June 8, 2017, 9:11pm

Code left untouched, but now I’m using SQLite plugin.

Phone 1:

06-08 21:57:54.710 22249-22249/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62156)] "--> running on device: ", source: file:///android_asset/www/build/main.js (62156)
06-08 21:57:54.710 22249-22249/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/plugins/cordova-sqlite-storage/www/SQLitePlugin.js: Line 106 : new transaction is waiting for open operation
06-08 21:57:54.710 22249-22249/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(106)] "new transaction is waiting for open operation", source: file:///android_asset/www/plugins/cordova-sqlite-storage/www/SQLitePlugin.js (106)
06-08 21:57:54.730 22249-22249/com.ionicframework.eclean372823 D/CordovaWebViewImpl: onPageFinished(file:///android_asset/www/index.html)
06-08 21:57:54.770 22249-22249/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/plugins/cordova-sqlite-storage/www/SQLitePlugin.js: Line 179 : OPEN database: Eclean - OK
06-08 21:57:54.770 22249-22249/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(179)] "OPEN database: Eclean - OK", source: file:///android_asset/www/plugins/cordova-sqlite-storage/www/SQLitePlugin.js (179)
06-08 21:57:54.770 22249-22249/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/plugins/cordova-sqlite-storage/www/SQLitePlugin.js: Line 80 : DB opened: Eclean
06-08 21:57:54.770 22249-22249/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(80)] "DB opened: Eclean", source: file:///android_asset/www/plugins/cordova-sqlite-storage/www/SQLitePlugin.js (80)
06-08 21:57:54.780 22249-22381/com.ionicframework.eclean372823 W/System.err: java.sql.SQLException: sqlite3_step failure: cannot rollback - no transaction is active
06-08 21:57:54.780 22249-22381/com.ionicframework.eclean372823 W/System.err:     at io.liteglue.SQLiteGlueConnection$SQLGStatement.step(SQLiteGlueConnection.java:135)
06-08 21:57:54.780 22249-22381/com.ionicframework.eclean372823 W/System.err:     at io.sqlc.SQLiteConnectorDatabase.executeSQLiteStatement(SQLiteConnectorDatabase.java:214)
06-08 21:57:54.780 22249-22381/com.ionicframework.eclean372823 W/System.err:     at io.sqlc.SQLiteConnectorDatabase.executeSqlBatch(SQLiteConnectorDatabase.java:114)
06-08 21:57:54.780 22249-22381/com.ionicframework.eclean372823 W/System.err:     at io.sqlc.SQLitePlugin$DBRunner.run(SQLitePlugin.java:340)
06-08 21:57:54.780 22249-22381/com.ionicframework.eclean372823 W/System.err:     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1113)
06-08 21:57:54.780 22249-22381/com.ionicframework.eclean372823 W/System.err:     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:588)
06-08 21:57:54.780 22249-22381/com.ionicframework.eclean372823 W/System.err:     at java.lang.Thread.run(Thread.java:818)
06-08 21:57:54.780 22249-22381/com.ionicframework.eclean372823 V/executeSqlBatch: SQLitePlugin.executeSql[Batch](): Error=sqlite3_step failure: cannot rollback - no transaction is active
06-08 21:57:54.780 22249-22381/com.ionicframework.eclean372823 W/System.err: java.sql.SQLException: sqlite3_step failure: cannot rollback - no transaction is active
06-08 21:57:54.780 22249-22381/com.ionicframework.eclean372823 W/System.err:     at io.liteglue.SQLiteGlueConnection$SQLGStatement.step(SQLiteGlueConnection.java:135)
06-08 21:57:54.780 22249-22381/com.ionicframework.eclean372823 W/System.err:     at io.sqlc.SQLiteConnectorDatabase.executeSQLiteStatement(SQLiteConnectorDatabase.java:214)
06-08 21:57:54.780 22249-22381/com.ionicframework.eclean372823 W/System.err:     at io.sqlc.SQLiteConnectorDatabase.executeSqlBatch(SQLiteConnectorDatabase.java:114)
06-08 21:57:54.780 22249-22381/com.ionicframework.eclean372823 W/System.err:     at io.sqlc.SQLitePlugin$DBRunner.run(SQLitePlugin.java:340)
06-08 21:57:54.780 22249-22381/com.ionicframework.eclean372823 W/System.err:     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1113)
06-08 21:57:54.780 22249-22381/com.ionicframework.eclean372823 W/System.err:     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:588)
06-08 21:57:54.780 22249-22381/com.ionicframework.eclean372823 W/System.err:     at java.lang.Thread.run(Thread.java:818)
06-08 21:57:54.780 22249-22381/com.ionicframework.eclean372823 V/executeSqlBatch: SQLitePlugin.executeSql[Batch](): SQL Error code = 1 message = sqlite3_step failure: cannot rollback - no transaction is active
06-08 21:57:54.800 22249-22249/com.ionicframework.eclean372823 E/cr_LocationProvider: Caught security exception while registering for location updates from the system. The application does not have sufficient geolocation permissions.
06-08 21:57:54.890 22249-22249/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 61603 : Result{"rows":{"length":1},"rowsAffected":0}
06-08 21:57:54.890 22249-22249/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(61603)] "Result{"rows":{"length":1},"rowsAffected":0}", source: file:///android_asset/www/build/main.js (61603)

Phone 2:

06-08 22:08:18.658 9894-9894/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(62156)] "--> running on device: ", source: file:///android_asset/www/build/main.js (62156)
06-08 22:08:18.658 9894-9894/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/plugins/cordova-sqlite-storage/www/SQLitePlugin.js: Line 106 : new transaction is waiting for open operation
06-08 22:08:18.658 9894-9894/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(106)] "new transaction is waiting for open operation", source: file:///android_asset/www/plugins/cordova-sqlite-storage/www/SQLitePlugin.js (106)
06-08 22:08:18.661 9894-9894/com.ionicframework.eclean372823 D/CordovaWebViewImpl: onPageFinished(file:///android_asset/www/index.html)
06-08 22:08:18.678 9894-9894/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/plugins/cordova-sqlite-storage/www/SQLitePlugin.js: Line 179 : OPEN database: Eclean - OK
06-08 22:08:18.678 9894-9894/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(179)] "OPEN database: Eclean - OK", source: file:///android_asset/www/plugins/cordova-sqlite-storage/www/SQLitePlugin.js (179)
06-08 22:08:18.678 9894-9894/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/plugins/cordova-sqlite-storage/www/SQLitePlugin.js: Line 80 : DB opened: Eclean
06-08 22:08:18.678 9894-9894/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(80)] "DB opened: Eclean", source: file:///android_asset/www/plugins/cordova-sqlite-storage/www/SQLitePlugin.js (80)
06-08 22:08:18.684 9894-10025/com.ionicframework.eclean372823 W/System.err: java.sql.SQLException: sqlite3_step failure: cannot rollback - no transaction is active
06-08 22:08:18.684 9894-10025/com.ionicframework.eclean372823 W/System.err:     at io.liteglue.SQLiteGlueConnection$SQLGStatement.step(SQLiteGlueConnection.java:135)
06-08 22:08:18.685 9894-10025/com.ionicframework.eclean372823 W/System.err:     at io.sqlc.SQLiteConnectorDatabase.executeSQLiteStatement(SQLiteConnectorDatabase.java:214)
06-08 22:08:18.685 9894-10025/com.ionicframework.eclean372823 W/System.err:     at io.sqlc.SQLiteConnectorDatabase.executeSqlBatch(SQLiteConnectorDatabase.java:114)
06-08 22:08:18.685 9894-10025/com.ionicframework.eclean372823 W/System.err:     at io.sqlc.SQLitePlugin$DBRunner.run(SQLitePlugin.java:340)
06-08 22:08:18.685 9894-10025/com.ionicframework.eclean372823 W/System.err:     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1112)
06-08 22:08:18.685 9894-10025/com.ionicframework.eclean372823 W/System.err:     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:587)
06-08 22:08:18.685 9894-10025/com.ionicframework.eclean372823 W/System.err:     at java.lang.Thread.run(Thread.java:818)
06-08 22:08:18.685 9894-10025/com.ionicframework.eclean372823 V/executeSqlBatch: SQLitePlugin.executeSql[Batch](): Error=sqlite3_step failure: cannot rollback - no transaction is active
06-08 22:08:18.685 9894-10025/com.ionicframework.eclean372823 W/System.err: java.sql.SQLException: sqlite3_step failure: cannot rollback - no transaction is active
06-08 22:08:18.685 9894-10025/com.ionicframework.eclean372823 W/System.err:     at io.liteglue.SQLiteGlueConnection$SQLGStatement.step(SQLiteGlueConnection.java:135)
06-08 22:08:18.686 9894-10025/com.ionicframework.eclean372823 W/System.err:     at io.sqlc.SQLiteConnectorDatabase.executeSQLiteStatement(SQLiteConnectorDatabase.java:214)
06-08 22:08:18.686 9894-10025/com.ionicframework.eclean372823 W/System.err:     at io.sqlc.SQLiteConnectorDatabase.executeSqlBatch(SQLiteConnectorDatabase.java:114)
06-08 22:08:18.686 9894-10025/com.ionicframework.eclean372823 W/System.err:     at io.sqlc.SQLitePlugin$DBRunner.run(SQLitePlugin.java:340)
06-08 22:08:18.686 9894-10025/com.ionicframework.eclean372823 W/System.err:     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1112)
06-08 22:08:18.686 9894-10025/com.ionicframework.eclean372823 W/System.err:     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:587)
06-08 22:08:18.686 9894-10025/com.ionicframework.eclean372823 W/System.err:     at java.lang.Thread.run(Thread.java:818)
06-08 22:08:18.686 9894-10025/com.ionicframework.eclean372823 V/executeSqlBatch: SQLitePlugin.executeSql[Batch](): SQL Error code = 1 message = sqlite3_step failure: cannot rollback - no transaction is active
06-08 22:08:18.730 9894-9894/com.ionicframework.eclean372823 E/LocationProvider: Caught security exception registering for location updates from system. This should only happen in DumpRenderTree.
06-08 22:08:18.817 9894-9894/com.ionicframework.eclean372823 D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 61603 : Result of select: {"rows":{"length":1},"rowsAffected":0}
06-08 22:08:18.817 9894-9894/com.ionicframework.eclean372823 I/chromium: [INFO:CONSOLE(61603)] "Result of select: {"rows":{"length":1},"rowsAffected":0}", source: file:///android_asset/www/build/main.js (61603)

In this case both phone returns successful on the select call but the content in empty…

rapropos · June 8, 2017, 9:17pm

Since you are only ever executing one statement per query() call, what happens if you take the transaction out and just use the single-statement db.executeSql() syntax?

borconi · June 9, 2017, 12:54am

Ok, so trying to do simple db.executeSql() without using the service was giving me all kind of compile errors or page just not loading, so I ended up reinstalling angular all the plugins from scratch and then the db.executeSql() was consistent on both devices, so I re-added my service and now it works… Uhhh one of those!

Thank you for the help.

I do not even dare try to use now the WebSQL not to break anything…

Topic		Replies	Views
Desperately need help with SQLite - Ionic Angular with Cordova DB Ionic Framework	0	799	November 27, 2019
Issue creating a SQLite service ionic-v3	2	1465	January 20, 2017
Syncing problem ionic-v3	7	919	April 2, 2017
Database TypeError: Cannot read property 'executeSql' of undefined ionic-v3	1	2119	July 19, 2021
Promises in promises ionic-v3	12	7862	September 13, 2017

SQL driving me mental

Related topics