React native SQLite insert statement not working

Question

I am trying to use SQLite with React native to insert some values into a table

In my main page I am creating the table like so...

db.transaction(tx => {
  tx.executeSql(
    `create table if not exists puzzles (
        id primary key not null, 
        level int not null, 
        image varchar(512) NOT NULL,
        imageSolution varchar(512) NULL,
        puzzleAnswer varchar(16) NULL,
        type varchar(16) NULL,
        availableLetters varchar(16) NULL,
        charactersGiven varchar(4) NULL);`
  );
});

In my page component I am trying to insert using the following:

    db.transaction(tx => {
      for (var puz of puzzlesFiltered) {
        tx.executeSql(
          `insert into
        puzzles (id, image, imageSolution, type, puzzleAnswer, level, charactersGiven, availableLetters)
        VALUES (?,?,?,?,?,?,?,?) WHERE NOT EXISTS(SELECT 1 FROM puzzles WHERE id = ${
          puz.id
        })`,
          [
            puz.id,
            puz.image,
            puz.imageSolution,
            puz.type,
            puz.puzzleAnswer,
            puz.level,
            puz.charactersGiven,
            puz.charactersGiven
          ],
          (tx, results) => {
            console.log("Results", results.rowsAffected);
          },
          (err) => {
            console.error(err);
          }
        );
      }
    });

I am getting the following error message returned, which I have no idea what it means, can anyone help?

Also, is there a better way to insert an array, rather than looping through each row?

[16:07:09] WebSQLTransaction { "_complete": false, "_error": null, "_running": true, "_runningTimeout": false, "_sqlQueue": Queue { "first": undefined, "last": undefined, "length": 0, }, "_websqlDatabase": WebSQLDatabase { "_currentTask": TransactionTask { "errorCallback": [Function anonymous], "readOnly": false, "successCallback": [Function anonymous], "txnCallback": [Function anonymous], }, "_db": SQLiteDatabase { "_closed": false, "_name": "db.db", }, "_running": true, "_txnQueue": Queue { "first": Object { "item": TransactionTask { "errorCallback": [Function anonymous], "readOnly": false, "successCallback": [Function anonymous], "txnCallback": [Function anonymous], }, }, "last": Object { "item": TransactionTask { "errorCallback": [Function anonymous], "readOnly": false, "successCallback": [Function anonymous], "txnCallback": [Function anonymous], }, }, "length": 1, }, "version": "1.0", }, }

Pragmateek · Accepted Answer · 2018-11-25 16:29:47Z

2

I'm not sure your query is supported by SQLite.

You could try something like:

INSERT INTO puzzles
(id, image, imageSolution, type, puzzleAnswer, level, charactersGiven, availableLetters)
SELECT ?,?,?,?,?,?,?,?
WHERE NOT EXISTS(SELECT 1 FROM puzzles WHERE id = ${puz.id})

answered Nov 25, 2018 at 16:29

Pragmateek

13.6k9 gold badges80 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

shira Over a year ago

i have some issue with sqlite, i create the insert fetch delete functions and for some reason it doesn't work for me. can you help me please ?

shira Over a year ago

that there is no insert into my db file . do have facebook or something that i can connect with u to show u my problem.

Pragmateek Over a year ago

Please rather open a dedicated SO question and share your code.

Collectives™ on Stack Overflow

React native SQLite insert statement not working

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related