nodejs mssql的查询为什么只显示一条记录?【求大神来指点迷津】
发布于 3个月前 作者 heixiaoshan 470 次浏览 来自 问答

问题1: 正常来讲应该显示有500条记录。为什么只显示一条呢,查了相关文档也没说关于这一块的。希望大神指导下!如何才能全部显示出来呢? 代码如下:

Mingo.getsales=function getsales(callback){

  var sqlstr="select * from Contract";
   var connection = new sql.Connection(connects, function (err) {
            if (err) console.log(err);//判断异常
            
            var request = new sql.Request(connection);
            request.multiple = true;
            request.query(sqlstr, function (err, recordsets) {
            if(recordsets.length===0) callback(err,'[]');
            
                console.log(recordsets.length); // return 1
                callback(err,recordsets);
            });
        });
}

结果显示为1。 问题2

 var sqlstr="select b.name, sum(a.bldarea) as bldarea,convert(varchar(7),a.qdate,120) as qdate ";
sqlstr+=" from Contract left join project on a.pid=b.pid where name='AAA' group by b.name order by b.name,qdate";

为什么执行这一条sql的结果为空?实际在sql查询分析中是有值的! 诚邀大神@a272121742 md说明还有个方法。但是不太明白该怎么用

Streaming example with one global connection

If you plan to work with large amount of rows, you should always use streaming. Once you enable this, you must listen for events to receive data.

var sql = require('mssql'); 

var config = {
    user: '...',
    password: '...',
    server: 'localhost', // You can use 'localhost\\instance' to connect to named instance
    database: '...',
    stream: true, // You can enable streaming globally
    
    options: {
        encrypt: true // Use this if you're on Windows Azure
    }
}

sql.connect(config, function(err) {
    // ... error checks
    
    var request = new sql.Request();
    request.stream = true; // You can set streaming differently for each request
    request.query('select * from verylargetable'); // or request.execute(procedure);
    
    request.on('recordset', function(columns) {
        // Emitted once for each recordset in a query
        这里该怎么用,不是太明白,没有看到相应的demo
    });
    
    request.on('row', function(row) {
        // Emitted for each row in a recordset
        ?????
    });
    
    request.on('error', function(err) {
        // May be emitted multiple times
        ????
    });
    
    request.on('done', function(returnValue) {
        // Always emitted as the last one
        ???
    });
});

其实他有一个这样的方法,我没太懂该怎么用。

18 回复

微软的东西不适合node(主要是服务器的问题,不适合互联网项目),哈哈,node搭配mysql和mongodb比较好

我用过这种组合,不过我不清楚你用的是那个模块?是微软出品的那个吗?

刚看id觉得这个名字好熟悉啊。。。和黄冠一起的?

@saighost 应该是你认错人了吧。

@i5ting 现在没办法,必须要从这个数据库里拿东西。没辙啊。

哦那个好像是黑山老妖,我用的是tds,如果是SQLserver05版的还OK。

@saighost 方便给我一个详细点的demo吗?嗯。数据库是05版的。感谢!

getOneFormSQLServer = function (sql, box, callback) {
    var conn = new tds.Connection(config.sql_server);
    conn.handler = {
      error: function(error) {
        console.error('Received error', error);
      },
      message: function(message) {
        console.info('Received message', message);
      }
    }
    conn.connect(function(err) {
        if (err) {
            console.error('connect error', err);
            callback(err, null);
        } else {
            var stmt = conn.createStatement(sql);
            stmt.on('row', function(row) {
                var d = box(row); 
                callback(null,d);
            });
            stmt.on('done', function(done) {
                conn.end();
            });
            stmt.execute();
        }
    });

};

getAllFormSQLServer = function (sql, box, callback) {
    var conn = new tds.Connection(config.sql_server);
    conn.handler = {
      error: function(error) {
        console.error('Received error', error);
      },
      message: function(message) {
        console.info('Received message', message);
      }
    }
    conn.connect(function(err) {
        if (err) {
            console.error('connect error', err);
            callback(err, null);
        } else {
            var datalist = [];
            var stmt = conn.createStatement(sql);
            stmt.on('row', function(row) {
                var d = box(row); 
                //callback(null,d);
                datalist.push(d);
            });
            stmt.on('done', function(done) {
                conn.end();
                callback(null,datalist);
            });
            stmt.execute();
        }
    });

};

exports.getHZJBQK = function (blh, callback) {
    var sql = "select * from DBO.VIEW_nh_test_inpatientRegister where blh = '"+blh+"'";
    getOneFormSQLServer(sql,
    function (row) {
        var data = {};
        data.blh = row.getValue(0).trim();
        data.zyh = row.getValue(1).trim();
        data.ylhm = row.getValue(2).trim();
        data.hz_sfzh = row.getValue(3).trim();
        data.jbdm = row.getValue(4).trim();
        data.jbmc = dict.gbkToUTF8(row.getBuffer(5)).trim();
        data.rysj = tool.formatDate(row.getValue(6));
        return data;
    },callback);
    
}

不好意思,两年前的代码了。凑合看吧。我用的tds

@saighost tds的地址方便给我一个吗?我搜了下发现好几个。

@saighost

Streaming example with one global connection

If you plan to work with large amount of rows, you should always use streaming. Once you enable this, you must listen for events to receive data.

var sql = require('mssql'); 

var config = {
    user: '...',
    password: '...',
    server: 'localhost', // You can use 'localhost\\instance' to connect to named instance
    database: '...',
    stream: true, // You can enable streaming globally
    
    options: {
        encrypt: true // Use this if you're on Windows Azure
    }
}

sql.connect(config, function(err) {
    // ... error checks
    
    var request = new sql.Request();
    request.stream = true; // You can set streaming differently for each request
    request.query('select * from verylargetable'); // or request.execute(procedure);
    
    request.on('recordset', function(columns) {
        // Emitted once for each recordset in a query
        这里该怎么用,不是太明白,没有看到相应的demo
    });
    
    request.on('row', function(row) {
        // Emitted for each row in a recordset
        ?????
    });
    
    request.on('error', function(err) {
        // May be emitted multiple times
        ????
    });
    
    request.on('done', function(returnValue) {
        // Always emitted as the last one
        ???
    });
});

其实他有一个这样的方法,我没太懂该怎么用。

大神来指点一下啊

就是每次查询结果会触发一次’recordset’事件,返回的应该是结果集的信息。 row是结果每行触发一下。

现在实在没环境,帮你测不了你用的库。

回到顶部