tediousjs/tedious

Node TDS module for connecting to SQL Server databases.

Tedious (node implementation of TDS)

Tedious is a pure-Javascript implementation of the TDS protocol, which is used to interact with instances of Microsoft's SQL Server. It is intended to be a fairly slim implementation of the protocol, with not too much additional functionality.

NOTE: New columns are nullable by default as of version 1.11.0

Previous behavior can be restored using config.options.enableAnsiNullDefault = false. See pull request 230.

NOTE: Default login behavior has changed slightly as of version 1.2

See the changelog for version history.

Supported TDS versions

• TDS 7.4 (SQL Server 2012/2014/2016/2017/2019/2022)
• TDS 7.3.B (SQL Server 2008 R2)
• TDS 7.3.A (SQL Server 2008)
• TDS 7.2 (SQL Server 2005)
• TDS 7.1 (SQL Server 2000)

Installation

Node.js is a prerequisite for installing tedious. Once you have installed Node.js, installing tedious is simple:

npm install tedious

Getting Started

• Node.js + macOS
• Node.js + Red Hat Enterprise Linux
• Node.js + SUSE Linux Enterprise Server
• Node.js + Ubuntu
• Node.js + Windows

Documentation

More documentation and code samples are available at tediousjs.github.io/tedious/

Name

Tedious is simply derived from a fast, slightly garbled, pronunciation of the letters T, D and S.

Developer Survey

We'd like to learn more about how you use tedious:

Contributing

We welcome contributions from the community. Feel free to checkout the code and submit pull requests.

License

Copyright (c) 2010-2021 Mike D Pilsbury

The MIT License

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

tedious's Issues

Stored Procedures

I don't know if you already have support for calling stored procedures. If you do, how do you call them? If not, would it take much effort to add support for stored procedures? Thanks!

datetime support

Hi,
I saw in the master, you are adding the support to the type datetime. Could you please tell me when you this will be released ?

In the meantime, i tried to add the declaration to the type datetime, but i get an message when i make an insert call -

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 5 ("@createdOn"): Data type 0x3D is unknown.

is this sometime which i can fix locally based on your recommendation until i get the released library ?

Thanks.

SQL pooling

i want to set sql pooling. how do i set it?

SQL Server 2012 ECONNREFUSED

Using latest nodejs and tedious versions and the following code:

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

var config =
{
server: '192.168.1.113',
userName: 'myUserName',
password: 'myPassword',
options:
{
database: 'MyDatabase',
port: '1433',
encrypt: true,
driver: 'SQL Server Native Client 11.0'
},
debug:
{
packet: true,
data: true,
payload: true,
token: true,
log: true
}
};

var connection = new Connection(config);

connection.on('connect', connected);
connection.on('infoMessage', infoError);
connection.on('errorMessage', infoError);
connection.on('end', end);
connection.on('debug', debug);

function connected(err)
{
console.log('connected=> ' + err);
}

function infoError(info)
{
console.log('infoError=> ' + info);
}

function debug(message)
{
console.log('debug=> ' + message);
}

function end()
{
}

I get the following error after i run it:

node test_sqlserver_tedious.js
debug=> connection to 192.168.1.113:1433 - failed Error: connect ECONNREFUSED
debug=> State change: Connecting -> Final
debug=> connection to 192.168.1.113:1433 closed
debug=> State change: Final -> Final

Any ideas? I could help you test the source if you show me how.

Thanks

Add support for prepared SQL

Use sp_prepare and sp_execute.

node 0.8 bug

Recently upgraded my install of node up to 0.8.0-release and I tracked down this error to be related to the tedious module.

  > node server.js
Assertion failed: (handle->InternalFieldCount() > 0), function Wrap, file ../src/node_object_wrap.h, line 62.
[1]    2506 abort      node server.js

Add support for parameterised requests

Named parameters (processed by SQL Server, not tedious) will reduce the likelihood of SQL injection.

Support the following:

• Input parameters
• Output parameters
• Datatypes - initially just a few, until comfortable with the implementation and API
  • int
  • varchar

Use sp_executesql to implement.

uniqueidentifier support

was wondering what the status on unique identifier support is i saw some code related to it commented out

connection config

hi all,
i am using tedious for my backend database (MSSQL 2008) with node.js.
while configuring tedious if i am setting 'local' instead of IP address in server tag, it is not working. when i am change (local)/local to IP address, it is going very fine.

following is the my config setting for tedious.

