| Author |
Message
|
| mqjeff |
 Posted: Thu Feb 02, 2017 9:28 am Post subject: |
 |
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| wmqstankela wrote: |
| GetEmployeeWorkHours procedure is select query into SQL Server db. Query return 3 columns with many rows. How to store this result into message tree? |
set abc=call
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
 |
| wmqstankela |
| Posted: Thu Feb 02, 2017 9:34 am Post subject: |
|
|
Voyager
Joined: 29 Feb 2016
Posts: 94
|
I've tried that and got syntax error:
Syntax error. Valid options include: = - ( + .NET CASE CAST COUNT CURRENT_DATE CURRENT_GMTDATE CURRENT_GMTTIME CURRENT_GMTTIMESTAMP CURRENT_TIME CURRENT_TIMESTAMP DATE DECIMAL EVAL EXISTS EXTRACT FALSE FOR GMTTIME GMTTIMESTAMP IDENTIFIER INTERVAL LIST LOCAL_TIMEZONE LOG MAX MIN NOT NULL OVERLAY PARSE PASSTHRU POSITION RAND ROUND ROW SELECT SIMPLE_FUNCTION SUBSTRING SUM THE TIME TIMESTAMP TRIM TRUE UNKNOWN URLENCODE UUIDASBLOB UUIDASCHAR |
|
| Back to top |
|
|
| wmqstankela |
| Posted: Thu Feb 02, 2017 9:48 am Post subject: |
|
|
Voyager
Joined: 29 Feb 2016
Posts: 94
|
I've added DYNAMIC RESULT SETS 1 into CREATE PROCEDURE statement and CALL GetEmployeeWorkHours(empId, dateFrom, dateTo, OutputLocalEnvironment.RetVal[]);
Thank you all for help! |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Feb 02, 2017 9:55 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| wmqstankela wrote: |
| I've added DYNAMIC RESULT SETS 1 into CREATE PROCEDURE statement and CALL GetEmployeeWorkHours(empId, dateFrom, dateTo, OutputLocalEnvironment.RetVal[]); |
Be aware that with a large result set, this (valid) construction will result in heavy memory & CPU use.
If you don't expect a large result set, this probably isn't a problem. But I'd recommend you add something to the stored procedure so that it can't return an unexpectedly large amount of data.
For example, someone accidentally sets dateFrom and dateTo 15 years apart. Or someone inserts the same combination of empId and whatever date the range is checked against 10,000 times due to an application bug.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| wmqstankela |
| Posted: Fri Feb 03, 2017 1:01 am Post subject: |
|
|
Voyager
Joined: 29 Feb 2016
Posts: 94
|
Thanks Vitor on your suggestion.
I don't expect to large result set, but you are totally right. I can't make any changes on stored procedure.
First I tried to use:
SET OutputRoot.XML.ResultSet[] = CALL GetEmployeeWorkHours(empId,dateFrom,dateTo);
and CREATE PROCEDURE without DYNAMIC RESULT SETS 1
but I've got syntax error. How can I store this result set without using dinamic result set? |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Fri Feb 03, 2017 5:27 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20696
Location: LI,NY
|
| wmqstankela wrote: |
Thanks Vitor on your suggestion.
I don't expect to large result set, but you are totally right. I can't make any changes on stored procedure.
First I tried to use:
SET OutputRoot.XML.ResultSet[] = CALL GetEmployeeWorkHours(empId,dateFrom,dateTo);
and CREATE PROCEDURE without DYNAMIC RESULT SETS 1
but I've got syntax error. How can I store this result set without using dinamic result set? |
Don't know that you can... The whole purpose of the procedure is to return a dynamic result set... and you said you could not change the procedure. I assume you mean the SQL procedure, not its definition in ESQL... 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Feb 03, 2017 5:32 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
If you can't change the stored procedure, you're probably stuck 
I'm sure you don't expect a large result set. Either of the scenarios I've mentioned will give you one. It might be worth mentioning that to whoever owns the stored procedure and making sure they insert proper safety code for you.
And if they say
| Quote: |
| we don't need to change the stored procedure because that will never happen |
get it in writing, on paper and keep it safe so when it does happen and your code blows out, you can just wave it at them & say, "not my problem".
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| wmqstankela |
| Posted: Fri Feb 03, 2017 6:21 am Post subject: |
|
|
Voyager
Joined: 29 Feb 2016
Posts: 94
|
I will restrict values of input parameters inside Compute node for maximum a month period of time. This is enough for my requirements and will limit result set to appropriate size.
Thanks again Vitor and fjb_saper! |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Feb 03, 2017 6:38 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| wmqstankela wrote: |
| This is enough for my requirements and will limit result set to appropriate size. |
Any date based size is subject to being overwhelmingly large, either from a large set of incoming data, or from a programmer who coded a loop wrong.
Better to figure out some way to page through the data...
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
|
|
