Ad

Node Mysql Async Await Issue While Using Transaction

I am writing mysql query with transaction but its not waiting to be completed and returns the result even before the function has executed.

Can someone please help me to know how to make function to wait for response?

The calling function is something like this:

deductUserPoint = await helper.deductUserPoint2(data)
console.log('===============================================')
  console.log(deductUserPoint)
  if (deductUserPoint.isError === true) {
    let error = {}
    error.isError = true
    error.message = 'Unable to deduct amount'
    error.error = deductUserPoint.error
    res.status(200).json(error)
  } else {
    res.status(200).json({ 'isError': false, 'message': 'success' })
  }

I always get deductUserPoint as undefined because it does not wait for deductUserPoint2 to return response

The deductUserPoint2 is as follows:

async deductUserPoint2(params) {
    try {
      this.db.connection.getConnection(function(err, conn) {
        console.log('1111111111')
        conn.beginTransaction(function(err) {
        if (err) {
          throw err 
        }
        console.log(params)

        console.log('2222222222')
        conn.query('SELECT id, `expert_id`, user_id, status FROM `call` WHERE `id` = ?', [params.callId], function (error, callInfo, fields) {
          if (error) {
            return conn.rollback(function() {
              throw error
            })
          }

          console.log('33333333')
          let callLength = null
          if (params.callMinute === 'entire_day') {
            callLength = 'entire_day'
          } else {
            const callMinutes = Math.round(params.callMinute)
            if (callMinutes <= 30) {
              callLength = '30_min'
            } else if (callMinutes >= 31 && callMinutes <= 60) {
              callLength = '60_min'
            } else if (callMinutes >= 61 && callMinutes <= 90) {
              callLength = '90_min'
            } else if (callMinutes >= 91) {
              callLength = '120_min'
            }
          }
          console.log('4444444444')
          conn.query('SELECT `amount` FROM `expert_charges` WHERE `status` = "active" AND `call_length` = ? AND `expert_id` = ?', [callLength, callInfo[0].expert_id], function (error, points, fields) {
            if (error) {
              return conn.rollback(function() {
                throw error
              })
            }
            console.log('555555555555')
            let data = {
              fromUserId: callInfo[0].user_id,
              fromUserType: 'user',
              to_user_id: 0,
              to_user_type: null,
              category: 'call',
              type: 'debited',
              callId: params.callId,
              amount: points[0].amount,
              note: params.note,
              point: points[0].amount,
              current_balance_point: 0
            }

            let input = Object.values(data)

            conn.query('INSERT INTO wallet (`from_user_id`, `from_user_type`, `to_user_id`, `to_user_type`, `category`, `type`, `call_id`, `amount`, `note`, `point`, `current_balance_point`) VALUES ?', [[input]], function (error, wallet, fields) {
              if (error) {
                return conn.rollback(function() {
                  throw error
                })
              }
              console.log('666666666666')
              conn.query('UPDATE user SET total_points = total_points - ? WHERE id = ?', [points[0].amount, callInfo[0].user_id], function (error, updateUserPoint, fields) {
                if (error) {
                  return conn.rollback(function() {
                    throw error
                  })
                }
                console.log('7777777777')
                conn.commit(function(err) {
                  if (err) {
                    return conn.rollback(function() {
                      throw err
                    })
                  }
                  console.log('888888888')
                  return {
                    "isError": false,
                    "status": "success"
                  }
                })

                })
              })
          })
        })     
      })
    })

    } catch (error) {
      console.log(error)
      return {
        "isError": true,
        "error": error.toString()
      }
    }

Error it prints is :

undefined
(node:4197) UnhandledPromiseRejectionWarning: TypeError: Cannot read property 'isError' of undefined
    at router.post (/Users/msmexmac/node/msmex-backend/msmex-api/api/wallet.js:120:23)
    at process._tickCallback (internal/process/next_tick.js:68:7)
(node:4197) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 1)
(node:4197) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.
1111111111
{ callId: 5, note: 'Deduction for call', callMinute: 25 }
2222222222
33333333
4444444444
555555555555
666666666666
7777777777
888888888
Ad

Answer

You can't use async functions like that, you have to return a promise to be able to wait for your callbacks. Your function would have to end up like this:

function deductUserPoint2(params) {
    return new Promise(function (resolve, reject) {
        try {
            this.db.connection.getConnection(function (err, conn) {
                console.log('1111111111')
                conn.beginTransaction(function (err) {
                    if (err) {
                        return reject(err)
                    }
                    console.log(params)

                    console.log('2222222222')
                    conn.query('SELECT id, `expert_id`, user_id, status FROM `call` WHERE `id` = ?', [params.callId], function (error, callInfo, fields) {
                        if (error) {
                            return conn.rollback(function () {
                                return reject(error)
                            })
                        }

                        console.log('33333333')
                        let callLength = null
                        if (params.callMinute === 'entire_day') {
                            callLength = 'entire_day'
                        } else {
                            const callMinutes = Math.round(params.callMinute)
                            if (callMinutes <= 30) {
                                callLength = '30_min'
                            } else if (callMinutes >= 31 && callMinutes <= 60) {
                                callLength = '60_min'
                            } else if (callMinutes >= 61 && callMinutes <= 90) {
                                callLength = '90_min'
                            } else if (callMinutes >= 91) {
                                callLength = '120_min'
                            }
                        }
                        console.log('4444444444')
                        conn.query('SELECT `amount` FROM `expert_charges` WHERE `status` = "active" AND `call_length` = ? AND `expert_id` = ?', [callLength, callInfo[0].expert_id], function (error, points, fields) {
                            if (error) {
                                return conn.rollback(function () {
                                    return reject(error)
                                })
                            }
                            console.log('555555555555')
                            let data = {
                                fromUserId: callInfo[0].user_id,
                                fromUserType: 'user',
                                to_user_id: 0,
                                to_user_type: null,
                                category: 'call',
                                type: 'debited',
                                callId: params.callId,
                                amount: points[0].amount,
                                note: params.note,
                                point: points[0].amount,
                                current_balance_point: 0
                            }

                            let input = Object.values(data)

                            conn.query('INSERT INTO wallet (`from_user_id`, `from_user_type`, `to_user_id`, `to_user_type`, `category`, `type`, `call_id`, `amount`, `note`, `point`, `current_balance_point`) VALUES ?', [[input]], function (error, wallet, fields) {
                                if (error) {
                                    return conn.rollback(function () {
                                        return reject(error)
                                    })
                                }
                                console.log('666666666666')
                                conn.query('UPDATE user SET total_points = total_points - ? WHERE id = ?', [points[0].amount, callInfo[0].user_id], function (error, updateUserPoint, fields) {
                                    if (error) {
                                        return conn.rollback(function () {
                                            return reject(error)
                                        })
                                    }
                                    console.log('7777777777')
                                    conn.commit(function (err) {
                                        if (err) {
                                            return conn.rollback(function () {
                                                return reject(err)
                                            })
                                        }
                                        console.log('888888888')
                                        return resolve({
                                            "isError": false,
                                            "status": "success"
                                        })
                                    })

                                })
                            })
                        })
                    })
                })
            })

        } catch (error) {
            console.log(error)
            return resolve({
                "isError": true,
                "error": error.toString()
            })
        }
    })
}

Then you use reject(err) instead of throw err and resolve(value) instead of return value.

Another approach is using utils.promisify as @georg suggested.

Ad
source: stackoverflow.com
Ad