| Author |
Message
|
| Dave Ziegler |
 Posted: Tue Mar 17, 2015 2:27 pm Post subject: Resultset to JSON |
 |
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
Long time listener, first time JSON'er. I'm doing this at the moment:
| Code: |
CALL SomeStoredProc(input1, input2, dataRow.results[]);
DECLARE count INT CARDINALITY(dataRow.results[]);
DECLARE i INT 1;
WHILE i <= count DO
DECLARE nextRow REFERENCE TO dataRow.results[i];
CREATE FIELD OutputRoot.JSON.Data IDENTITY (JSON.Array)Data;
CREATE LASTCHILD OF OutputRoot.JSON.Data.ResultSet[i] TYPE NameValue NAME 'abc' VALUE nextRow.abc;
CREATE LASTCHILD OF OutputRoot.JSON.Data.ResultSet[i] TYPE NameValue NAME 'xyz' VALUE nextRow.xyz;
SET i = i + 1;
END WHILE;
|
But I could probably loop through the fields returned by my stored proc first and use FIELDNAME or something similar and create a resulting JSON array completely on-the-fly, right? Or is there an easier way yet to do this? I'm trying to make something reusable because I'll have a number of stored proc to JSON methods to implement. |
|
| Back to top |
|
 |
| kimbert |
| Posted: Wed Mar 18, 2015 3:01 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
Probably best not to use CARDINALITY + counted loop. It's not fashionable these days. A FOR loop is just as easy, and always performs at least as well.
Even better would be a SELECT statement. Tricky to write the first one, but after that it is a really compact way of doing this type of task. There should be plenty of examples on this forum.
_________________
Before you criticize someone, walk a mile in their shoes. That way you're a mile away, and you have their shoes too. |
|
| Back to top |
|
|
| Dave Ziegler |
| Posted: Wed Mar 18, 2015 3:15 am Post subject: |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
Good point, I tend to forget about for loops in ESQL. And I like the SELECT idea, but yeah... that may take some digging. I'll take a stab at it. (MGK just went over a few samples with me, but it's one thing to see and another to do!)
Am I correct though, I should be able to produce something that would dynamically handle the field names so I don't have to write a separate procedure for each one of these little SQL to JSON maps? Any tips on which ESQL keywords I should be considering? |
|
| Back to top |
|
|
| kimbert |
| Posted: Wed Mar 18, 2015 3:37 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Quote: |
| Am I correct though, I should be able to produce something that would dynamically handle the field names so I don't have to write a separate procedure for each one of these little SQL to JSON maps? |
I guess it depends on the technical requirements. If there is a pattern that all of these transformations conform to, then you should be able to code up an algorithm that 'does the right thing' for all cases. I'm choosing my words carefully because I haven't seen the specification for all of the transformations.
_________________
Before you criticize someone, walk a mile in their shoes. That way you're a mile away, and you have their shoes too. |
|
| Back to top |
|
|
| Dave Ziegler |
| Posted: Wed Mar 18, 2015 3:45 am Post subject: |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
Pretty straightforward, I will have a number of SELECTs to perform and each will return 0..N rows. I need to pass that back up to a client app in the form of JSON arrays.
I should probably just use .NET nodes since C# is my language of choice, but I'm on an ESQL kick at the moment and want to improve my understanding of that language... |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Mar 18, 2015 5:11 am Post subject: Re: Resultset to JSON |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Looking at your code I would consider the following changes
| Code: |
CALL SomeStoredProc(input1, input2, dataRow.results[]);
DECLARE nextRow REFERENCE TO dataRow.results[1];
CREATE FIELD OutputRoot.JSON.Data IDENTITY (JSON.Array)Data;
Declare outptr REFERENCE TO OutputRoot.JSON.Data;
Declare outrow REFERENCE to OutputRoot;
WHILE LASTMOVE(nextRow) DO
CREATE LASTCHILD OF outptr AS outrow NAME 'ResultSet';
CREATE LASTCHILD OF outrow TYPE NameValue NAME 'abc' VALUE nextRow.abc;
CREATE LASTCHILD OF outrow TYPE NameValue NAME 'xyz' VALUE nextRow.xyz;
MOVE nextRow NEXTSIBLING;
END WHILE;
|
Of course you would need a reference to make it entirely dynamic and iterate over the children of nextRow to use Fieldname and Fieldvalue...
Hope this gives some pointers... 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| joebuckeye |
| Posted: Wed Mar 18, 2015 5:38 am Post subject: Re: Resultset to JSON |
|
|
Partisan
Joined: 24 Aug 2007
Posts: 365
Location: Columbus, OH
|
Why do people not use FOR?
It seems much cleaner to me.
| Code: |
CALL SomeStoredProc(input1, input2, dataRow.results[]);
CREATE FIELD OutputRoot.JSON.Data IDENTITY (JSON.Array)Data;
Declare outptr REFERENCE TO OutputRoot.JSON.Data;
Declare outrow REFERENCE to OutputRoot;
FOR nextRow AS dataRow.results[] DO
CREATE LASTCHILD OF outptr AS outrow NAME 'ResultSet';
CREATE LASTCHILD OF outrow TYPE NameValue NAME 'abc' VALUE nextRow.abc;
CREATE LASTCHILD OF outrow TYPE NameValue NAME 'xyz' VALUE nextRow.xyz;
END FOR;
|
|
|
| Back to top |
|
|
| Dave Ziegler |
| Posted: Wed Mar 18, 2015 6:18 am Post subject: Re: Resultset to JSON |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
| joebuckeye wrote: |
Why do people not use FOR?
It seems much cleaner to me.
|
Agreed.
To answer your question, in my case: because I'm fairly new to ESQL and didn't know about it until recently.
People are always quick to say RTFM, but the FM is quite large for Integration Bus and ESQL and I just haven't read the entire FM yet  |
|
| Back to top |
|
|
| Dave Ziegler |
| Posted: Wed Mar 18, 2015 6:36 am Post subject: |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
| @joebuckeye thanks for the code snippet by the way! |
|
| Back to top |
|
|
| Dave Ziegler |
| Posted: Wed Mar 18, 2015 6:57 am Post subject: Re: Resultset to JSON |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
| joebuckeye wrote: |
| Code: |
FOR nextRow AS dataRow.results[] DO
|
|
This is very fast... I like it. Thanks again. |
|
| Back to top |
|
|
| joebuckeye |
| Posted: Mon Mar 23, 2015 8:04 am Post subject: Re: Resultset to JSON |
|
|
Partisan
Joined: 24 Aug 2007
Posts: 365
Location: Columbus, OH
|
| Dave Ziegler wrote: |
| joebuckeye wrote: |
| Code: |
FOR nextRow AS dataRow.results[] DO
|
|
This is very fast... I like it. Thanks again. |
You're welcome.
I discovered FOR loops about 18 months or so ago (as you said, the FM is quite large) and have never looked back.
I try to promote them when possible since they handle so much for you and remove sources of errors. |
|
| Back to top |
|
|
|
|
