MQSeries.net :: View topic - UPDATE PROBLEMS????

MQSeries.net

Tech Exchange

Education

Certifications

Library

Info Center

SupportPacs

FAQÂ Â

Usergroups

RSS Feed - WebSphere MQ Support

RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » UPDATE PROBLEMS????

UPDATE PROBLEMS????

« View previous topic :: View next topic »

Author

Message

nik_iway

Posted: Thu Feb 16, 2006 10:32 pm Post subject: UPDATE PROBLEMS????

Centurion

Joined: 21 Jul 2005
Posts: 115

Hi,
My Message Flow :
Input Node - Compute NOde - Output Node

My Message
<A>
<B>3</B>
<C>2</C>
<D>3</D>
</A>

In my Compute Node the Following is the Esql:

CREATE DATABASE MODULE UPDATE_Database
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
UPDATE Database.TABLE1 AS SP
SET A = SP.A - OutputRoot.XML.A.B
WHERE SP.B = OutputRoot.XML.A.C;
RETURN TRUE;
END;

END MODULE;

I am not able to update the database , can any body tell me if there is any wrong in the ESQL

Regards
Nik

fjb_saper

Posted: Fri Feb 17, 2006 1:40 am Post subject:

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY

Did you check the exception tree? Did it say something about mismatched types?

If yes you will have to cast each of your numbers to a numeric type. If not typed the XML will give you char....

_________________
MQ & Broker admin

nik_iway

Posted: Fri Feb 17, 2006 2:35 am Post subject:

Centurion

Joined: 21 Jul 2005
Posts: 115

Hi fjb_saper,
Thank you for the reply
. I [put the trace node before and after the compute node and i am getting the trace output for the first trace node before the compute node. the following is the information:

(
(0x01000000):Properties = (
(0x03000000):MessageSet = ''
(0x03000000):MessageType = ''
(0x03000000):MessageFormat = ''
(0x03000000):Encoding = 546
(0x03000000):CodedCharSetId = 437
(0x03000000):Transactional = TRUE
(0x03000000):Persistence = FALSE
(0x03000000):CreationTime = GMTTIMESTAMP '2006-02-17 06:25:43.250'
(0x03000000):ExpirationTime = -1
(0x03000000):Priority = 0
(0x03000000):ReplyIdentifier = X'000000000000000000000000000000000000000000000000'
(0x03000000):ReplyProtocol = 'MQ'
(0x03000000):Topic = NULL
)
(0x01000000):MQMD = (
(0x03000000):SourceQueue = 'IN'
(0x03000000):Transactional = TRUE
(0x03000000):Encoding = 546
(0x03000000):CodedCharSetId = 437
(0x03000000):Format = ' '
(0x03000000):Version = 2
(0x03000000):Report = 0
(0x03000000):MsgType = 8
(0x03000000):Expiry = -1
(0x03000000):Feedback = 0
(0x03000000):Priority = 0
(0x03000000):Persistence = 0
(0x03000000):MsgId = X'414d51205742524b5f514d20202020207aa7f24320000f10'
(0x03000000):CorrelId = X'000000000000000000000000000000000000000000000000'
(0x03000000):BackoutCount = 0
(0x03000000):ReplyToQ = ' '
(0x03000000):ReplyToQMgr = 'WBRK_QM '
(0x03000000):UserIdentifier = 'Master '
(0x03000000):AccountingToken = X'1601051500000093e362481525af4707e53b2bf401000000000000000000000b'
(0x03000000):ApplIdentityData = ' '
(0x03000000):PutApplType = 11
(0x03000000):PutApplName = ':\Naresh\rfhutil\RFHUTIL.EXE'
(0x03000000):PutDate = DATE '2006-02-17'
(0x03000000):PutTime = GMTTIME '06:25:43.250'
(0x03000000):ApplOriginData = ' '
(0x03000000):GroupId = X'000000000000000000000000000000000000000000000000'
(0x03000000):MsgSeqNumber = 1
(0x03000000):Offset = 0
(0x03000000):MsgFlags = 0
(0x03000000):OriginalLength = -1
)
(0x01000010):XML = (
(0x01000000):A = (
(0x02000000): = '
'
(0x01000000):B = (
(0x02000000): = '3'
)
(0x02000000): = '
'
(0x01000000):C = (
(0x02000000): = '2'
)
(0x02000000): = '
'
(0x01000000)

= (
(0x02000000): = '3'
)
(0x02000000): = '
'
)
)
)

Can you tell me how to catch the exception tree.....

waiting for your reply
regards
Nik

fschofer

Posted: Fri Feb 17, 2006 2:53 am Post subject:

Knight

Joined: 02 Jul 2001
Posts: 524
Location: Mainz, Germany

Hi,

Quote:

Can you tell me how to catch the exception tree.....

connect the catch terminal of your MQInput Node to a trace node.

Trace Node Pattern:
${ExceptionList}

Greetings
Frank

nik_iway

Posted: Fri Feb 17, 2006 3:34 am Post subject:

Centurion

Joined: 21 Jul 2005
Posts: 115

Hi fschofer,
The following is the exception tree information

(
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbComputeNode.cpp'
(0x03000000):Line = 471
(0x03000000):Function = 'ImbComputeNode::evaluate'
(0x03000000):Type = 'ComIbmComputeNode'
(0x03000000):Name = 'UPDATE#FCMComposite_1_6'
(0x03000000):Label = 'UPDATE.Compute'
(0x03000000):Text = 'Caught exception and rethrowing'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2230
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp'
(0x03000000):Line = 252
(0x03000000):Function = 'SqlStatementGroup::generateRuntimeObjects'
(0x03000000):Type = 'ComIbmComputeNode'
(0x03000000):Name = 'UPDATE#FCMComposite_1_6'
(0x03000000):Label = 'UPDATE.Compute'
(0x03000000):Text = 'Error detected, rethrowing'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2488
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '.UPDATE_Compute.Main'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '6.3'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = ''
)
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbRdl\ImbRdlQueryOdbcRep.cpp'
(0x03000000):Line = 183
(0x03000000):Function = 'SqlQueryOdbcRepresentation::visitFieldReference'
(0x03000000):Type = 'ComIbmComputeNode'
(0x03000000):Name = 'UPDATE#FCMComposite_1_6'
(0x03000000):Label = 'UPDATE.Compute'
(0x03000000):Text = 'Must give a dataSourceName attribute'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2580
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '.UPDATE_Compute.Main'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '7.12'
)
)
)
)
)

