[CONJS-189] connector doesn't seem to support multi statements. Created: 2022-02-15  Updated: 2022-04-29

Status: Open
Project: MariaDB Connector/node.js
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Tom Miller Assignee: Diego Dupin
Resolution: Unresolved Votes: 0
Labels: None
Environment:

node => v16.13.1
"express": "^4.17.2",
"joi": "^17.6.0",
"mariadb": "^2.5.5",


 Description   

We use stored procedures for everything. We also use sequences and want to return the sequence number back from the stored procedure as an out parameter. We still haven't gotten to select statements yet in our prototype work, but that is beginning to scare me too. I am new to JavaScript, so it could be me making a newbie mistake with promises. Here is my route.

authProcRoutes.post('/userinvite', async (req, res, next) => {
 
    let conn;
 
    let emailSent = false;
 
 
 
    console.log(req.body);
 
 
 
    try {
 
        // validate input 
        const inputValidated = await validateUserInviteInput(req.body); 
        if (typeof inputValidated === 'string' || inputValidated instanceof String) {
 
            return res.status(400).send((inputValidated)); 
        }
 
 
 
        // process security settings
 
        const exprDateTimeUTC = new Date(getDatePlus(48).toUTCString());
 
        const userPWResetExpr = exprDateTimeUTC.toISOString().replace('Z','').replace('T', ' ');
 
        const userPWResetToken = await hashPassword(userPWResetExpr + req.body.userEmail);
 
 
 
        // connect to database
 
        const mdbpool = getPool();
 
        conn = await mdbpool.getConnection();
 
        console.log("connected ! connection id is " + conn.threadId);
 
 
 
        const dbResponse = await conn.query(
 
            'CALL auth_user_P__userinvite_sp(?,?,?,?,?,?,?,?,?,@NewRcrdID); SELECT @NewRcrdID;', [
 
                req.body.customerID,  
                req.body.userEmail, 
                req.body.userFirstName, 
                req.body.userLastName, 
                userPWResetToken,
 
                userPWResetExpr,
 
                req.body.userType,
 
                req.body.userSummary,
 
                req.body.userRequesterID 
            ]
 
        );
 
    
 
 
        console.log(dbResponse);
 
        const dbrSQL1 = dbResponse[0];
 
        const dbrOkPacket = dbResponse[1];
 
        const dbrSQL2 = dbResponse[2];
 
        const resObject = dbrSQL2[0]; 
        console.log(dbrSQL1);
 
        console.log(dbrOkPacket);
 
        console.log(dbrSQL2);
 
        console.log(resObject['@NewRcrdID']);
 
 
 
        if (dbResponse[1].affectedRows > 1) {
 
            // emailSent = code to send email 
 
 
        };  
 
 
        if (conn) conn.end();
 
        console.log('conn.end()');
 
        res.send('Invite sent to ' + req.body.userFirstName);
 
    } catch (err) {
 
        if (conn) conn.end();
 
        console.error("Catch Block: ", err);
 
        res.status(500).send('Error: ' + err.text);
 
    }
 
});

And I get this as my result from my console.log deconstruction of the dbResponse object:
There has to be a better way to get to the data vs what I have in my function. I can't imagine doing this kind of stuff for 100 rows of data vs one out param.
[
[

{ '@NewRcrdID': null }

, meta: [ [ColumnDef] ] ],
OkPacket

{ affectedRows: 2, insertId: 0, warningStatus: 0 }

,
[

{ '@NewRcrdID': 10014 }

, meta: [ [ColumnDef] ] ]
]
[

{ '@NewRcrdID': null }

,
meta: [
ColumnDef

{ _parse: [StringParser], collation: [Collation], columnLength: 16777216, columnType: 251, flags: 128, scale: 39, type: 'LONG_BLOB' }

]
]
OkPacket

{ affectedRows: 2, insertId: 0, warningStatus: 0 }

