Save array of data in SQL server using Tedious & Node

Question

I have this issue where I'm receiving an array of data that I need to save in the database. I have to loop through the array and create a row in the table. But the issue is that the loop is iterating to the next index before the first request is even finished

How do I approach this?

code: Below is creating a connection inside a loop so that each request can have a connection

for (let i = 0; i < newroomname.length; i++) {
     var addroomconnection = new Connection(config);
     addroomconnection.on('connect', function (err) {
     // If no error, then good to proceed.
     console.log("Connected");
     addNewRoom(addroomconnection, floorID, newroomname[i]);
   });
     addroomconnection.connect();
}

Below is the function that handles the request:

function addNewRoom(addroomconnection, floorID, newroomname) {
    return new Promise((resolve, reject) => {

        console.log("Writing rows into table room in function addNewRoom()...");

        let roomID = v4();
        // ? Write row into table
        console.log('inside loop');
        const request = new Request(
            `INSERT INTO [dbo].[room] (room_id, floor_id, room_name, time_created, time_modified)
                VALUES ('${roomID}', '${floorID}', '${newroomname}', GETDATE(), GETDATE());`,
            (err, rowCount) => {
                if (err) {
                    console.error(err.message);
                    reject(err);
                } else {
                    console.log(`${rowCount} row(s) for rooms returned`);
                }
            }
        );

        request.on('requestCompleted', function (rowCount, more) {
            console.log('req completed');
         
        });
        addroomconnection.execSql(request);
    });
}

Error

RequestError: Requests can only be made in the LoggedIn state, not the LoggedInSendingInitialSql state
    at Connection.makeRequest (C:\Users\N.A.A\Documents\express-test\node_modules\tedious\lib\connection.js:2208:24)
    at Connection.execSql (C:\Users\N.A.A\Documents\express-test\node_modules\tedious\lib\connection.js:1738:10)
    at C:\Users\N.A.A\Documents\express-test\server.js:477:27
    at new Promise (<anonymous>)
    at addNewRoom (C:\Users\N.A.A\Documents\express-test\server.js:429:12)
    at Connection.<anonymous> (C:\Users\N.A.A\Documents\express-test\server.js:413:21)
    at Connection.emit (node:events:390:28)
    at Connection.emit (C:\Users\N.A.A\Documents\express-test\node_modules\tedious\lib\connection.js:1048:18)
    at Connection.processedInitialSql (C:\Users\N.A.A\Documents\express-test\node_modules\tedious\lib\connection.js:1669:10)
    at C:\Users\N.A.A\Documents\express-test\node_modules\tedious\lib\connection.js:2723:14 {
  code: 'EINVALIDSTATE',
  number: undefined,
  state: undefined,
  class: undefined,
  serverName: undefined,
  procName: undefined,
  lineNumber: undefined
}

That SQL looks wide open to injection attacks. You really need to look up about parameterisation. — Thom A
– Thom A ♦, Commented Sep 29, 2022 at 10:12
Why doesn't it check connection errors? Why does addNewRoom return a Promise when it's not going to .then() it or await it? Or resolve it? — AlwaysLearning
– AlwaysLearning, Commented Sep 29, 2022 at 11:37
trying to make the code work before I clean it up. the promise servers no purpose here @AlwaysLearning — SadCoder
– SadCoder, Commented Sep 30, 2022 at 7:02
@SadCoder You got the answer or shall I post it ? If you got the answer please close it. — Vivek Rahul
– Vivek Rahul, Commented Oct 12, 2022 at 9:24
@VivekRahul thank you for reminding me. I just posted my answer — SadCoder
– SadCoder, Commented Oct 12, 2022 at 11:49

SadCoder · Accepted Answer · 2022-10-12 11:48:38Z

1

I figured this out on my own. It's incredibly easy. All you have to do is run a for loop and concat a string of requests as such:

let stringrequest = '';
for (let i = 0; i < newroomname.length; i++) {
    let roomID = v4();
    stringrequest = stringrequest + `INSERT INTO [dbo].[room] (room_id, floor_id, room_name, room_number, time_created, time_modified)
    VALUES ('${roomID}', '${floorID}', '${newroomname[i]}', ${editroomnumber[i]}, GETDATE(), GETDATE())`;
            }

Then I pass the whole string as one request:

const request = new Request(stringrequest,
     (err, rowCount) => {
       if (err) {
         console.error(err.message);
         reject(err);
     } else {
         console.log(`${rowCount} row(s) for rooms returned`);
       }
      }
   );

answered Oct 12, 2022 at 11:48

SadCoder

234 bronze badges

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

Collectives™ on Stack Overflow

Save array of data in SQL server using Tedious & Node

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related