CODEWITHSUNDEEP
node.jssql-serverpromisees6-promisetedious
ang
ang

Reputation: 1581

How to resolve Node.js mssql promise

I am trying to use the response from a SQL Server stored procedure and pass it into another stored procedure to log the response data.

However, I'm not sure how to chain them together as it appears they each need their own connection pool.

Attempt 1

// [email protected]

exports.chain = (req, res) => {
  sql.connect(config.properties).then(pool => {
    return pool.request()
      .execute("chain")
      .then(response => {
        return pool.request()
          .input("param", sql.NVarChar(300), result[0][0]["response"])
          .execute("chain2")
          .then(result => res.send(result))
          .catch(err => res.send(err))
      })
      .catch(err => res.send(err))
  })
}

// returns {}

Attempt 2

exports.chain = (req, res) => {
  sql.connect(config)
    .then(pool => {
      return pool.request()
        .execute("chain")
    }).then(result => {
      return pool.request()
        .input("param", sql.NVarChar(300), result[0][0]["response"])
        .execute("chain2")
    }).then(result => {
      res.send(result)
    }).catch(err => {
        // ... error checks
    })

  sql.on('error', err => {
    // ... error handler
  })
}

// Throws error HTTP Status: 500, HTTP subStatus: 1013

Attempt 3

sql.connect(config.properties).then(pool => {
  return pool.request()
    .execute("chain")
  }).then(response => {
    pool.request()
      .execute("chain2")
      .input('param', sql.NVarChar(300), response[0][0]['response'])
      .then(response => res.send(response))
      .catch(err => res.send(err))
})

// Throws error HTTP Status: 500, HTTP subStatus: 1013

Timeouts might be related to

DeprecationWarning: Buffer() is deprecated due to security and usability issues. Please use the Buffer.alloc(), Buffer.allocUnsafe(), or Buffer.from() methods instead.

How would I take the response from the first stored procedure and pass it into a second one for logging?

Upvotes: 1

Views: 2295

Answers (1)

cnps
cnps

Reputation: 151

A connection pool is a an instance consisting of multiple TDS connections, so you should be able to connect several procedures to the same pool, as a new request only accuires one of the TDS connections. Maybe it's a flaw in your config probs.

In your third example. Your input comes after execute, so that's probably causing the error.

As a request returns a promise, you can chain them with then, as you are already doing, but variables in each then are not in the same scope, so you can't access a user, received from the user record, when you are in the then for a different request.

Alternatively, you can also nest them via callback, if you need to access variables across responses:

sql.connect(config.properties).then(pool => {
    pool.request()
        .execute("chain", (error, response) => {
            const row = response.recordset;

            pool.request()
                .input('param', sql.NVarChar(300), row.foo)
                .execute("chain2", (err, res) => {
                    const row_ = res.recordset;

                    // send response to client
                });
        });
}).catch(err => {
    // error handling here
});

Best solution, would probably be to return your stored procedure as JSON, and you would only need one request anyway. Downside is, you probably need more specialized procedures.

Upvotes: 1

Related Questions