[

{ '@NewRcrdID': 10014 }

,
meta: [
ColumnDef

{ _parse: [StringParser], collation: [Collation], columnLength: 20, columnType: 8, flags: 128, scale: 0, type: 'LONGLONG' }

]
]
10014

 Comments   
Comment by Tom Miller [ 2022-02-15 ]

I am new to JavaScript and I may be missing some syntatical sugar, but this is what I came up with as a work around:

        const dbResponseError = ((dbResponse[0])[0])['@full_error'];
 
        if (dbResponseError) throw {
 
            name: 'Database Error:',
 
            message: dbResponseError
 
        };
 
        const statement2Res = ((dbResponse[2])[0])['@NewRcrdID'];

I am hoping I am missing something or you will add something to the system to make this information more accessible for multi-statement queries without jumping through hoops.

Comment by Diego Dupin [ 2022-02-18 ]

I'm not sure to understand the problem.

With actual driver, if you have some OUT parameters, you'll have to use user variable like you indicate. 

await conn.query('CALL auth_user_P__userinvite_sp(?,?,?,?,?,?,?,?,?,@NewRcrdID)', [params...]);
 
const dbResponse = await conn.query('SELECT @NewRcrdID;');

With 3.0 release (before the end of the month), it can be simplier : 

const dbResponse = await conn.execute('CALL auth_user_P__userinvite_sp(?,?,?,?,?,?,?,?,?,?,?)', [params... ]);

output parameters will be returned as a resultset without need to use user variables.

Comment by Tom Miller [ 2022-02-18 ]

My concern is how hard it is to get to the stuff in the result.
I am completely fine with @NewRcrdID but it will be nice not to have to put the out params.
And the @NewRrcdID comes back in the response. There are actual 3 parts to the response:

  • response[0] -first SQL statement or error for first SQL statement
  • response[1] - statistics of first SQL statement (affectedRows etc)
  • response[2] - select @NewRcrdID; // wish it would drop the @ sign on the column name as it makes it much harder to get to the data.

((dbResponse[0])[0])['@full_error'];
 
((dbResponse[2])[0])['@NewRcrdID'];

It currently doesn't throw an error either. I have to do this manually:

const dbResponseError = ((dbResponse[0])[0])['@full_error'];
 
        if (dbResponseError) throw {
 
            name: 'Database Error:',
 
            message: dbResponseError
 
        };

My main problem is needing to access everything like this:

const dbResponseError = ((dbResponse[0])[0])['@full_error'];
 
 
 
const strUserId = (((dbResponse[0])[0]).UserID).toString();

This is very error prone.

Something like this is more logical and less error prone:

dbResponse.error.code;
 
dbResponse.error.message;
 
(dbResponse.data[0].UserID).toString();  // first record or single row
 
dbResponse.data[0] // rows of data
 
dbResponse.data[1].NewRcrdID;
 
dbResponse.res.affectedRows;

Or is this available already and I just don't know it?

Do you need testers for the new connector?

Comment by Tom Miller [ 2022-04-29 ]

I have upgraded to version 3. I like the new response, but

It doesn't seem you have the ability for me to pick up the error. It doesn't throw an error and the error message is the same old response as before. Now I have to figure out which "response" envelope is being returned and then pick through the rubble.

It would be nice to have a dbResponse.error.code and dbResponse.error.msg

This is the easiest one to work with I have seen so far (mssql):

console.log(result.recordsets.length) // count of recordsets returned by the procedure
console.log(result.recordsets[0].length) // count of rows contained in first recordset
console.log(result.recordset) // first recordset from result.recordsets
console.log(result.returnValue) // procedure return value
console.log(result.output) // key/value collection of output values
console.log(result.rowsAffected) // array of numbers, each number represents the number of rows affected by executed statement

If I have 5 statements in the stored procedure, there are 5 numbers in the "rowsAffected".

Can I get the best practices for catching an error with version 3.0?

Generated at Thu Feb 08 03:23:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.