我是node.js的新手，在连接到MSSQL数据库和准备/执行准备好的语句时遇到了一个问题。
我有以下代码:
this.connectionPool.connect().then(pool => {
// Create prepared statement
stmt = new mssql.PreparedStatement(pool);
// PREPARE
stmt.prepare(command, err => {
//TODO: check for errors in prepare
// EXECUTE
stmt.execute((err, result) => {
//TODO: check for errors in execute
// UNPREPARE
stmt.unprepare(err => {
//TODO: check for errors in unprepare
});
console.log(`Rows affected: ${stmt.rowsAffected}`);
console.log(`Result: ${result}`);
return result;
});
});
}).catch(err => {
console.log(`Connection pool error: ${err}`);
}).finally(() => {
// Close connection
console.log("Closing connection.");
if (this.connectionPool.connected) {
this.connectionPool.close();
}
});
我发现finally()承诺与then()同时执行，这是我没有预料到的。这将导致连接在可以准备语句之前关闭。
ConnectionError: connection is closed
如何确保仅在语句执行后才关闭连接？
回调是异步执行的，因此需要在promise链中添加execute的结果:
this.connectionPool.connect().then(pool => {
// Create prepared statement
stmt = new mssql.PreparedStatement(pool)
// return a promise to add it to the promise chain
return new Promise((resolve, reject) => {
stmt.prepare(command, err => {
// TODO: check for errors in prepare
if (err) {
reject(err)
return
}
// EXECUTE
stmt.execute((err, result) => {
// TODO: check for errors in execute
if (err) {
reject(err)
return
}
// UNPREPARE
stmt.unprepare(err => {
// TODO: check for errors in unprepare
if (err) {
reject(err)
}
})
console.log(`Rows affected: ${stmt.rowsAffected}`)
console.log(`Result: ${result}`)
resolve(result)
})
})
})
}).catch(err => {
console.log(`Connection pool error: ${err}`)
}).finally(() => {
// Close connection
console.log('Closing connection.')
if (this.connectionPool.connected) {
this.connectionPool.close()
}
})
如果stmt.prepare支持开箱即用的允诺，则可以返回它而不将其包装在New promise中
所有承诺版本:
this.connectionPool.connect().then(pool => {
// Create prepared statement
stmt = new mssql.PreparedStatement(pool)
return stmt.prepare(command)
.then(() => stmt.execute())
.then((result) => {
console.log(`Rows affected: ${stmt.rowsAffected}`)
console.log(`Result: ${result}`)
})
.finally(() => stmt.unprepare())
}).catch(err => {
console.log(`Connection pool error: ${err}`)
}).finally(() => {
// Close connection
console.log('Closing connection.')
if (this.connectionPool.connected) {
this.connectionPool.close()
}
})
