node.js - NODEJS - mysqlPool.getConnection - calling stored proc within MYSQL - out return value -
i have stored proc in mysql working fine. trying call nodejs. how out value of stored proc in 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 ..... thanks phil
========================
i still having issues...
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, 'end' event emitted after console.log('error ============= ', err); }) .on('fields', function(fields) { // field packets rows follow console.log('fields ============= ', fields); //res.status(200).jsonp({insertresults:fields}); }) .on('result', function(row) { // pausing connnection useful if processing involves i/o // connection.pause(); console.log('results ============= ', row); //res.status(200).jsonp({insertresults:row}); //processrow(row, function() { // connection.resume(); //}); }) .on('end', function() { // 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 .... the output empty.
no records inputed .
========= 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 } =============
i added more test stuff , see falling .on('result , 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, 'end' event emitted after console.log('error ============= ', err); return; }) .on('fields', function(fields) { // field packets rows 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 connnection useful if 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() { // rows have been received });
add select @p3; in con.query statement, after call proc_member_insert
hope helps!
Comments
Post a Comment