CODEWITHSUNDEEP
node.jssql-serverlambdabuffertable-valued-parameters
Karthik
Karthik

Reputation: 222

How to pass buffer data in TVP from node.js to sql server in lambda?

I have a lambda function to save multiple records in sql server via stored procedure. I am passing data from lambda as table valued parameters(TVP).

I am successfully able to send 2 columns of type varchar in TVP but i need to pass a 3rd column which contains buffer data of a document which is to be saved in sql server table(column type is Image).

Data in documentList is as below

 [ 'abc', 'SIGNED', <Buffer 25 50 44 46 ... > ]

When I run the code, I get the error in console as "RequestError: The data for table-valued parameter "@documents" doesn't conform to the table type of the parameter. SQL Server error is: 8029, state: 2"

let list = []
    for (let i = 0; i < documentList.length; i++) {
        list.push({
            "id": documentList[i].id,
            "status": documentList[i].status,
            "fileContent": documentList[i].fileContent
        });
    }
    if (list.length > 0) {
        var rowList = []; 
        for (var i = 0; i < list.length; i++) {
            var paramList = []
            Object.keys(list[i]).forEach(key => {
                paramList.push(list[i][key]);
            });
            rowList.push(paramList);
        }
        var table = {
            columns: [
                { name: "id", type: TYPES.VarChar, length: 200 },
                { name: "status", type: TYPES.VarChar, length: 50 },
                { name: "fileContent", type: TYPES.Image }
            ],
            rows: rowList
        };
        var request = new Request(spUpdateDocumentStatus,
            function (err) {
                if (err) {
                    console.log(err);
                }
               connection.close();
            });
        request.addParameter('documents', TYPES.TVP, table);
        connection.callProcedure(request);
    }

Data in variable "table" while execution is as below

{ columns:
   [ { name: 'id', type: [Object], length: 200 },
     { name: 'status', type: [Object], length: 50 },
     { name: 'fileContent', type: [Object] } ],
  rows:
   [ [ 'abc',
       'SIGNED',
       <Buffer 25 50 44 46 ... > ] ] }

Table type that I have created in sql server is as below

CREATE TYPE [dbo].[TestDocuments] AS TABLE(
    [id] [varchar] (200),
    [status] [varchar] (50),
    [fileContent] [image]
)

Stored procedure that is being called is as below

CREATE PROCEDURE [dbo].[spUpdateDocumentStatus] @documents TestDocuments readonly
AS
BEGIN
    -- logic
END

Note: Without using TVP, if I pass the same buffer data what i have above as type Image to the stored procedure(modified the SP to accept Image datatype) then the code works.

Upvotes: 1

Views: 1809

Answers (1)

Santhosh Kumar Jayaraman
Santhosh Kumar Jayaraman

Reputation: 200

Try varBinary(max) instead of Image and it should be fine.

 var table = {
            columns: [
                { name: "id", type: TYPES.VarChar, length: 200 },
                { name: "status", type: TYPES.VarChar, length: 50 },
                { name: "fileContent", type: TYPES.VarBinary}
            ],
            rows: rowList
        };

and change table type to

CREATE TYPE [dbo].[TestDocuments] AS TABLE(
    [id] [varchar] (200),
    [status] [varchar] (50),
    [fileContent] [varBinary](max)
)

Upvotes: 1

Related Questions