var config = {
// server: 'local',
server: 'XXX.XXX.X.XXX',
userName: 'UserName',
password: 'UserPassword',
options: {
database: 'databaseName'
}
};

so, i don't understand what is the problem?

I would like to enhance the implementation of data-type.TYPE.0x24 to support parameterization

i.e. add declaration: and writeParameterData: functions.

caveat: i really don't know what I am doing here and mean the following code more as poetry about a solution than an actual solution.

Is there something about TDS that makes this tough? Looks like I need binary representation. If I added something like what is shown in the answer http://stackoverflow.com/questions/6095115/javascript-convert-guid-in-string-format-into-base64 it seems like I could insert that into the buffer via something like:

(./lib/data-type.js)
...
0x24: {
    type: 'GUIDN',
    name: 'UniqueIdentifierN',
    dataLengthLength: 1,
    declaration: function(parameter) {
      return 'uniqueidentifier';
    },
    writeParameterData: function(buffer, parameter) {
      buffer.writeUInt8(typeByName.UniqueIdentiferN.id);
      buffer.writeUInt16(16); //?????

      if (parameter.value != null) {
        var hexlist = '0123456789abcdef';
        var b64list = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';

        // GUID string with four dashes is always MSB first,
        // but base-64 GUID's vary by target-system endian-ness.
        // Little-endian systems are far more common.  Set le==true
        // when target system is little-endian (e.g., x86 machine).
        //
        var s = g.replace(/[^0-9a-f]/ig,'').toLowerCase();
        if (s.length != 32) return '';

        if (le) s = s.slice(6,8) + s.slice(4,6) + s.slice(2,4) + s.slice(0,2) +
          s.slice(10,12) + s.slice(8,10) +
          s.slice(14,16) + s.slice(12,14) +
          s.slice(16);
        s += '0';

        var a, p, q;
        var binaryGuid = '';
        var i = 0;
        while (i < 33) {
          a =  (hexlist.indexOf(s.charAt(i++)) << 8) |
            (hexlist.indexOf(s.charAt(i++)) << 4) |
            (hexlist.indexOf(s.charAt(i++)));

          p = a >> 6;
          q = a & 63;

          binaryGuid += b64list.charAt(p) + b64list.charAt(q);
        }
        binaryGuid += '==';
        buffer.writeUInt16(16); //?????
        return buffer.writeUniqueIdentifier(binaryGuid);
      } else {
        return buffer.writeUInt16(0);
      }
    }
  },
...

which uses:

(./lib/tracking-buffer/writable-tracking-buffer.js)
...
WritableTrackingBuffer.prototype.writeUniqueIdentifier = function(value) {
    var length;
    length = 16;
    this.makeRoomFor(length);
    this.buffer.writeUniqueIdentifier(value, this.position);
    return this.position += length;
  };
...

Am I missing something tricky?

Support for float type is missing

Support is required for:

• 0x3E FLT8TYPE
• 0x6D FLTNTYPE

Tedious seems oblivious to schemas

If I have tables within schemas (e.g. Foo.MyTable rather than MyTable, or dbo.MyTable) then queries such as 'select * from Foo.MyTable' respond with 'invalid object name'.

I've had a quick look at the code and I can't see why it should be a problem for tedious, but presumably when using schemas we need to tell the server some more information or something (as the error is server driven) ?

However IF you fully-qualify it e.g. <DB_NAME>.<SCHEMA_NAME>.<TABLE_NAME> (rather than just .<SCHEMA_NAME>.<TABLE_NAME> ) it does work.

I guess I'm raising this as an issue so others know how to fix it :)

'Done' event not firing when request is complete

Per the title, the 'done' event is not being fired when the request returns all rows. I have something like the following, which is retrieving row data:

request = new Request(statement, function (err, rowCount) {
    // Check for errors etc.
    connection.close();
});

request.on("row", function (fields) {
    // Do stuff with row data
});

request.on("done", function (rowCount, more) {
    console.log(rowCount + " rows returned.");   // <-- This is never executed.
});

More details available upon request, thanks.

Matt

Connecting to remote Sql Server using node tedious

I am trying to connect to my test database server form my local box. suing tedious connection pool and having great difficulty.,

Can you please help me ASAP.

Thanks heaps.. My code:

var ConnectionPool = require('tedious-connection-pool');
var Request = require('tedious').Request;

var connectionConfig = {
  "userName": "user",
  "password": "user1",
  "server": "test001",

  "options": {
    "database": "employees"
  }
};

