Node.js and mssql dynamically populate table based on JSON

Question

I am attempting to store some JSON data to a SQL Server table using Node.js and the "mssql" package. However, I need to dynamically generate the table and populate it.

I have the table defined based on whatever JSON data is coming in, now I need to populate it with the JSON data. My problem is I cannot figure out how to dynamically create mssql.Table rows. Doing the following

var sql = require('mssql');

var table = new sql.Table('table');

table.columns.add('one', sql.VarChar(2));
table.columns.add('two', sql.VarChar(2));
table.columns.add('three', sql.VarChar(2));

table.rows.add('1', '2', '3');
table.rows.add('4', '5', '6');
table.rows.add('7', '8', '9');
table.rows.add('10', '11', '12');

table.rows.add(row);

results in

Table {
  name: 'table',
  schema: null,
  database: null,
  path: '[table]',
  temporary: false,
  columns:
   [ { type: [sql.VarChar],
       length: 2,
       name: 'one',
       nullable: undefined,
       primary: undefined },
     { type: [sql.VarChar],
       length: 2,
       name: 'two',
       nullable: undefined,
       primary: undefined },
     { type: [sql.VarChar],
       length: 2,
       name: 'three',
       nullable: undefined,
       primary: undefined } ],
  rows:
   [ [ '1', '2', '3' ],
     [ '4', '5', '6' ],
     [ '7', '8', '9' ],
     [ '10', '11', '12' ] ] }

When adding

var row = {one: '13', two: '14', three: '15'};
table.rows.add(row);
row = ['16', '17', '18']
table.rows.add(row);

I get

Table {
  name: 'table',
  schema: null,
  database: null,
  path: '[table]',
  temporary: false,
  columns:
   [ { type: [sql.VarChar],
       length: 2,
       name: 'one',
       nullable: undefined,
       primary: undefined },
     { type: [sql.VarChar],
       length: 2,
       name: 'two',
       nullable: undefined,
       primary: undefined },
     { type: [sql.VarChar],
       length: 2,
       name: 'three',
       nullable: undefined,
       primary: undefined } ],
  rows:
   [ [ '1', '2', '3' ],
     [ '4', '5', '6' ],
     [ '7', '8', '9' ],
     [ '10', '11', '12' ],
     [ [Object] ],
     [ [Array] ] ] }

which clearly is not what I want.

Doing something like

var row = new sql.Table.row;
row.item.add('13')
row.item.add('14')
row.item.add('15')
table.rows.add(row);

is what I am trying to do, which would allow me to add whatever values I want to the table without hard coding them. Looking through the mssql package docs, I cannot find anything that mentions anything of the sort.

Any help would be much appreciated! Thanks!

Raghav Garg · Accepted Answer · 2017-08-24 16:13:37Z

3

You need to destructure the array in order to send the values of array as individual, you can do something like:

var rowObj = { one: '13', two: '14', three: '15' };
var rowArr = ['16', '17', '18'];

// converting obj to arr of object value
var rowObjArr = Object.values(rowObj);

// adding array to the table, using array destructuring
table.rows.add(...rowObjArr);
table.rows.add(...rowArr);

With array spread operator (...array), you can do something like this:

function myFunction(x, y, z) { }

var args = [0, 1, 2];
myFunction(...args);

which will enable me to send values of the array as an individual argument to the function.

Reference: Spread in function calls

edited Aug 24, 2017 at 16:13

answered Aug 24, 2017 at 16:07

Raghav Garg

3,7072 gold badges26 silver badges33 bronze badges

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

1 Comment

David Over a year ago

Fantastic! That is indeed what I was missing. I really appreciate the quick help!

Collectives™ on Stack Overflow

Node.js and mssql dynamically populate table based on JSON

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related