我在 mysql 中有一个存储过程工作正常。我试图用 NODEJS 调用它。如何在 nodejs 中获取存储过程的输出值？

  mysqlPool.getConnection(function(error,conn){
        conn.query(" SET @p1   =   '"  + P_MEMBER_ID           + "'  ; " +
            " SET @p2      =       '"  + P_MEMBER_ID_TEST      + "'  ; " +
            " SET @p3      =       @insertResults'"  + "'  ; " +
            "CALL PROC_MEMBER_INSERT(" +
                " @p1   ,  "  +
                " @p2   ,  "  +
                " @p3 )", function(error,results)
            {
                if (error)
                {
                    console.error(error);
                    throw error;
                }else{
                    console.log(results);
                     res.status(200).jsonp({insertResults:test[2][0]});       
                };
            });
            conn.release();
        });





      MYSQL stored proc example. 
      ..
     CREATE DEFINER=`phil`@`%` PROCEDURE `PROC_MEMBER_INSERT`(
       in P_MEMBER_ID         INT,
       in P_MEMBER_ID_TEST    INT,
      out RESULT_MESSAGE          varchar(120)
      )
      BEGIN
       .....

谢谢 菲尔

========================

我还有问题...

   var connection = mysql.createConnection({
        host: '11.11.11.11',
        user: 'phil',
        password: 'password',
        port: 3306,
        database: 'testdb',
        connectionLimit : 100,
        multipleStatements: true
    });



    var query = connection.query(" SET @p1      =       '"  + P_MEMBER_ID                    + "'  ; " +
                                 " SET @p2      =       '"  + P_MEMBER_ID_TEST               + "'  ; " +
                                 " SET @p3      =            @insertResults "                + "   ; " +
         "CALL PROC_MEMBER_INSERT(" +
        " @p1   ,  "  +
        " @p2   ,  "  +
        " @p3 ); select @insertResults;");
    query
        .on('error', function(err) {
            // Handle error, an 'end' event will be emitted after this as well
            console.log('error  ============= ', err);
        })
        .on('fields', function(fields) {
            // the field packets for the rows to follow

            console.log('fields ============= ', fields);

            //res.status(200).jsonp({insertResults:fields});       
        })
        .on('result', function(row) {
            // Pausing the connnection is useful if your processing involves I/O
            // connection.pause();

            console.log('results ============= ', row);

            //res.status(200).jsonp({insertResults:row});      

            //processRow(row, function() {
            //    connection.resume();
            //});
        })
        .on('end', function() {
            // all rows have been received
        });


  MYSQL stored proc example. 
  ..
 CREATE DEFINER=`phil`@`%` PROCEDURE `PROC_MEMBER_INSERT`(
   in P_MEMBER_ID         INT,
   in P_MEMBER_ID_TEST    INT,
  out RESULT_MESSAGE          varchar(120)
  )
  BEGIN
   ....

输出看起来是空的。

没有输入记录。

   ========= output ==============

           api: 2 -- end PROC_MEMBER_INSERT
            api: 2 P_MEMBER_ID ---------->  5
            api: 2 P_MEMBER_ID_TEST ----->  1195
            results =============  { fieldCount: 0,
              affectedRows: 0,
              insertId: 0,
              serverStatus: 10,
              warningCount: 0,
              message: '',
              protocol41: true,
              changedRows: 0 }
            results =============  { fieldCount: 0,
              affectedRows: 0,
              insertId: 0,
              serverStatus: 10,
              warningCount: 0,
              message: '',
              protocol41: true,
              changedRows: 0 }
            results =============  { fieldCount: 0,
              affectedRows: 0,
              insertId: 0,
              serverStatus: 10,
              warningCount: 0,
              message: '',
              protocol41: true,
              changedRows: 0 }
            results =============  { fieldCount: 0,
              affectedRows: 1,
              insertId: 0,
              serverStatus: 10,
              warningCount: 0,
              message: '',
              protocol41: true,
              changedRows: 0 }
            fields =============  [ { catalog: 'def',
                db: '',
                table: '',
                orgTable: '',
                name: '@RESULT_MESSAGE',
                orgName: '',
                charsetNr: 63,
                length: 16777216,
                type: 251,
                flags: 128,
                decimals: 31,
                default: undefined,
                zeroFill: false,
                protocol41: true } ]
            results =============  { '@insertResults': null }

=============

我添加了更多测试内容，现在看到它正在下降到 .on('result and always null.

          results =============  { fieldCount: 0,
            affectedRows: 0,
            insertId: 0,
            serverStatus: 10,
            warningCount: 0,
            message: '',
            protocol41: true,
            changedRows: 0 }
          row user not found
          results =============  { fieldCount: 0,
            affectedRows: 0,
            insertId: 0,
            serverStatus: 10,
            warningCount: 0,
            message: '',
            protocol41: true,
            changedRows: 0 }
          row user not found
          results =============  { fieldCount: 0,
            affectedRows: 0,
            insertId: 0,
            serverStatus: 10,
            warningCount: 0,
            message: '',
            protocol41: true,
            changedRows: 0 }
          row user not found
          results =============  { fieldCount: 0,
            affectedRows: 1,
            insertId: 0,
            serverStatus: 2,
            warningCount: 0,
            message: '',
            protocol41: true,
            changedRows: 0 }


      .on('error', function(err) {
            // Handle error, an 'end' event will be emitted after this as well
            console.log('error  ============= ', err);
            return;
        })
        .on('fields', function(fields) {
            // the field packets for the rows to follow
            console.log('fields ============= ', fields);



            if (fields && fields.length)
                console.log('fields user found :)');
            else
                console.log('fields user not found');




            //res.status(200).jsonp({winkResults:fields}); 
        })
        .on('result', function(row) {
            // Pausing the connnection is useful if your processing involves I/O
            // connection.pause();

            console.log('results ============= ', row);

            if (row && row.length)
                console.log('row user found :)');
            else
                console.log('row user not found');


            //res.status(200).jsonp({winkResults:row});      

            //processRow(row, function() {
            //    connection.resume();
            //});
        })
        .on('end', function() {


            // all rows have been received
        });

最佳答案

在您的 con.query 语句中添加 select @p3;，就在 CALL PROC_MEMBER_INSERT 之后

希望这对您有所帮助!

关于mysql - NODEJS - mysqlPool.getConnection - 在 MYSQL 中调用存储过程 - 输出返回值，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/33140391/