var poolConfig = { max: 10, min: 2, idelTimeOutMills:30000 };

var pool = new ConnectionPool(poolConfig, connectionConfig);

pool.requestConnection(function (err, connection) {

  if(!err) {
    var request = new Request('select 42', function(err, rowCount) {
      // Release the connection back to the pool.
      connection.close();
    });

    request.on('row', function(columns) {
      console.log(columns[0].value, 42);
    });

    connection.on('connect', function(err) {
      connection.execSql(request);
    });
 }
});

text and ntext datatypes

This driver works great except for the text data type. We need that datatype to get our project working. Any word on when that datatype will be added? Thanks for the good work.

How to select @@Identity

in a single request i want to be able to do a sql query like this

"insert into table (a,b,c) values (1,2,3); select @@identity as insertID;"

this is basically executed as two statements aka the done event is fired twice and i a suppose row event is also being fired for each statement how would i go about getting the identity without firing events twice

Possible to create standalone Javascript library for TDS?

More of a question than an issue....

Would it be possible to create a standalone Javascript-based TDS library that one could include on the client side (say in a Chrome App) versus relying on node.js?

Instance Names not supported

I can connect to the server "myServer", but a connection to a named server instance (e.g. "myServer\SQLExpress") results in a "connection failed" error.

State still in SentClientRequest when 'done' event was fired

When I try to benchmark a simple query as below:

var i = 0;
var start = new Date;
var query = function () {
  request = new Request("select top 100 cityid from dbo.cities", function(err) {
    console.log('err', err);
  });

  request.on('row', function(columns) {
    columns.forEach(function(column) {         
    });
  });

  request.on('done', function(rowCount, more) {
    if(i++ < 200){
      console.log('i:', i);
      query();
    }
    else {
      console.log(new Date - start);
    }
  });
  connection.execSql(request);
};

connection.on('connect', function(err) {
    // If no error, then good to go...
    console.log('connection established');
    query();
});

below exception was thrown:
Invalid state; requests can only be made in the LoggedIn state, not the SentClientRequest state

Consider switching parameterized queries with sp_prepexec family

TDS should switch to using parameterized queries with setting it as a value in the stream as opposed to being a wrapper around sp_executesql
http://msdn.microsoft.com/en-us/library/ff848812.aspx

The documentation on this store-procedure family is abysmal however this seems to be the "right" way to do it. Here is one of the better articles I found on this sproc
http://www.sqlservercentral.com/articles/sp_prepexec/76511/

It appears that this is the "native" way and will be handled in the stream and could allow for tedious to cache handles for sessions, for improved cache resolution.

varchar limited to 4kB, packetSize option getting ignored

I am trying to query a database that contains ~40kB big VARCHAR fields.
When I query the table for this field, tedious only returns the first 4096 bytes, cutting everything after that off. Even if I increase the packetSize option, the returned text size is still limited to 4096 bytes.

This happens on node.js 0.6.12 and 0.7.5

I suspect that there is some hard coded limit at some place in the library but I'm not that good at CoffeeScript so I can't find the bug myself.

To recreate this:
Use the below SQL file to create a table and fill it with 19kB sample data
http://exar.ch/files/table.sql

Then run this code:
http://exar.ch/files/debug.js

Support for #temp tables across requests with same connection

I can only use temporary tables inside the same request that creates the table. This works:

con.execSql new (tedious.Request) "CREATE TABLE #Tags (TagName varchar(50) NOT NULL)
   SELECT * FROM #Tags", (err, rowCount) =>

But this fails with Invalid object name '#Tags'.:

con.execSql new (tedious.Request) "CREATE TABLE #Tags (TagName varchar(50) NOT NULL)", (err, rowCount) =>
   con.execSql new (tedious.Request) "SELECT * FROM #Tags", (err, rowCount) =>

Full sample code:

tedious = require 'tedious'

console.logInGold = (message) -> console.log '\u001b[33m', message, '\u001b[0m'

connectOptions =
   userName: 'sa'
   password: 'sa'
   server:'localhost'

printCountText = "
   DECLARE @ct int
   SELECT @ct = count(*) FROM #Tags
   PRINT 'Count of #Tags: ' + Convert(VarChar(10), @ct)
   "
createTempTableText = "
   CREATE TABLE #Tags (TagName varchar(50) NOT NULL)
   PRINT '#Tags table created'
   " + printCountText
