Skip to content

number multiply by 100 when using bulk insert #364

Description

@PichaiSBG

I use msnodesqlv8 v.4.5.0, Nodejs v. 21.5.0, MSSQL 2022 in docker to test bulk insert. original data have 14 rows. there are 2 rows that incorrectly. Even I reduce number of row and column to be 1 as I provide below. the inconsistency still there.
Please advice. Thanks.

following are code, json data and result
TABLE structure : CREATE TABLE [dbo].[Z1](
[ABC] [numeric](12, 2) NULL,
)

JSON DATA : [{"ABC":16763.60}]

result after do bulk insert:
select * from Z1
ABC: 1676359.99

const sql = require("msnodesqlv8");
const fs = require("fs");

main();
async function main () {
const driver = "ODBC Driver 17 for SQL Server";
const connectionString = "Driver={" + driver + "};Server=" + server + ";" +
(useTrustedConnection == 'true' ? "Trusted_Connection={Yes};" : "UID=" + user + ";PWD=" + pwd + ";") + "Database={" + database + "};";
const parsedJSON = JSON.parse(fs.readFileSync('data.json','utf8'));
const tableName='Z1';
let conn; // Declare connection outside to ensure it's closed in finally
try {

    conn = await new Promise((resolve, reject) => {
        sql.open(connectionString, function (err, connection) {
            if (err) {
                console.error("Connection Error:", err);
                return reject(err);
            }
            console.log('Successfully connected to SQL Server.');
            resolve(connection);
        });
    });

    // Crucial for correct handling of dates/times based on local server time
    // If your database stores UTC and you want to insert UTC, you can remove this or set to true.
    conn.setUseUTC(false); 
    console.log(`Attempting to bind to table: ${tableName}`);

    const tm = conn.tableMgr();

    // Use a Promise wrapper for tm.bind for better async flow
    const bulkMgr = await new Promise((resolve, reject) => {
        tm.getTable(tableName, (bindErr, manager) => {
            if (bindErr) {
                console.error("Table Binding Error:", bindErr);
                return reject(bindErr);
            }
            if (!manager) {
                // This can happen if tableMgr.bind doesn't return a manager,
                // indicating the table was not found or accessible.
                return reject(new Error(`Could not bind to table '${tableName}'. Ensure it exists and is accessible.`));
            }
            console.log(`Successfully bound to table: ${tableName}. Starting bulk insert.`);
            resolve(manager);
        });
    });

    if (parsedJSON.length === 0) {
        console.log("No data to insert. Skipping bulk insert.");
        return;
    }

    // Perform the bulk insert
    // Use a Promise wrapper for bulkMgr.insertRows for better async flow
    await new Promise((resolve, reject) => {
        bulkMgr.insertRows(parsedJSON, (insertErr, result) => {
            if (insertErr) {
                console.error("Bulk Insert Error:");
                return reject(insertErr);
            }
            // The 'result' object might contain information like rows affected,
            // depending on the driver version and operation.
            console.log(`Bulk insert successful! Result:`, result);
            resolve(result);
        });
    });

} catch (error) {
    // Consolidated error handling
    console.error("An error occurred during the bulk insert process:");
    // You can add more specific error handling here based on 'error' object properties if needed
    // For example:
    // if (error.code === 'ELOGIN') { ... }
    // if (error.message.includes('Invalid column name')) { ... }
} finally {
    // Ensure the connection is always closed
    if (conn) {
        conn.close(() => {
            console.log('Database connection closed.');
        });
    }
}

}

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions