stored-procedure return results that I do not want

Question

I use node-mysql to call a stored-procedure, result contains some schema I dont want like fieldCount,affectedRows,how to ignore these?

when I use mysql commend to call the procedure there is no these schemas.

procedure:

DELIMITER \\
CREATE PROCEDURE QUERY_USER (IN p_name VARCHAR(15))
BEGIN
    SELECT id,name FROM user WHERE name=p_name;
END\\

Result:

[   
    [
    {
      "id": 2,
      "name": "codar"
    },
    {
      "id": 3,
      "name": "codar"
    }   
    ],   
    {
    "fieldCount": 0,
    "affectedRows": 0,
    "insertId": 0,
    "serverStatus": 34,
    "warningCount": 0,
    "message": "",
    "protocol41": true,
    "changedRows": 0   
    } 
]

score 3 · Accepted Answer · answered Nov 28 '13 at 04:53

The results you are looking for are not members of the array that you are looking at... they are nested in the first element of that array, which is itself another array, which in your example only has one member, but if it had more rows, they would be members of the inner array, not the outer one. You need to move one level in.

With stored procedures, you expect to find the first row of the first result-set at element [0][0], the second row (if there is one) at [0][1], the third (if there is one) at [0][2]... and so on. The results are not in [0] then [1] then [2], they're all in an array nested inside [0].

score 1 · Answer 2 · answered Aug 04 '15 at 03:53

yes, like above said, when you call a procedure to get a query result, the return set is different with direct query, it includes some information like fieldCount,affectedRows. so you can do it in your node code side, like:

var GetgroupList_Sql = "call GetActiveGroup(1); ";
mysqlHelper.ExecuteQuery(GetgroupList_Sql,, function (error, result) {
    if (error) {
        res.json({success: false, data: error});
        return;
    }
    console.log(result[0]);
});

the major point is using result[0] and not result like normal query. that is it:)

reference doc: http://www.sitepoint.com/using-node-mysql-javascript-client/

stored-procedure return results that I do not want

2 Answers2