BigInt as Parameter works, but does this look right?

[beautifulcoder]

Hello, when I add a BigInt type as a parameter I get weird behavior.

Say:

commandBigInt.addParameter('Id', TYPES.BigInt, '9223372036854775807');

SQL Server tells me that a -9223372036854775808 was put in this parameter. This seems weird because it is a string type in JavaScript.

But, when I do this instead:

commandBigInt.addParameter('Id', TYPES.VarChar, '9223372036854775806');

It works! But, Tedious requires a VarChar type? I get that it has to be a string in JavaScript, but why in Tedious?

[tvrprasad]

Can you please put together a complete script? I can take a look. Thanks.

[beautifulcoder]

You bet.

Create a basic table with a BitInt.

CREATE DATABASE FunWithBigIntInNode;
GO

USE FunWithBigIntInNode;
GO

CREATE TABLE dbo.BigIntInNodeTest (
Id BIGINT NOT NULL,
IsAtMax BIT NOT NULL,
CONSTRAINT PK_BigIntInNodeTest_Id PRIMARY KEY(Id));
GO

Then, use this script:

var Connection = require('tedious').Connection;
var Request = require('tedious').Request;
var TYPES = require('tedious').TYPES;
var config = require('./sqlConfig');

var connection = new Connection(config);

var commandBigInt = new Request('dbo.InsertBigInt', function onRequest() {
connection.close();
});

commandBigInt.addParameter('Id', TYPES.BigInt, '9223372036854775807');
commandBigInt.addParameter('IsAtMax', TYPES.Bit, true);

connection.on('connect', function onConnect() {
connection.callProcedure(commandBigInt);
});

sqlConfig is a JSON key / value pair file, as such:

{
"userName": "",
"password": "",
"server": "",
"options": {
"database": "FunWithBigIntInNode",
"encrypt": true
}
}

[tvrprasad]

@beautifulcoder Got around to checking this out. This is essentially a JavaScript Numeric range limitation. You lose precision outside the range -(253 - 1) and (253 - 1). Using VarChar for values outside this range is the right behavior.

I sent a PR to validate range on BigInt - #504.

[tvrprasad]

@beautifulcoder This change is now merged. Happy coding :-)