Passing table value parameter to Store Proc about node-sqlserver-v8 HOT 12 CLOSED

from node-sqlserver-v8.

from node-sqlserver-v8.

@TimelordUK : I guess when we use just import mssql it is using tedious TDS by default and when we use
await db
.request()
.input("tvpTable", tvp)
OR
await db
.request()
.input("tvpTable",sql.TYPES.TVP, tvp) it works fine.

But when we import sql/msnodesqlv8 this doesnt work and gives above mentioned error.

from node-sqlserver-v8.

I have been looking at adding TVP parameter stored proc support to msnodesqlv8

in my library all that will be required is a call to the proc with an object parameter where columns are defined.

i.e.

var tvpObj = {
col_1 : "Hello",
col_2 : "World"
}

and call the proc as usual - all the binding is done automatically by the driver.

I hope to have that released shortly but I will have to see how to integrate that into the mssql API.

from node-sqlserver-v8.

this is now fixed on latest version 0.4.5

from node-sqlserver-v8.

@TimelordUK , I cannot get this to work although I'm running version 0.4.7.

Try 1

        const tvp = new sql.Table("[General].[BigintValueTVP]");
        tvp.columns.add("value", sql.BigInt(), { nullable: false });
        tvp.rows.add(1);
        tvp.rows.add(2);

        const request = new sql.Request(connection);
        request.input("tTVP", tvp);
        await request.execute("mySP");

Throws the same error as above: IMNOD: [msnodesql] Parameter 1: Invalid parameter type

Try 2

        const tvp = [{value:1}, {value:2}]
        
        const request = new sql.Request(this.connection);
        request.input("tTVP", tvp);
        await request.execute("mySP");

This throws the following error: Operand type clash: nvarchar is incompatible with BigintValueTVP

Try 3

        const tvp = [{value:1}, {value:2}]
        
        const request = new sql.Request(this.connection);
        request.input("tTVP", sql.TVP("[General].[BigintValueTVP]"), tvp);
        await request.execute("mySP");

This throws the first error: IMNOD: [msnodesql] Parameter 1: Invalid parameter type

So how do you work with TVPs when using this plugin?

from node-sqlserver-v8.

from node-sqlserver-v8.

Thanks for your reply @TimelordUK , but even after the patch this does not work. If the TVP is in the dbo schema all works, but if it is in a custom scheme (like in my examples above) I get the following error:
Column, parameter, or variable #1: Cannot find data type BigintValueTVP.

from node-sqlserver-v8.

Here is the test scenario I'm using:
DB Setup:

CREATE SCHEMA [Gen]
GO
CREATE TYPE [dbo].[Test1] AS TABLE(
	[value] [bigint] NOT NULL
)
GO
CREATE TYPE [Gen].[Test] AS TABLE(
	[value] [bigint] NOT NULL
)
GO
CREATE PROCEDURE [Gen].[TestSp]
	@t			[Gen].[Test]		READONLY
AS
BEGIN
	select *
	from @t
END
GO
CREATE PROCEDURE [Gen].[TestSp1]
	@t			[Test1]		READONLY
AS
BEGIN
	select *
	from @t
END
GO

app

var sql = require("mssql/msnodesqlv8");

var connection = new sql.ConnectionPool(config);

connection.connect()
    .then(function () {
        // This one is ok
        var tvp = new sql.Table("Test1");
        tvp.columns.add("value", sql.BigInt(), { nullable: false });        
        tvp.rows.add(5);
        const request = new sql.Request(connection);
        request.input("t", sql.TVP("Test1"), tvp);
        request.execute("Gen.TestSp1")
            .then(function () {
                console.log("OK");
            })
            .catch(function (e) {
                console.log(e);
            });

        // This one fails
        var tvp = new sql.Table("Gen.Test");
        tvp.columns.add("value", sql.BigInt(), { nullable: false });        
        tvp.rows.add(5);
        const request = new sql.Request(connection);
        request.input("t", sql.TVP("Gen.Test"), tvp);
        request.execute("Gen.TestSp")
            .then(function () {
                console.log("OK");
            })
            .catch(function (e) {
                console.log(e);
            });
    })
    .catch(function (e) { console.log(e) });

from node-sqlserver-v8.

OK, I have made some progress on this, unfortunately this will require a change to the c++ driver as it has to inform ODBC of the schema being used, I have hacked up an experimental build which gets your examples working.

I will hope to get this released in next day or 2 after tidying up and testing.

from node-sqlserver-v8.

0.4.9 should now run these 2 examples OK - thanks they are good tests.

C:\Program Files\nodejs\node.exe index.js
OK
OK

var sql = require('mssql/msnodesqlv8')

const connectionConf = {
user: '',
password: '',
database: 'scratch',
host: 'localhost',
connectionString: 'Driver={SQL Server Native Client 11.0}; Server=np:\\.\pipe\LOCALDB#EBF94DD0\tsql\query; Database={scratch}; Trusted_Connection=Yes;',
options: {
},

pool: {
max: 5,
min: 0,
idle: 10000
}
}

var connection = new sql.ConnectionPool(connectionConf)

connection.connect()
.then(function () {
// This one is ok
var tvp = new sql.Table('Test1')
tvp.columns.add('value', sql.BigInt(), {nullable: false})
tvp.rows.add(5)
const request = new sql.Request(connection)
request.input('t', sql.TVP('Test1'), tvp)
request.execute('Gen.TestSp1')
.then(function () {
console.log('OK')
})
.catch(function (e) {
console.log(e)
})
}).then(function () {
// This one fails
var tvp = new sql.Table('Gen.Test')
tvp.columns.add('value', sql.BigInt(), {nullable: false})
tvp.rows.add(5)
const request = new sql.Request(connection)
request.input('t', sql.TVP('Gen.Test'), tvp)
request.execute('Gen.TestSp')
.then(function () {
console.log('OK')
})
.catch(function (e) {
console.log(e)
})
})

from node-sqlserver-v8.

I believe this is now sorted - this has been merged to mssql so hopefully tvp now works OK

from node-sqlserver-v8.