node js -- mysql (Store procedure)

[pranavs80]

i am having an store procedure in mysql -- which is like this

BEGIN
SELECT 1 AS res;

end

my node js code for this is as 👍

connection.query("call adx.usp_TestNodeJS()",
function(err, results, fields) {
console.log('The solution is: ', results[0].res);
}
);

---

i am not able to to read the data.
instead of store procedure, i use inline query. then it works fine.

please help me out.

thanks
[pranav sheth]

[kai-koch]

#407 #385 #391

[felixge]

@kai-koch thanks for all the help you've provided in the other tickets!

Closing this now.

[pranavs80]

thanks for the quick reply.

but i am not getting how to read the reply of my store procedure.

as it is returning some rows and columns.

[kai-koch]

http://dev.mysql.com/doc/refman/5.5/en/call.html

if you need a procedure for your stuff, you need to make more than on SQL-Statement.
like in the example in the mysql documentation:

mysql> SET @increment = 10;
mysql> CALL p(@Version, @increment);
mysql> SELECT @Version, @increment;
+--------------+------------+
| @Version | @increment |
+--------------+------------+
| 5.5.3-m3-log | 11 |
+--------------+------------+

If you can rewrite it as a stored function you only need to make one SELECT-Statement to get the returned value.

[pranavs80]

Thank you very much. now i am getting the output of store procedure.

C:\nginx\html>node spdbconn.js
The solution is: 2
The solution is 1: [ { usercode: -11, res: '
{ usercode: -10, res: 'Casale Media' },
{ usercode: -9, res: 'mansi' },
{ usercode: -8, res: 'Yellow Hammer Network
{ usercode: -7, res: 'Quadrant One' } ]

this output is in the command window.

how should i read this values in code?

[kai-koch]

function query_callback(err, result) {
    var i,
        ilen;
    if (err) {
        throw err;
    }
    i = 0;
    ilen = result.length;
    while (i < ilen) {
        // Do something with the result:
        console.log ('Row ' + i + ': usercode = "' + result[i].usercode
            + '" res = "' + result[i].res + '"');
        i += 1;
    }
}

[edit: fixed typos and missing counter increment]

[pranavs80]

i am getting result.lenth as 2 instead of 5.
and in result[0] i got that whole above output.

[kai-koch]

I do not get your problem, then.
You get back a resultset of nested Arrays and Objects.
So simply access them, as Array or Object.

if this is in result[0][2]:

result[0][2] = [
    { usercode: -11, res: ''},
    { usercode: -10, res: 'Casale Media' },
    { usercode: -9, res: 'mansi' },
    { usercode: -8, res: 'Yellow Hammer Network'},
    { usercode: -7, res: 'Quadrant One' }
];

simply use result[0][2] in a while or for loop.