`exec` succeeds but `run` on equivalent statement fails

[RossBoylan]

Two things that seem as if they are equivalent are not. If I prepare a statement and then run it I get an error; if I do the same thing in one step with db.exec() it works.

Details

This code produces a SqliteError the first time through the loop:

            let sql = db.prepare(`INSERT INTO data (iSim, fullvarid,
            year, demoid, value) VALUES (?, ?, ?, ?, ?)`);
            for (const i of master.indices) {
                const coldata = master.columns[i];
                /* This statement runs without error, albeit slowly
                db.exec(`INSERT INTO data (iSim, fullvarid,
                    year, demoid, value) VALUES (?, ?, ?, ?, ?)`,
                    iSim, coldata.fullvarid, xs[master.year], demoid, xs[i]);
                But the next, seemingly equivalent statement, does not.
                */
                sql.run(iSim, coldata.fullvarid, xs[master.year], demoid, xs[i]); //error here
            }

The error is uninformative:

Uncaught SqliteError SqliteError
    at first_data (c:\Users\rdboylan\Documents\KBD\node-sqlight-test\test.js:217:21)
    at <anonymous> (c:\Users\rdboylan\Documents\KBD\node-sqlight-test\test.js:227:1)
    at Module._compile (internal/modules/cjs/loader:1256:14)
    at Module._extensions..js (internal/modules/cjs/loader:1310:10)
    at Module.load (internal/modules/cjs/loader:1119:32)
    at Module._load (internal/modules/cjs/loader:960:12)
    at executeUserEntryPoint (internal/modules/run_main:81:12)
    at <anonymous> (internal/main/run_main_module:23:47)
test.js:217
No debugger available, can not send 'variables'
Process exited with code 1

However, if I switch to the db.exec() statement in the commented out code and eliminate the sql.run() there are no errors, I created the db.exec() arguments by pasting the first argument from db.prepare() and the remainder from sql.run(). The loop goes through 109 iterations. It is quite slow, perhaps because it was running in the debugger, or because I haven't wrapped it in a transaction.

Questions

1. Is there a way to get the prepared statement to work?
2. Is there a way to get more information about the SqliteError?

Environment

• better-sqlite3 8.2.0
• node v18.17.1
• VS Code 1.82.2
• MS-Windows Server 2019 Standard, version 1809.
• Virtual Machine

[neoxpert]

Well, the error message is strange. Without having more information about the schema and datatypes going into the statement I created this minimal sample:

package.json

{
  "name": "sample",
  "version": "1.0.0",
  "main": "index.js",
  "dependencies": {
    "better-sqlite3": "8.2.0"
  }
}

index.js

const db = require('better-sqlite3')(':memory:');

const personsData = [{
    id: 1,
    name: 'Jane'
}, {
    id: 2,
    name: 'John'
}];

db.exec('CREATE TABLE persons("id" INTEGER, "name" TEXT)');

let sql = db.prepare('INSERT INTO persons("id", "name") VALUES (?, ?)');

for (const persons of personsData) {
    sql.run(persons.id, persons.name);
}

for (const row of db.prepare('SELECT * FROM persons').all()) {
    console.log(`${row.id} - ${row.name}\n`);
}

db.close();

Running this example with NodeJS 18.18.0 on Windows 10 x64 the error does not occur. The better-sqlite3.node module resolved during the installation has a SHA1 checksum of 7448AC43A435E62073D4F4CFB9A1D846E9467700E977B40EFD54BCBBEF2CF69D. Can you check if this example creates the same error that you receive in your test?

[RossBoylan]

The example also runs fine for me.

[RossBoylan]

It is failing when it attempts to insert a value that violates a foreign key constraint.

I am inserting into a table data that is defined with columns scenario text, iSim integer, fullvarid references fullvar(fullvarid), year integer, demoid references demo(demoid), value real and the value for demoid does not occur in the demo table.

Why aren't I getting a more informative error message?

The demo table was not populated properly because I used statements like db.exec("INSERT INTO demo VALUES (?, ?, ?, ?, ?)", demoid, label, sexname, ageStart, ageEnd);. But exec() doesn't take any parameter values as arguments; as a result I just inserted a bunch of nulls and the autoincremented primary key.

I believe this behavior of exec is also the reason that I "succeeded" with exec but failed with run on a prepared statement. The 2 were not equivalent since only run() uses the argument values.

BTW, I think my use of exec with arguments was a holdover from using the sqlite3 package; perhaps the docs could highlight the fact that exec doesn't bind parameter values a bit more.

[Prinzhorn]

Why aren't I getting a more informative error message?

You are probably using some sort of error handling library that cannot handle custom Error classes? You mentioned debugger and VS Code, what if you just run the code on its own (node index.js)? Can you use the minimal example provided by @neoxpert and adjust it to reproduce the error? Without a minimal example that demonstrates the issue there is nothing I can do.

[mceachen]

Closing due to inactivity.