I'm trying to run a script where I pull records from one database and then insert the records into another database, but during the insertion step I keep running into this error 8 or 9 repetitions in.
I'm not super familiar with the node-mssql library and would appreciate any insight on what's happening here! I've attached a copy of the stack trace and the script.
const sql = require('mssql')
const humENV = require('../env').humENV
const rbENV = require('../env').rbENV
const logger = require('./logger')
const getReviewHumanatic = async () => {
try {
let pool = await sql.connect(humENV.SQL_CONFIG)
let req = await pool.request()
.query(`
WITH cte
AS (SELECT DISTINCT TOP 500 htaskid,
ht.frn_leuserid,
ht.frn_callid,
ht.frn_hcatid,
ht.frn_hcat_optionid,
frn_lskinid,
cq.duration_seconds,
add_path,
CASE
WHEN call_queueid IS NULL THEN 3
ELSE 1
END AS status,
CASE
WHEN hcab_reviewid IS NULL THEN 0
ELSE 1
END AS HCAB
FROM htask ht
LEFT JOIN call_queue cq
ON ht.frn_callid = cq.frn_callid
LEFT JOIN hcab_review hr
ON hr.frn_callid = cq.frn_callid
AND ht.frn_leuserid = hr.frn_leuserid)
SELECT Isnull(frn_callid, 0) AS frn_callid,
Isnull(frn_hcatid, 0) AS frn_hcatid,
Isnull(HCAB, 0) AS HCAB,
Isnull(frn_hcat_optionid, 0) AS frn_hcat_optionid,
Isnull(frn_lskinid, 0) AS frn_lskinid,
Isnull(frn_leuserid, 0) AS frn_leuserid,
Isnull(status, 0) AS status,
Isnull(duration_seconds, 0) AS duration_seconds,
Isnull(add_path, '') AS add_path,
Isnull(htaskid, 0) AS htaskid
FROM cte
ORDER BY frn_callid
`)
if (req.recordset.length !== 0) {
console.log(req.recordset)
logger.info('returning query records')
return req.recordset
}
pool.close()
} catch (err) {
logger.error(err)
}
}
const insertRebase = async (data) => {
try {
sql.close()
let pool = await sql.connect(rbENV.SQL_CONFIG)
for (let i = 0; i < data.length; i++) {
console.log(data[i])
let req = await pool.request()
.input('frn_callid', sql.BigInt, data[i].frn_callid)
.input('frn_categoryid', sql.Int, data[i].frn_hcatid)
.input('HCAB', sql.Int, data[i].HCAB)
.input('frn_answer_optionid', sql.Int, data[i].frn_hcat_optionid)
.input('frn_leuserid', sql.Int, data[i].frn_leuserid)
.input('frn_lskinid', sql.Int, data[i].frn_lskinid)
.input('frn_statusid', sql.TinyInt, data[i].status)
.input('reviewid', sql.Int, data[i].htaskid)
.input('call_duration', sql.Int, data[i].duration_seconds)
.input('HCAB_trigger', sql.VarChar, data[i].add_path)
.query('SET IDENTITY_INSERT review ON; INSERT INTO review (frn_callid, frn_categoryid, HCAB, frn_answer_optionid, frn_leuserid, frn_lskinid, frn_statusid, reviewid, call_duration, HCAB_trigger) VALUES (@frn_callid, @frn_categoryid, @HCAB, @frn_answer_optionid, @frn_leuserid, @frn_lskinid, @frn_statusid, @reviewid, @call_duration, @HCAB_trigger)', function (err, recordset) {
if (err) {
logger.error(err)
console.log(err)
}
pool.close()
})
}
} catch (err) {
logger.error(err)
}
}
const initializeReview = async () => {
try {
const humData = await getReviewHumanatic()
insertRebase(humData)
} catch (err) {
console.log(err)
}
}
initializeReview()
I looked at this question NPM MSSQL - error: uncaughtException: Cannot read property 'release' of null but it doesn't look like there was a conclusive solution.
Stack trace of error:
EDIT: Since my original post, I've converted the insert query to a preparedstatement:
const insertRebase = async (data) => {
try {
sql.close()
let pool = await sql.connect(rbENV.SQL_CONFIG)
for (let i = 0; i < data.length; i++) {
let ps = new sql.PreparedStatement(pool)
ps.input('frn_callid', sql.BigInt)
ps.input('frn_categoryid', sql.Int)
ps.input('HCAB', sql.Int)
ps.input('frn_answer_optionid', sql.Int)
ps.input('frn_leuserid', sql.Int)
ps.input('frn_lskinid', sql.Int)
ps.input('frn_statusid', sql.TinyInt)
ps.input('reviewid', sql.Int)
ps.input('call_duration', sql.Int)
ps.input('HCAB_trigger', sql.VarChar)
ps.prepare(`SET IDENTITY_INSERT review ON; INSERT INTO review (frn_callid, frn_categoryid, HCAB, frn_answer_optionid, frn_leuserid, frn_lskinid, frn_statusid, reviewid, call_duration, HCAB_trigger) VALUES (@frn_callid, @frn_categoryid, @HCAB, @frn_answer_optionid, @frn_leuserid, @frn_lskinid, @frn_statusid, @reviewid, @call_duration, @HCAB_trigger)`, err => {
if (err) {
console.log(err)
logger.error(err)
} else {
ps.execute({frn_callid: data[i].frn_callid, frn_categoryid: data[i].frn_hcatid, HCAB: data[i].HCAB, frn_answer_optionid: data[i].frn_hcat_optionid, frn_leuserid: data[i].frn_leuserid, frn_lskinid: data[i].frn_lskinid, frn_statusid: data[i].status, reviewid: data[i].htaskid, call_duration: data[i].duration_seconds, HCAB_trigger: data[i].add_path}, (err, result) => {
if (err) {
console.log(err)
logger.error(err)
} else {
ps.unprepare(err => {
if (err) {
console.log(err)
logger.error(err)
}
})
}
})
}
})
}
} catch (err) {
logger.error(err)
}
}
I'm unsure as to why a preparedstatement solution works over the previous solution, but the preparedstatement solution doesn't seem to error out and properly inserts all of the records.
As for the sql.close() global connection issue, does doing
const getReviewHumanatic = async () => {
try {
let pool1 = await sql.connect(SQL_CONFIG1)
let req = await pool1.request()
}
create a global connection? I was under the assumption that it was creating a local connection and that if I did
let pool2 = await sql.connect(SQL_CONFIG2)
it would create a separate sql connection, but that doesn't seem to be the case because I can't run the script without doing sql.close() before I can create pool2.
I checked the node-mssql documentation and it looks like if I do a request without a pool specification, it'll use the global sql connection; maybe that's what's happening with my script? https://www.npmjs.com/package/mssql#request
