Real numbers get rounded.

[sanderd17]

When I try to insert a field with a real number, it gets rounded down in the table.

F.e.

let v = 8.5
sql.query`INSERT INTO Table (columnName) VALUES (${v})`

Then the new record gets a value of 8 instead of 8.5, while the type of columnName is set to real.

I think this is related to the decimal separator in MS SQL, as I'm currently working in a Dutch environment, and the decimal separator of the database is set to a comma instead of a period. So I guess the driver tries to do a conversion and ends up trunkating the result to the first period.

I also tried converting it to a string, and replacing the default JS period with a comma character, but then the query would not accept the value due to mismatch in types (nvarchar vs real).

Edit: it doesn't happen when I include the value literally in the query. So it's probably related to the escaping of the query instead.

[sanderd17]

Meanwhile found in the docs that I can assign other SQL types to JS types. F.e. with sql.map.register(Number, sql.Real);

But I do use other (integer) numbers in those queries. Is it advisable then to map the number type to real. And if so, why isn't it always done?

[toddtarsi]

US localization user here, and I've seen this issue occur similarly with decimal types.
Using a decimal column with an accuracy of 6, I assumed the correct input would look something like this:

return Number(inputNum.toFixed(6));

Nope! It loses all precision this way and is basically an int.
Instead, I found the correct way to pass in the decimals is to convert them to a string:

return inputNum.toFixed(6);

Not an issue per se, as the workaround here wasn't too bad, but I thought it may help. Thanks for the great contribution to open source!

[statyan]

I can't believe they always convert JS Number to Int. But it's a fact. And there is no "normal" way to fix this without pull-request. So here is my silly patch:

let numberFix = (() => {
    return {
        type: sql.Decimal,
        precision: 38,
        scale: 10
    };
});
sql.map.register(Number, numberFix);

MSSQL is clever enough to convert decimal to int. So I tell him all Number are always decimal.

[toddtarsi]

To pass it in, (at least in 3.3.0), use a fixed length string in JS (which mssql will handle properly):

  sql.input(‘decimal’, 4.23.toFixed());

OR

  sql.input(‘decimal’, ‘4.23’);

On Oct 18, 2016, at 9:32 AM, statyan notifications@github.com wrote:

I can't believe they always convert JS Number to Int. But it's a fact. And there is no "normal" way to fix this without pull-request. So here is my silly patch:
let numberFix = (() => {
return {
type: sql.Decimal,
precision: 38,
scale: 10
};
});
sql.map.register(Number, numberFix);
MSSQL is clever enough to convert decimal to int. So I tell him all Number are always decimal.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub #341 (comment), or mute the thread https://github.com/notifications/unsubscribe-auth/AEfUsd20oTSA5uug2yCyYiZqRRSW9-WCks5q1NiGgaJpZM4Jt07d.

[statyan]

It's not so simlpe, cause I'm using knex-js to work with MSSQL with some ORM lib on the top of knex. And to fix it in that way I have to make pull request to knex mssql dialect library.

[statyan]

I wish I had an opportunity to set type mapper as a function which receives value as param, so I can manually detect correct type. But as I see, it's not possible for now. 'pg' driver allows developer to override default type detectors. And 'mssql' library - not.

[patriksimek]

You can override default type for each input parameter.

request.input('decimal', sql.Decimal(38, 10), 4.23)

[statyan]

Cause I'm using knex lib on the top of mssql driver, I have no access to knex query compilation (without changing knex code), unfortunately

[patriksimek]

Closing this one since it seems fixed inside knex.

[xinthose]

My issue was not specifying the precision and scale in the data type, without it, the library rounds the decimal apparently:
.input("pH", TYPES.Decimal(12, 2), spData.pH)