wschutz

Posted: Fri Feb 17, 2006 3:36 am Post subject:

Jedi Knight

Joined: 02 Jun 2005
Posts: 3316
Location: IBM (retired)

Quote:

'Must give a dataSourceName attribute

On the properties of your compute node, you must specify the data source name. This must correspond to an entry in your .odbc.ini file.
_________________
-wayne

nik_iway

Posted: Fri Feb 17, 2006 3:38 am Post subject:

Centurion

Joined: 21 Jul 2005
Posts: 115

Hi All,

I am Reposting the Exception Tree since i forgot to include the DSN name in the Compute Node

(
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbComputeNode.cpp'
(0x03000000):Line = 471
(0x03000000):Function = 'ImbComputeNode::evaluate'
(0x03000000):Type = 'ComIbmComputeNode'
(0x03000000):Name = 'UPDATE#FCMComposite_1_6'
(0x03000000):Label = 'UPDATE.Compute'
(0x03000000):Text = 'Caught exception and rethrowing'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2230
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp'
(0x03000000):Line = 158
(0x03000000):Function = 'SqlStatementGroup::execute'
(0x03000000):Type = 'ComIbmComputeNode'
(0x03000000):Name = 'UPDATE#FCMComposite_1_6'
(0x03000000):Label = 'UPDATE.Compute'
(0x03000000):Text = 'Error detected, rethrowing'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2488
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '.UPDATE_Compute.Main'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '6.3'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'DATABASE(UPDATE TABLE1 SET A = (A)-(?) WHERE (B)=(?), Environment.Varaibles.A.B, OutputRoot.XML.A.C);'
)
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbRdl\ImbRdlExternalDb.cpp'
(0x03000000):Line = 276
(0x03000000):Function = 'SqlExternalDbStmt::executeStmt'
(0x03000000):Type = 'ComIbmComputeNode'
(0x03000000):Name = 'UPDATE#FCMComposite_1_6'
(0x03000000):Label = 'UPDATE.Compute'
(0x03000000):Text = 'The following error occurred during execution of an SQL statement'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2519
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '.UPDATE_Compute.Main'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '6.3'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'EMPLOYEE'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'UPDATE TABLE1 SET A = (A)-(?) WHERE (B)=(?)'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'NULL, '2', '
)
(0x01000000):DatabaseException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbOdbc.cpp'
(0x03000000):Line = 181
(0x03000000):Function = 'ImbOdbcHandle::checkRcInner'
(0x03000000):Type = ''
(0x03000000):Name = ''
(0x03000000):Label = ''
(0x03000000):Text = 'Root SQL exception'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2321
(0x01000000):Insert = (
(0x03000000):Type = 2
(0x03000000):Text = '-1'
)
(0x01000000):DatabaseException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbOdbc.cpp'
(0x03000000):Line = 296
(0x03000000):Function = 'ImbOdbcHandle::checkRcInner'
(0x03000000):Type = ''
(0x03000000):Name = ''
(0x03000000):Label = ''
(0x03000000):Text = 'Child SQL exception'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2322
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '42819'
)
(0x01000000):Insert = (
(0x03000000):Type = 2
(0x03000000):Text = '-402'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '[IBM][CLI Driver][DB2/NT] SQL0402N The data type of an operand of an arithmetic function or operation "-" is not numeric. SQLSTATE=42819

'
)
)
)
)
)
)
)

