6 Replies Latest reply on Jul 13, 2011 1:45 PM by Jochen Lauer

    Question on sql result processing

    Jochen Lauer Apprentice

      Hello,

       

      I have some questions regarding the processing of an sql endpoint.

      My Route looks like

       

           

                     <simple>${in.body}</simple>

                           

                       <camel:setBody><el>${in.body[xxx]}</el></camel:setBody>

                        

                                magic stuff

                     <setHeader headerName="value of paramName from sql">

                          <constant>"value of paramValue from sql"</constant>

                      

       

      and I tried to solve the whole processing without an extra bean but I failed.

       

      How can I access the values ?

      I tried it with a tokenize, the simple but I did not got the expected result. Most of the implementations end up in a javax.el.PropertyNotFoundException.

       

      I know about a solution using an extra bean but since I try to keep my custom code base as small as possible using the power of the existing components.

       

      Thanks in advance

        • 1. Re: Question on sql result processing
          Claus Ibsen Master

          What do you want to do?

           

          Do you want to process each record of the SQL ResultSet?

           

          You can just split the body

          <split>
             <simple>${body}</simple>
             <!-- do what you want with the record -->
          </split>
          

           

          • 2. Re: Question on sql result processing
            Jochen Lauer Apprentice

            I solved the first part but I failed after the second sql.

             

            My payload after the sql looks like

             

            Exchange[ExchangePattern:InOnly, BodyType:java.util.ArrayList, Body:[{paramName=leagueShortcut, paramValue=bl1}, {paramName=leagueSaison, paramValue=2005}]]

             

            and I need to convert it to an input of my camel-freemarker component.

             

            The 2 fetched rows should be converted to two key value pairs

             

            leagueShortcut=bl1

            leagueSaison=2005

             

            The information could either be stored in the header or the body to be accessable in the template via ${header.leagueShortcut} and ${header.leagueSaison} or ${body.leagueShortcut} and ${body.leagueSaison}

             

            Regards

            labo

            • 3. Re: Question on sql result processing
              Jochen Lauer Apprentice

              I got it.... but not in the way I'd want to do.

               

                    map : inputMap){

                             String pn = map.get("paramName");

                             String pv = map.get("paramValue");

                             inEx.getIn().setHeader(pn, pv);

                        }

                   }

               

               

              Now the question:

              Is there a simple way to replace the process bean with the use of camel components ?

              • 4. Re: Question on sql result processing
                Claus Ibsen Master

                I suggest to look at documentation for Freemarker how to access list and maps, as I would assume you could define this in the template.

                 

                In Camel you can also use some of the languages to set those 2 fields as headers. If you use a recent version of Camel the Simple language may be able to do that out of the box

                http://camel.apache.org/simple.html

                 

                <setHeader headerName="pn"><simple>${body[0][paramName]}</simple></setHeader>
                <setHeader headerName="pv"><simple>${body[0][paramValue]}</simple></setHeader>
                

                 

                And then use the pn and pv in the freemarker template.

                 

                The Simple language may have issues doing a double access as above, but some of the other languages is more powerful

                http://camel.apache.org/languages.html

                • 5. Re: Question on sql result processing
                  Claus Ibsen Master

                  The Simple language cannot do chained [] so I have created a ticket to add support for that in Camel 2.8

                  https://issues.apache.org/jira/browse/CAMEL-4218

                   

                  As a wokaround you can do

                  <setHeader headerName="row"><simple>${body[0]}</simple></setHeader>
                  <setHeader headerName="pn"><simple>${header.row[paramName]}</simple></setHeader>
                  <setHeader headerName="pv"><simple>${header.row[paramValue]}</simple></setHeader>
                  

                   

                  • 6. Re: Question on sql result processing
                    Jochen Lauer Apprentice

                    It is not exact what I want to do

                     

                    The sql returns 0....n rows and for each row I need to set a header

                    where the header name is the paramName and the header value is the paramValue

                     

                    Is it possible to set the header name from a variable..... something like

                     

                    setHeader headerName="${header.row[[paramName]|http://]}".....

                     

                    BTW: what ist the syntax for a code block ?