Calling stored procedure which returns no results sets more results set to true

[LaurenceCarr]

I'm seeing a problem in my nodejs application:

If a stored procedure returns an empty table, then more results is set to true (even when I don't expect it to be).

Here is a simple repro (using SQL Server 2008):

SQL Server:

create table mytable 
(
  ID int IDENTITY(1,1) PRIMARY KEY,
  count int
)

create procedure dbo.qry_mytable
@id INT
AS 
SET NOCOUNT ON
select * from mytable where ID = @id

insert into mytable (count) values (1)

Node application:

var express = require('express');
var odbc = require('odbc')();
var app = express();
var cn = "DSN=mydsn;UID=myuid;PWD=mypwd;DATABASE=mydb";

app.get('/:id', function(req, res, next) {
  var id = req.param('id');
  odbc.open(cn, function (err) {
    if (err) return console.log(err);

    var sql = 'exec dbo.qry_mytable @id=?';
    var params = [id]; 

    odbc.query(sql, params, function (err, data, more) {
      if (err) console.log(err);

      console.log('more', more);
      res.json(more);

      if (!more) {
        odbc.close(function () {
          console.log('done');
        });
      }
    });
  });  
});

var server = app.listen(3000, function() {
  console.log('Listening on port %d', server.address().port);
});

Navigating to http://:3000/1 returns false, whereas navigating to http://:3000/2 returns true!

Any ideas if this is a problem with my SQL/node application or if this is a bug in odbc?

Thanks

[wankdanker]

What version of node-odbc are you using? I can not replicate this issue with v0.6.4. Also what platform are you on (Windows/Linux/OSX)?

[LaurenceCarr]

This is with node-odbc v0.6.4.
Linux (CentOS 6.3)
Output setting the DEBUG flag in odbc is:

ODBC::Init
ODBCResult::Init
ODBCConnection::Init
ODBCStatement::Init
ODBC::New
ODBC::CreateConnection
ODBC::UV_CreateConnection
ODBC::UV_AfterCreateConnection
ODBCConnection::New
ODBCConnection::Open
ODBCConnection::UV_Open
ODBCConnection::UV_AfterOpen
ODBCConnection::Query
ODBC::GetParametersFromArray
ODBC::GetParametersFromArray - &param[0].length = 1C309A0
ODBC::GetParametersFromArray - IsInt32(): params[0] c_type=-25 type=-5 buffer_length=0 size=0 length=0 value=2
ODBCConnection::Query : sqlLen=26, sqlSize=54, sql=E
ODBCConnection::UV_Query
ODBCConnection::UV_Query - param[0]: c_type=-25 type=-5 buffer_length=0 size=0 length=0 &length=1C309A0
ODBCConnection::UV_AfterQuery
ODBCConnection::UV_AfterQuery : data->result=0, data->noResultObject=0
ODBCResult::New
ODBCResult::New m_hDBC=1C30000 m_hDBC=440008C0 m_hSTMT=3C0008C0 canFreeHandle=1
ODBCResult::FetchAll
ODBCResult::UV_FetchAll
ODBCResult::UV_AfterFetchAll
ODBCResult::MoreResultsSync
ODBCResult::FetchAll
ODBCResult::UV_FetchAll
ODBCResult::UV_AfterFetchAll
ODBCResult::MoreResultsSync
ODBCResult::CloseSync
ODBCResult::CloseSync closeOption=9999 m_canFreeHandle=1
ODBCResult::Free m_hSTMT=3C0008C0 m_canFreeHandle=1
ODBCConnection::Close
ODBCConnection::UV_Close
ODBCConnection::Free
ODBCConnection::UV_AfterClose

Thanks

[LaurenceCarr]

I don't think this is a node-odbc issue after all.

We're using FreeTDS to connect to SQL Server 2008, using protocol version 7.0. According to http://www.freetds.org/userguide/choosingtdsprotocol.htm SQL Server 2008 supports upto TDS protocol version 7.2. I switched to using 7.2 and I could no longer repro the problem.

Thanks anyway!

[wankdanker]

Ahhh. OK. I have my global tds version set at 8.0. That's probably why I couldn't replicate. Thank you for following up.