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

Question

See my answer below for a MWE!

I know it sound stupid and the answer is probably right in front of me, but I can't figure out why I get this SQLITE_RANGE error, since my object looks like it has every properties needed.

    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!

Solution 1

Apparently what you need to do is make sure the keys in your user object are prefixed with $. Otherwise sqlite3 won't recognise 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

It's not very well documented, but looking at the test cases you can see that there are other options, like prefixing them with @ or :. In any case, your object has to match the placeholders including the prefix.

Solution 2

You should always specify column list 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 keyword and should be quoted with " or you can rename table to users.

Comments

[m0tive]

Also I found that if you have any extra properties in the object that don't map to keys in the query, then it gives the same column index out of range error