| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
| ESQL Behavior (Caching?) |
« View previous topic :: View next topic » |
| Author |
Message
|
| Dave Ziegler |
 Posted: Wed Apr 01, 2015 6:48 am Post subject: ESQL Behavior (Caching?) |
 |
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
I'm passing some parameters to a stored procedure and it works the first time through:
| Code: |
DECLARE input REFERENCE TO InputRoot.JSON.Data;
DECLARE dataRow ROW;
CALL MyProc(input.username, input.password, input.status, input.from, input.to, FALSE, dataRow.results[]);
CREATE PROCEDURE MyProc(IN username CHARACTER, IN pwd CHARACTER, IN status CHARACTER, IN datefrom TIMESTAMP, IN dateto TIMESTAMP, IN Debug BOOLEAN)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "dbo.usp_MyProc";
|
The first time through, dataRow.results[] gets populated with 1..N result rows. I execute this a second time, and now dataRow (in the debug viewer) does not have the .results[] array tacked on. In a user trace, I see this:
| Code: |
2015-04-01 09:23:45.657728 7392 UserTrace BIP2539I: Node 'ImbESQLManager': Evaluating expression ''input.username'' at ('.HandlePost.MyProc', '6.24'). This resolved to ''input.username''. The result was '''admin'''.
2015-04-01 09:23:45.657800 7392 UserTrace BIP2539I: Node 'ImbESQLManager': Evaluating expression ''input.password'' at ('.HandlePost.MyProc', '6.40'). This resolved to ''input.password''. The result was '''password'''.
2015-04-01 09:23:45.657848 7392 UserTrace BIP2539I: Node 'ImbESQLManager': Evaluating expression ''input.status'' at ('.HandlePost.MyProc', '6.56'). This resolved to ''input.status''. The result was '''new'''.
2015-04-01 09:23:45.657892 7392 UserTrace BIP2543I: Node 'ImbESQLManager': ('.HandlePost.MyProc', '6.70') : Failed to navigate to path element number '2' because it does not exist.
2015-04-01 09:23:45.658028 7392 UserTrace BIP2539I: Node 'ImbESQLManager': Evaluating expression ''input.from'' at ('.HandlePost.MyProc', '6.70'). This resolved to ''input.from''. The result was ''NULL''.
2015-04-01 09:23:45.658084 7392 UserTrace BIP2543I: Node 'ImbESQLManager': ('.HandlePost.MyProc', '6.82') : Failed to navigate to path element number '2' because it does not exist.
2015-04-01 09:23:45.658120 7392 UserTrace BIP2539I: Node 'ImbESQLManager': Evaluating expression ''input.to'' at ('.HandlePost.MyProc', '6.82'). This resolved to ''input.to''. The result was ''NULL''.
|
If I wait 30-60 seconds or so, things work again. What am I overlooking? It seems to be having issues with the two date parameters. I tried casting them to TIMESTAMP but that didn't change anything. Any idea why this works the first time through but not on subsequent tries without waiting for things to clear up? |
|
| Back to top |
|
 |
