I am attempting to use NodeJS with the Tedious (/) sql server plugin to make multiple database calls. My intent is to: 1. Open a connection 2. Start a transaction 3. Make multiple database (stored procedure) calls, which will not return any data. 4. Commit transaction (or roll back on error). 5. Close connection
Here is an example .js file, (without using a transaction) for NodeJS where I am attempting to make multiple database calls and it is failing with the error "Requests can only be made in the LoggedIn state, not the SentClientRequest state." Nothing I try resolves this issue.
Does anyone know how to resolve this?
var Connection = require('tedious').Connection;
var Request = require('tedious').Request;
var config = {
userName: 'login',
password: 'password',
server: '127.0.0.1',
options: { rowCollectionOnDone: true }
};
var max = 1;
for (var i = 0; i < max; i++) {
var connection = new Connection(config);
function executeStatement() {
request = new Request("select 42, 'hello world'", function (err, rowCount) {
if (err) {
console.log(err);
} else {
console.log(rowCount + ' rows');
}
});
request.on('row', function (columns) {
columns.forEach(function (column) {
console.log(column.value);
});
});
request.on('doneInProc', function (rowCount, more, rows) {
});
request.on('doneProc', function (rowCount, more, rows) {
console.log('statement pleted!')
connection.execSql(request);
});
request.on('returnStatus', function (status) {
console.log('statement pleted!')
});
connection.execSql(request);
}
connection.on('connect', function (err) {
// If no error, then good to go...
executeStatement();
});
}
console.log('Done!');
I am attempting to use NodeJS with the Tedious (http://pekim.github.io/tedious/) sql server plugin to make multiple database calls. My intent is to: 1. Open a connection 2. Start a transaction 3. Make multiple database (stored procedure) calls, which will not return any data. 4. Commit transaction (or roll back on error). 5. Close connection
Here is an example .js file, (without using a transaction) for NodeJS where I am attempting to make multiple database calls and it is failing with the error "Requests can only be made in the LoggedIn state, not the SentClientRequest state." Nothing I try resolves this issue.
Does anyone know how to resolve this?
var Connection = require('tedious').Connection;
var Request = require('tedious').Request;
var config = {
userName: 'login',
password: 'password',
server: '127.0.0.1',
options: { rowCollectionOnDone: true }
};
var max = 1;
for (var i = 0; i < max; i++) {
var connection = new Connection(config);
function executeStatement() {
request = new Request("select 42, 'hello world'", function (err, rowCount) {
if (err) {
console.log(err);
} else {
console.log(rowCount + ' rows');
}
});
request.on('row', function (columns) {
columns.forEach(function (column) {
console.log(column.value);
});
});
request.on('doneInProc', function (rowCount, more, rows) {
});
request.on('doneProc', function (rowCount, more, rows) {
console.log('statement pleted!')
connection.execSql(request);
});
request.on('returnStatus', function (status) {
console.log('statement pleted!')
});
connection.execSql(request);
}
connection.on('connect', function (err) {
// If no error, then good to go...
executeStatement();
});
}
console.log('Done!');
4 Answers
Reset to default 3You're trying to execute a statement on a connection that is not established. You're missing an error handler before you call executeStatement.
connection.on('connect', function (err) {
if (err) {
console.log(err); // replace with your code
return;
};
// If no error, then good to go...
executeStatement();
});
Edit:
How to execute multiple statements in a transaction in serial:
var statements = ["select 1", "select 2", "select 3"];
var transaction = new sql.Transaction(connection);
transaction.begin(function(err) {
// ... error checks
async.mapSeries(statements, function(statement, next) {
var request = new sql.Request(transaction);
request.query(statement, next);
}, function(err, results) {
// ... error checks
transaction.mit(function(err, recordset) {
// ... error checks
console.log("Transaction mited.");
});
});
});
You should use tedious connection pools to create a pool of multiple connections. For node js, a npm module is available at : https://www.npmjs./package/tedious-connection-pool
For every new value inside for loop you can acquire a new connection and use connection.reset on doneInProc event.
The case which you have been doing is performing 1st iteration of for loop correctly(LoggedIn State) and as you have proceeded without closing or releasing the connection you are using same connection object (SentClientRequest state).
Hence the same object is at final state when the code reaches second iteration of for loop.
Hope it resolves your issue
you can use Tedious Connection pools https://github./pekim/tedious-connection-pool
As @zevsuld and @mannutech said, tedious-connection-pool will enable multiple connections, and prevent erring out when simultaneous requests e into your server.
Below is a generic example that allows you to write multiple queries within one connection pool, and expose them for use in your api. I'm just adding this in case others e along who are trying to acplish this type of implementation.
const ConnectionPool = require('tedious-connection-pool');
const path = require('path');
require('dotenv').config({
path: path.join(__dirname, '../../.env')
})
let Request = require('tedious').Request;
let poolConfig = {
min: 10,
max: 50,
log: true
}
let connectionConfig = {
userName: process.env.user,
password: process.env.password,
server: process.env.server
};
//create the pool
let pool = new ConnectionPool(poolConfig, connectionConfig);
pool.on('error', function(err) {
console.error(err);
});
// At this point in the code, we have established a connection pool. If you run node, you'll see it log out all then connections to your database.
// Let's add some methods which your server might use in fulfilling requests to various endpoints.
let query1 = (cb, res, query) => {
// acquire a connection:
pool.acquire(function(err, connection) {
if (err) {
console.error(err);
return;
} else {
// form your query
let sql_query = `SELECT column1, colum2 from TABLE WHERE column1 LIKE '${query.param}%%' ORDER BY column1 ASC`
// use the connection as usual:
request = new Request(sql_query, (err, rowCount) => {
if (err) {
console.log(err);
return;
} else {
// console.log('rowCount:', rowCount);
}
//release the connection back to the pool when finished
connection.release();
});
let records = [];
request.on("row", function(columns) {
let rowArray = [];
columns.forEach(function(column) {
rowArray.push(column.value);
});
records.push(rowArray);
});
request.on("doneInProc", function() {
cb(records, res);
});
// lastly exectue the request on the open connection.
connection.execSql(request);
}
});
};
let query2 = (cb, res, query) => {
// acquire a connection:
pool.acquire(function(err, connection) {
if (err) {
console.error(err);
return;
} else {
// form your query
let sql_query = `SELECT column3, colum4 from TABLE2 WHERE column3 LIKE '${query.param}%%' ORDER BY column3 ASC`;
// use the connection as usual:
request = new Request(sql_query, (err, rowCount) => {
if (err) {
console.log(err);
return;
} else {
// console.log('rowCount:', rowCount);
}
//release the connection back to the pool when finished
connection.release();
});
let records = [];
request.on("row", function(columns) {
let rowArray = [];
columns.forEach(function(column) {
rowArray.push(column.value);
});
records.push(rowArray);
});
request.on("doneInProc", function() {
cb(records, res);
});
// lastly exectue the request on the open connection.
connection.execSql(request);
}
});
};
// Let's expose these two functions to the rest of your API:
module.exports = {
query1,
query2
}