timelorduk / node-sqlserver-v8 Goto Github PK

var msnodesql = require('msnodesqlv8');
msnodesql.open(connectionString, function(err, conn) {
    if (err) return console.error(err);

    cmd = [
        'create table rowsAffectedTest (id int)',
        'insert into rowsAffectedTest values (1)',
        'insert into rowsAffectedTest values (1)',
        'insert into rowsAffectedTest values (1)',
        'update rowsAffectedTest set id = 1',
        'drop table rowsAffectedTest'
    ];

    var q = conn.query(cmd.join(';'));
    q.on('rowcount', function(count) {
        console.log(count);
    })
});

Result:

1
1
3
3

Expected:

1
1
1
3

Just a small withlist. You know... it's Christmas :)

• Connection timeout (documentation says it can't be set via connection string)
• Request cancellation (required to support request timeouts)
• Multiple errors handling (atm only the first error that occurs is reported)

When the package is used in Node 9.2.0 there is an issue that crops up as below.

Please is there any work around this. This is surely not the expected behaviour.

Unhandled rejection Error: None of the binaries loaded successfully. Is your node version either latest 4, 5, 6, 7 or 8 ?
at failIfNoBinaryExported (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\msnodesqlv8\lib\bootstrap.js:61:15)
at Object.liveLoad (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\msnodesqlv8\lib\bootstrap.js:48:5)
at Object. (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\msnodesqlv8\lib\bootstrap.js:77:12)
at Module._compile (module.js:641:30)
at Object.Module._extensions..js (module.js:652:10)
at Module.load (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\coffee-script\lib\coffee-script\register.js:45:36)
at tryModuleLoad (module.js:503:12)
at Function.Module._load (module.js:495:3)
at Module.require (module.js:585:17)
at require (internal/module.js:11:18)
at D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\msnodesqlv8\lib\connection.js:10:19
at Object. (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\msnodesqlv8\lib\connection.js:449:2)
at Module._compile (module.js:641:30)
at Object.Module._extensions..js (module.js:652:10)
at Module.load (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\coffee-script\lib\coffee-script\register.js:45:36)
at tryModuleLoad (module.js:503:12)
at Function.Module._load (module.js:495:3)
at Module.require (module.js:585:17)
at require (internal/module.js:11:18)
at Object. (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\msnodesqlv8\lib\sql.js:20:10)
at Module._compile (module.js:641:30)
at Object.Module._extensions..js (module.js:652:10)
at Module.load (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\coffee-script\lib\coffee-script\register.js:45:36)
at tryModuleLoad (module.js:503:12)
at Function.Module._load (module.js:495:3)
at Module.require (module.js:585:17)
at require (internal/module.js:11:18)
at Object. (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\mssql\lib\msnodesqlv8.js:3:19)
at Module._compile (module.js:641:30)
at Object.Module._extensions..js (module.js:652:10)
at Module.load (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\coffee-script\lib\coffee-script\register.js:45:36)
at tryModuleLoad (module.js:503:12)
at Function.Module._load (module.js:495:3)
at Module.require (module.js:585:17)
at require (internal/module.js:11:18)
at Object. (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\mssql\msnodesqlv8.js:1:18)
at Module._compile (module.js:641:30)
at Object.Module._extensions..js (module.js:652:10)
at Module.load (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\coffee-script\lib\coffee-script\register.js:45:36)
at tryModuleLoad (module.js:503:12)
at Function.Module._load (module.js:495:3)
at Module.require (module.js:585:17)
at require (internal/module.js:11:18)
at Object. (D:\development\iViewPlus\iViewPlus.External.Web.v2\e2e\pages\utils\databaseabstraction\iViewPlus-database-helper.ts:2:13)
at Module._compile (module.js:641:30)
at Module.m._compile (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\ts-node\src\index.ts:392:23)
at Module._extensions..js (module.js:652:10)
at Object.require.extensions.(anonymous function) [as .ts] (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\ts-node\src\index.ts:395:12)
at Module.load (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\coffee-script\lib\coffee-script\register.js:45:36)
at tryModuleLoad (module.js:503:12)
at Function.Module._load (module.js:495:3)
at Module.require (module.js:585:17)
at require (internal/module.js:11:18)
at Object. (D:\development\iViewPlus\iViewPlus.External.Web.v2\e2e\pages\utils\databaseabstraction\query-manager-database-helper.ts:1:1)
at Module._compile (module.js:641:30)
at Module.m._compile (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\ts-node\src\index.ts:392:23)
at Module._extensions..js (module.js:652:10)
at Object.require.extensions.(anonymous function) [as .ts] (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\ts-node\src\index.ts:395:12)
at Module.load (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\coffee-script\lib\coffee-script\register.js:45:36)
at tryModuleLoad (module.js:503:12)
at Function.Module._load (module.js:495:3)
at Module.require (module.js:585:17)
at require (internal/module.js:11:18)
at Object. (D:\development\iViewPlus\iViewPlus.External.Web.v2\e2e\stepdefinitions\querymanagerStep.ts:3:1)
at Module._compile (module.js:641:30)
at Module.m._compile (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\ts-node\src\index.ts:392:23)
at Module._extensions..js (module.js:652:10)
at Object.require.extensions.(anonymous function) [as .ts] (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\ts-node\src\index.ts:395:12)
at Module.load (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\coffee-script\lib\coffee-script\register.js:45:36)
at tryModuleLoad (module.js:503:12)
at Function.Module._load (module.js:495:3)
at Module.require (module.js:585:17)
at require (internal/module.js:11:18)
at D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\cucumber\src\cli\index.js:61:42
at Array.forEach ()
at Cli.getSupportCodeLibrary (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\cucumber\src\cli\index.js:61:22)
at Cli. (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\cucumber\src\cli\index.js:76:37)
at Generator.next ()
at Generator.tryCatcher (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\bluebird\js\release\util.js:16:23)
at PromiseSpawn._promiseFulfilled (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\bluebird\js\release\generators.js:97:49)
at Promise._settlePromise (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\bluebird\js\release\promise.js:574:26)
at Promise._settlePromise0 (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\bluebird\js\release\promise.js:614:10)
at Promise._settlePromises (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\bluebird\js\release\promise.js:693:18)
at Async._drainQueue (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\bluebird\js\release\async.js:133:16)
at Async._drainQueues (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\bluebird\js\release\async.js:143:10)
at Immediate.Async.drainQueues [as _onImmediate] (D:\development\iViewPlus\iViewPlus.External.Web.v2\node_modules\bluebird\js\release\async.js:17:14)
at runCallback (timers.js:800:20)
at tryOnImmediate (timers.js:762:5)
at processImmediate [as _immediateCallback] (timers.js:733:5)

This code block throws error, but I could not catch it from my code (only by uncaughtException): msnodesqlv8\lib\reader.js:29

const sql = require('msnodesqlv8')
const server = 'server'
const connStr = `Driver={SQL Server Native Client 11.0};Server=${server};Trusted_Connection=Yes`
const query = `PRINT 1`

sql.open(connStr, function (err, conn) {
  console.error('First ERROR', err)
  conn.query(query, function (err, fetched) {
    console.error('Second ERROR', err)
  })
})

process.on('uncaughtException', (err) => {
  console.error('Caught exception: ' + err)
})

Hey there,

I have noticed that trying to pass a XML string of over 2000 characters to a stored procedure as a parameter results in SQL returning "[Microsoft][SQL Server Native Client 11.0]Invalid precision value".

I suppose it's being truncated, but I can't find a way to stop this happening?

I'm just using:

procManager.callproc('SEL_GetDataByXml', parameters, function (err, result) {

Any advice?

Thanks!

Dave

Hi,

I am moving across to this driver from tedious as tedious has severe issues when in streaming mode and causing the entire node app to be blocked. This package/driver does not cause those issues :)

However we have some warning messages in SQL that we cant always suppress. In tedious these warnings did not cause errors, however in this driver the warnings such as "Warning: Null value is eliminated by an aggregate or other SET operation" or "Warning: The join order has been enforced because a local join hint is used" are throwing errors, even though these warnings do not stop or prevent the queries from actually running and returning results.

I commented out some of the error callbacks and the issue went away of course, but I am wondering if there is already a way to handle these cases.

I think it would be best tied to severity levels. E.g severity levels 10 and below (https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-error-severities) of which my two examples above are included, do not actually prevent the code from running and are just informational messages. I would like to find a solution where informational messages do not cause errors to be thrown please. Could you point me in the correct place in the code to implement something like this if it is not already possible.

Thanks,

Hi @TimelordUK ,
I'm using the Microsoft's driver node-sqlserver for connecting to Azure SQL Database from lower versions of Node. But to connect from newer versions of Node.js I was trying to use this module. How can I set the Application Name to identify that the connection request coming from node-sqlserver-v8?

I would like to use this module in my electron (v1.2.4) application, but unfortunately the module is not able to require the precompiled ".node" file (sqlserverv8.v6.1.0.x64.node).

Electron (v1.2.4) is using the following versions:

I think that i have to build the c++ node addon for the correct electron version. But i have no idea if this is correct and how i have to do it.

Can someone help me please?

While any other versions up to 0.1.28 allowed to install binaries without 'node-gyp rebuild'

Log is below:

C:\app\test_npm_install>npm install
npm WARN package.json [email protected] No README data
npm WARN package.json [email protected] license should be a valid SPDX lic
ense expression
/
> [email protected] install C:\app\test_npm_install\node_modules\msnodesqlv8
> node-gyp rebuild


C:\app\test_npm_install\node_modules\msnodesqlv8>if not defined npm_config_node_
gyp (node "C:\Users\Administrator\AppData\Roaming\npm\node_modules\npm\bin\node-
gyp-bin\\..\..\node_modules\node-gyp\bin\node-gyp.js" rebuild )  else (node  reb
uild )
gyp WARN install got an error, rolling back install
gyp ERR! configure error
gyp ERR! stack Error: 404 status code downloading 32-bit node.lib
gyp ERR! stack     at Request.<anonymous> (C:\Users\Administrator\AppData\Roamin
g\npm\node_modules\npm\node_modules\node-gyp\lib\install.js:377:20)
gyp ERR! stack     at emitOne (events.js:82:20)
gyp ERR! stack     at Request.emit (events.js:169:7)
gyp ERR! stack     at Request.onRequestResponse (C:\Users\Administrator\AppData\
Roaming\npm\node_modules\npm\node_modules\request\request.js:992:10)
gyp ERR! stack     at emitOne (events.js:77:13)
gyp ERR! stack     at ClientRequest.emit (events.js:169:7)
gyp ERR! stack     at HTTPParser.parserOnIncomingClient (_http_client.js:415:21)

gyp ERR! stack     at HTTPParser.parserOnHeadersComplete (_http_common.js:88:23)

gyp ERR! stack     at TLSSocket.socketOnData (_http_client.js:305:20)
gyp ERR! stack     at emitOne (events.js:77:13)
gyp ERR! System Windows_NT 6.1.7601
gyp ERR! command "C:\\Program Files\\nodejs\\node.exe" "C:\\Users\\Administrator
\\AppData\\Roaming\\npm\\node_modules\\npm\\node_modules\\node-gyp\\bin\\node-gy
p.js" "rebuild"
gyp ERR! cwd C:\app\test_npm_install\node_modules\msnodesqlv8
gyp ERR! node -v v4.2.2
gyp ERR! node-gyp -v v2.0.2
gyp ERR! not ok
npm ERR! Windows_NT 6.1.7601
npm ERR! argv "C:\\Program Files\\nodejs\\node.exe" "C:\\Users\\Administrator\\A
ppData\\Roaming\\npm\\node_modules\\npm\\bin\\npm-cli.js" "install"
npm ERR! node v4.2.2
npm ERR! npm  v2.13.4
npm ERR! code ELIFECYCLE

npm ERR! [email protected] install: `node-gyp rebuild`
npm ERR! Exit status 1
npm ERR!
npm ERR! Failed at the [email protected] install script 'node-gyp rebuild'.
npm ERR! This is most likely a problem with the msnodesqlv8 package,
npm ERR! not with npm itself.
npm ERR! Tell the author that this fails on your system:
npm ERR!     node-gyp rebuild
npm ERR! You can get their info via:
npm ERR!     npm owner ls msnodesqlv8
npm ERR! There is likely additional logging output above.

npm ERR! Please include the following file with any support request:
npm ERR!     C:\app\test_npm_install\npm-debug.log

C:\app\test_npm_install>

Is there a way to get an Binary/Image column as actual binary data or better as a buffer?

For example we have some pdf's stored in an old database as the sql "image" type and when I do a raw query the metadata confirms it as the "image" type but the data returned from the query is already decoded and in a string.... while this would normally be ok something is not being decoded properly and the PDF's are not showing up right.

so instead of this:
%PDF-1.4 %���� 1 0 obj<</Producer(DynamicPDF Generator v3.0.3 for .NET)/CreationDate(D:20080721130746)>> endobj ..........

so is there a way to get the actual raw data like this:
0x255044462D312E340A25E2E3CFD30A312030206F626A3C3C2F50726F64756365722844.......

I think the underling issue is with the characters used in a pdf file and JavaScript native encoding/decoding being used, I had a similar issue when writing our new pdf file uploader and as a workaround/fix I kept the files in binary/base64 and they were left intact both in and out of the database.

Thanks,
Jeff

Is there a byte length limit for values in an insert, or for the entire array during an insert? The following loop fails when value.length > 2000...

/* table structure:

USE [bn_dev]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ntextTest](
  [testme] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
*/

var sql = require('msnodesqlv8');
var conn_str = "Driver={SQL Server Native Client 11.0};Server=(local);Database=bn_dev;Trusted_Connection={Yes}";

value = 'x';
q='insert into ntextTest values ( ? )';
didIFail=false;
times=0;
while(times<335) {
   sql.query(conn_str, q, [value], function (err, results) { if(err){ console.log(err); } });
   value = value + '+x+x+x';
   times = times+1;
}

/* output:

$ node load_test.js
{ Error: [Microsoft][SQL Server Native Client 11.0]COUNT field incorrect or syntax error
    at Error (native) sqlstate: '07002', code: 0 }

*/

`

Hi,

It seems that this module error when running via Electron v.1.7.5. It will throw an error

"None of the binaries loaded successfully. Is your node version either >= 0.12.7 or >= 4.2.x or >= 5.1.1 or >= 6.1.0"

I have tried using Electon v1.6.12 and it was working fine.

greetings. I hope you find some use in this module. I intend to add additional features which hopefully will be based on the community feedback. So please feel free to make suggestions for improvements or issues with the new stored procedure feature.

While trying to require the library I'm receiving an error loading the binaires:

Error: None of the binaries loaded successfully. Is your node version either >= 0.12.7 or >= 4.2.x or >= 5.1.1 or >= 6.1.0
    at failIfNoBinaryExported (...dir\node_modules\msnodesqlv8\lib\sqlserver.native.js:64:13)
    at liveLoad (...dir\node_modules\msnodesqlv8\lib\sqlserver.native.js:51:3)
    at Object.<anonymous> (...dir\node_modules\msnodesqlv8\lib\sqlserver.native.js:24:1)

I'm trying to get script to run in an environment with:

• Windows Server 2008 SP2 (x64)
• Node v4.8.4 x86, npm v2.15.11
• VS C++ 2015 Redistributable installed (x86, x64)
• SQL Native Client 11
• [email protected]

Adding logging in the attemptToExportBinary (filename) function in sqlserver.native.js lists the expected binaries, but suppresses an error:

sqlserverv8.node.v4.8.4.ia32.node
[Error: The specified procedure could not be found.
\\?\c:\...dir\node_modules\msnodesqlv8\lib\bin\sqlserverv8.node.v4.8.4.ia32.node]

sqlserverv8.node.v4.8.4.x64.node
[Error: %1 is not a valid Win32 application.
\\?\c:\...dir\node_modules\msnodesqlv8\lib\bin\sqlserverv8.node.v4.8.4.x64.node]

Any suggestions what to try?

Maybe varchar(0) is a invalid type in sqlserver. So if input string is empty, it will throw "Error: [Microsoft][ODBC SQL Server Driver]Invalid precision value".
So change the code like:

binding.param_size = str_len==0?1:str_len;

Would it be possible to remove the dependency on electron when you do a production npm install.

Hi,
I'm running into an issue with a nested connection callback running twice. What is returned in the console is:
x is 0
x is 1
y is 0
y is 1

And my code is:

x = 0;

y = 0;

sql.open(sqlConnectionString, function(err, conn) {
  if(err){
    return;
  }
  conn.queryRaw("USE master CREATE DATABASE [db_" + guid + "]", function(err, results) {
    //the function below's callback runs twice
    conn.queryRaw("USE BuilderCatalog; insert into Events (Guid, Name, Edited, Date) VALUES('" + guid + "', '" + name + "', GETDATE(), '" + date.format() + "' )", function(err, results) {
      console.log("x is " + x);
      x++;
      conn.queryRaw("USE db_" + guid + "]; " + createTableString, function(err, results) {
        console.log("y is " + y);
        y++;
      }
    }
  }
});

Maybe I'm missing something, but any help would be appreciated!

Hi,

Version: 0.1.35

We'd like to try out Node version 6.0 (6.1.0 to be exact), but node-sqlserver-v8 load fails with the exception above. The bin directory appears to have builds only for a couple of versions.

Is it possible to release a build that is compatible with later version of Node?

I'm having an issue when I try to pass a table value parameter to Store proc.

const tvp= new sql.Table();
tvp.columns.add('x',sql.TYPES.Int);
tvp.columns.add('y', sql.TYPES.SmallInt);

When I try to pass this tvp to store proc
await db
.request()
.input("tvpTable", tvp) i'm getting this error RequestError: IMNOD: [msnodesql] Parameter 1: Invalid parameter type.

But the same call works with MSSQL node module. Is table value parameter not supported in node-sqlserver-v8?

Hi @TimelordUK, I was wondering if you could help me solve tediousjs/node-mssql#451. I was able to reproduce the behavior mentioned in the issue and I'm not sure whether this is a feature or bug? I can quite easily merge those string on my side but before I do that, I'm first asking you about our opinion there. Thank you.

As a simple test case:
create PROCEDURE bigint_test
@A bigint = 0,
@b bigint = 0 output
AS
BEGIN
set @b = @A
select @b as b
END
GO

When you call this via the stored proc manager:
pm.callproc("bigint_test", [1234567890], function (err, results, output) {
console.log("err", err);
console.log("results", results);
console.log("output", output);
});

You get the following:
err null
results [ { b: 1234567890 } ]
output [ 0, null ]

So the "b" output parameter does not get assigned correctly

I'm having an issue pulling the correct datetime value. For example, the value in the database is 2009-05-27 00:00:00.000 but when I view the query results, the value is Tue May 26 2009 20:00:00 GMT-0400 (Eastern Daylight Time). How can I pull exactly what is in the database?

Hello!

I'm trying to learn more about Node.js developers using SQL Server. I created a survey to gather some data: https://www.surveybuilder.com/s/VjJ3Z

Questions include types of projects, drivers, Node.js frameworks and versions. I will share the results back to satisfy your curiosity :)

This code throws error "Invalid precision value" for STR_LEN from 2001 to 4000.
const connStr = 'Driver={SQL Server Native Client 11.0};Server=Database;Trusted_Connection=Yes;'

const sql = require('msnodesqlv8')

var co = {
conn_str : connStr,
conn_timeout : 2
}

const STR_LEN = 2001

const str = '1'.repeat(STR_LEN)
sql.open(co, (err, conn) => {
if (err) {
console.log(err)
return
}
conn.query('select ? as cnt', [str], (err, res) => {
if (err) {
console.log(err)
} else {
console.log(res)
}
})
})

Hi, first of all thanks for your efforts on making this lib up to date with latest node releases. I'm in the process of implementing support into node-mssql and during that I found some glitches. I'm going to create a separate issues for each of them.

var msnodesql = require('msnodesqlv8');
msnodesql.open("Driver={SQL Server Native Client 11.0};Server=localhost;Database=xsp;Uid=xsp_test;Pwd=...;Trusted_Connection=No;", function(err, conn1) {
    if (err) return console.error(err);

    msnodesql.open("Driver={SQL Server Native Client 11.0};Server=localhost;Database=xsp;Uid=xsp_test2;Pwd=...;Trusted_Connection=No;", function(err, conn2) {
        if (err) return console.error(err);

        conn1.query("select @@SPID as id, CURRENT_USER as name", function(err, res) {
            if (err) return console.error(err);

            console.log('CONN1', res);
        });

        conn2.query("select @@SPID as id, CURRENT_USER as name", function(err, res) {
            if (err) return console.error(err);

            console.log('CONN2', res);
        });
    });
});

Got:

CONN1 [ { id: 56, name: 'xsp_test2' } ]
CONN2 [ { id: 56, name: 'xsp_test2' } ]

Expected:

CONN1 [ { id: 56, name: 'xsp_test' } ]
CONN2 [ { id: 57, name: 'xsp_test2' } ]

This problem was not present in original msnodesql.

{ "errorMessage": "None of the binaries loaded successfully. Is your node version either >= 0.12.7 or >= 4.2.x or >= 5.1.1 or >= 6.1.0", "errorType": "Error", "stackTrace": [ "Object.<anonymous> (/var/task/node_modules/msnodesqlv8/lib/sqlserver.native.js:24:1)", "Module._compile (module.js:409:26)", "Object.Module._extensions..js (module.js:416:10)", "Module.load (module.js:343:32)", "Function.Module._load (module.js:300:12)", "Module.require (module.js:353:17)", "require (internal/module.js:12:17)", "Object.<anonymous> (/var/task/node_modules/msnodesqlv8/lib/ConnectionWrapper.js:5:11)" ] }

I am run on Amazon Lambda.
Please help me. Thank you!

This code throws error on the last version (0.2.16) of node-sqlserver-v8. In 0.2.15 everything is OK.
const connStr = 'Driver={SQL Server Native Client 11.0};Server=Server;Trusted_Connection=Yes;'
const sql = require('msnodesqlv8')
var co = {
conn_str : connStr,
conn_timeout : 2
}
sql.open(co, (err, conn) => {
if (err) {
console.log(err)
return
}
conn.query('print 'print error'; select 1 as cnt', [], (err, res) => {
if (err) {
console.log(err)
} else {
console.log(res)
}
})
})

I want to contact sqlserver 2008 , how to do？
tank you !

I am not sure if this is a bug, or a misunderstanding.

I have a query (actually a stored proc) which I can't change. I can't run it as a stored proc because it's one of many queries that I'm retrieving from a datastore - I don't know what it's going to be when I load it, except that it's SQL.

Unfortunately, whoever wrote the proc didn't add NOCOUNT ON, so for everything the proc does which affects any rows, the handler fires.

I know that eventually the proc returns a single table (which might be empty), so I thought perhaps I could do something like this -

let results: any = [];

let q = conn.query(loadedQuery, (err, res) => {
  results = res;
  console.log(`Result from query: ${JSON.stringify(results)}`)
});

q.on('done', () => {
  console.log(`Completed: ${JSON.stringify(results)}`)
});

But the output for this is -

... 
Result from query: []
Result from query: []
Completed: []
Result from query: [ /* things in here */ ]

So, is this normal behaviour? If so, how can I get the last result and know it's the last result? (especially if the last result is actually empty) If not, well...consider this a bug report 😃

Originally posted here: Azure/node-sqlserver#160

var msnodesql = require('msnodesqlv8');
msnodesql.open(connectionString, function(err, conn) {
    if (err) return console.error(err);

    conn.query("select convert(datetimeoffset(7), '2014-02-14 22:59:59.9999999 +05:00') as dto1, convert(datetimeoffset(7), '2014-02-14 17:59:59.9999999 +00:00') as dto2", function(err, res) {
        console.log(res);
    });
});

Got:

[ { dto1: { Sat, 15 Feb 2014 03:59:59 GMT nanosecondsDelta: 0.0009999 },
    dto2: { Fri, 14 Feb 2014 17:59:59 GMT nanosecondsDelta: 0.0009999 } } ]

Expected:

[ { dto1: { Fri, 14 Feb 2014 17:59:59 GMT nanosecondsDelta: 0.0009999 },
    dto2: { Fri, 14 Feb 2014 17:59:59 GMT nanosecondsDelta: 0.0009999 } } ]

Please merge this pull request. Azure/node-sqlserver#134

Hi,
Is there any guide for building the binary for specific Node version like v8.0.0 ?

Thanks!

Hi,

I have reported the following issue in node-mssql, but it seems to be a bug in msnodesqlv8 itself:

tediousjs/node-mssql#414

The bug seems to be introduced in the 0.2.x releases. I do not have an example using msnodesqlv8 directly just yet, but I reference the issue here so you are aware of it.

Thanks

I've been trying to find the cause of a memory leak in my application that uses msnodesqlv8 and it seems to be caused by msnodesqlv8. I have the following test code:

const msnodesqlv8 = require('msnodesqlv8');

msnodesqlv8.open('Driver={SQL Server Native Client 11.0};Server=localhost\\SQLEXPRESS;Trusted_Connection=yes;', (err, conn) => {
	if (err) {
		throw err;
	}
	setInterval(() => {
		conn.queryRaw('select 1+1', (err, results) => {
			if (err) {
				throw err;
			}
			console.log(results);
		});
	}, 100);
});

The query executes successfully, but when monitoring the memory usage, the usage just keeps going up until it uses so much the process has to be killed. It does not look like queries are being cleaned up properly.

I took a look into the code and think it might have to do with SQL handles not being freed. I noticed that in the Free method of the OdbcHandle class, handle is set to nullptr before the call to SQLFreeHandle. This would cause the handle to not be freed. (Reference)

open(function(conn) {
    query(conn, "select cast(11 as sql_variant) as data;", [null], function(res) {
        console.log(res);
    });
});

Result:

Hey there,

There's a bug inside procedureMgr.bindParams where the 'j' variable is never updated; this means that parameters with more than one input parameter won't work properly.

I have altered the function to:

function bindParams(meta, params) {
        var j = 0;
        for (var i = 0; i < params.length; ++i) {
            while (j < meta.params.length && meta.params[j].is_output === true) {
                ++j;
            }
            if (j < meta.params.length) meta.params[j++].val = params[i];
        }
    }

Looks good?

Hi,

I'm trying to make a standalone app with electron.
If use electron i get following error: A dynamic link library (DLL) initialization routine failed
Without electron everything works fine.
I've already used electron-rebuild but still the same error.
What do i have to do to get it working?

Not able to install msnodesqlv8 on linux. Getting the below error.
Unsupported platform for [email protected]: Wanted {"os":"win32", "arch":"any"} (Current:{"OS":"linux","arch":"x64"
Could you please give a work around for this.

Greetings,

Just moved our app to production and suddenly getting the following error (abbreviated) This works just fine on the dev side of our app.

Error { ConnectionError: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified at msnodesql.open (c:\apps\vim\node_modules\mssql\lib\msnodesqlv8.js:174:17)

Anyone ever run into this issue?
Thanks

tm = connection.tableMgr();
tm.bind("bulk_table", function(mgr) {
    assert(mgr.columns.length > 0); // OK
});

tm = connection.tableMgr();
tm.bind("[bulk_table]", function(mgr) {
    assert(mgr.columns.length > 0); // Fails
});

Temporary tables are not recognized regardless if they're in brackets or not.

tm = connection.tableMgr();
tm.bind("#mytemptable", function(mgr) {
    assert(mgr.columns.length > 0); // Fails
});

Hi,

Version: 0.1.35, node version 4.4.3

Firstly thanks for the work on this.

We've recently looked at replacing the original node-sqlserver with your v8 version and have run into a small issue. The code we have for connecting to SQL Server looks like this:

new Promise(function (resolve, reject) {

    // Open the connection
    nodesqlv8.open('connectionString', function (error, con) {

        // Execute the query
        con.query('SELECT * FROM foo.bar', undefined, function (error, results, hasMore) {

            // Close the connection.
            con.close(function (closeError) {

                // Resolve result
                resolve(results);
            });
        });
    });
});

This bit of code was working correctly in the original node-sqlserver, but in the v8 version it hangs occasionally. Stepping into the code I see that the resolve(results) line gets executed, but control flow never reaches the outer world. I have not been able to reproduce this consistently, but have found that the following two workarounds get rid of the hang:

Workaround 1: Add a setTimeout

  // Execute the query
  con.query('SELECT * FROM foo.bar', undefined, function (error, results, hasMore) {

            // Close the connection.
            con.close(function (closeError) {

                // Resolve with setTimeout
                setTimeout(function () { resolve(results); });
            });
});

Workaround 2: Do not wait for the connection close callback

        // Execute the query
        con.query('SELECT * FROM foo.bar', undefined, function (error, results, hasMore) {

            // Resolve
            resolve(results);

            // Close the connection.
            con.close(function () { });
        });

This is a bit strange and is probably related to #5.

Using this driver when i try to insert long strings in sql server tables whose columns set to nvarchar(max) through stored procedures via mssql client and setting input type to sql.NVarChar(sql.MAX), for long wkt text it gets:

Error:  String or binary would be truncated

and for other long strings it gets:

Error: Invalid precision value

and no insertion; But using tedious driver it works flawlessly with the same values.
I use your driver because of windows auth support but i have to make a sql login to use tedious.

Ok i lost 1-2 days until i realized where the problem is. I am posting this issue in hopes of saving somebody time in searching.
To replicate the issue
in project directory
npm init npm install electron-prebuilt --save-dev npm install electron-packager --save-dev npm install mssql --save npm install msnodesqlv8 --save
then setup project to run and use msnodesqlv8 as a driver for mssql, when you run the project with
npm run test
where test in package.json is "test": "electron main.js" everything is ok. Now here is the tricky part, when you package your application with the command
"electron-packager . \"App Name\" --version=1.4.10 --overwrite --platform=win32 --arch=ia32 --asar=true

the asar=true options creates app.asar archive in the resources folder within the release folder, and when you run the from there it can't load the binaries for the msnodesqlv8 driver.
BTW i use asar archives to ensure that "path too long in windows" error doesn't happen.

Hope i have helped some one.

It seems that when binding an empty string, the resulting value in my column is NULL, not the empty string. This has come in recently - between 0.1.35 (where this doesn't occur) and the latest (0.1.44):

UPDATE [mytable] SET [textfield] = ?', ['']

Results in textfield being NULL, not the empty string

Hi,

I don't know the exact cause of this, but we encountered a problem with using the bulk insert feature of this library. The standard query option that we fell back to works like a charm.

We had a CSV file with 13.8m rows in it that we streamed in batches of 25,000 into the tableMgr.insertRows function. At the end of the run we found that the script exited with 13.5m records inserted into the database (SQL Server 2008 R2). We then used a standard query to run through the csv again, and the missing records were inserted into the database.

The strange thing was that records in the very early parts of the csv file had been missed by the bulk insert, which wasn't what I expected.

I can provide supporting files (db schema, csv file, the script I ran) if that would help, with the caveat that the data is representative of the data inserted, but not the actual data.

Binaries are missing for 4.x. Is it still supported?

(I'm not sure if this is the right place for these questions and how much pertains to the original node-sqlserver)

I have essentially the following code:

var sql = require('msnodesqlv8');

function doQuery() {
    sql.open(this.connectionString, function (err, conn) {
        if (err) {
            console.log("Error opening the SQL connection: " + err);
            self.emit('error', err);
            conn.close();
            return;
        }

        var q = conn.queryRaw(query, values, function (err, results) {
            if (err) {
                self.emit('error', err);
                conn.close();
                return;
            }
            callback(null, true, results.rowcount);
        });

        q.on('rowcount', function (count) {
            console.log('rowcount = ' + count);
        }); 

        q.on('closed', function () {
            console.log('closed');
        });

        q.on('done', function () {
            console.log('done');
        });

        q.on('error', function (err) {
            console.log('error: ' + err);
        });
    });

}

I'm noticing log messages for 'rowcount' and 'done' but not 'closed'.
Unless I'm misunderstanding, under the hood the queryRaw() method calls queryCloseOnDone() which upon a 'complete' callback calls thisConn.close() which does a notify.emit('closed'). Since there's no 'closed' event, why isn't this connection being automatically closed?

If I do a conn.close() within the queryRaw() callback it seems that the 'closed' event is not emitted either.

As an additional question, is there any way to read the state of a connection object? e.g. to determine if it is still open/valid or if it has been closed.

I seem to be getting an Invalid precision value error when attempting to insert a long string. The string is just over 2000 chars long, and the column is an NVARCHAR(MAX), which is plenty big enough to hold it. The query works fine when running in a tool (e.g. Visual Studio / SQL Server Management Studio), but falls over when using this library.

My research led me here:

http://stackoverflow.com/questions/15007721/sqlbindparameter-invalid-precision-value-on-a-nvarcharmax-column

which suggests that we need to use SQL_WLONGVARCHAR rather than SQL_WVARCHAR when binding long strings. Looking through the code, my best guess is in src/BoundDatum.cpp, should we set sql_type to SQL_WLONGVARCHAR rather than SQL_WVARCHAR if it's over a certain length? Does that sound plausible, or am I barking up the wrong tree?

var msnodesql = require('msnodesqlv8');
msnodesql.open("Driver={SQL Server Native Client 11.0};Server=localhost;Database=xsp;Uid=xsp_test;Pwd=...;Trusted_Connection=No;", function(err, conn) {
    if (err) return console.error(err);

    conn.query("declare @bin binary(4) = ?; select @bin as bin", [null], function(err, res) {
        console.error(err);
    });
});

Results in:

[Error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Implicit conversion from data type char to binary is not allowed. Use the CONVERT function to run this query.]

I had a workaround for original msnodesql - sending an empty buffer to the argument (new Buffer(0)) but this doesn't work anymore. Any ideas?

Hello @TimelordUK
Hope you are doing well :) Thanks for all the great work you are doing on this driver. We recently released a new ODBC Driver. Will node-sqlserver-v8 work with Microsoft ODBC Driver 13 for SQL Server?