insertRowText = "
   INSERT #Tags VALUES ('tag1')
   " + printCountText

con = new(tedious.Connection) connectOptions
con.on 'connect', (err) => 
   con.on 'infoMessage', (msg) -> console.log msg.message
   console.logInGold err if err?
   con.execSql new (tedious.Request) createTempTableText, (err, rowCount) =>
      console.logInGold err if err?
      con.execSql new (tedious.Request) insertRowText, (err, rowCount) => 
         console.logInGold err if err?
         con.close()

Unrecognised token 0 when logging in.

(I looked through previous issues and couldn't find one similar so thought I'd open this)

Hey,

I'm having trouble using tedious to actually connect to the database, with the connection throwing an error:

Error: Unrecognised token 0 at offset 150 at Parser.nextToken

The config I'm using is nothing unusual:

{userName: 'sa', password: '*******', server: '192.168.1.194'} // tried with and without setting the database name

Other modules (node-tds) have logged into the database fine.

Any other information you need just let me know.

Please note that I don't have full access to the server itself.

@param value of 0 is treated as null

When I add an Int parameter with a value of 0, the parameter reaches SQL Server with a value of NULL.

tedious = require 'tedious'
console.logInGold = (message) -> console.log '\u001b[33m', message, '\u001b[0m'

connectOptions =
   userName: 'sa'
   password: 'sa'
   server:'localhost'

createTempTableText = "
   CREATE TABLE #Nums (Num int NOT NULL)
   PRINT '#Nums table created'
   INSERT #Nums VALUES (@Num)
   DECLARE @ct int
   SELECT @ct = count(*) FROM #Nums
   PRINT 'Count of #Nums: ' + Convert(VarChar(10), @ct)
   "

con = new(tedious.Connection) connectOptions
con.on 'connect', (err) => 
   con.on 'infoMessage', (msg) -> console.log msg.message
   console.logInGold err if err?
   req = new (tedious.Request) createTempTableText, (err, rowCount) =>
      console.logInGold err if err?
   req.addParameter 'Num', tedious.TYPES.Int, 0
   con.execSql req, =>
         con.close()

Connection instance not utilizing requestTimeout?

Prior to execution, I have set both the options.connectTimeout and options.requestTimeout fields with the following call to the constructor:

  var conn = new tedious.Connection({
      userName: opts.auth.user
    , password: opts.auth.password
    , server: opts.host
    , database: opts.database
    , options: { 
          connectTimeout: opts.connectTimeout 
        , requestTimeout: opts.requestTimeout
      }
  });

I inspected on the conn object and both values are properly set:

config: 
   { userName: 'admin',
     password: 'password',
     server: '10.0.0.1',
     database: 'master',
     options: 
      { connectTimeout: 10000,
        requestTimeout: 10000,
        textsize: '2147483647',
        cancelTimeout: 5000,
        packetSize: 4096,
        port: 1433 } },

Testing the connectionTimeout was successful (attempting to connect to a non-existent server did return a debug message, 'timeout: failed to connect...' after the expected duration), however requests will not timeout. I am hooking the errorMessage and debug events, but they are not invoked (or if they are, they do not have any relevant info). Those hooks, plus the connect hook are as follows:

  conn.on('debug', function(msg) {
    console.log('DEBUG > ' + msg);
    // This is the only way to grab timeout errors??
    if (msg.indexOf('timeout : failed') > -1) {
      self.connectionError = true;
      fn(msg, null);
    }
  });

  conn.on('errorMessage', function(err) {
    self.connectionError = true;
    fn(err, null);
  });

  conn.on('connect', function(err) {
    if (!err) {

      req = new tedious.Request('use ' + opts.database + '; ' + opts.query, function(err, rowCount) {
        if (err) {
          console.log('REQUEST ERROR SEND =========================');
          self.connectionError = true;
          fn(err, null);
          return;
        }
      });

      var rows = [];

      req.on('row', function(columns) {
        var row = {};

        columns.forEach(function(column) {
          row[column.metadata.colName] = column.value;
        });

        rows.push(row);
      });

      req.on('done', function(rowCount, more) {
        console.log('REQUEST DONE ==========================================');

      });

      req.on('doneProc', function(rowCount, more) {
        if (!self.connectionError) {
          console.log('REQUEST DONEPROC SEND===================================');
          fn(null, rows);
        }
      });

      conn.execSql(req);
    }
  });

I did a little digging into the source, and I'm not sure the requestTimeout setting is ever used when the request is made. However, I admit I didn't look very deep into it. I could very well be missing something. So, I was hoping to get some clarification on that option value.

P.S. - If you notice, I'm using the debug event to hook the connection timeout. This seems odd to me as well and was wondering if there is a specific timeout callback to receive connection timeout errors.

Thanks for the great library. This is going to help my project immensely.

multiple request on same connection in tedious

//Code starts here

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

var config = {
server: '192.168.1.XXX',
userName: 'username',
password: 'password,
options: {
database: 'database'
}
};

module.exports.execute = function (sp, aParam, callback) {
var paramList = [];
paramList = aParam;

console.log('\r\n trying to execute : ' + sp + '\r\n');
if (typeof (connection) === 'undefined') {
    console.log('\r\n Creating New Connection \r\n');
    connection = new Connection(config);

    connection.on('connect', connected);
    connection.on('databaseChange', databaseChange);
    connection.on('errorMessage', ErrorMessage);
    connection.on('end', end);
}
else {
    console.log('\r\n executing sp using old connection');
    exec(sp);
}

var rowCollection = [];
function connected(err) {
    if (err) {
        console.log('\r\n Error while Connecting..., sp : ' + sp + ", Error : " + err + '\r\n');
        connection.close();
        delete connection;
    }
    else {
        console.log('\r\n Connected.. executing sp using new connection : ' + sp + '\r\n');
        exec(sp);
    }
}

function exec(sql) {
    request = new Request(sql, statementComplete, function (err) {
        console.log('\r\n Error while executing request : ' + err + '\r\n');
        connection.close();
    });

    request.on('returnValue', function (parameterName, value, metadata) { });

    paramList.forEach(function (param) {
        if (param['paramType'] === 'Numeric') {param['paramType'] = TYPES.Numeric;}
        else if (param['paramType'] === 'BigInt') {param['paramType'] = TYPES.Int;}
        else if (param['paramType'] === 'Decimal') {param['paramType'] = TYPES.Decimal;}
        else { param['paramType'] = TYPES.VarChar;}

        request.addParameter(param['paramName'], param['paramType'], param['paramValue']);
    });

    paramList = [];
    //        request.on('columnMetadata', columnMetadata);
    request.on('row', row);

// request.on('done', requestDone);
request.on('doneProc', requestDoneProc);
// request.on('doneInProc', requestDoneInProc);
// connection.prepare(request);
connection.callProcedure(request);
}

function row(columns) {
    var row = {};

    columns.forEach(function (column) {
        if (column.isNull) {
        } else {
            row[column.metadata.colName] = column.value;
        }
    });

    rowCollection.push(row);
}

function statementComplete(err) {
    console.log('\r\n Statement Complete ' + '\r\n');
    if (err) {
        console.log('\r\n Statement failed: ' + err + '\r\n');
    }
    else { callback(rowCollection); }
}

function end() {
    connection.close();
    delete connection;
    console.log('\r\n Connection Ended ' + '\r\n');
}

function requestDoneProc(rowCount, more, returnStatus) { console.log("\r\n Request Done Proc : " + returnStatus + '\r\n'); }

function requestDoneInProc(rowCount, more) { console.log("\r\n Request Done InProc : " + rowCount + '\r\n'); }

function requestDone(rowCount, more) { console.log("\r\n Request Done : " + rowCount + '\r\n'); }

function columnMetadata(columnsMetadata) { console.log('\r\n column Metadata : ' + columnsMetadata + '\r\n'); }

function infoError(info) {
    console.log(info.number + ' : ' + info.message + ' : ' + info.procName + ' : ' + info.state + ' : ' + info.class + ' : ' + info.lineNumber);
}

function ErrorMessage(error) {
    console.log('\r\n ErrorMessage : ' + error.number + ' : ' + error.message + ' : ' + error.procName + ' : ' + error.state + ' : ' + error.class + ' : ' + error.lineNumber);
    return;
}

function databaseChange(databaseName) {
    console.log('\r\n databaseChange : ' + databaseName + '\r\n');
}

};

//Code ends here

here is our one SQLConnect.js file. that we are using to get resultset from SQL database.
when we call multipul request, it give us errro : "Invalid state; requests can only be made in the LoggedIn state, not the SentPrelogin state"

how can we manage this type of situation.
please help us.

Connection from Windows

Not sure where to ask you this, so here it goes.

I'm using Windows trying to use node to connect to SQL Server 2008.
I can specify the IP of the server, but i don't know where to set the instance, for example in VisualStudio goes like:

".\SQLEXPRESS\databasename.dbo"

...where SQLEXPRESS is the instance name. I see there's a "database" property in the options array, but not the instance name.

You reference in your tests the file test-connection.json. ¿Could you provide an example of that file? Just to see the properties or something to specify that... I hope I explained my issue clearly.

Thank you!

batch processing

Hi,
I am trying to use your driver, was wondering is there a possibility to execute requests in a batch, what i mean is, a bunch of updates or insert statements together in one single request ?

Thanks.

VarChar and NVarChar do not support max variants when used as parameters

http://pekim.github.com/tedious/api-datatypes.html
States that nvarchar(max) and varchar(max) are also supported but no mention as to how to use them as parameters.
Trying it out by generating a large string as a parameter leads to the following error:

"The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@p"): Data type 0xA7 has an invalid data length or metadata length."

This was the result of setting @p defined as a TYPES.VarChar to a string that was 8001 characters long.

execSqlWithOrder test fails on some servers

The integration test execSqlWithOrder (in connection-test.coffer) can fail on some servers. It executes select top 2 * from sys.columns order by 4, 2, and expects 25 columns in the result set. SQL Server 2005 and SQL Server 2008 have a different number of columns in sys.columns, so the number of columns in the result set will vary.

The test should explicitly request one or two columns only, probably the same columns that it is ordering by.

column name [201006]

For example I have a simple query

SELECT [201006] FROM test

column name (201006) causes error. here is the stack trace

/[myapppath]/node_modules/tedious/lib/token/token-stream-parser.js:103
        throw error;
              ^
TypeError: Cannot read property 'type' of undefined
    at parse (/[myapppath]/node_modules/tedious/lib/value-parser.js:29:18)
    at Object.parser [as 209] (/[myapppath]/node_modules/tedious/lib/token/row-token-parser.js:13:13)
    at Parser.nextToken (/[myapppath]/node_modules/tedious/lib/token/token-stream-parser.js:81:35)
    at Parser.addBuffer (/[myapppath]/node_modules/tedious/lib/token/token-stream-parser.js:65:17)
    at Connection.sendDataToTokenStreamParser (/[myapppath]/node_modules/tedious/lib/connection.js:527:35)
    at Connection.STATE.SENT_CLIENT_REQUEST.events.data (/[myapppath]/node_modules/tedious/lib/connection.js:189:23)
    at Connection.dispatchEvent (/[myapppath]/node_modules/tedious/lib/connection.js:433:59)
    at MessageIO.Connection.connectOnPort (/[myapppath]/node_modules/tedious/lib/connection.js:387:20)
    at MessageIO.EventEmitter.emit (events.js:96:17)
    at MessageIO.eventData (/[myapppath]/node_modules/tedious/lib/message-io.js:57:12)

Int type parameters with the value 0 are evaluated as null.

Int types as parameters are treated as null when you sent the value 0. Using the following syntax
request=new Request("insert into SomeTable(p) values (@p)",function(err) {
....
});
request.AddParameters("p",TYPES.Int,0)

When I try to insert the value into a table it is treated as null.

Transaction Management

Hey,

As per the readme v0.0.8 will support transaction. When will v0.0.8 be released?

Returned data types...

With node-uuid, would it be possible to have the UNIQUEIDENTIFIER return the string representation?

Also, what about having binary strings return a Buffer?

---

On a side note... really like tedious, way more polished than node-sqlserver is at this point... what a headache.

Tables with xml columns: Unrecognised data type 0xF1 at offset 0x02BC

When I try to do a select on a table with one of the columns defined as XML data type, I get the following error. Removing xml from the selected columns resolves the issue.

/home/dataservices/node_modules/tedious/lib/token/token-stream-parser.js:103
        throw error;
              ^
Error: Unrecognised data type 0xF1 at offset 0x02BC
    at parse (/home/dataservices/node_modules/tedious/lib/metadata-parser.js:21:11)
    at Object.parser [as 129] (/home/dataservices/node_modules/tedious/lib/token/colmetadata-token-parser.js:11:16)
    at Parser.nextToken (/home/dataservices/node_modules/tedious/lib/token/token-stream-parser.js:81:35)
    at Parser.addBuffer (/home/dataservices/node_modules/tedious/lib/token/token-stream-parser.js:65:17)
    at Connection.sendDataToTokenStreamParser (/home/dataservices/node_modules/tedious/lib/connection.js:527:35)
    at Connection.STATE.SENT_CLIENT_REQUEST.events.data (/home/dataservices/node_modules/tedious/lib/connection.js:189:23)
    at Connection.dispatchEvent (/home/dataservices/node_modules/tedious/lib/connection.js:433:59)
    at MessageIO.Connection.connectOnPort (/home/dataservices/node_modules/tedious/lib/connection.js:387:20)
    at MessageIO.EventEmitter.emit (events.js:96:17)
    at MessageIO.eventData (/home/dataservices/node_modules/tedious/lib/message-io.js:57:12)

Parameters of varchar & nvarchar treat empty string as null

If you add a parameter with '' as a varchar or nvarchar it is treated as a null.
Viewing the src code it seems that the error is the same as with the int being treated as zero.
In the file data-type.coffee line 236 & 294 parameter.value should be changed to parameter.value?

Installing from npm seems to not be including the new changes in v0.1

the new version seems to not have the changes when installing from "npm install tedious"

consider an enhancement: default a connection to SET CONCAT_NULL_YIELDS_NULL ON

in the future this will be hard wired to ON in sql server and attempts to set it to OFF will fail. The native driver apparently is setting it to ON. We should emulate that and also set it to ON, or allow a config setting to have tedious set this to on.

implementation would be to send the SET command upon the open of the connection.

let me know if you agree or disagree. I am going to guess: "disagree" because you are keeping tedious light, which is great too. I wanted to give you the benefit of the idea in case it fit into your strategy for this driver.

not working with sql server native client 11 (in azure)

I tried my code on sql server r2 and it works but it does not work on sql azure which is using sql server native client 11. Any ideas?
I also tried adding some options, but in vain.

options : {
database: 'xxxxxxxx',
driver: 'SQL Server Native Client 11.0'
}

Only publish *.js files, not *.coffee files to the npm registry.

There's no reason for users of tedious to have to install coffeescript.

• Change the dependency on coffeescript to a devDependency.
• Add *.js to .gitignore.
• Add a .npmignore file to stop *.coffee files making it in to the deployed package.
• Add prepublish script to compile the coffee files to js.

try catch...

in my store procedure in ms sql 2008, i am using try...catch... finally block.
but it gives me error.
eg.

ALTER PROCEDURE [dbo].[usp_UserFriends](@mode VARCHAR%2850%29='',
.... Some Parameter%28s%29)
AS
BEGIN
SET NOCOUNT ON

--BEGIN TRY
--  BEGIN TRANSACTION 

        DECLARE @SQL VARCHAR(8000) = ''

    IF @Mode='Insert'   
    BEGIN
                       -- My code goes here....
            END

--  IF @@TRANCOUNT > 0
--      COMMIT TRANSACTION 
--END TRY
--BEGIN CATCH
--  IF @@TRANCOUNT > 0
--      ROLLBACK TRANSACTION 

--  DECLARE @ErrorMessage NVARCHAR(4000) = 'usp_UserProfileMst Error : ' + ERROR_MESSAGE();
--  RAISERROR (@ErrorMessage, 16, 1);
--END CATCH

SET NOCOUNT OFF

END

here i am comment out my try...catch block..
if i have to made any correction, please suggest me.

thank you very much

Database Name in congifuration

Hi,

Awesome implementation... But Isnt database name a part of the connection string?? It would be great if database name can be passed in the configuration

Error with BigInt parameter on callProcedure()

I have a stored procedure in SQL Server 2008 that takes a BigInt as it's only parameter - I ran into this error while trying to call that proc:

TypeError: Object #<Object> has no method 'writeParameterData'
at new RpcRequestPayload (C:\Projects\WT\source\server\api\node_modules\tedious\lib\rpcrequest-payload.js:52:22)
at Connection.callProcedure (C:\Projects\WT\source\server\api\node_modules\tedious\lib\connection.js:575:56)
at Connection.Repository.execute (C:\Projects\WT\source\server\api\lib\data\repository.js:71:16)
at Connection.EventEmitter.emit (events.js:85:17)
at Connection.processedInitialSql (C:\Projects\WT\source\server\api\node_modules\tedious\lib\connection.js:538:17)
at Connection.STATE.LOGGED_IN_SENDING_INITIAL_SQL.events.message (C:\Projects\WT\source\server\api\node_modules\tedious\lib\connection.js:170:23)
at Connection.dispatchEvent (C:\Projects\WT\source\server\api\node_modules\tedious\lib\connection.js:433:59)
at MessageIO.Connection.connectOnPort (C:\Projects\WT\source\server\api\node_modules\tedious\lib\connection.js:390:20)
at MessageIO.EventEmitter.emit (events.js:85:17)
at MessageIO.eventData (C:\Projects\WT\source\server\api\node_modules\tedious\lib\message-io.js:59:21)

Not sure that it matters that much, since I can pass the param as Int.

Support for MONEYNTYPE

I've found that the support for MONEYNTYPE variable length data type is not currently implemented. Would be great to see it soon.

Calling a stored Procedure with out parameter

I am calling a insert stored proc which has a output parameter from node using tedious connection pool and having very less success.

Can someone please shed some light on how this is done?

my Code:

server.js

pool.requestConnection(function (err, connection) {
if(!err) {
var storedProcName = '[test].[dbo].[spCustomer_Insert]';
var request = new Request(storedProcName, function(err, rowCount){
if(err){
console.log('Error on customer Insert ' + err); }
else
{
console.log(rowCount + ' rows');
}
connection.close();
});

            request.addParameter('CustomerCode', TYPES.VarChar, customer.customerCode || NULL);
            request.addParameter('CustomerName', TYPES.VarChar, customer.customerName || NULL);
            request.addOutputParameter('PartyId', TYPES.Int);

            request.on('returnValue', function(parameterName, value, metadata) {
                console.log('Value returned ' + parameterName + ' = ' + value);                                                        
            });

            connection.on('connect', function(err) {                    
              connection.callProcedure(request);
            });
        }
    });

How do i call so partyId is a output parameter!

I need an answer ASAP please....

Thanks in advance

Regards,
Kimi

tedious query not retrieving correct data from db

Using the provided code I test my sql query. The results are different than running the query in SQL Server Management Studio.

The data from the tedious query does not find any data after july 6, 2012, which is inconsistent with the actual database that I am getting the data from.

function 
   executeStatement() {
      request = new Request("select top 5 data, date from realTime where date > '2012-11-10'", 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);
   }

I am at a loss to why the query is not executing correctly.

Dealing with multiple result sets...

I have several existing stored procedures that will return multiple results sets. I would like to be able to utilize them, I'm uncertain as to if this is even supported, or how one would determine which result set a record is for... This would certainly be beneficial as I'm hoping to replace a few very simple, but compiled .Net projects with scripts that perform the same functionality.

A couple options spring to mind...

//1. simplest passing the result set number to the row handler method.
request.on('row',function(columns,RESULT_SET_NUMBER){ ... });

//2. this may be much more difficult, but honestly makes the most sense.
//specifying with the event binder, RESULT_SET_NUMBER - 0-based
request.on('row',RESULT_SET_NUMBER,function(columns){ ... });

//3. have a resultset event
request.on('resultset',function(set,RESULT_SET_NUMBER){
    //setup listener against the set.
    set.on('row',function(columns){
    });
});

Invalid state; requests can only be made in the LoggedIn state, not the SentClientRequest state

This issue arises when calling one request then refreshing the page. I have the code that I have wrote for this if anyone is interested in duplicating it.

http://c9754731.r31.cf2.rackcdn.com/WebServicesServlet_Exam.zip

All you have to do to set this up (I built it and use it on windows so I dont know how it will work on linux lol). You just need to setup the ConnectionConfig.js file in the lib folder with your connection info and then modify the gateway test method.

Any help with this would be greatly appreciated!!!

No row returned from Ordered Query

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

var config = {
    userName: 'sa',
    password: 'sapass',
    server: '192.168.32.151',
    options: {
        database: 'db'
    }
};

var connection = new Connection(config);

connection.on('connect', function(err) {
    // If no error, then good to go...
    console.log('connection established');

    request = new Request("select cityid from dbo.cities order by cityid;", function(err) {
        console.log(err);
    });

    request.on('row', function(columns) {
        columns.forEach(function(column) {
            console.log(column.value);
        });
    });

    request.on('done', function(rowCount, more) {
        console.log(rowCount + ' rows returned');
    });

    connection.execSql(request);

});

returns no row, while removing the order by clause turns okay.

Proper way of creating rows from column object in "on" event

Probably a stupid question, but is there a better way than this: https://gist.github.com/2760475 to retrieve rows from the "row" event, given that it returns columns instead.

Thanks for this module, btw. It's perfect for the project I'm working on right now.