Parameters not being replaced

[Simon Van de Water]

Hi,

I have been using sequelize for the past two months and haven't encountered any serious problems but I've been stuck with this one for quite some time now.

The code-snippet I am presenting here is a simplified version of what I am actually trying to do. 

I am trying to perform a query with replacements (as described at the bottom of this page: http://sequelize.readthedocs.org/en/latest/docs/raw-queries/index.html?highlight=query) but for some reason, the "question marks" or identifiers I am using, are not being replaced. I am using sequelize 2.0.0-rc4. I have tried following pieces of code without luck:

First approach:

var findUserByName = function(name,companyId) {

return models.sequelize.query('SELECT * FROM users WHERE firstName = ?',

  {replacements: ['Simon']});

}

Error generated by this chunk of code: 

Executing (default): SELECT * FROM users WHERE firstName = ?

{ [SequelizeDatabaseError: syntax error at end of input]

  name: 'SequelizeDatabaseError',

  message: 'syntax error at end of input',

Second approach:

var findUserByName = function(name,companyId) {

return models.sequelize.query('SELECT * FROM users WHERE firstName = :firstName',

  {replacements: {firstName: 'Simon' }});

}

Error generated by this chunk of code:

Executing (default): SELECT * FROM users WHERE firstName = :firstName

{ [SequelizeDatabaseError: syntax error at or near ":"]

  name: 'SequelizeDatabaseError',

  message: 'syntax error at or near ":"',

Third approach:

var findUserByName = function(name,companyId) {
debugger;
return models.sequelize.query('SELECT * FROM users WHERE firstName=:name',

  null,{name: 'Simon'});

}

 
Error generated by this chunk of code:

Executing (default): SELECT * FROM users WHERE firstName=:name

{ [SequelizeDatabaseError: syntax error at or near ":"]

  name: 'SequelizeDatabaseError',

  message: 'syntax error at or near ":"',

The error messages make me believe that the identifier (:firstName) or the question marks I am using in the queries are not being replaced by the values I am passing in the next argument of the query-operator. Can somebody confirm that this is the problem and tell me how to fix this. According to the docs this should work.

Kind regards,
Simon Van de Water

[Bulkan]

Hi Simon,

Try the first approach but pass in null as your the second arg to query and change back your sql to use ? as replacement placeholders

var findUserByName = function(name,companyId) {

  var q = "SELECT * FROM users WHERE firstName = '?'"

  return models.sequelize.query(q, null, {

    replacements: {firstName: 'Simon' }});

  }

Also just a heads up Sequelize is at v2.0.4 [1] no need to be using the rc4 version.

Cheers

1 - https://github.com/sequelize/sequelize/releases

[Simon Van de Water]

Hi Bulkan,

Thanks for your reply!

Unfortunately, the problem is not yet resolved. Maybe I should have also mentioned that I am using Postgresql. 

So I first tried the following:

var findUserByName = function(name,companyId) {

var q = "SELECT * FROM users WHERE firstName = '?'";

return models.sequelize.query(q, null, {

    replacements: {firstName: 'Simon' }});

  }

Which gave me the following error: 

Executing (default): SELECT * FROM users WHERE firstName = '?'

{ [SequelizeDatabaseError: column "firstname" does not exist]

  name: 'SequelizeDatabaseError',

  message: 'column "firstname" does not exist',

Since I am using postgresql, the column names need to be between double quotes, so I updated my code to this (notice I added quotes around firstName):

var findUserByName = function(name,companyId) {

debugger;

var q = "SELECT * FROM users WHERE \"firstName\" = '?'";

return models.sequelize.query(q, null, {

    replacements: {firstName: 'Simon' }});

}

Which gave me this error:

Executing (default): SELECT * FROM users WHERE "firstName" = '?'

[TypeError: Cannot call method 'bulkBuild' of null]

After googling, it turned out I needed to set the "raw"-property to true to resolve this error, so I changed the code to this:

var findUserByName = function(name,companyId) {

debugger;

var q = "SELECT * FROM users WHERE \"firstName\" = '?'";

return models.sequelize.query(q, null,{raw:true}, {

    replacements: {firstName: 'Simon' }

    });

}

The code no longer crashed but it just returned an empty dataset, which was to be expected since the question mark is between single quotes so I think that the query is now looking for records where the firstName is equal to '?'. So I removed the question marks. The code now looks like this:

 var findUserByName = function(name,companyId) {

debugger;

var q = "SELECT * FROM users WHERE \"firstName\" = ?";

return models.sequelize.query(q, null,{raw:true}, {

    replacements: {firstName: 'Simon' }

    });

}

This then again gave me this error:

Executing (default): SELECT * FROM users WHERE "firstName" = ?

{ [SequelizeDatabaseError: syntax error at end of input]

  name: 'SequelizeDatabaseError',

  message: 'syntax error at end of input', 

I thought that if I would replace the question mark by :firstName, the problem would be resolved, but this was not the case as the query then returned;

"Named parameter ":firstName" has no value in the given object."

So I'm at a dead end again. Any more ideas what could be causing this issue? 

Kind regards and thanks again,

Simon

[Bulkan]

The question mark is needed because it is the replacement placeholder.  I am not sure why this isn't working for you as I am doing a raw query with replacements and Postgres. 

Have you upgraded to latest Sequelize ?

[Simon Van de Water]

Hey Bulkan,

I was reluctant to upgrading because I feared it might break my code but in the end I decided to upgrade nevertheless.

After upgrading I got an error that "pg-hstore" was missing. After installing this package, everything seems to be working just fine.

I expected it to work in version 2.0 as well but apparently things have changed since then.:-)

Thank you!
Simon