Requests can only be made in the LoggedIn state
nvsoares opened this issue · 7 comments
I'm having trouble making multiple requests.
Here is my code:
var ConnectionPool = require('tedious-connection-pool');
var Request = require('tedious').Request;
var poolConfig = {
min: 2,
max: 4,
log: true
};
var connectionConfig = {
userName: 'login',
password: 'password',
server: 'localhost'
options: {
database: 'database'
}
};
var commands = [
"select 1"
, "select 2"
, "select 3"
, "select 4"
];
//create the pool
var pool = new ConnectionPool(poolConfig, connectionConfig);
//acquire a connection
pool.acquire(function (err, connection) {
if (!err) {
if (commands.length > 0) {
commands.forEach(function (cmd) {
console.log(cmd);
var request = new Request(cmd, function (err, rowCount) {
if (err)
console.error(err);
console.log('\nrowCount: ' + rowCount + '\n');
// Release the connection back to the pool.
connection.release();
});
request.on('row', function (columns) {
console.log(columns[0].value);
});
connection.execSql(request);
});
}
} else {
console.log(err);
}
});
pool.on('error', function (err) {
console.error(err);
});When I run the application I get the following error:
Tedious-Connection-Pool: filling pool with 2
Tedious-Connection-Pool: creating connection: 1
Tedious-Connection-Pool: creating connection: 2
Tedious-Connection-Pool: connection connected: 1
{ [RequestError: Requests can only be made in the LoggedIn state, not the SentClientRequest state]
message: 'Requests can only be made in the LoggedIn state, not the SentClientRequest state',
code: 'EINVALIDSTATE' }
rowCount: undefined
Tedious-Connection-Pool: connection ended: 1
Tedious-Connection-Pool: filling pool with 1
Tedious-Connection-Pool: creating connection: 3
{ [RequestError: Requests can only be made in the LoggedIn state, not the Final state]
message: 'Requests can only be made in the LoggedIn state, not the Final state',
code: 'EINVALIDSTATE' }
rowCount: undefined
{ [RequestError: Requests can only be made in the LoggedIn state, not the Final state]
message: 'Requests can only be made in the LoggedIn state, not the Final state',
code: 'EINVALIDSTATE' }
rowCount: undefined
Tedious-Connection-Pool: connection connected: 2
Tedious-Connection-Pool: connection connected: 3What should I do to execute commands at a time?
I'm not sure if this is what you intended, but your code uses the same connection for all four request. There are two problems with your approach. First, you are releasing the connection it back to the pool after the first request. You should not continue to use the connection after releasing it. Second, you are not waiting for the previous request to complete before starting another request. SQL Server only supports a single request at a time per connection.
If your goal really is to run the commands serially on the same connection, here is an example of how to accomplish that:
pool.acquire(function (err, connection) {
if (err) {
console.log(err);
return;
}
var i = 0;
var runQuery = function () {
if (i >= commands.length) {
connection.release();
return;
}
var cmd = commands[i++];
console.log(cmd);
var request = new Request(cmd, function (err, rowCount) {
if (err)
console.error(err);
console.log('\nrowCount: ' + rowCount + '\n');
runQuery();
});
request.on('row', function (columns) {
console.log(columns[0].value);
});
connection.execSql(request);
};
runQuery();
});
Most of the time you want to run queries parallelly. In that can you want to call pool.acquire() for every command. For example:
if (commands.length > 0) {
commands.forEach(function (cmd) {
console.log(cmd);
//acquire a connection
pool.acquire(function (err, connection) {
if (err) {
console.log(err);
return;
}
var request = new Request(cmd, function (err, rowCount) {
if (err)
console.error(err);
console.log('\nrowCount: ' + rowCount + '\n');
// Release the connection back to the pool.
connection.release();
});
request.on('row', function (columns) {
console.log(columns[0].value);
});
connection.execSql(request);
});
});
}Hello @ ben-page.
I followed your instructions and I'm closer to the end result I want to achieve. Now, we need to ensure is that all controls are within the same transaction, so that if one fails all the others are broken.
How to do this using the connection-pool?
Hello @ ben-page.
Sorry but I do not see how. Here is a brief example. How would you do the transaction management? Can you show, please?
app.js
'use strict';
const ConnectionPool = require('tedious-connection-pool');
const Request = require('tedious').Request;
const poolConfig = {
min: 2,
max: 2,
log: true
};
const connectionConfig = {
userName: 'sa',
password: 'Pa$$w0rd',
server: '192.168.0.70',
options: {
instanceName: 'SQL2K12S',
database: '615_demo',
rowCollectionOnRequestCompletion: true
}
};
//create the pool
let pool = new ConnectionPool(poolConfig, connectionConfig);
let commands = [
"select codigo, nome from wgcmarcas"
, "update wgcmarcas set exportado=0"
, "update wgcfamilias set exportado=0"
, "select codigo, nome from wgcfamilias"
];
runCommands();
function runCommands() {
if (commands.length > 0) {
commands.forEach((cmd) => {
execute(cmd);
});
}
}
pool.on('error', (err) => {
console.error(err);
});
function execute(cmd) {
//acquire a connection
pool.acquire((err, connection) => {
if (err) {
console.log(err);
return;
}
let request = new Request(cmd, (err, rowCount) => {
if (err) {
console.error(err);
}
console.log('\n rowCount: ' + rowCount + '\n');
// Release the connection back to the pool.
connection.release();
});
request.on('row', (columns) => {
console.log(columns[0].value);
});
connection.execSql(request);
});
}
@nvsoares, you can't run the queries in a transaction in parallel. Queries in a transaction must be run one at a time. What you need to do (in this order) is acquire a connection, begin a transaction, execute each query one at a time, commit the transaction, and release the connection.
FYI Your question is really about basic database programming patterns. It isn't about tedious-connection-pool or even tedious. Really, GitHub Issues are for bug reports and feature requests. For more general help, I suggest that you try Stack Overflow. There may already be questions there that will help you. If not, your question will likely help others.
hi, @ben-page. thanks for your help.
In fact my question is very basic . I confess that it is extremely difficult to work with tedious . Although it is well documented, the truth is that for those coming from other approaches ( desktop / .NET) is a challenge. What in .NET is simple and with only 5 lines of code is done and it works here is very difficult and , despite the explanations , still does not work . Of course the probelma can be on my side . Certainly. But not here or on Stack Overflow are satisfactory answers. and what a satisfactory answer ? One that will overcome the problem.
In our company we are assessing work with node and SQL Azure , but without a proper evaluation can not make decisions. And all have to be based on answers , who dominates the subject.
The main "problem" to be overcome is able to have a central definition of a connection and can use it whenever needed. Where I come from it is simple:
OpenConnection (open transaction)
//of some stuff
CloseConnection (close transaction)
and I can do the same then with other commands without problems.
So , I ask: where can I continue to discuss / clarify my questions with practical examples ? Where can I get answers . not just opinions , or " I think .. "
Hello, I had same error , 1st thing I did was to fix TCP/IP port in
SQL server 2017 configuration manager-->SQL server network configuration-->protocols for sql express -->Protocol Name: TCP/IP -->open -->click tab IP address--> set port to 1433 under dynamic port.-->apply -->ok
Restart service from SQL server services in same window --> SQL server [SQL Express]
recheck changes in TCP/IP -->if 1433 is present and change configuration in your script file as follows.
var config = {
userName: 'sa',
password: 'password_123',
server: 'localhost',
dialect: "mssql",
options: {encrypt: false, instanceName: "sqlexpress", database: 'patientData'}
};
this should connect you to MSSQL using tedius.
I could do it with above changes.