Wrong parameter types in parameterizedSQL #87
Description
If you have SQL table with char or varchar datatypes, loopback connector always sends nvarchar as parameter type. At least it should get proper type from model definition, instead of completely ignoring type defined in model and leave guessing of type to mssql package.
For example if properties definition in model is like this:
"properties": {
"Vrsta": {
"type": "String",
"id": true,
"required": true,
"length": 2,
"precision": null,
"scale": null,
"mssql": {
"columnName": "Vrsta",
"dataType": "char",
"dataLength": 2,
"dataPrecision": null,
"dataScale": null,
"nullable": "NO"
},
"_selectable": false
},
"Stevilka": {
"type": "String",
"id": true,
"required": true,
"length": 8,
"precision": null,
"scale": null,
"mssql": {
"columnName": "Stevilka",
"dataType": "char",
"dataLength": 8,
"dataPrecision": null,
"dataScale": null,
"nullable": "NO"
},
"_selectable": false
}
}
parameter type in generated sql should be char instead of nvarchar, because when this happen, SQL server will not use indexes and that dramatically slows down queries.
As I see the problem is in this line of loopback-conector-mssql:
https://github.com/strongloop/loopback-connector-mssql/blob/master/lib/mssql.js#L140
where guessing of paramater type is left to mssql package instead using it from model definition.
Mssql generates SQL like this:
exec sp_executesql @Statement=N'SELECT [Vrsta],[Stevilka] FROM [eNarocanje].[ZahtevekPozicija] WHERE [Stevilka]=@param1 AND [Vrsta]=@Param2 ORDER BY [Vrsta],[Stevilka]',@params=N'@param1 nvarchar(8), @Param2 nvarchar(2)',@param1=N'16-00039',@Param2=N'Z1'
But the correct SQL would be:
exec sp_executesql @Statement=N'SELECT [Vrsta],[Stevilka] FROM [eNarocanje].[ZahtevekPozicija] WHERE [Stevilka]=@param1 AND [Vrsta]=@Param2 ORDER BY [Vrsta],[Stevilka]',@params=N'@param1 char(8), @Param2 char(2)',@param1=N'16-00039',@Param2=N'Z1'