Waiting for reply

Regards
Nik

wschutz

Posted: Fri Feb 17, 2006 3:48 am Post subject:

Jedi Knight

Joined: 02 Jun 2005
Posts: 3316
Location: IBM (retired)

This:

Quote:

'DATABASE(UPDATE TABLE1 SET A = (A)-(?) WHERE (B)=(?), Environment.Varaibles.A.B, OutputRoot.XML.A.C);'

doesn't match this:

Quote:

UPDATE Database.TABLE1 AS SP
SET A = SP.A - OutputRoot.XML.A.B
WHERE SP.B = OutputRoot.XML.A.C;

Are you using Environment.Varaibles(sic).A.B ?
_________________
-wayne

nik_iway

Posted: Fri Feb 17, 2006 4:12 am Post subject:

Centurion

Joined: 21 Jul 2005
Posts: 115

Hi wschutz
The ESQL in the Compute Node is as Below:

CREATE COMPUTE MODULE UPDATE_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN

SET OutputRoot = InputRoot;
UPDATE Database.TABLE1 AS SP
SET A = SP.A - Environment.Varaibles.A.B
WHERE SP.B = OutputRoot.XML.A.C;
RETURN TRUE;
END;
END MODULE;

Correct me if I am Wrong
Regards
Nik

wschutz

Posted: Fri Feb 17, 2006 4:16 am Post subject:

Jedi Knight

Joined: 02 Jun 2005
Posts: 3316
Location: IBM (retired)

Thats not what you originally pasted! Is

Quote:

Environment.Varaibles.A.B

correctly spelt? You should also dump/trace the Environment tree in the trace node ....

Trace; ${Root}
_________________
-wayne

nik_iway

Posted: Fri Feb 17, 2006 4:35 am Post subject:

Centurion

Joined: 21 Jul 2005
Posts: 115

Hi wschutz,

I changed the Code in the Compute Node to the Following and put a Message, there was no update in the Database...??

My Code in the Compute Node is AS Below:

CREATE COMPUTE MODULE UPDATE_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
SET OutputRoot = InputRoot;
UPDATE Database.TABLE1 AS SP
SET A = SP.A - OutputRoot.XML.A.B
WHERE SP.B = OutputRoot.XML.A.C;
RETURN TRUE;
END;
END MODULE;

The Following in the Trace node Output before the Compute Node:

(
(0x01000000):Properties = (
(0x03000000):MessageSet = ''
(0x03000000):MessageType = ''
(0x03000000):MessageFormat = ''
(0x03000000):Encoding = 546
(0x03000000):CodedCharSetId = 437
(0x03000000):Transactional = TRUE
(0x03000000):Persistence = FALSE
(0x03000000):CreationTime = GMTTIMESTAMP '2006-02-17 12:31:14.920'
(0x03000000):ExpirationTime = -1
(0x03000000):Priority = 0
(0x03000000):ReplyIdentifier = X'000000000000000000000000000000000000000000000000'
(0x03000000):ReplyProtocol = 'MQ'
(0x03000000):Topic = NULL
)
(0x01000000):MQMD = (
(0x03000000):SourceQueue = 'IN'
(0x03000000):Transactional = TRUE
(0x03000000):Encoding = 546
(0x03000000):CodedCharSetId = 437
(0x03000000):Format = ' '
(0x03000000):Version = 2
(0x03000000):Report = 0
(0x03000000):MsgType = 8
(0x03000000):Expiry = -1
(0x03000000):Feedback = 0
(0x03000000):Priority = 0
(0x03000000):Persistence = 0
(0x03000000):MsgId = X'414d51205742524b5f514d20202020207aa7f24320000f18'
(0x03000000):CorrelId = X'000000000000000000000000000000000000000000000000'
(0x03000000):BackoutCount = 0
(0x03000000):ReplyToQ = ' '
(0x03000000):ReplyToQMgr = 'WBRK_QM '
(0x03000000):UserIdentifier = 'Master '
(0x03000000):AccountingToken = X'1601051500000093e362481525af4707e53b2bf401000000000000000000000b'
(0x03000000):ApplIdentityData = ' '
(0x03000000):PutApplType = 11
(0x03000000):PutApplName = ':\Naresh\rfhutil\RFHUTIL.EXE'
(0x03000000):PutDate = DATE '2006-02-17'
(0x03000000):PutTime = GMTTIME '12:31:14.920'
(0x03000000):ApplOriginData = ' '
(0x03000000):GroupId = X'000000000000000000000000000000000000000000000000'
(0x03000000):MsgSeqNumber = 1
(0x03000000):Offset = 0
(0x03000000):MsgFlags = 0
(0x03000000):OriginalLength = -1
)
(0x01000010):XML = (
(0x01000000):A = (
(0x02000000): = '
'
(0x01000000):B = (
(0x02000000): = '3'
)
(0x02000000): = '
'
(0x01000000):C = (
(0x02000000): = '2'
)
(0x02000000): = '
'
(0x01000000)

= (
(0x02000000): = '3'
)
(0x02000000): = '
'
)
)
)

