Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
-
None
-
None
-
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.
[
[
, meta: [ [ColumnDef] ] ],
OkPacket
,
[
, meta: [ [ColumnDef] ] ]
]
[
,
meta: [
ColumnDef
]
]
OkPacket
[
,
meta: [
ColumnDef
]
]
10014
Attachments
Activity
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.
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?
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?
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:
message: dbResponseError};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.