CODEWITHSUNDEEP
node.jssqlite
wullxz
wullxz

Reputation: 19490

named parameter binding with sql-wildcard not working

I'm using the node-sqlite3 package to access my db.
I'm trying to get rows from a Clients table with this code: 

var st = db.prepare("SELECT * FROM Clients where name LIKE '%$name%'");
st.all({ $name: "test" }, function (err, rows) {
      console.log("this: " + JSON.stringify(this));
      if (err)
        console.log(err);
      else {
        console.log("found: " + JSON.stringify(rows));
      }
});

Output of err is this:

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

The query works and doesn't throw errors when I change the sql to SELECT * FROM Clients where name LIKE '%$name%'. So I guess the problem is, that node-sqlite3 tries to find a variable called $name% or something like that in the object passed as first parameter to Statement#all.
I've searched the API doc for more hints about this, but couldn't find any.

Do I need to escape something? How do I get my query to work with named binding and the sql wildcards %?

Upvotes: 1

Views: 1321

Answers (1)

Jerome WAGNER
Jerome WAGNER

Reputation: 22442

This is not the way bindings work.

You can have

SELECT * FROM Clients where name LIKE $name

and 

var name = "%"+"test"+"%";
..
{ $name: name }

bound variables are negociated with the backend database as a "whole" variable and you should not confuse this with variable replacement.

you should also be able to use the concatenate function of sqlite (not tested) :

SELECT * FROM Clients where name LIKE '%'||$name||'%'
..
{ $name: test }

Upvotes: 4

Related Questions