Tips for sending a timestamp as input to a stored procedure in TypeScript with the mssql module

Question

Tips for sending a timestamp as input to a stored procedure in TypeScript with the mssql module

Currently, I am utilizing the mssql npm package to communicate with SQL server. I have encountered a dilemma where the variable (which is of type TIMESTAMP in sql server) fetched from a stored procedure is returned as a byte array. Now, I need to pass this variable as a parameter to another stored procedure, which also requires it to be of type TIMESTAMP in sql server. This particular functionality is part of a migration process from dotnet to nodejs.

When retrieving a variable of type DATESTAMP from SQL server, the output appears like this:

invoiceDetail.UpdTS = { type: 'Buffer', data: [ 0, 0, 0, 0, 44, 88, 104, 217 ] }

My goal now is to successfully send this variable into the other stored procedure.

I attempted the following:

.input("UpdTS", sql.####, invoiceDetail.UpdTS) => #### -> undefined

To address this issue, I tried converting the byte array into a hex string and then passing it as follows:

(invoiceDetail.UpdTS was converted to a class, followed by running the data through a function to acquire the hex string)

.input("UpdTS", sql.Varchar, '000000002c5868d9') => error occurred at this point

node.js sql-server typescript npm

Answer 1

Answer №1

For this task, you should consider using the sql.Binary or sql.VarBinary input types based on the mssql DataTypes documentation. For example:

const sql = require('mssql')

const sqlConfig = {
    user: process.env.DB_USER,
    password: process.env.DB_PWD,
    database: process.env.DB_NAME,
    server: process.env.DB_SERVER,
    port: parseInt(process.env.DB_PORT ?? '1433'),
    pool: {
        max: 10,
        min: 0,
        idleTimeoutMillis: 30000
    },
    options: {
        encrypt: false,
        trustServerCertificate: true
    }
}

const foo = async () => {
    try {
        const ID = 1
        const pool = await sql.connect(sqlConfig)
        const result = await pool.request()
            .input('ID', sql.Int, ID)
            .query('select ID, UpdTS from dbo.InvoiceDetail where ID = @ID')
        console.log('Result:')
        console.dir(result)

        const UpdTS_Buffer = result.recordset[0].UpdTS
        console.log('UpdTS_Buffer:')
        console.dir(UpdTS_Buffer)

        await pool.request()
            .input('OldID', sql.Int, ID)
            .input('NewID', sql.Int, ID+1)
            .input('UpdTS', sql.Binary(8), UpdTS_Buffer)
            .execute('dbo.InvoiceDetailUpdate')

    } catch (err) {
        console.error(`An error occurred:`, err)
    }
}
foo().then(() => process.exit(0))

This code block will produce the following output:

Result:
{
  recordsets: [ [ [Object] ] ],
  recordset: [ { ID: 1, UpdTS: [Buffer [Uint8Array]] } ],
  output: {},
  rowsAffected: [ 1 ]
}
UpdTS_Buffer:
Buffer(8) [Uint8Array] [
  0, 0, 0,   0,
  0, 0, 7, 209
]

Answer 2

For this task, you should consider using the sql.Binary or sql.VarBinary input types based on the mssql DataTypes documentation. For example:

const sql = require('mssql')

const sqlConfig = {
    user: process.env.DB_USER,
    password: process.env.DB_PWD,
    database: process.env.DB_NAME,
    server: process.env.DB_SERVER,
    port: parseInt(process.env.DB_PORT ?? '1433'),
    pool: {
        max: 10,
        min: 0,
        idleTimeoutMillis: 30000
    },
    options: {
        encrypt: false,
        trustServerCertificate: true
    }
}

const foo = async () => {
    try {
        const ID = 1
        const pool = await sql.connect(sqlConfig)
        const result = await pool.request()
            .input('ID', sql.Int, ID)
            .query('select ID, UpdTS from dbo.InvoiceDetail where ID = @ID')
        console.log('Result:')
        console.dir(result)

        const UpdTS_Buffer = result.recordset[0].UpdTS
        console.log('UpdTS_Buffer:')
        console.dir(UpdTS_Buffer)

        await pool.request()
            .input('OldID', sql.Int, ID)
            .input('NewID', sql.Int, ID+1)
            .input('UpdTS', sql.Binary(8), UpdTS_Buffer)
            .execute('dbo.InvoiceDetailUpdate')

    } catch (err) {
        console.error(`An error occurred:`, err)
    }
}
foo().then(() => process.exit(0))

This code block will produce the following output:

Result:
{
  recordsets: [ [ [Object] ] ],
  recordset: [ { ID: 1, UpdTS: [Buffer [Uint8Array]] } ],
  output: {},
  rowsAffected: [ 1 ]
}
UpdTS_Buffer:
Buffer(8) [Uint8Array] [
  0, 0, 0,   0,
  0, 0, 7, 209
]

Tips for sending a timestamp as input to a stored procedure in TypeScript with the mssql module

Answer №1

Similar questions

having difficulty connecting to an IP address from heroku

SPFX - Slow Installation of NPM Packages

The disappearance of hashtag (#) when passed as req.query in the backend has been observed

Error: Unable to initiate session: Although some devices were detected, they are not accessible:

Encountering: Unable to break down the property 'DynamicServerError' of 'serverHooks' as it does not have a defined value

Retrieve the outermost shell of the VUEjs wrapper test-utils

Guide to setting up a new create-react-app project after removing an existing one

Adding connected types to a list using Typescript

Combining multiple 'Eithers' and 'Promises' in fp-ts: A guide to piping and chaining operations

The term 'eval' is not identified as an internal or external command, executable program, or batch file in the Windows npm script environment

Having difficulty accessing the PDFKit generated document

Tips for transferring a null value from a json to a dynamodb

I asked for 3 ids to search for nodes/express, but I only received 1 document in return

None of the NPM commands are working due to an error stating: "TypeError: Class extends value undefined is not a constructor

How can I efficiently include all css from node_modules in vuejs?

Tips on separating a function into two separate files in Node.js

Are there type declarations in TypeScript for the InputEvent?

Tips for retrieving a local variable from inside a callback and using it outside the function

Learn the best method to turn off caching for specific routes within your Express 4 applications

Can the flow of code in NodeJs be disrupted by throwing an Exception in Javascript?