bug: Select * returns {"changes":{"changes":0}}

[sbignell]

Plugin version:

@capacitor-community/sqlite: ^6.0.2

Platform(s):

iOS

Current behavior:

Using Vue2 with capacitor and @capacitor-community/sqlite I can create a sqlite db, connect to it and insert data to it, receiving a
{"changes":{"changes":1}} log that my row inserted, by following examples and documentation - thank you.

When I try to select * to retrieve the row it returns {"changes":{"changes":0}} though. Can you please explain what I'm doing wrong? I've even resorted to putting the select statement into a 9000ms timeout which runs after the insert statement finishes (using await)....

Expected behavior:

Steps to reproduce:

const platform = Capacitor.getPlatform();
const sqlite = new SQLiteConnection(CapacitorSQLite);
const ret = await sqlite.checkConnectionsConsistency();
const isConn = (await sqlite.isConnection("st")).result;
let db;
if (ret.result && isConn) {
db = await sqlite.retrieveConnection("st");
} else {
db = await sqlite.createConnection(
"st",
false,
"no-encryption",
1
);
}
await db.open();
const queries = CREATE TABLE IF NOT EXISTS products ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, price TEXT );;
const res = await db.execute(queries);
if (res.changes && res.changes.changes && res.changes.changes < 0) {
throw new Error(Error: execute failed);
} else {
console.log('Executed Create if not Exist queries');
}
const queries2 = INSERT INTO products (name, price) VALUES ('TestRocket', '$1.23');;
await db.execute(queries2).then(async function(res2){
if (res2.changes && res2.changes.changes && res2.changes.changes < 0) {
throw new Error(Error: execute failed);
} else {
var str3 = JSON.stringify(res2);
console.log('Executed Insert query: ' + str3);
console.dir(res2);
}

var that = this;
this.timeout = setTimeout(async function(){
const queries4 = SELECT * FROM products;;

  const res4 = await db.execute(queries4);
  if (res4.changes && res4.changes.changes && res4.changes.changes < 0) {
    throw new Error(`Error: execute failed`);
  } else {
    var str4 = JSON.stringify(res4);
    console.log('Executed Select query: ' + str4);
    console.dir(res4);
  }

}, 9000)

});

Related code:

insert short code snippets here

Other information:

Capacitor doctor:

💊 Capacitor Doctor 💊

Latest Dependencies:

@capacitor/cli: 6.2.0
@capacitor/core: 6.2.0
@capacitor/android: 6.2.0
@capacitor/ios: 6.2.0

Installed Dependencies:

@capacitor/cli: 6.2.0
@capacitor/core: 6.2.0
@capacitor/android: 6.2.0
@capacitor/ios: 6.2.0

[success] iOS looking great! 👌
[success] Android looking great! 👌

insert the output from `npx cap doctor` here

[mccabeservant]

If I'm not mistaken, you don't want to do a SELECT using db.execute(), which returns a capSQLiteChanges. Instead, you want to use db.query(), which returns a capSQLiteValues.

But I'm having trouble making db.query() work properly myself at the moment. TypeScript says it wants a single SELECT statement, but when I send a single SELECT statement, the query API responds with an error saying it's expecting an array instead.

If you figure out how to do a working SELECT, I'd love to know!

[mccabeservant]

I think I have it figured out with 6.0.2 now. My problem was that it wanted an array for values, not for statement like I thought. Something like this should work.

const result = await db.query({
    database: 'mydatabasename',
    statement: 'SELECT * FROM table;',
    values: [],
});
return result.values;

[sbignell]

Thank you so much @mccabeservant! This worked.

For the record for others, in my code above I simply changed db.execute to db.query.