2 Replies Latest reply on Aug 26, 2008 5:29 AM by Sentah

    JDBC usage techniques for multi insert/update operations

    Sentah
      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.
        • 1. Re: JDBC usage techniques for multi insert/update operations
          HowardTreisman Level 1
          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% 2Fqpacdownload
          More info here:
          http://avoka.dnsalias.com/confluence/display/Public/XML+to+SQL+DSC

          Howard
          http://www.avoka.com
          • 2. Re: JDBC usage techniques for multi insert/update operations
            Sentah Level 1
            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.