Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

JDBC usage techniques for multi insert/update operations

Avatar

Avatar
Level 2
Sentah
Level 2

Likes

0 likes

Total Posts

29 posts

Correct Reply

0 solutions
View profile

Avatar
Level 2
Sentah
Level 2

Likes

0 likes

Total Posts

29 posts

Correct Reply

0 solutions
View profile
Sentah
Level 2

25-08-2008

Hi,<br /><br />I have a scenario wherein I have the following xml format(simplified version)<br /><br /><student><br /><examHeader><br /> <examNo>EX123</examNo><br /> <examMajor>IndustrialChemistry</examMajor><br /> <studentNo>AS221</studentNo><br /> <status>submitted</status><br /></examHeader><br /><examDetails><br /> <Questionnaire><br /> <answers><br /> <answer><br /> <questionId>12</questionId><br /> <questionVersion>1</questionVersion><br /> <requestId>refnumber</requestId><br /> <response>Y</response><br /> <comment>Default</comment><br /> </answer><br /> </answers><br /></Questionnaire><br /><br /> <br />Now I need to update the header details into 1 table and the details content into multiple table for each question/answer combination.<br /><br />What would be the best way to achieve this ? interms of performance and also reusablilty wise ?<br /><br />I can think of an SQL execute component to perform this , but it would be a series of sql inserts as the tables have foreign key relationships.<br /><br />how do we extract the repeatable data and insert into database in an efficient way ?<br /><br />Any help would be greatly appreciated.

Replies

Avatar

Avatar
Boost 5
Level 9
HowardTreisman
Level 9

Likes

5 likes

Total Posts

936 posts

Correct Reply

9 solutions
Top badges earned
Boost 5
Boost 3
Boost 1
Affirm 5
Affirm 3
View profile

Avatar
Boost 5
Level 9
HowardTreisman
Level 9

Likes

5 likes

Total Posts

936 posts

Correct Reply

9 solutions
Top badges earned
Boost 5
Boost 3
Boost 1
Affirm 5
Affirm 3
View profile
HowardTreisman
Level 9

25-08-2008

Hi Senthil

You have two options:

1. Create a counter, and a loop in your orchestration. Loop through the rows in your XML, and on each iteration, perform the SQL statement you require. This can be a bit fiddly, but does work.



2. This pattern comes up often enough to warrant us having built a component to automate it. We have a component that does this in a single step - it's called "XML2SQL". You can download it here:

http://www.avoka.com/apps/checkcookie?qpac=y&qpac_code=avokaESComponents&location=%2Fapps%2Fqpacdown...

More info here:

http://avoka.dnsalias.com/confluence/display/Public/XML+to+SQL+DSC



Howard

http://www.avoka.com

Avatar

Avatar
Level 2
Sentah
Level 2

Likes

0 likes

Total Posts

29 posts

Correct Reply

0 solutions
View profile

Avatar
Level 2
Sentah
Level 2

Likes

0 likes

Total Posts

29 posts

Correct Reply

0 solutions
View profile
Sentah
Level 2

26-08-2008

Hi Howard,



Thanks for the suggestions. I was planning to write a custom component which would perform the tricky multiple update inserts as the first option wouldnt really fit into the SOA paradigm.



Looks like you already have one.