| Author |
Message
|
| goldym |
 Posted: Thu Aug 25, 2005 9:31 pm Post subject: Problem Inserting Multiple Records into Database |
 |
|
Centurion
Joined: 24 Jun 2005
Posts: 116
|
Does anyone know how to continue with iterations once you find an invalid one. I am trying to check for null values but continue with the next record after one is found. I can insert the first two records but that's and write an exception for the third but the the next iteration is not written. It stops after the exception.
Sample data looks like this:
<?xml version="1.0" encoding="UTF-8"?>
<DATA>
element element_id="1111111" element_date_time="1124789689" element_type="NEW" />
element element_id="22222" element_date_time="1124789689" element_type="NEW"/>
element element_id="" element_date_time="1124789689" element_type="NEW"/>
element element_id="44444" element_date_time="1124789689" element_type="NEW"/>
</DATA>
ESQL Code Looks like this:
DECLARE Y INTEGER CARDINALITY(InputRoot.MRM.element[]);
DECLARE Counter2 INTEGER 1;
WHILE Counter2 <= Y DO
IF FIELDVALUE(InputRoot.MRM.element[Counter2].element_id) IS NULL
OR FIELDVALUE(InputRoot.MRM.element[Counter2].element_date_time) IS NULL
OR FIELDVALUE(InputRoot.MRM.element[Counter2].element_event) IS NULL
THEN
INSERT INTO Database.MESSAGE_EXCEPTION_TABLE (INSERTDATETIME,ELEMENT_ID, ELEMENT_DATE_TIME, ELEMENT_EVENT)
VALUES (CURRENT_TIMESTAMP, InputRoot.MRM.element[Counter2].element_Id, InputRoot.MRM.element[Counter2].element_date_time, InputRoot.MRM.element[Counter2].element_event);
ELSE
INSERT INTO Database.TABLE (createdDateTime, tradeId, lastmodifieddatetime)
VALUES ( createDateTime, FIELDVALUE(InputRoot.MRM.element[Counter2].element_id),FIELDVALUE(InputRoot.MRM.element[Counter2].element_date_time),
FIELDVALUE(InputRoot.MRM.element[Counter2].element_event);
END IF;
SET Counter2 = Counter2 + 1; -- Increment the counter each time the while condition is sastified
END WHILE; |
|
| Back to top |
|
 |
| mgk |
| Posted: Fri Aug 26, 2005 1:47 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Have you unchecked the "Throw Exception On Database Error" property on the compute node?
If you have not, do this and read about the DATABASE SQLQSTATE register function in the docs.
regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| JT |
| Posted: Fri Aug 26, 2005 5:31 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
| Are the columns in MESSAGE_EXCEPTION_TABLE configured to accept NULL values ? |
|
| Back to top |
|
|
| goldym |
| Posted: Fri Aug 26, 2005 6:19 am Post subject: |
|
|
Centurion
Joined: 24 Jun 2005
Posts: 116
|
| Yes - the there are a couple of NON-NULLABLE FIELDS but I think those are being populated. It populated the EXCEPTION TABLE just fine but doesn't continue processing. You think it is throwing some kind of database error? That would cause it not to go to the next record? |
|
| Back to top |
|
|
| JT |
| Posted: Fri Aug 26, 2005 7:49 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
So, the 3rd iteration of element results in a successful insert to the MESSAGE_EXCEPTION_TABLE.
Do you have the Compute node property Treat warnings as errors checked ? It's possible that a warning is being generated.
Have you examined the SQLCODE/SQLSTATE values returned from the SQL call as suggested by mgk ? |
|
| Back to top |
|
|
| goldym |
| Posted: Sun Aug 28, 2005 9:07 am Post subject: |
|
|
Centurion
Joined: 24 Jun 2005
Posts: 116
|
[quote="JT"]So, the 3rd iteration of [i]element[/i] results in a successful insert to the MESSAGE_EXCEPTION_TABLE.
Do you have the Compute node property [i]Treat warnings as errors[/i] checked ? It's possible that a warning is being generated.
Have you examined the SQLCODE/SQLSTATE values returned from the SQL call as suggested by mgk ?[/quote]
Yes the first two iterations are inserted in to the proper table and then it gets to the 3rd iteration inserts correctly into the exception database and then that's it. I'm not sure its b/c I only have one loop not sure. I havent been able to example the SQLCODE/STATE values will have to wait until Monday when I get can to environment. Will know those Monday around noon. |
|
| Back to top |
|
|
| goldym |
| Posted: Wed Aug 31, 2005 9:00 am Post subject: |
|
|
Centurion
Joined: 24 Jun 2005
Posts: 116
|
| The problem was occuring in the parser. Even though I changed those fields to optional fields it was still treating them as Mandatory. Thanks for your help. |
|
| Back to top |
|
|
| goldym |
| Posted: Wed Aug 31, 2005 2:36 pm Post subject: |
|
|
Centurion
Joined: 24 Jun 2005
Posts: 116
|
I am trying to get a values from aa Element that will occur twice and insert the two values into two different fields in the database.
<Test a="LON" b="rates" c="20050609" d="100981">
<Element>
<Element2 type="total" amount="1" currency="YEN"/>
<Element2 type="dummy" amount="2" currency="EUR"/>
<Element2 type="total" amount="3" currency="DOL"/>
</Element>
</Test>
I am trying to get "amount" and "currency" when type = 'total'. I need to insert these values into a database. I will have 2 occurrences where type=total. I need to get amount and currency from both iterations.
I tried:
DECLARE X INTEGER CARDINALITY(InputRoot.MRM.Test.Element.Element2[]);
DECLARE Counter INTEGER 1;
WHILE Counter < X DO
DECLARE tempAmt CHARACTER THE(SELECT ITEM R.amount FROM InputRoot.MRM.Test.Element.Element2[Counter] AS R WHERE R.type = 'total');
DECLARE tempCur CHARACTER THE(SELECT ITEM R.currency FROM InputRoot.MRM.Test.Element.Element2[Counter] AS R WHERE R.type = 'total');
IF Counter = X THEN
DECLARE tempAmt2 CHARACTER THE(SELECT ITEM R.amount FROM InputRoot.MRM.Test.Element.Element2[Counter] AS R WHERE R.type = 'total');
DECLARE tempCur2 CHARACTER THE(SELECT ITEM R.currency FROM InputRoot.MRM.Test.Element.Element2[Counter] AS R WHERE R.type = 'total');
ELSE
END IF;
SET Counter = Counter + 1; -- Increment the counter each time the while condition is sastified
END WHILE;
But its not working does anyone have any ideas |
|
| Back to top |
|
|
| goldym |
| Posted: Fri Sep 02, 2005 4:10 am Post subject: |
|
|
Centurion
Joined: 24 Jun 2005
Posts: 116
|
[quote="goldym"]I am trying to get a values from aa Element that will occur twice and insert the two values into two different fields in the database.
<Test a="LON" b="rates" c="20050609" d="100981">
<Element>
<Element2 type="total" amount="1" currency="YEN"/>
<Element2 type="dummy" amount="2" currency="EUR"/>
<Element2 type="total" amount="3" currency="DOL"/>
</Element>
</Test>
I am trying to get "amount" and "currency" when type = 'total'. I need to insert these values into a database. I will have 2 occurrences where type=total. I need to get amount and currency from both iterations.
I tried:
DECLARE X INTEGER CARDINALITY(InputRoot.MRM.Test.Element.Element2[]);
DECLARE Counter INTEGER 1;
WHILE Counter < X DO
DECLARE tempAmt CHARACTER THE(SELECT ITEM R.amount FROM InputRoot.MRM.Test.Element.Element2[Counter] AS R WHERE R.type = 'total');
DECLARE tempCur CHARACTER THE(SELECT ITEM R.currency FROM InputRoot.MRM.Test.Element.Element2[Counter] AS R WHERE R.type = 'total');
IF Counter = X THEN
DECLARE tempAmt2 CHARACTER THE(SELECT ITEM R.amount FROM InputRoot.MRM.Test.Element.Element2[Counter] AS R WHERE R.type = 'total');
DECLARE tempCur2 CHARACTER THE(SELECT ITEM R.currency FROM InputRoot.MRM.Test.Element.Element2[Counter] AS R WHERE R.type = 'total');
ELSE
END IF;
SET Counter = Counter + 1; -- Increment the counter each time the while condition is sastified
END WHILE;
But its not working does anyone have any ideas[/quote]
Resolved with the following:
DECLARE X INTEGER CARDINALITY(InputRoot.MRM.Element.Element2[]);
declare cnt int 1;
DECLARE Counter INTEGER 1;
WHILE Counter <= X DO
If InputRoot.MRM.Element.Element2[Counter].eventtype='total' then
declare amt,cur char ;
Set Environment.Variables.amt[cnt] =InputRoot.MRM.Element.Element2[Counter].amount;
Set Environment.Variables.cur[cnt] =InputRoot.MRM.Element.Element2[Counter].currency;
Set cnt=cnt+1;
End If;
Set Counter=Counter+1;
End While; |
|
| Back to top |
|
|
|
|
