Node.js and sqlite, SQLITE_RANGE: binding or index of a column out of range

Question

See my answer below for MWE!

I know this sounds silly and the answer is probably right in front of me, but I can't understand why I am getting this SQLITE_RANGE error, because my object looks like it has all the necessary properties.

console.log "values " , values # Recording in db console.assert values.login? console.assert values.password_sha? console.assert values.email? console.assert values.token? values.password = null @db.run "INSERT INTO user VALUES (NULL, $login, $password_sha, $email, $token)", values, (err) -> console.error err if err? 

Here is the output of my server

 values { login: 'ostream', email: 'ijw', password: 'justine', token: 'acppn99ngiafw29', password_sha: 'b1820c2ec34175954bdaa42038b48886b4c83f8d53f88b5315c415898855e4f8' } { [Error: SQLITE_RANGE: bind or column index out of range] errno: 25, code: 'SQLITE_RANGE' } 

Thanks in advance!

Answer 1

I tried to do a test and I get the same error:

 sqlite3 = require 'sqlite3' db = new sqlite3.Database('testfile.db'); user = name: 'hello' password: 'jambon' db.run 'insert into "user" (name, password) VALUES ($name, $password)', user, (err) -> console.log 'request executed : ', err 

Server response:

 request executed : { [Error: SQLITE_RANGE: bind or column index out of range] errno: 25, code: 'SQLITE_RANGE' } 

How did it happen?

Answer 2

You should always specify a list of columns when you INSERT :

 INSERT INTO "user"(login, password_sha, email, token) VALUES ('ostream', 'b1820c2ec34175954bdaa42038b48886b4c83f8d53f88b5315c415898855e4f8', 'ijw', 'acppn99ngiafw29'); SELECT * FROM "user" 

SqlFiddleDemo

Keep in mind that user is a keyword and must be specified with " or you can rename the table to users .

Answer 3

Obviously, you need to make the keys in your user object prefixed with $ . Otherwise, sqlite3 will not recognize them as values ​​for placeholders. Try:

 sqlite3 = require 'sqlite3' db = new sqlite3.Database('testfile.db'); user = $name: 'hello' $password: 'jambon' db.run 'insert into "user" (name, password) VALUES ($name, $password)', user, (err) -> console.log 'request executed : ', err 

This is not well documented, but looking at the test cases , you can see that there are other options, for example, prefixing them with @ or : In any case, your object must match placeholders, including the prefix.