Wrong parameter types in parameterizedSQL

[sajmons]

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.

See: https://lostechies.com/jimmybogard/2012/07/18/troubleshooting-sql-index-performance-on-varchar-columns/

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'

[marcosluna]

Any news about this issue, still happens on loopback 4, is there a way to enable the query parameters to be sent as Unicode?

[marcosluna]

hello, any progress on this issue?