Getting RangeError [ERR_OUT_OF_RANGE] for "smalldatetime" type column while using bulk insert

[kartikgupta2607]

Software versions

• Tedious: Tried with v16.4.0 and v16.6.0
• SQL Server: MsSQL Server 2017 - 14.0.3381.3
• Node.js: 18.12.1

Additional Libraries Used and Versions

N/A

Table schema

Columns - id (char 100), smalldatetime (smalldatetime)

Connection configuration
{ authentication: { type: 'default', options: { userName: '***', password: '***' } }, server: 'ms-sql-sql-server-2017.***.amazonaws.com', options: { database: 'MsSQL_Restart', encrypt: true, port: 1433, requestTimeout: 100000, trustServerCertificate: true } }

Problem description

I am trying to bulk insert data in MsSQL using bulkLoad. The destination column contains smalldatetime column, and in insert data if we have value outside the allowed range, it crashes the server while inserting the value in buffer here. I've attached a sample script to replicate the behaviour
temp.txt

Expected behavior

Ideally, the range for any column type should be validated before-hand and respective error should be returned.

Actual behavior

Node internals throw error, thus crashing the server

Error message/stack trace

RangeError [ERR_OUT_OF_RANGE]: The value of "value" is out of range. It must be >= 0 and <= 65535. Received 401402

    at new NodeError (node:internal/errors:393:5)

    at checkInt (node:internal/buffer:72:11)

    at writeU_Int16LE (node:internal/buffer:722:3)

    at Buffer.writeUInt16LE (node:internal/buffer:730:10)

    at Object.generateParameterData (/rdbms-adaptor/node_modules/tedious/lib/data-types/smalldatetime.js:43:12)

    at generateParameterData.next (<anonymous>)

    at RowTransform._transform (/rdbms-adaptor/node_modules/tedious/lib/bulk-load.js:126:18)

    at Transform._write (node:internal/streams/transform:175:8)

    at doWrite (node:internal/streams/writable:411:12)

    at clearBuffer (node:internal/streams/writable:572:7)

    at onwrite (node:internal/streams/writable:464:7)

    at node:internal/streams/transform:190:7

    at process.processTicksAndRejections (node:internal/process/task_queues:77:11) {

  code: 'ERR_OUT_OF_RANGE'

Any other details that can be helpful

There's an existing issue which hasn't been resolved yet - #1265

[mShan0]

Hi @kartikgupta2607, we'll try to get the fix merged soon

[kartikgupta2607]

Hey @mShan0, a similar error was also reported for the following data type (also verified the same on my setup)

• large -ve "decimal" values fix: don't throw in _transform of BulkLoad by everhardt · Pull Request #1590 · tediousjs/tedious (Verified the same locally)
• too large “money” datatype column value - Too large money value will make nodejs crash. · Issue #812 · tediousjs/tedious
• too large “numeric” datatype column value - Value out of bounds for Numeric types · Issue #474 · tediousjs/tedious

Can you also please check on the above issues? Ideally we should be having range validation for all datatypes

[kartikgupta2607]

Hi @mShan0 @arthurschreiber @MichaelSun90 Any updates on the above request?

[MichaelSun90]

Hi @kartikgupta2607 , we are working on a change to add range checks for datatypes. Will gather the changes into a PR soon.

[kartikgupta2607]

Hi @MichaelSun90, any update on the changes?

[MichaelSun90]

Hi @kartikgupta2607, we have raised a PR #1594 which should handle this error better. If you want, you can give it a try, and see if it behaves as expected. Meanwhile, we will working on merging it into master as soon as possible.

[MichaelSun90]

The fix #1594 has been merged into the master branch.

[kartikgupta2607]

Thanks @MichaelSun90!

[kartikgupta2607]

Hi @MichaelSun90, tested the changes for other data types, its still failing for decimal and numeric type columns. There's an existing issue also for the same - #474

[MichaelSun90]

Hi @kartikgupta2607 , we were looking into all the datatype, and due to some JavaScript side shortage for handling numeric type, we just fixed the these types: date,datetime,datetime2,smalldatetime,datetimeoffset,money,Int,SmallInt,TinyInt. We are also discussing a solution for better handling the numeric types.