node.jssql-serverasync-awaittable-valued-parameters

How I can send Multiple TVPs in Nodejs with MSSQL?


I'm trying to send Multiple Tvps to the procedure using npm i mssql

exports.createNewApplication = async (req, res) => {
try {
 const ServidorConfig = serverConfig();

 const applicationTable = new sql.Table();

 applicationTable.columns.add("id_aplicacion", sql.Int());
 applicationTable.columns.add("producto_aplicado", sql.NVarChar());
 applicationTable.columns.add("fecha_aplicacion", sql.Date());
 applicationTable.columns.add("dosis", sql.Decimal());
 applicationTable.columns.add("eliminado", sql.Bit());

 applicationTable.rows.add(5, "testNode", new Date("2022-03-21"), 13.2, 0);


 const applicationPortionsTable = new sql.Table();

 applicationPortionsTable.columns.add("id_aplicacion",sql.Int)
 applicationPortionsTable.columns.add("id_lote",sql.Int)

 applicationPortionsTable.rows.add(5,1)

 const fuidVarBinary = Buffer.from("42C7F1C99233D000", "utf16le");

 const pool = await sql.connect(ServidorConfig);
 const result = await pool.request()
                .input("type", sql.TinyInt, 2)
                .input("applications", sql.TVP, applicationTable)
                .input("applications_portions", sql.TVP, applicationPortionsTable)
                .input("id_company", sql.Int,1 )
                .input("FUID", sql.VarBinary, fuidVarBinary)
                .execute("brb_CRUD_applications")

 console.log(result)

} catch (error) {

 console.log(error)
 }
};

But I get an error

RequestError: The number NaN cannot be converted to BigInt because it is not an integer at Request.userCallback (D:\Test\FitoMasivo\testLibrary\node_modules\mssql\lib\tedious\request.js:808:19) at Request.callback (D:\Test\FitoMasivo\testLibrary\node_modules\tedious\lib\request.js:205:14) at Parser.onEndOfMessage (D:\Test\FitoMasivo\testLibrary\node_modules\tedious\lib\connection.js:2854:22) at Object.onceWrapper (events.js:519:28) at Parser.emit (events.js:400:28) at Readable. (D:\Test\FitoMasivo\testLibrary\node_modules\tedious\lib\token\token-stream-parser.js:32:12) at Readable.emit (events.js:400:28) at endReadableNT (internal/streams/readable.js:1317:12) at processTicksAndRejections (internal/process/task_queues.js:82:21) { code: 'EREQUEST', originalError: RangeError: The number NaN cannot be converted to BigInt because it is not an integer at Function.BigInt (D:\Test\FitoMasivo\testLibrary\node_modules\jsbi\dist\jsbi-cjs.js:1:366) at WritableTrackingBuffer.writeUInt64LE (D:\Test\FitoMasivo\testLibrary\node_modules\tedious\lib\tracking-buffer\writable-tracking-buffer.js:161:41) at Object.generateParameterData (D:\Test\FitoMasivo\testLibrary\node_modules\tedious\lib\data-types\decimal.js:101:14) at generateParameterData.next () at Object.generateParameterData (D:\Test\FitoMasivo\testLibrary\node_modules\tedious\lib\data-types\tvp.js:96:28) at generateParameterData.next () at RpcRequestPayload.generateParameterData (D:\Test\FitoMasivo\testLibrary\node_modules\tedious\lib\rpcrequest-payload.js:120:17) at generateParameterData.next () at RpcRequestPayload.generateData (D:\Test\FitoMasivo\testLibrary\node_modules\tedious\lib\rpcrequest-payload.js:70:19) at generateData.next (), number: undefined, lineNumber: undefined, state: undefined, class: undefined, serverName: undefined, procName: undefined, precedingErrors: [] }

So my question is: How to send multiples TVPs to let me performance the query


Solution

  • It was the non-declaration of the precision and scale of the Type Decimal but the error displayed was not correct

    applicationTable.columns.add("dosis", sql.Decimal(10,3));