| Author |
Message
|
| NiceGuy |
 Posted: Fri Jun 11, 2010 6:39 am Post subject: QUERY on Broker Message Set |
 |
|
Apprentice
Joined: 11 Jun 2009
Posts: 37
|
Hi,
I want to apologize in advance as I am relatively new to message broker.
I have a simple question, but is it possible to run a sql query on a in put message set?
Please allow me to elaborate:
I have a ref to a parsed message coming in a node in, InputRoot.MRM.Invoice[], and would like to perform a SET (similair to an SQL SET) on the said input.
For example:
SET InputRoot.MRM.Invoice.id='12345'
FROM InputRoot.MRM.Invoice[]
WHERE InputRoot.MRM.Invoice.Name='Somebody';
If possible I would ask help building the query?
I hope this request makes sense.
Thanks for your time |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Fri Jun 11, 2010 7:22 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
So what happened when you tried to do this?
What were your problems?
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| NiceGuy |
| Posted: Fri Jun 11, 2010 8:59 am Post subject: |
|
|
Apprentice
Joined: 11 Jun 2009
Posts: 37
|
Thanks for the reply,
To be completely honest I do not know how, but my error are
I believe because the flow thinks the ref is an actual database?
Here let me clarify:
Procedure CALL
CALL addMissingId(InputRoot.MRM);
Procedure definition:
| Code: |
CREATE PROCEDURE addMissingId(IN refToInvoice REFERENCE)
BEGIN
UPDATE refToInvoice.Invoice AS T SET Id = '1234556789'
WHERE T.VendorName = 'SomeName';
END; |
Also please note that the passed InputRoot.MRM.Invoice[] is any array of
invoices.
Hope this makes sense and someone see's my error somewhere?
Thanks |
|
| Back to top |
|
|
| kimbert |
| Posted: Fri Jun 11, 2010 1:13 pm Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
Strictly speaking, you do not query or set a message set in a message flow. A message set is a description of your data ( i.e. it is 'metadata' ) and it is created and edited in the message broker toolkit, using the message set editor.
I suspect that you already know that, and what you really want to do is to find and update a field in the message tree using some SQL-like syntax. The good news is that you can. The SELECT function in ESQL is not limited ( like the UPDATE statement ) to databases and it can select one or more fields from the message tree. It is a very powerful ( and probably underused ) statement which can replace a lot of ESQL when used skillfully.
I suggest that you use a SELECT statement to get a reference to the field(s) that you want to update, then use the SET statement with a VALUE clause to update its/their value(s). |
|
| Back to top |
|
|
| NiceGuy |
| Posted: Fri Jun 11, 2010 2:41 pm Post subject: |
|
|
Apprentice
Joined: 11 Jun 2009
Posts: 37
|
Hi Kimbert,
Thanks for your help,
Just wanted to verify as per your suggestion:
| Code: |
DECLARE invoices ROW;
SET invoices = InputRoot.MRM;
DECLARE test ROW;
SET test = SELECT * FROM invoices.Invoice AS T WHERE T.VendorName='SomeVendor';
SET test.Id = '123456789'; |
I think this is what you mean (it appears to work). If you agree,
could you suggest a way to SET all test.Id returned to '123456789'
currently the last line line, namely:
| Code: |
| SET test.Id = '123456789'; |
Only sets the first record in the result set to '123456789'.
Also, I've declared the 'test' variable to be a ROW, is suggested?
Thanks |
|
| Back to top |
|
|
| kimbert |
| Posted: Sat Jun 12, 2010 12:55 pm Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
SET only works on a single reference. I would use a FOR statement on the ROW returned by the SELECT statement.
There may be a single-statement solution that exploits the full power of the SELECT statement - if so, I expect mgk will enlighten us. |
|
| Back to top |
|
|
|
|
