最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

javascript - How do you make multiple database calls from a single connectiontransaction with Node.js and Tedious - Stack Overfl

programmeradmin2浏览0评论

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!');
Share Improve this question asked Dec 1, 2014 at 16:07 Ken KKen K 8493 gold badges11 silver badges21 bronze badges
Add a ment  | 

4 Answers 4

Reset to default 3

You'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
}

与本文相关的文章

发布评论

评论列表(0)

  1. 暂无评论