| Author |
Message
|
| vmurdesh |
 Posted: Fri May 09, 2003 10:35 am Post subject: Root SQL exception |
 |
|
Novice
Joined: 09 May 2002
Posts: 18
|
Hi,
I get the above 'Root SQL exception' when i do a database select as shown
on page 113 Chapter 8 of the ESQL reference manual..
SET OutputRoot =InputRoot;
SET OutputRoot.XML.Test.Result []=
(SELECT T.COLUMN1 AS Column1,T.COLUMN2 AS Column2
FROM Database.USERTABLE AS T);
What is the correct syntax to select multiple row and move to XML output?
Thanks. |
|
| Back to top |
|
 |
| Empeterson |
| Posted: Fri May 09, 2003 11:05 am Post subject: |
|
|
Centurion
Joined: 14 Apr 2003
Posts: 125
Location: Foxboro, MA
|
Try this:
SET OutputRoot.XML.Test.Result []=
LIST(SELECT T.COLUMN1 AS Column1,T.COLUMN2 AS Column2
FROM Database.USERTABLE AS T);
I got this from page 104, Chapter 6 of the ESQL reference manual. No idea if it works but its worth a shot hehe. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri May 09, 2003 11:52 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| Do you have 'Treat Warnings as Errors' checked on the advanced tab in your compute node? |
|
| Back to top |
|
|
| vmurdesh |
| Posted: Fri May 09, 2003 12:34 pm Post subject: The LIST syntax does not work for Database SELECT |
|
|
Novice
Joined: 09 May 2002
Posts: 18
|
and i do have 'Treat Warnings as Errors' checked on the advanced tab in your compute node...
Thanks |
|
| Back to top |
|
|
| kirani |
| Posted: Fri May 09, 2003 4:31 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Well, in that case if you don't have any record in the DB table, the compute node will throw an exception. Uncheck that particular check box and then retry the operation.
_________________
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 |
|
|
| vmurdesh |
| Posted: Mon May 12, 2003 5:25 pm Post subject: What is the correct syntax for multiple SELECT ? |
|
|
Novice
Joined: 09 May 2002
Posts: 18
|
My table does have 1 row and i tried with the 'Treat Warnings as Errors'
box both checked and unchecked..
I used the input xml to trigger this select and the SELECT clause as specified on page 113 Chapter 8 of the ESQL reference manual..
To trigger the SELECT, you must send in a trigger message with an XML body that
is of the following form:
<Test>
<Result>
<Column1>value1</Column1>
<Column2>value2</Column2>
</Result>
<Result>
<Column1>value3</Column1>
<Column2>value4</Column2>
</Result>
</Test>
Thanks for all your help.. |
|
| Back to top |
|
|
| kirani |
| Posted: Mon May 12, 2003 7:13 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
could you post your complete ExceptionList here?
_________________
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 |
|
|
| vmurdesh |
| Posted: Tue May 13, 2003 8:16 am Post subject: Here is the Root and ExceptionList |
|
|
Novice
Joined: 09 May 2002
Posts: 18
|
(
(0x1000000)Properties = (
(0x3000000)MessageSet = ''
(0x3000000)MessageType = ''
(0x3000000)MessageFormat = ''
(0x3000000)Encoding = 546
(0x3000000)CodedCharSetId = 437
(0x3000000)Transactional = TRUE
(0x3000000)Persistence = FALSE
(0x3000000)CreationTime = GMTTIMESTAMP '2003-05-13 16:11:27.120'
(0x3000000)ExpirationTime = -1
(0x3000000)Priority = 0
(0x3000000)ReplyIdentifier = X'000000000000000000000000000000000000000000000000'
(0x3000000)ReplyProtocol = 'MQ'
(0x3000000)Topic = NULL
)
(0x1000000)MQMD = (
(0x3000000)SourceQueue = 'MESSAGEBUS.ADMIN.REQUEST'
(0x3000000)Transactional = TRUE
(0x3000000)Encoding = 546
(0x3000000)CodedCharSetId = 437
(0x3000000)Format = 'MQSTR '
(0x3000000)Version = 2
(0x3000000)Report = 0
(0x3000000)MsgType = 8
(0x3000000)Expiry = -1
(0x3000000)Feedback = 0
(0x3000000)Priority = 0
(0x3000000)Persistence = 0
(0x3000000)MsgId = X'414d51204d515349514d20202020202083d7bf3e12900100'
(0x3000000)CorrelId = X'000000000000000000000000000000000000000000000000'
(0x3000000)BackoutCount = 0
(0x3000000)ReplyToQ = ' '
(0x3000000)ReplyToQMgr = 'MQSIQM '
(0x3000000)UserIdentifier = 'vmurdesh '
(0x3000000)AccountingToken = X'160105150000002d09156ee9776c3af423dd54ef03000000000000000000000b'
(0x3000000)ApplIdentityData = ' '
(0x3000000)PutApplType = 11
(0x3000000)PutApplName = 'E:\MQSI\ih03\rfhutil.exe '
(0x3000000)PutDate = DATE '2003-05-13'
(0x3000000)PutTime = GMTTIME '16:11:27.120'
(0x3000000)ApplOriginData = ' '
(0x3000000)GroupId = X'000000000000000000000000000000000000000000000000'
(0x3000000)MsgSeqNumber = 1
(0x3000000)Offset = 0
(0x3000000)MsgFlags = 0
(0x3000000)OriginalLength = -1
)
(0x1000010)XML = (
(0x5000018)XML = (
(0x6000011) = '1.0'
(0x6000012) = 'UTF-8'
)
(0x6000002) = '
'
(0x1000000)GetMessageList = (
(0x1000000)Results = (
(0x1000000)a =
)
)
(0x6000002) = '
'
)
)
(
(0x1000000)RecoverableException = (
(0x3000000)File = 'F:\build\S210_P\src\DataFlowEngine\ImbComputeNode.cpp'
(0x3000000)Line = 390
(0x3000000)Function = 'ImbComputeNode::evaluate'
(0x3000000)Type = 'ComIbmComputeNode'
(0x3000000)Name = '3b69e209-f500-0000-0080-e1077a5a4d9e'
(0x3000000)Label = 'MessageBusLogMaint.Compute1'
(0x3000000)Text = 'Caught exception and rethrowing'
(0x3000000)Catalog = 'WMQIv210'
(0x3000000)Severity = 3
(0x3000000)Number = 2230
(0x1000000)DatabaseException = (
(0x3000000)File = 'F:\build\S210_P\src\DataFlowEngine\ImbOdbc.cpp'
(0x3000000)Line = 143
(0x3000000)Function = 'ImbOdbcHandle::checkRcInner'
(0x3000000)Type = ''
(0x3000000)Name = ''
(0x3000000)Label = ''
(0x3000000)Text = 'Root SQL exception'
(0x3000000)Catalog = 'WMQIv210'
(0x3000000)Severity = 3
(0x3000000)Number = 2321
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = '100'
)
)
)
) |
|
| Back to top |
|
|
| kirani |
| Posted: Wed May 14, 2003 9:21 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Try this,
Create a message flow as follows,
MQInput(out)->Compute->Trace1->MQOutput
MQInput(catch)->Trace2
1. Read your input message as BLOB in MQInput node.
2. Connect a Compute node to the out terminal of MQInput node. In this compute node, select "Copy Complete Message" and enter following ESQL code.
| Code: |
SET Environment.Variables.ResultSet[] = ( SELECT T.COLUMN1 AS Column1, T.COLUMN2 AS Column2 FROM Database.USERTABLE AS T);
|
In advanced tab, select "Throw Errors on DB Exception" and un-check "Treat Warnings as Errors".
3. Attach a Trace node to the out terminal of the compute node, In this node, print ${Environment} into some trace file (Output File).
4. Attach a MQOutput node to the out terminal of the trace node. Configure the node to write the message to some output queue.
5. Attach a trace node to the "catch" terminal of the MQInput node, write ${ExceptionList} into this trace node (Exception File).
First make sure you have your ODBC connection setup properly and the same query "select * from USERTABLE" works from the command prompt and shows some results.
Now deploy this message flow to your broker and then put any message on the input queue. Take a look at your Output and Exception trace file as post your observations here.
_________________
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 |
|
|
|
|
