javascript - 使用 tedious(Node.js) 执行 SQL 查询

Question

我目前正在开发一个项目，该项目要求我操作与时间报告应用程序相关的 SQL 表。我现在连接的简单代码如下:

var Connection = require('tedious').Connection;  
var config = {  
    userName: 'my_user_name',  
    password: 'my_password',  
    server: 'server_to_access',
    database: 'database_in_SQL_Server_Management_Studio'  
};  
var connection = new Connection(config);  
connection.on('connect', function(err) {  
    console.log("Connected");  
}); 

它显示已建立连接，但是当我运行以下命令时:

var Connection = require('tedious').Connection;  
var Request = require('tedious').Request;



var config = {   
    userName: 'my_user_name',  
    password: 'my_password',  
    server: 'server_to_access',
    database: 'database_in_SQL_Server_Management_Studio'  
};  
var connection = new Connection(config);  
connection.on('connect', function(err) {  
    console.log("Connected"); 
    executeStatement();
});  

function executeStatement() {
    request = new Request("SELECT * FROM Employees", 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);
      });
    });
    connection.execSql(request);
}

这是我在 cmd 中的输出:

C:\Users\name\attempt>node test.js
Connected
{ [RequestError: Requests can only be made in the LoggedIn state, not the Connec
ting state]
  message: 'Requests can only be made in the LoggedIn state, not the Connecting
state',
  code: 'EINVALIDSTATE' }

C:\Users\name\attempt>

管理 SQL 表的应用程序是 MS SQL Server Management Studio 2008 R2。

任何关于我做错了什么的指示将不胜感激。谢谢!

Answer 1

您需要根据连接的可用性来管理请求队列和进程(繁琐的包装器可能会有所帮助，我已经有一段时间没有使用它了 https://github.com/mhingston/tedious-wrapper )。该伪代码是根据我自己的工作解决方案改编的(未经测试):

connection.on('connect', (err)=>{ RequestWrapper.next(err); });

const queue = [];

class RequestWrapper(){
    constructor(sql){ this.sql = sql; }
    static next(err){
        const next = queue.shift();
        if(next) next(err);
    }
    queue(args){
        return new Promise((ok, fail)=>{ 
            queue.push((err)=>{
                if(err) fail(err); else ok(this.request(args));
            })
        })
    }
    request(args){
        if(!connection) return Promise.reject(new Error(`connection required`));
        if(!connection.loggedIn || connection.state.name !== 'LoggedIn') return this.queue(args);

        return new Promise((ok, fail)=>{
            try{
                const request = new tedious.Request(this.sql, (err, count, rows)=>{

                    RequestWrapper.next();

                    if(err) return fail(err);
                    ok(rows);
                });
                ...add params here...
                connection.execSql(request);
            }catch(err){
                RequestWrapper.next();
                fail(err);
            }
        });
    }
};