The Following is the Exception List Tree outpu for the trace node attached to the catch Terminal of the MQ Input Node:

(
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbComputeNode.cpp'
(0x03000000):Line = 471
(0x03000000):Function = 'ImbComputeNode::evaluate'
(0x03000000):Type = 'ComIbmComputeNode'
(0x03000000):Name = 'UPDATE#FCMComposite_1_6'
(0x03000000):Label = 'UPDATE.Compute'
(0x03000000):Text = 'Caught exception and rethrowing'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2230
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp'
(0x03000000):Line = 158
(0x03000000):Function = 'SqlStatementGroup::execute'
(0x03000000):Type = 'ComIbmComputeNode'
(0x03000000):Name = 'UPDATE#FCMComposite_1_6'
(0x03000000):Label = 'UPDATE.Compute'
(0x03000000):Text = 'Error detected, rethrowing'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2488
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '.UPDATE_Compute.Main'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '6.3'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'DATABASE(UPDATE TABLE1 SET A = (A)-(?) WHERE (B)=(?), OutputRoot.XML.A.B, OutputRoot.XML.A.C);'
)
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbRdl\ImbRdlExternalDb.cpp'
(0x03000000):Line = 276
(0x03000000):Function = 'SqlExternalDbStmt::executeStmt'
(0x03000000):Type = 'ComIbmComputeNode'
(0x03000000):Name = 'UPDATE#FCMComposite_1_6'
(0x03000000):Label = 'UPDATE.Compute'
(0x03000000):Text = 'The following error occurred during execution of an SQL statement'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2519
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '.UPDATE_Compute.Main'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '6.3'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'EMPLOYEE'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'UPDATE TABLE1 SET A = (A)-(?) WHERE (B)=(?)'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = ''3', '2', '
)
(0x01000000):DatabaseException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbOdbc.cpp'
(0x03000000):Line = 181
(0x03000000):Function = 'ImbOdbcHandle::checkRcInner'
(0x03000000):Type = ''
(0x03000000):Name = ''
(0x03000000):Label = ''
(0x03000000):Text = 'Root SQL exception'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2321
(0x01000000):Insert = (
(0x03000000):Type = 2
(0x03000000):Text = '-1'
)
(0x01000000):DatabaseException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbOdbc.cpp'
(0x03000000):Line = 296
(0x03000000):Function = 'ImbOdbcHandle::checkRcInner'
(0x03000000):Type = ''
(0x03000000):Name = ''
(0x03000000):Label = ''
(0x03000000):Text = 'Child SQL exception'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2322
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '42819'
)
(0x01000000):Insert = (
(0x03000000):Type = 2
(0x03000000):Text = '-402'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '[IBM][CLI Driver][DB2/NT] SQL0402N The data type of an operand of an arithmetic function or operation "-" is not numeric. SQLSTATE=42819

'
)
)
)
)
)
)
)

Is my ESQL wrong in the Compur Node.???

Regards
Nik

fjb_saper

Posted: Fri Feb 17, 2006 5:05 am Post subject:

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY

Get over it already. I told you that your xml was not typed :i.e. A and B even though digits are considered to be of type Char!
You need to declare your vars and cast the XML to int.
Then you can attempt the update using the vars.

Enjoy

_________________
MQ & Broker admin

nik_iway

Posted: Fri Feb 17, 2006 5:31 am Post subject:

Centurion

Joined: 21 Jul 2005
Posts: 115

Thank you Saper, i Got it working, Thanks for the help

Display posts from previous:

Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » UPDATE PROBLEMS????

Jump to:

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

Protected by Anti-Spam ACP