number multiply by 100 when using bulk insert #364
Description
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.');
});
}
}
}