Handling of queries that return BIGINT

[nvp152]

Why is my query returning a truncated number if the table contains columns with BIGINT.

e.g. -9120127830603665000 vs -9120127830603665327

Based on what I can tell this issue was fixed. I may be missing something obvious. Running version 4.2.1.

This is what I am doing:

const sql = require('msnodesqlv8')
async function main() {
    try {
        var connParamsArr=[];
        connParamsArr.push('Driver=ODBC Driver 18 for SQL Server');
        if(process.env.DB_INSTANCE) {
          connParamsArr.push('Server=' + process.env.SERVICE_HOST + '\\' + process.env.DB_INSTANCE);
        } else {
          connParamsArr.push('Server=' + process.env.SERVICE_HOST + ',' + process.env.SERVICE_PORT);
        }
        connParamsArr.push('Database=dbg_warehouse');
        connParamsArr.push('TrustServerCertificate=Yes');
        connParamsArr.push('Trusted_Connection=Yes');
        connParamsArr.push('Encrypt=Yes');

        var connectionString=connParamsArr.join(';');
        console.log("Connection string:[" + connectionString + "]");

        this.connectionPool = new sql.Pool({
          connectionString: connectionString
        })

        await this.connectionPool.promises.open()

        var prom = this.connectionPool.promises.query(`select id as bigint_id, concat(id,'') as string_id from table_with_big_columns`)
        var results = await Promise.all([prom])
        var result = results.map(r => r.first[0].bigint_id + ',' + r.first[0].string_id)
        await this.connectionPool.promises.close()

        console.dir(result)
       await this.connectionPool.promises.close()
    } catch (err) {
        console.log("Error:" + err);
    }
}
main();

[TimelordUK]

This is related to the size of a Java script number which can’t hold the full size when working with huge numbers. The change you refer to is probably the one where there is an option to simply return numbers as strings Sent from my iPhoneOn 3 May 2024, at 07:08, nvp152 @.***> wrote: Why is my query returning a truncated number if the table contains columns with BIGINT. e.g. -9120127830603665000 vs -9120127830603665327 Based on what I can tell this issue was fixed. I may be missing something obvious. Running version 4.2.1. This is what I am doing: const sql = require('msnodesqlv8') async function main() { try { var connParamsArr=[]; connParamsArr.push('Driver=ODBC Driver 18 for SQL Server'); if(process.env.DB_INSTANCE) { connParamsArr.push('Server=' + process.env.SERVICE_HOST + '\' + process.env.DB_INSTANCE); } else { connParamsArr.push('Server=' + process.env.SERVICE_HOST + ',' + process.env.SERVICE_PORT); } connParamsArr.push('Database=dbg_warehouse'); connParamsArr.push('TrustServerCertificate=Yes'); connParamsArr.push('Trusted_Connection=Yes'); connParamsArr.push('Encrypt=Yes');

    var connectionString=connParamsArr.join(';');
    console.log("Connection string:[" + connectionString + "]");

    this.connectionPool = new sql.Pool({
      connectionString: connectionString
    })

    await this.connectionPool.promises.open()

    var prom = this.connectionPool.promises.query(`select id as bigint_id, concat(id,'') as string_id from table_with_big_columns`)
    var results = await Promise.all([prom])
    var result = results.map(r => r.first[0].bigint_id + ',' + r.first[0].string_id)
    await this.connectionPool.promises.close()

    console.dir(result)
   await this.connectionPool.promises.close()
} catch (err) {
    console.log("Error:" + err);
}

} main();

—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you are subscribed to this thread.Message ID: @.***>

[nvp152]

Corrrect. I see mention of an option to return bigints as strings but i could not get that to kick in. After closer inspection of the code I see what looks like a bug in pool.js at line 555. It works when i make the change (or set useUTC=true).

          if (options.useNumericString === true || options.useNumericString === false) {
            c.setUseNumericString(options.useUTC)
          }

Should be

          if (options.useNumericString === true || options.useNumericString === false) {
            c.setUseNumericString(options.useNumericString)
          }

[TimelordUK]

thanks for finding and correcting this, a clear mistake. I will patch change in at least to master not sure at this point when it will rol to npm