How to insert table valued parameters into Sql Azure from Node?

Question

I'm working on an application that inserts data in bulk and in order to reduce the number of queries I wanted to use Table Valued parameters.

Example:

var sql = require('node-sqlserver');

var connectionString = 'Driver={SQL Server Native Client 11.0};server=tcp:serverName.database.windows.net,1433;UID=user@serverName;PWD=password;Database={databaseName};Encrypt=yes;Connection Timeout=30;';

sql.open(connectionString, function(err, conn) {
    if(err) {
        return console.error('could not connect to sql', err);
    }

    var tableValuedObject = ???;

    var query = 'usp_InsertSomeTable ?';

    conn.query(query, tableValuedObject, function(err, result) {
        if(err) {
            return console.error('error running insert', err);
        }
    });
});

Where usp_InsertSomeTable is defined as

CREATE PROCEDURE usp_InsertSomeTable
    @TVP SomeTableType READONLY
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO SomeTable (Data) SELECT Data FROM @TVP;
END

I have my table valued object defined as

CREATE TYPE SomeTableType AS TABLE
(
     Data VARCHAR(50)
);

What structure should the JavaScript object have or has that not been implemented in the Node-SqlServer project?

Edit 1:

using var tableValuedObject = { Data: 'Hello World' }; produces an error:

node-sqlserver\lib\sql.js:3254: Uncaught Error: [msnodesql] Invalid parameter(s) passed to function query or queryRaw.

Edit 2:

using var tableValuedObject = [{ Data: 'Hello World'}]; produces and error:

error running query { [Error: IMNOD: [msnodesql] Parameter 1: Invalid parameter type] sqlstate: 'IMNOD', code: -1 }

carlosfigueira · Accepted Answer · 2013-08-27 04:39:22Z

3

The parameters to the function query have to be passed as an array (see example here). Try calling it as such, it should work:

sql.open(connectionString, function(err, conn) {
    if(err) {
        return console.error('could not connect to sql', err);
    }

    var tableValuedObject = { Data: 'Hello World' };

    var query = 'usp_InsertSomeTable ?';

    conn.query(query, [tableValuedObject], function(err, result) {
        if(err) {
            return console.error('error running insert', err);
        }
    });
});

answered Aug 27, 2013 at 4:39

carlosfigueira

87.7k14 gold badges137 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dustin Kingen Over a year ago

Alright I tried it and it returned: error running query { [Error: IMNOD: [msnodesql] Parameter 1: Invalid parameter type] sqlstate: 'IMNOD', code: -1 }

Tomasz Janczuk · Accepted Answer · 2013-09-12 22:04:26Z

0

+150

If your Node.js application is running on Windows (e.g. in Windows Azure Web Sites), a convenient way of running any SQL commands is to use ADO.NET from .NET Framework within a Node.js process via Edge.js (http://tjanczuk.github.io/edge). Edge.js allows you to run .NET and Node.js in-process.

There is also edge-sql, an Edge.js extension that exposes basic CRUD operations to Node.js using ADO.NET and Edge (http://tomasz.janczuk.org/2013/06/access-sql-azure-from-nodejs-app.html). While current functionality of edge-sql will not allow you to accomplish what you need here, the implementation of edge-sql itself may be a good starting point for your own (https://github.com/tjanczuk/edge-sql). I also do take contributions to edge-sql if you feel like enhancing its feature set.

answered Sep 12, 2013 at 22:04

Tomasz Janczuk

3,24624 silver badges19 bronze badges

1 Comment

matteofigus Over a year ago

You are not answering to the question.

Charlie Brown · Accepted Answer · 2013-09-06 15:21:03Z

From the source, it currently only supports native sql server types.

https://github.com/WindowsAzure/node-sqlserver/blob/2ac58c8dda77406f081db693c70f2986b29b6025/src/OdbcOperation.cpp

However, you can run multiple inserts in one query call.

var query = "INSERT INTO dbo.test ([key], [value]) VALUES ('1', 'a');" +
"INSERT INTO dbo.test ([key], [value]) VALUES ('2', 'b');" +
"INSERT INTO dbo.test ([key], [value]) VALUES ('3', 'c');" +
"INSERT INTO dbo.test ([key], [value]) VALUES ('4', 'd');" +
"INSERT INTO dbo.test ([key], [value]) VALUES ('5', 'e');";

conn.query(query, function(err, result) {
        if(err) {
            return console.error('error running insert', err);
        }
    });

O course, you probably won't want to inline your values, in which case you can use parameters and send all the values as an array.

Collectives™ on Stack Overflow

How to insert table valued parameters into Sql Azure from Node?

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related