| Author |
Message
|
| Gideon |
 Posted: Tue Nov 08, 2011 11:41 am Post subject: Deferencing a fow from DB2 |
 |
|
Chevalier
Joined: 18 Aug 2009
Posts: 403
|
After viewing the following doc:
I am trying to dereference a row derived from a SLECT statement of a DB2 table
I am trying to get the pid and price values in separate fields.
I wrote the following code:
| Code: |
DECLARE cmd CHARACTER;
DECLARE row1 ROW;
SET cmd = 'select price, pid from db2inst1.catalog where pid = 1';
--SET pid = PASSTHRU(cmd);
SET row1.result[] = PASSTHRU(cmd);
DECLARE ref1 REFERENCE TO row1.results[1];
SET OutputLocalEnvironment.Variables.Ret3 = SQLCODE;
SET OutputLocalEnvironment.Variables.Ret31 = row1.result[1];
SET OutputLocalEnvironment.Variables.Ret32 = ref1; |
From that I get the following in the Local Environment:
| Code: |
(0x01000000:Name):Variables = (
(0x03000000:NameValue):Ret3 = 0 (INTEGER)
(0x01000000:Name ):Ret31 = (
(0x03000000:NameValue):PRICE = 1.00 (DECIMAL)
(0x03000000:NameValue):PID = 1 (INTEGER)
)
(0x01000000:Name ):Ret32 = (
(0x01000000:Name):result = (
(0x03000000:NameValue):PRICE = 1.00 (DECIMAL)
(0x03000000:NameValue):PID = 1 (INTEGER)
)
) |
I tried to dereference the reference as follows:
| Code: |
| SET OutputLocalEnvironment.Variables.Ret32 = ref1[1]; |
But this causes a syntax error
How do I write this so I can grab the '1' value from the "PID = 1 " data |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Tue Nov 08, 2011 11:45 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Don't know if it is a typo but you have
DECLARE ref1 REFERENCE TO row1.results[1];
and
SET OutputLocalEnvironment.Variables.Ret31 = row1.result[1];
One has an s at the end. The other does not... 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Gideon |
| Posted: Tue Nov 08, 2011 12:11 pm Post subject: |
|
|
Chevalier
Joined: 18 Aug 2009
Posts: 403
|
I cleaned up my code:
| Code: |
DECLARE cmd CHARACTER;
DECLARE row1 ROW;
SET cmd = 'select price, pid from db2inst1.catalog where pid = 1';
--SET pid = PASSTHRU(cmd);
SET row1.results[] = PASSTHRU(cmd);
DECLARE ref1 REFERENCE TO row1.results[1];
SET OutputLocalEnvironment.Variables.Ret3 = SQLCODE;
SET OutputLocalEnvironment.Variables.Ret31 = row1.results[1];
SET OutputLocalEnvironment.Variables.Ret32 = ref1; |
And reran the program, and got the following:
| Code: |
(0x01000000:Name):Variables = (
(0x03000000:NameValue):Ret3 = 0 (INTEGER)
(0x01000000:Name ):Ret31 = (
(0x03000000:NameValue):PRICE = 1.00 (DECIMAL)
(0x03000000:NameValue):PID = 1 (INTEGER)
)
(0x01000000:Name ):Ret32 = (
(0x03000000:NameValue):PRICE = 1.00 (DECIMAL)
(0x03000000:NameValue):PID = 1 (INTEGER) |
I still do not know how to get the first section of the data, when I try to do the following:
| Code: |
| SET OutputLocalEnvironment.Variables.Ret32 = ref1[1]; |
I get a syntax error |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Nov 08, 2011 12:23 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
So, typically, if you wanted the value of PID, you would
| Code: |
| SET OutputLocalEnvironment.Variables.Ret32= ref1.PID; |
If you wanted "the first child of ref1", you would
| Code: |
| SET OutputLocalEnvironment.Variables.Ret32 = ref1.[1]; |
You could likewise
| Code: |
| MOVE ref1 FIRSTCHILD; |
or something similar...
Treat a Reference variable as if it were something like OutputRoot. |
|
| Back to top |
|
|
| vmcgloin |
| Posted: Tue Nov 08, 2011 12:34 pm Post subject: |
|
|
Knight
Joined: 04 Apr 2002
Posts: 560
Location: Scotland
|
mqjeff - does ref1.[1] work or should it be ref1.*[1]?
(just curious... I am no longer a mq/broker gal otherwise I'd check myself, but can't keep away) |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Nov 08, 2011 12:52 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| vmcgloin wrote: |
mqjeff - does ref1.[1] work or should it be ref1.*[1]?
(just curious... I am no longer a mq/broker gal otherwise I'd check myself, but can't keep away) |
I think they both work but could do slightly different things.
but I haven't checked lately, so an exercise for the reader...  |
|
| Back to top |
|
|
|
|
