iOS: Error: selectSQL prepare failed

[tobiasmuecksch]

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?

[jepiqueau]

@tobiasmuecksch First thanks for using the plugin. Which release of the plugin are you using?. According to the readme, it should be

const result = await this._sqlite.query({statement:selectStatement, values: []});

this should work.

[tobiasmuecksch]

@jepiqueau Thank you for your response. I'm using version 2.0.0-4

[tobiasmuecksch]

@jepiqueau I have created a demo repository for you.

1. clone this repo: https://github.com/tobiasmuecksch/capacitor-sqlite-demo
2. ionic cap run ios
3. connect safari developer console with device or simulator
4. push "Test SQLite" button
5. check console output

[jepiqueau]

@tobiasmuecksch The run and select methods are for one statement and not for multiple statements so when you defined

    const statement = `
    BEGIN TRANSACTION;
    CREATE TABLE IF NOT EXISTS "myTable" (
            "_id"   TEXT,
            "Type"  TEXT,
            "Title" TEXT
            PRIMARY KEY("_id")
    );
    COMMIT TRANSACTION;`;

it can only be executed by the execute method so replace

    const result = await this.sql.run({statement, values: []});

with

const result = await this.sql.execute({statements:statement})

this should be better if your statement were correct but it is wrong SQL Statement

look first at the design of your database you intend to create a table "myTable" and you make a query on table "conversation" which is not existing

i have provided an app starter https://github.com/jepiqueau/angular-sqlite-app-starter you should be looking at it

finally i made the following changes and it works

  async testSqlite() {
    const statement = `
    BEGIN TRANSACTION;
    CREATE TABLE IF NOT EXISTS "myTable" (
            id TEXT PRIMARY KEY NOT NULL,
            Type TEXT,
            Title TEXT
    );
    PRAGMA user_version = 1;
    COMMIT TRANSACTION;`;

    await this.sql.init();
    const result = await this.sql.execute({statements:statement});

    console.log('RESULT', result);

    const selectStatement = 'SELECT * FROM myTable';

    const result1 = await this.sql.query({statement: selectStatement, values: []});

    console.log('SELECT result (should be empty)', result1);

    const insertStatement = `INSERT INTO myTable (id,Title) VALUES (?,?);`;

    const result2 = await this.sql.run({statement: insertStatement, values: ['1234', 'Titel für 1234']});
    const result3 = await this.sql.run({statement: insertStatement, values: ['4321', 'Titel für 4321']});

    console.log('RESULTS', result2, result3);

    // As you didn't enter the field "Type", you have to do a query 
    // only on non "NULL" fields current limitation that i am working on
    const query1 = "SELECT id,Title from myTable"
    const result4 = await this.sql.query({statement: query1, values: []});
    console.log('SELECT 2 RESULTS (should have two results)', result4);

  }

and in the sqlite.service

  async run(query: capSQLiteOptions): Promise<capSQLiteResult> {
    await this.waitForReady();
    console.debug('SQL RUN:', query);

    return await this.sqlite.run(query);
  }
You also understand now that the transformQuery method will never work

[tobiasmuecksch]

look first at the design of your database you intend to create a table "myTable" and you make a query on table "conversation" which is not existing

You are right, that's a copy-paste fault, because I copy-pasted the code from my main project.

Thank you very much for your help. I will try that and then close the issue.

[jepiqueau]

@tobiasmuecksch Ok you are welcome

[jepiqueau]

I will have a look to deal with sqlite NULL value in the next release, to day i am currently working on storing base64 image strings as Blob in sqlite3 which is not as easy

[tobiasmuecksch]

Good to know. So I'd have to set empty strings instead?