Node-mssql: RequestError: Incorrect syntax near

[VM999]

Could someone explains the next error ?

Request :
Select Code, Clef_decryptage from [DataBase].[dbo].[Principal] WHERE Id_device = {{{payload.type}}}

msg.payload.type is String[7] and equals "744DF02"

Error :
RequestError: Incorrect syntax near 'DF02'.

[patriksimek]

What type of data is stored in the table? Table definition would be nice.

[VM999]

Ci dessous la définition des colonnes de la table :

[VM999]

I have now a more explicit error :
RequestError: Conversion failed when converting the varchar value '744DF02' to data type int.

[patriksimek]

I believe you're missing quotation marks around parameter type in your statement. It should be something like:

Select Code, Clef_decryptage from [DataBase].[dbo].[Principal] WHERE Id_device = '{{{payload.type}}}'

But this is not a good practice to do so I would recommend you to use input parameters instead.

new sql.Request();
.input('type', msg.payload.type);
.query('Select Code, Clef_decryptage from [DataBase].[dbo].[Principal] WHERE Id_device = @type').then(function(recordset) {
    console.dir(recordset);
}).catch(function(err) {
    // ... error checks
});

[VM999]

Thanks, the first correction is working.
But as I'm using the Node-Red Node MSSQL, I have only a "Request" field, where I can't type your script : new sql.request() ...
Or I don't understand exactly what you meant.

[patriksimek]

What MSSQL module do you use with Node-Red?

[VM999]

http://flows.nodered.org/node/node-red-contrib-mssql

[patriksimek]

The module you posted doesn't support parametrized statements so the approach I wrote is irrelevant. Please, keep in mind that once you concatenate statements manually, you also have to sanitize your statements against sql injection manually.

[basefox]

.input('type', msg.payload.type);

  sql.connect(config, () => {
    var request = new sql.Request();
    request.input("msg", req.body.BodyMessage);
    request.input("id", req.params.id);

    request.query(
      "UPDATE Notification SET BodyMessage=@msg WHERE Id=@id",
      function (err, recordset) {
        if (err) {
          res.send(err);
        } else {
          res.status(200).send(recordset);
        }
      }
    );
  });