| Author |
Message
|
| kiran26 |
 Posted: Wed Aug 21, 2002 8:36 am Post subject: DATABASE SELECT ????? |
 |
|
Acolyte
Joined: 06 Feb 2002
Posts: 69
|
Hi all,
I have question on database select statement. I have a select query , which will fetch me multiple records. I am not sure how to store them and display them in XML.
<College>
<Person Name=”kiran” class=”hhhh” >
<PersonDescription>hello world </PersonDescription>
</Program>
</WMQI>
I will be fetching the Person name , class and PersonDescription from the table and i have to display them in the above pattern. Can any one point me on this will be a helpfull to me.
Thanks
vishnu |
|
| Back to top |
|
 |
| AlexeiSkate |
| Posted: Wed Aug 21, 2002 10:15 am Post subject: |
|
|
Centurion
Joined: 10 Apr 2002
Posts: 123
|
Kiran26,
Do you have a copy of the IBM Redbook "WebSphere MQ Integrator ESQL Reference V2.1" (SC34-5923-01) ? Page 29 has some helpful information on referencing information in XML message. Hopes this help.
- Alex |
|
| Back to top |
|
|
| kiran26 |
| Posted: Wed Aug 21, 2002 10:25 am Post subject: |
|
|
Acolyte
Joined: 06 Feb 2002
Posts: 69
|
Hi AlexeiSkate,
Can send me the link.
Thanks vishnu |
|
| Back to top |
|
|
| AlexeiSkate |
| Posted: Wed Aug 21, 2002 10:31 am Post subject: |
|
|
Centurion
Joined: 10 Apr 2002
Posts: 123
|
|
| Back to top |
|
|
| kiran26 |
| Posted: Wed Aug 21, 2002 10:44 am Post subject: |
|
|
Acolyte
Joined: 06 Feb 2002
Posts: 69
|
Hi,
I am not able to get from the book, can u be able to explain me how to work around with esql for my above question. If u can it will be a great help to me. |
|
| Back to top |
|
|
| kirani |
| Posted: Wed Aug 21, 2002 10:47 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
I don't think you can do this directly. You will have to retrieve resultset into EnvironmentTree and then format your XML message by assigning values to it.
SET Environment.Variables.Results[] = (SELECT p.name, p.class from Database.Person as t where ...)
SET OutputRoot.XML.College.Person.....(XML.Attribute)Name = Environment.Variables.Results[].name;
Hope this gives you some idea.
Chapter 7 in ESQL Reference manual will be useful to you.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| abilott |
| Posted: Wed Aug 21, 2002 11:19 am Post subject: Database Select |
|
|
Newbie
Joined: 21 Aug 2002
Posts: 3
|
| I have a similar problem in MQSI v2.0.1. I'm sending a message out to two recipients, who send me an XML response. I have to wait for both responses before merging them together, so each XML reply is stored in a SQL7 database waiting for both to arrive (the whole individual XML reply goes into one database field). The problem is that when I get the data out, I can't get MQSI to recognize it as XML, it handles it as a character string. I've tried using a cast and the reset content descriptor node, but nothing's working. Thanks for any help you can give. |
|
| Back to top |
|
|
| kirani |
| Posted: Wed Aug 21, 2002 11:29 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
abilott,
What is the data type of the field into db? Is it character or BLOB?
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| kiran26 |
| Posted: Wed Aug 21, 2002 11:39 am Post subject: |
|
|
Acolyte
Joined: 06 Feb 2002
Posts: 69
|
Everything is character.
Thanks
vishnu |
|
| Back to top |
|
|
| kiran26 |
| Posted: Wed Aug 21, 2002 11:44 am Post subject: |
|
|
Acolyte
Joined: 06 Feb 2002
Posts: 69
|
Hi Kiran 26,
How to loop Results[] if i get multipe records.
Thanks
vishnu |
|
| Back to top |
|
|
| kiran26 |
| Posted: Wed Aug 21, 2002 11:50 am Post subject: |
|
|
Acolyte
Joined: 06 Feb 2002
Posts: 69
|
Hi kiran,
I have used your esql as u side,
SET Environment.Variables.Results[] = (SELECT p.name, p.class from Database.Person as t where ...)
SET OutputRoot.XML.College.Person.....(XML.Attribute)Name = Environment.Variables.Results[].name;
but i am getting following error,
**********************************************************
BIP2406E: (17, 112) : Syntax error : cannot specify '[]' in the middle of a field reference.
The list specifier ('[]') can only be used at the end of a field reference.
Correct the syntax of the expression and redeploy the message flow.
********************************************************
Thanks
vishnu |
|
| Back to top |
|
|
| abilott |
| Posted: Wed Aug 21, 2002 11:59 am Post subject: Database Select |
|
|
Newbie
Joined: 21 Aug 2002
Posts: 3
|
| kirani, the SQL7 column type is text and I've tried putting the data in both as character and blob. When I tried blob, however, getting the data back out and converting back to character created several integrator errors. |
|
| Back to top |
|
|
| kirani |
| Posted: Wed Aug 21, 2002 12:00 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Vishnu,
My ESQL was in pseudo code format. You cannot use it directly into your message flow.
If your resultset returns more than one row, you have to iterate through the resultset to assign retrieved values to your XML attributes.
You need to put second line in a loop to create multiple occurances of <Person> tags.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| kiran26 |
| Posted: Wed Aug 21, 2002 12:05 pm Post subject: |
|
|
Acolyte
Joined: 06 Feb 2002
Posts: 69
|
Hi Kiran,
I did not used your esql as it was.I have modified it , can u pl take time and can u reply back with the esql how to loop Result[] to get all the values.
Thanks
vishnu |
|
| Back to top |
|
|
| kirani |
| Posted: Wed Aug 21, 2002 12:12 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
could you post your ESQL code here and we will see what's wrong with it?
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
|
|