| Dave Ziegler |
| Posted: Wed Apr 01, 2015 7:32 am Post subject: |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
| I have removed the TIMESTAMP question from this equation. Changing the input params to CHARACTER and passing them into the stored proc as VARCHAR had no effect. Still works the first time, but not on subsequent calls until a timeout period has passed. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Apr 01, 2015 7:39 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Dave Ziegler wrote: |
| I have removed the TIMESTAMP question from this equation. Changing the input params to CHARACTER and passing them into the stored proc as VARCHAR had no effect. Still works the first time, but not on subsequent calls until a timeout period has passed. |
I would create local variables to hold the resolved items, and pass those to the call.
| Code: |
DECLARE input REFERENCE TO InputRoot.JSON.Data;
DECLARE dataRow ROW;
set Userame = input.username;
set Password = input.password;
set Statis= input.status;
set From = input.from;
set To = input.to;
CALL MyProc(Username, Password, Status, From, To, FALSE, dataRow.results[]); |
Then your usertrace will show you whether the issue is passing things to the CALL, or in resolving the tree fields. |
|
| Back to top |
|
|
| Dave Ziegler |
| Posted: Wed Apr 01, 2015 7:41 am Post subject: |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
Good suggestion, I'll try this first.
Also noticed the to/from dateto/datefrom weren't lining up, so I fixed that. Not sure how it was working at all to be honest, I guess the parser is position-based...?
Will report back shortly. |
|
| Back to top |
|
|
| nelson |
| Posted: Wed Apr 01, 2015 7:44 am Post subject: |
|
|
Partisan
Joined: 02 Oct 2012
Posts: 313
|
Sounds obvious, but, are you sure that you're sending the same message every time to the compute node?
Have you tested your SP outside the broker, calling it in the same way you're calling it from broker? |
|
| Back to top |
|
|
| Dave Ziegler |
| Posted: Wed Apr 01, 2015 7:46 am Post subject: |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
| nelson wrote: |
Sounds obvious, but, are you sure that you're sending the same message every time to the compute node?
|
Yes, I'm clicking Execute on the same JSON from Fiddler. Works again after a timeout elapses.
| nelson wrote: |
Have you tested your SP outside the broker, calling it in the same way you're calling it from broker? |
Yes, works as expected.
Tried mqjeff's suggestion, the vars populate every time but the proc call doesn't return anything after the first attempt. Seems like something is being cached somewhere. My DBA insists there is nothing untoward going on in his proc... |
|
| Back to top |
|
|
| nelson |
| Posted: Wed Apr 01, 2015 7:54 am Post subject: |
|
|
Partisan
Joined: 02 Oct 2012
Posts: 313
|
Try wiring the values and see what happens 
| Code: |
| 2015-04-01 09:23:45.658028 7392 UserTrace BIP2539I: Node 'ImbESQLManager': Evaluating expression ''input.from'' at ('.HandlePost.MyProc', '6.70'). This resolved to ''input.from''. The result was ''NULL''. |
Coming back to your trace, it is suggesting problems resolving the tree. |
|
| Back to top |
|
|
| Dave Ziegler |
| Posted: Wed Apr 01, 2015 7:55 am Post subject: |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
| nelson wrote: |
Try wiring the values and see what happens
| Code: |
| 2015-04-01 09:23:45.658028 7392 UserTrace BIP2539I: Node 'ImbESQLManager': Evaluating expression ''input.from'' at ('.HandlePost.MyProc', '6.70'). This resolved to ''input.from''. The result was ''NULL''. |
Coming back to your trace, it is suggesting problems resolving the tree. |
Yeah.. wiring the values? I can see everything in the debugger just fine, and things work after a timeout (which is the weird point I can't get past). And a trace node shows me the tree is the same structure on each request. |
|
| Back to top |
|
|
| Dave Ziegler |
| Posted: Wed Apr 01, 2015 7:56 am Post subject: |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
| Do you mean hard-code the values? |
|
| Back to top |
|
|
| Dave Ziegler |
| Posted: Wed Apr 01, 2015 8:00 am Post subject: |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
| Hard-coded values produce the same results fyi. |
|
| Back to top |
|
|
| Dave Ziegler |
| Posted: Wed Apr 01, 2015 8:06 am Post subject: |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
| Just because... I set the domain on my HTTP Input node to 'blank' (was JSON), and popped a RCD node directly after it with JSON set as the domain. Same results, but I expected as much. |
|
| Back to top |
|
|
| Dave Ziegler |
| Posted: Wed Apr 01, 2015 8:10 am Post subject: |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
So this is weird. I did this too:
| Code: |
DECLARE input REFERENCE TO InputRoot.JSON.Data;
DECLARE dataRow ROW;
CALL MyProc(input.username, input.password, input.status, input.datefrom, input.dateto, FALSE, dataRow.results[]);
CALL MyProc(input.username, input.password, input.status, input.datefrom, input.dateto, FALSE, dataRow.results[]);
|
The first line executes and populates the list. The second line executes and blows it away. |
|
| Back to top |
|
|
| Dave Ziegler |
| Posted: Wed Apr 01, 2015 8:16 am Post subject: |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
|
| Back to top |
|
|
| nelson |
| Posted: Wed Apr 01, 2015 8:20 am Post subject: |
|
|
Partisan
Joined: 02 Oct 2012
Posts: 313
|
| Dave Ziegler wrote: |
| Hard-coded values produce the same results fyi. |
What do you see in the trace?
| Code: |
| 2015-04-01 09:23:45.658028 7392 UserTrace BIP2539I: Node 'ImbESQLManager': Evaluating expression ''input.from'' at ('.HandlePost.MyProc', '6.70'). This resolved to ''input.from''. The result was ''NULL''. |
This shouldn't appear anymore. What do you see? |
|
| Back to top |
|
|
| Dave Ziegler |
| Posted: Wed Apr 01, 2015 8:35 am Post subject: |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
Works the first time, fails the second time (with hard-coded values), and this is in the trace:
| Code: |
2015-04-01 11:29:58.959208 3920 UserTrace BIP2562I: Node 'Main.Handle POST': Assigning a list to ''dataRow.results[]''.
2015-04-01 11:29:58.959404 3920 UserTrace BIP2544I: Node 'Main.Handle POST': Executing database SQL statement ''{ CALL dbo.usp_MyProc ( ? , ? , ? , ? , ? , ? ) }'' derived from ('Database.MyProc', '1.1'); expressions '''admin', 'password', 'new', '2015-03-02', '2015-03-18', FALSE''; resulting parameter values '''admin', 'password', 'new', '2015-03-02', '2015-03-18', FALSE''.
2015-04-01 11:29:58.959524 3920 UserTrace BIP2537I: Node 'Main.Handle POST': Executing statement ''CREATE FIELD OutputRoot.JSON.Data IDENTITY (JSON.Array)Data;'' at ('.HandlePost.MyProc', '9.3').
2015-04-01 11:29:58.959636 3920 UserTrace BIP2539I: Node 'ImbESQLManager': Evaluating expression ''JSON.Array'' at ('.HandlePost.MyProc', '9.47'). This resolved to ''JSON.Array''. The result was ''16781312''.
2015-04-01 11:29:58.959704 3920 UserTrace BIP2537I: Node 'Main.Handle POST': Executing statement ''DECLARE jsonData REFERENCE TO OutputRoot.JSON.Data;'' at ('.HandlePost.MyProc', '10.3').
2015-04-01 11:29:58.959944 3920 UserTrace BIP2537I: Node 'Main.Handle POST': Executing statement ''DECLARE jsonOutput REFERENCE TO OutputRoot;'' at ('.HandlePost.MyProc', '11.4').
2015-04-01 11:29:58.960016 3920 UserTrace BIP2537I: Node 'Main.Handle POST': Executing statement ''FOR nextRow AS dataRow.results[ ] DO ... END FOR;'' at ('.HandlePost.MyProc', '13.3').
2015-04-01 11:29:58.960064 3920 UserTrace BIP2543I: Node 'ImbESQLManager': ('.HandlePost.MyProc', '13.18') : Failed to navigate to path element number '2' because it does not exist.
2015-04-01 11:29:58.960128 3920 UserTrace BIP2537I: Node 'Main.Handle POST': Executing statement ''RETURN TRUE;'' at ('.HandlePost.Main', '22.3').
2015-04-01 11:29:58.960212 3920 UserTrace BIP4015I: Message propagated to the 'out' terminal of node 'Main.Handle POST' with the following message trees: ''.
2015-04-01 11:29:58.960452 3920 UserTrace BIP3722I: Node 'Main.HTTP Reply' sending a 'HTTP' reply message to the originating client.
See subsequent messages for success or failure messages relating to this reply, and for any transport-specific messages.
No action required.
2015-04-01 11:29:59.102396 3920 UserTrace BIP11506I: Committed a local transaction.
|
|
|
| Back to top |
|
|
|
|
|
  |
Goto page 1, 2 Next |
Page 1 of 2 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
