我有一个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)并且总是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
之后
希望这对你有帮助!
您应该创建一个支持多条语句的连接;
var connection = mysql.createConnection({multipleStatements: true});
执行查询时,out值可以通过如下字段和结果访问:
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 ');)";
query
.on('fields', function(fields, index) {
// the fields for the result rows that follow
})
.on('result', function(row, index) {
// index refers to the statement this result belongs to (starts at 0)
});
字段和行数组应该保留过程的值
我不能测试它,但是如果输出参数在字段数组中不存在,那么-如果你的逻辑与此一致-,你可以在你的过程调用中添加另一个SELECT
语句,它访问输出,如下所示。
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 @p3;")";
query
.on('fields', function(fields, index) {
// the fields for the result rows that follow
})
.on('result', function(row, index) {
// index refers to the statement this result belongs to (starts at 0)
});
console.log('n Output ' + row[#index_of_last_select]);
引用:https://github.com/felixge/node-mysql#multiple-statement-querieshttp://dev.mysql.com/doc/refman/5.5/en/stored-routines-syntax.html
修复了我的问题与一个简单的选择从双。
select ' insert '作为result from dual;
或
select 'Fail' as result from dual;
我没有使用或弄清楚如何获得out参数。下次我会在这个问题上花更多的时间。
谢谢菲尔。