tediousjs / node-mssql Goto Github PK
View Code? Open in Web Editor NEWMicrosoft SQL Server client for Node.js
Home Page: https://tediousjs.github.io/node-mssql
License: MIT License
Microsoft SQL Server client for Node.js
Home Page: https://tediousjs.github.io/node-mssql
License: MIT License
Anybody have issues with retrieving the last inserted ID from db after an insert statement?
Assume a table student contains 3 fields (id, age, name).
Here's my code:
var query = "INSERT INTO student(age, tagname) values (6, 'Bob') ";
db = new sql.Request();
db.query(query, function(err, data) {
if (!err)
{
var str = "SELECT @@IDENTITY AS 'identity'";
db = new sql.Request();
db.query(str, function(suberr, subdata)
{
console.log(" this is LAST inserted id : ");
console.log(subdata); // -> RETURNED NOTHING
});
}
The above code returned nothing as the ID eventhough the student record was captured correctly into the db.
OUTPUT
this is last LAST inserted id :
[ { identity: null } ]
I am wondering if there's something wrong with my code, or if this is a bug.
Any feedbacks are appreciated.
PS. If I do another query in between the INSERT and SELECT @@IDENTITY, be it a select query, or insert query, then it would give me the ID that I am looking for.
Hello,
I am trying to save 3 documents (head and body in transaction) in sql server 2008 r2. Each document has 4 or 5 rows as body, so in total I need just to insert about 20 rows in database.
The time that it is needed to make this insertion is about 1 minute. I tried to insert just one document with 5 rows in body, and it was needed about 45 seconds. While reading this 3 documents it was needed just 0.8 seconds. My question is: Is this a normal performance or am I doing something wrong?
Best Regards
Hello,
I'm pretty much having this issue
tediousjs/tedious#79
can you please provide a way to use tedious' execSqlBatch?
I added a these lines around line 205 in tedious.coffee.
if @batch
connection.execSqlBatch req
else connection.execSqlBatch req
But i am not sure if these would have any implications, or cause any problems.
Hello,
I was wondering the most elegant way to handle errors using prepared statements or transactions.. In my code, there are always 3 nested functions (preparedstatement .prepare, .execute, and .unprepare). The code gets really convoluted quickly when handling the possibility of an error at each of these steps. Also, I am using prepared statements for each of my queries so I can bind input variables.
ps.prepare('select @param as value', function(err) {
// ... error checks
ps.execute({param: 12345}, function(err, recordset) {
// ... error checks
ps.unprepare(function(err) {
// ... error checks
});
});
});
Is there a clean, recommended method of handling errors for a prepared statement query if I want to handle most of these errors the same way?
How to setCharset?
Hi,
I have problem with local temporary tables - it seems that they are not visible in concurent requests.
The error I got is: [RequestError: Invalid object name '#tbltemp'.]
create procedure p_temp as
begin
insert into #tbltemp (dt) values (GetDate());
end;
var sqlConfig: {
user: 'xxx',
password: 'xxx',
server: 'xxx',
database: 'xxx'
}
/* POST new events . */
router.post('/loctmp', function(req, res) {
try {
var connection = sql.connect(config.sqlConfig, function(err) {
var transaction = new sql.Transaction(connection);
transaction.begin(function(err) {
var request = new sql.Request(transaction);
//var request = new sql.Request(transaction);
request.query("create table #tbltemp (dt datetime)", function(err, recordsets, returnValue) {
console.dir(err);
//var request = new sql.Request(transaction);
request.execute('p_temp', function(err, recordsets, returnValue) {
console.dir(err);
//var request = new sql.Request(transaction);
request.query('select * from #tbltemp', function(err, recordset)
{
console.dir(err);
console.log(recordset);
});
}); //p_temp
}); //create table
}); //transaction
}); //connection
} catch (e) {
console.error("Error:", e);
}
});
I'm not too familiar with Coffeescript, but I think there might be a scoping issue with connections and requests.
I wrote a little test case to show what's happening. Below 2 connections are created, but a request is only run on 1 of them. The callback however is called twice however, with the request being run against both connections.
Thanks for the work you've put into this library by the way - I prefer this api much more than the usual tedious api
var server1Config = {
user: "s1user",
password: "s1password",
server: "s1server",
database: "s1database"
}
var server2Config = {
user: "s2user",
password: "s2password",
server: "s2server",
database: "s2database"
}
var server1 = require('mssql');
server1.connect(server1Config, function(err) {
// At this point server1 is connected.
// We'll go ahead and also connect up to another server at the same time
var server2 = require('mssql');
server2.connect(server2Config, function(err) {
// now server2 is connected
// Here we'll create a request with server1,
// but it will actually be executed twice using both server1 and server2
var request = new server1.Request();
var sql = "SELECT @@Servername AS ServerName";
request.query(sql, function(err, recordset) {
console.log(recordset);
});
});
});
Is the connection reusable like with the oracle package? Is so would an example be possible?
Oracle.connect = function(callback) {
oracle_driver.connect(connectData, function(err, connection) {
if(err) {
console.log(err);
} else {
callback(err, connection);
}
connection.close();
});
}
Oracle.query = function(query, callback) {
Oracle.connect(function(err, connection) {
connection.execute(query, [], function(err, results) {
if (err) {
console.log(err);
callback(err, results);
} else {
callback(err, results);
}
});
});
}
Here's a simple patch to add that support:
src/tedious.coffee
===================================================================
--- src/tedious.coffee (revision )
+++ src/tedious.coffee (revision )
@@ -172,6 +172,16 @@
recordset.push row
if @verbose then console.log "---------- response -----------"
+
+ for name, param of @parameters when param.io is 1
+ if @verbose
+ if param.value is tds.TYPES.Null
+ console.log " input: @#{param.name}, null"
+ else
+ console.log " input: @#{param.name}, #{param.type.name.toLowerCase()}, #{param.value}"
+
+ req.addParameter param.name, getTediousType(param.type), if param.value? then param.value else tds.TYPES.Null
+
connection.execSql req
else
Hello
Is there any built in support for prepared statements? Do you have any suggestions as to how to implement them?
Thank you for your amazing work!
[root@ISAAPI01 ser-web-api]# node api.js
Kill PID:6644
buffer.js:784
throw TypeError('value is out of bounds');
^
TypeError: value is out of bounds
at TypeError (<anonymous>)
at checkInt (buffer.js:784:11)
at Buffer.writeUInt32LE (buffer.js:841:5)
at WritableTrackingBuffer.writeUInt32LE (/sites/ser-web-api/node_modules/mssql/node_modules/tedious/lib/tracking-buffer/writable-tracking-buffer.js:92:17)
at WritableTrackingBuffer.writeUInt64LE (/sites/ser-web-api/node_modules/mssql/node_modules/tedious/lib/tracking-buffer/writable-tracking-buffer.js:119:17)
at Object.TYPE.writeParameterData (/sites/ser-web-api/node_modules/mssql/node_modules/tedious/lib/data-type.js:268:25)
at new RpcRequestPayload (/sites/ser-web-api/node_modules/mssql/node_modules/tedious/lib/rpcrequest-payload.js:66:22)
at Connection.callProcedure (/sites/ser-web-api/node_modules/mssql/node_modules/tedious/lib/connection.js:731:56)
at /sites/ser-web-api/node_modules/mssql/lib/tedious.js:723:33
at dispense (/sites/ser-web-api/node_modules/mssql/node_modules/generic-pool/lib/generic-pool.js:247:16)
at Object.me.acquire (/sites/ser-web-api/node_modules/mssql/node_modules/generic-pool/lib/generic-pool.js:316:5)
at Request._acquire (/sites/ser-web-api/node_modules/mssql/lib/main.js:884:37)
at Request.TediousRequest.execute (/sites/ser-web-api/node_modules/mssql/lib/tedious.js:586:21)
at Request.execute (/sites/ser-web-api/node_modules/mssql/lib/main.js:1088:56)
at /sites/ser-web-api/controllers/lubricantes.js:28:21
at Array.forEach (native)
at /sites/ser-web-api/controllers/lubricantes.js:12:14
at /sites/ser-web-api/helpers/mssql.js:40:17
at /sites/ser-web-api/node_modules/mssql/lib/main.js:236:51
at /sites/ser-web-api/node_modules/mssql/lib/tedious.js:330:20
at /sites/ser-web-api/node_modules/mssql/node_modules/generic-pool/lib/generic-pool.js:278:11
at Connection.<anonymous> (/sites/ser-web-api/node_modules/mssql/lib/tedious.js:308:24)
at Connection.g (events.js:180:16)
at Connection.EventEmitter.emit (events.js:92:17)
at Connection.processedInitialSql (/sites/ser-web-api/node_modules/mssql/node_modules/tedious/lib/connection.js:690:17)
at Connection.STATE.LOGGED_IN_SENDING_INITIAL_SQL.events.message (/sites/ser-web-api/node_modules/mssql/node_modules/tedious/lib/connection.js:173:23)
at Connection.dispatchEvent (/sites/ser-web-api/node_modules/mssql/node_modules/tedious/lib/connection.js:573:59)
at MessageIO.<anonymous> (/sites/ser-web-api/node_modules/mssql/node_modules/tedious/lib/connection.js:528:22)
at MessageIO.EventEmitter.emit (events.js:92:17)
at MessageIO.eventData (/sites/ser-web-api/node_modules/mssql/node_modules/tedious/lib/message-io.js:58:21)
at Socket.<anonymous> (/sites/ser-web-api/node_modules/mssql/node_modules/tedious/lib/message-io.js:3:59)
at Socket.EventEmitter.emit (events.js:95:17)
at Socket.<anonymous> (_stream_readable.js:746:14)
at Socket.EventEmitter.emit (events.js:92:17)
at emitReadable_ (_stream_readable.js:408:10)
at emitReadable (_stream_readable.js:404:5)
at readableAddChunk (_stream_readable.js:165:9)
at Socket.Readable.push (_stream_readable.js:127:10)
at TCP.onread (net.js:528:21)
[root@ISAAPI01 ser-web-api]#
I am logging into a remote server for our SQL DB. Currently the DB uses Windows Authentication for the login. Is this supported. Looking to see if this is supported before submitting a request to change security items in the DB.
Thanks in advance.
I have config like below, but get connection error. When i use port instead of instance-name everything works.
var config = {
user: 'sa',
password: '...',
server: '192.168.1.2\\sql2005',
database: 'db1'
};
Using options (tedious format) works good
var config = {
user: 'sa',
password: '...',
server: '192.168.1.2',
database: 'db1',
options: {
instanceName: 'sql2005'
}
};
I am getting this error while getting records from mssql. If there are 1 - 2 records it is working fine but in case of more records it is throwing this error: RangeError: Trying to access beyond buffer length. The function where I am getting error is ReadableTrackingBuffer.prototype.assertEnoughLeftFor. and the function is in file the function in the files : \node_modules\mssql\node_modules\tedious\lib\tracking-buffer\readable-tracking-buffer.js .
The way I am connecting and fetching data is:
var sqlQuery = "SELECT * from [BackOffice.Applications] ";
var request = new sql.Request(connection);
request.query(sqlQuery, function (err, recordset) {
//Some logic here
});
Please help.
I'm probably missing something simple but I'm getting a request error (statement not prepared) when I try to prepare a statement that uses sql.MAX with a sql.NVarChar. My SQL table has a field named SValue that is an nvarchar(MAX). I get the error when I execute something like the following:
...
var ps = new sql.PreparedStatement(connection);
ps.input('TID', sql.Int);
ps.input('SValue',sql.NVarChar(sql.MAX));
var statement = 'insert into TTable (TID,SValue) values (@tid,@svalue)'
ps .prepare(statement, function(err, recordsets) {
...
If I replace sql.MAX with a number, it works fine. What am I missing?
I am on a machine with node-mssql installed but also MSSMS. I can connect to a SQL Server (2000) using MSSMS (Microsoft SQL Server Management Studio), but when I use node-mssql to connect to the same SQL Server I don't get an error, but it doesn't connect either. This is my code:
var sql = require('mssql');
sql.connect({server: "ip", user: "user", password: "password"}, function(err) {
console.log('sql.connect error', err);
if (err) return callback(err);
var request = new sql.Request();
request.query(sql, callback);
});
The line console.log is never called and instead the node application throws an uncaught exception and is terminated:
TypeError: Uncaught, unspecified "error" event.
at TypeError (<anonymous>)
at Connection.EventEmitter.emit (events.js:74:15)
at Parser.<anonymous> (folder\node_modules\mssql\node_modules\tedious\lib\connection.js:490:15)
at Parser.EventEmitter.emit (events.js:95:17)
at Parser.nextToken (folder\node_modules\mssql\node_modules\tedious\lib\token\token-stream-parser.js:100:14)
at Parser.addBuffer (folder\node_modules\mssql\node_modules\tedious\lib\token\token-stream-parser.js:66:17)
at Connection.sendDataToTokenStreamParser (folder\node_modules\mssql\node_modules\tedious\lib\connection.js:675:35)
at Connection.STATE.SENT_LOGIN7_WITH_STANDARD_LOGIN.events.data (folder\node_modules\mssql\node_modules\tedious\lib\connection.js:146:23)
at Connection.dispatchEvent (folder\node_modules\mssql\node_modules\tedious\lib\connection.js:573:59)
at MessageIO.<anonymous> (folder\node_modules\mssql\node_modules\tedious\lib\connection.js:523:22)
When I connect with node-mssql using an ip address of another machine without mssql I get a nice ETIMEOUT, "Failed to connect to 10.101.0.11:1433 in 15000ms"
PS: I changed a lot of content in this issue since I originally wrote it 20 minutes ago!
After a hard time, I successfully connecting to azure database with this configuration
var config = {
"user": "MyService@server",
"password": "password",
"options":{
"database": "database",
"encrypt": true,
},
};
but when i tried to request a query like (select * from people), it shows an error
RequestError: Invalid object name 'people'.although i have a table named people and i can use it with this statement locally.
please help.
From what I've seen, Tedious provides events to let you know when a connection has ended. I'm not 100% certain if this even is fired if the server has gone down or the connection is broken by some other source. If it does though, it would be nice to gain access to such events. From what I've seen, this library doesn't handle failed connections.
I tested the connected property of the connection object, but it seems to only stay true all the time once a connection has been successfully created. I tested it by pulling out the ethernet wire and it still claimed to be connected.
I've created 2 simultaneous sql.connect call to 2 different configuration on a Hapi server, where each call is mapped to a different path.
When testing each call on it's on it seems to work just fine.
but if I try to run both of them at the same time only 1 of them seem to return results, the other one returns an error message: 'Invalid object name...'
if I add a timeout function in the front end when making the call and if one of the calls wait for a bit it works just fine.
can you please advice?
Thanks in advance.
With tedious.
Currently, the 'close' event is not fired on a connection when said connection is closed by the connection pool manager (ex. on idle timeout)
From briefly poking around, may be a tedious issue; but thought I'd raise it here first
Hi,
Im a begginer in programming, i canยดt pass an input parameter to my sql statement, please can you help me. Also i need to insert the value of the input parameter from another external variable, is it possible?
var sql = require('mssql');
var qstring = {
user: 'sa',
password: '*',
server: '**',
database: '****'
}
var connection = new sql.Connection(qstring, function(err) {
var r = new sql.Request(connection);
r.input('cinco',sql.VarChar,'tres');
r.multiple = true;
r.query("INSERT INTO Test(userid,userod) values (cinco,'ridiculo')",function(err,recordsets) {
connection.close();
});
});
the transmission parameters stored in the procedure as described in your reference, (for example "request.input ('input_parameter', sql.Int, 10);" )
when writing code:
var sql = require('mssql');
var request = new sql.Request();
request.input('position', sql.Int, 99);
request.execute('[TestSql].[dbo].[Test]', function(err) {
if (err){
console.error(err);
return next();
}
res.redirect('/');
});
error:
TypeError: Cannot read property 'Int' of undefined
at ContentHandler.postAddUser (D:\Node JS\TestDbSql\routes\post\index.js:98:38)
at callbacks (D:\Node JS\TestDbSql\node_modules\express\lib\router\index.js:164:37)
at idSprav (D:\Node JS\TestDbSql\middleware\idSprav.js:11:5)
at callbacks (D:\Node JS\TestDbSql\node_modules\express\lib\router\index.js:164:37)
at param (D:\Node JS\TestDbSql\node_modules\express\lib\router\index.js:138:11)
at pass (D:\Node JS\TestDbSql\node_modules\express\lib\router\index.js:145:5)
at Router._dispatch (D:\Node JS\TestDbSql\node_modules\express\lib\router\index.js:173:5)
at Object.router (D:\Node JS\TestDbSql\node_modules\express\lib\router\index.js:33:10)
at next (D:\Node JS\TestDbSql\node_modules\express\node_modules\connect\lib\proto.js:193:15)
at Object.handle (D:\Node JS\TestDbSql\middleware\index.js:22:9)
POST /add 500 140ms - 892b
As a little background I'm writing a simple script to check if a connection to a DB can be established. When the DB connection is successful everything works great, however in the case where one or more of the connections fails the script will hang until idleTimeoutMillis + connectTimeout has passed and then errors with the following:
mssql-test-connection/node_modules/mssql/lib/tedious.js:204
return c.close();
^
TypeError: Cannot call method 'close' of null
at Object.cfg_pool.destroy (mssql-test-connection/node_modules/mssql/lib/tedious.js:204:22)
at Object.me.destroy (mssql-test-connection/node_modules/mssql/node_modules/generic-pool/lib/generic-pool.js:149:13)
at removeIdle (mssql-test-connection/node_modules/mssql/node_modules/generic-pool/lib/generic-pool.js:178:10)
at Timer.listOnTimeout [as ontimeout] (timers.js:110:15)
It appears that the code is waiting for the failed connection to go idle and then trying to close it at that point causing an error.
Here's the associated code to reproduce the error:
var sql = require('mssql');
//To Reproduce: this is a connection to a DB that does not exist or is inaccessible
connectionsToTest = [
{
name: '...',
user: '...',
password: '...',
server: '...',
database: '...'
}
];
connectionsToTest.forEach(function(config) {
//To prevent a 45sec wait for failure
config.pool = { idleTimeoutMillis : 500 };
config.options = { connectTimeout : 5000 };
var connection = new sql.Connection(config, function(err) {
var result = 'Attempting to Connect to '+config.name+' DB ';
if(err) {
result += '[ '+err+' ]\n';
process.stdout.write(result);
} else {
var request = new sql.Request(connection);
request.query('select 1 as number', function(err, recordset) {
if(recordset[0].number == 1) {
result += '[ Success ]\n';
} else {
result += '[ '+err+' ]\n';
}
process.stdout.write(result);
});
}
});
});
Currently the signature for the execute() callback is function (err, recordsets, returnValue) {}
. This causes a problem with every node library that relies on the standard node callback signature of function (err, result) {}
.
I'd like to propose that the standard signature be adopted, and an object literal be returned with the following signature:
{
recordsets: [...],
returnValue: ...
}
When run the following code, keep getting error
"Login failed; one or more errorMessage events should have been emitted"
config = {user: p.username, password: p.password, server: p.host, database: p.other};
console.log("connecting to mssql");
CBS[id].conn = new mssql.Connection(config, function(err) {
if (err) {
sendMsg(id, {error: err}); return;
}
sendMsg(id, {result:"connected"} );
});
packet sniffer shows it failed too. The question is, what mode of authentication the above code uses. Is it Windows Authentication?
Thanks in advance.
When I get more than 30 rows data from a mssql table, the function : ReadableTrackingBuffer.prototype.assertEnoughLeftFor will throw the error : "required : 4, available : 0" or "Error: required : 76, available : 42" . but if I get less than 20 rows data from the same table, it can return the right result .
the function in the files : \node_modules\mssql\node_modules\tedious\lib\tracking-buffer\readable-tracking-buffer.js
I want to insert 6 millions data into mssql using node-mssql. Should I use transaction or use single sql string?
I have a node service connect successfully to azure database from local host using tedious and retrieve data correctly ,but when i deploy the service on a cloud service like nodejitsu or any cloud service, the request takes long time and ends with "The server did not return a response for this request. Server returned 0 bytes".Where is the problem?
I have the following code:
var config = {
. . . . .
. };
var callback = printError;
var connection = new sql.Connection(config, function(err) {
if(err) return callback(err);
var request = new sql.Request(connection);
request.query('select p.PresentationId from PlaybackAudits p', function(err, recordset) {
if(err) return callback(err);
console.dir(recordset);
});
});
And I get the error:
{ name: 'ConnectionError',
message: 'Login failed; one or more errorMessage events should have been emitted' }
I tried these same credentials with SQL Server Management Studio and it succeeded but these credentials fail with node-mssql.
Kevin
I've found that an SP that returns -1 will result in a returnVal of 4294967295.
POC:
create proc sp_negone as begin
return -1
end
app.get("/poc",function(req,res){
var sqlcmd = new mssql.Request(dbc);
sqlcmd.execute("sp_negOne",function(err,rs,rv){
console.log(rv);
res.end(rv.toString());
})
});
4294967295 is logged to console and sent as response.
Thanks
Have you any plans to support streaming?
The library looks really promising as an alternative to tedious, but we have some procs which returns hundreds of thousands of records and waiting for the callback will hurt performance.
hello! How i can use MS SQL Server instance name?
localhost\i2008 dont work
Hey, guys
I'm trying to use node-mssql for testing performance of MSSQL Server using nodejs.
Currently, I just fork 256 child processes which in charge of sending query to MSSQL Server, and I need to create 256 active connections definitely, how can I make it work?
thanks,
Khalil
I'm trying to use Prepared Statements and am running into an issue where Decimal types will not prepare. I'm using mssql
version 0.5.5 with the bundled tedious
, connecting to SQL Server 2000.
The following is minimal code reproduction:
var sql = require('mssql');
var opts = {
user : "username",
password : "password",
server : "10.0.0.1",
database : "database",
options : {
// SQL Server 2000
tdsVersion : "7_1"
}
};
var conn = new sql.Connection(opts, function(err) {
var query = "select @param as value";
var data = {
param : 155.33,
};
var ps = new sql.PreparedStatement(conn);
ps.input('param', sql.Decimal);
ps.prepare(query, function(err) {
if (err) {
console.log(err);
return;
}
ps.execute(data, function(err, recordset) {
// ... snip error check ...
console.log(recordset);
ps.unprepare(function(err) {
// ...
});
});
});
});
The error message is as follows:
{ [RequestError: Statement(s) could not be prepared.]
name: 'RequestError',
message: 'Statement(s) could not be prepared.',
code: 'EREQUEST',
precedingErrors:
[ { [RequestError: Line 1: Incorrect syntax near '18'.]
name: 'RequestError',
message: 'Line 1: Incorrect syntax near \'18\'.',
code: 'EREQUEST' },
{ [RequestError: Must declare the variable '@param'.]
name: 'RequestError',
message: 'Must declare the variable \'@param\'.',
code: 'EREQUEST' } ] }
If I use sql.Int
, the query is prepared properly. sql.Numeric
errors as well, with the same error message as above. Any help would be appreciated.
When I do
var request = new sql.Request();
request.query('select a;select b;', function(err, recordset) {
console.log(err.originalError)
});
I would only get the last error
{
name: 'RequestError',
message: 'Msg 207, Level 16, State 1, Line 2. Invalid column name 'b'.',
code: 'EREQUEST'
}
Would you consider implementing something like this that shows the preceding messages too in a way that we don't break existing code?
{
name: 'RequestError',
message: 'Msg 207, Level 16, State 1, Line 2. Invalid column name 'b'.',
code: 'EREQUEST',
precedingErrors: [{
name: 'RequestError',
message: 'Msg 207, Level 16, State 1, Line 2. Invalid column name 'a'.',
code: 'EREQUEST'
}]
}
This error is crashing my app, and I'm having a hard time tracking down the parameter and value that's causing it:
buffer.js:784
throw TypeError('value is out of bounds');
^
TypeError: value is out of bounds
at TypeError (<anonymous>)
at checkInt (buffer.js:784:11)
at Buffer.writeUInt32LE (buffer.js:841:5)
at WritableTrackingBuffer.writeUInt32LE (./node_modules/mssql/node_modules/tedious/lib/tracking-buffer/writable-tracking-buffer.js:92:17)
at Object.TYPE.writeParameterData (./node_modules/mssql/node_modules/tedious/lib/data-type.js:264:25)
at new RpcRequestPayload (./node_modules/mssql/node_modules/tedious/lib/rpcrequest-payload.js:66:22)
at Connection.execSql (./node_modules/mssql/node_modules/tedious/lib/connection.js:712:56)
at ./node_modules/mssql/lib/tedious.js:563:33
at Transaction.next (./node_modules/mssql/lib/main.js:738:28)
at Request._release (./node_modules/mssql/lib/main.js:895:33)
at Request.userCallback (./node_modules/mssql/lib/tedious.js:462:23)
It's happening in a batch of about 2k inserts inside a transaction. Any pointers to figuring this out would be greatly appreciated.
Looking at the coffee script, it appears that when a connection is closed and pooling is enabled, the entire pool is cleared. (Not coffee script literate)
What's the recommended use of connections with pooling enabled? Should i
thanks for a great module
For any decent sized webapp, you'll have situations where the query of a dataset requires the result of another dataset. You'll also have situations in which you want to query data in a particular order.
At the moment I'm using chaining/shifting and separate synchronous libraries to replicate this. As evidenced by node.js core modules and a very large number of popular modules, 'Sync' methods are becoming commonplace.
It'd be wonderful for this module to implement sync methods as well.
Test code:
sql = require('mssql')
config =
database: 'MyDb'
user: 'username'
password: 'INVALID'
conn = new sql.Connection config
conn.connect (err)->
if err
console.error err.message
else
console.log 'connected'
conn.close()
If the password in the above configuration is correct, the I get:
$ coffee test-login.coffee
connected
$
Buf if it's incorrect, I get
$ coffee test-login.coffee
Login failed for user 'username'
and then I have to wait about 30 seconds until the connection times out. I believe I've traced this to the fact that neither the @connecting
or @connected
flag gets set on the connection, so when the @close
method is called on the Connection
object, the underlying driver's Connection::close
method never gets called.
I tried applying this patch:
diff --git a/src/main.coffee b/src/main.coffee
index 5de6756..05ac216 100644
--- a/src/main.coffee
+++ b/src/main.coffee
@@ -210,7 +210,11 @@ class Connection extends EventEmitter
callback? err
@driver = null
-
+ else
+ @driver.Connection::close.call @, (err) =>
+ callback? err
+
+ @driver = null
@
But still no luck. Is this an issue with node-mssql
or with the generic-pool
module?
I start a new server connected to the database server, after a long time no connections to db, when reconnect it, the app idled.
Can you add a 'error' or 'timeout' event for connection that we can use it.
Thank you so much.
When using tedious, result.columns is always undefined, regardless if any actual rows returned or not.
I am using Ubuntu on my PC. So when I use node-mssql module to set up connections, I can be using either tedious module or node-tds module. From the quick example, it wasn't clear which of the two is being used. How can we tell?
Thanks in advance.
First of all I want to say that I really like your module and it is definitely the best MSSQL node module I've tried so far.
Not sure if I'm missing something but I have a problem with request.execute() it seems that the returning recordsets argument has more recordsets than I expect. I created a stored procedure which simply returns me 2 tables: 1 with the returnCode and message and another table contains the result.
For some reason request.execute() returns me 4 tables where some of them are empty. If I execute the same stored procedure using request.query I will get a precise number of recordsets back which is 2.
Here is my example code:
var sql = require('mssql');
var connection = new sql.Connection(config.dbconfig);
connection.connect(function(err) {
if (err) {
console.log(err);
}
});
var request1 = new sql.Request(connection);
request1.multiple = true;
request1.verbose = false;
request1.execute('stGetLocations', function(err, recordsets) {
console.log('====== stored procedure request ======');
console.log(recordsets.length);
console.log(recordsets);
});
var request2 = new sql.Request(connection);
request2.multiple = true;
request2.verbose = false;
request2.query('EXEC [dbo].[stGetLocations]', function(err, recordsets) {
console.log('====== query request ======');
console.log(recordsets.length);
console.log(recordsets);
});
And here is what I see in my output
====== stored procedure request ======
4
[ [],
[ { errorCode: 1,
errorMessage: 'Locations retrieved successfully' } ],
[ { locationid: 1, name: 'Ontario' },
{ locationid: 2, name: 'Quebec' } ],
[] ]
====== query request ======
2
[ [ { errorCode: 1,
errorMessage: 'Locations retrieved successfully' } ],
[ { locationid: 1, name: 'Ontario' },
{ locationid: 2, name: 'Quebec' } ] ]
using OSX 10.7.5
mssql 0.3.4
node 0.10.12
sql server 11.0.3000 (SQL Server 2012)
Is it possible to have a parameter of type SQLDbType.Structured?
http://msdn.microsoft.com/en-us/library/system.data.sqldbtype(v=vs.110).aspx
Hello,
I'm working on an API that will be pulling information from multiple databases, lots of different mssql databases. But it's possible it would receive concurrent requests for the same db during short periods of time.
Naturally, I still haven't done an all out testing of the product, but I'm already wondering if there are gotchas, best practices or, in general, any kind of information that would help me get the best out of this library.
For instance, I was thinking about probably preparing some sort of connection pool, or a store of connections that have been recently used. Maybe keep those connections open or something.
BTW, I'm also new to this node culture. Maybe my question is a silly. Yet any help would be greatly appreciated :)
Thanks in advance,
I get this error when i try connect in my database.
Follow the code:
var sql = require('mssql');
var config = {
user: 'xx',
password: 'xx',
server: 'localhost',
database: 'xx'
}
var connection = new sql.Connection(config, function(err) {
if (err) {
console.log(err);
}
else {
console.log('connected');
}
});
and the error:
Thanks.
Will
Hi. Check this validate Transact-SQL Data Types https://github.com/paulomcnally/tsdt.js
It allows me to implement your module?
Any recommendation is welcome.
Regards.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.