12 Replies Latest reply on Mar 7, 2008 8:01 AM by Mark Little

    Actions and jdbc

    Olivier Lemaitre Newbie

      Hello,

      I have been working with JBoss ESB for a few weeks.
      I am using it to prototype a database synchronisation and evaluate if it can suite this kind of use.

      Actually I have something that seems to work, but I would like to make it better.

      I use jdbc to acces my Oracle database. When I need to access it in my actions I connect and disconnect each time in order to execute my sql commands.

      I wanted to know how I could connect just once in a service in a way my actions can refer to this connection.

      I couldn't see neither a predefined action ( like a notifier or a jms router ) that could just accept a simple sql command as a parameter ( INSERT or UPDATE ). Is it something that I have to code myself ?

      Thanks for your answers.

        • 1. Re: Actions and jdbc
          Mark Little Master

          Can you describe your requirements a bit more?

          • 2. Re: Actions and jdbc
            Olivier Lemaitre Newbie

            Hello,

            To be more accurate I give you an example :

            I want to synchronise table T1 and table T2.
            When T1 changes then table T2 must be informed.

            I use 3 services :
            The first one is using the sql-listener to get the data from T1 and turn them into xml format using xml-sql function of the data base.
            The second one is doing transformation.
            The third one is doing enrichement and insert into T2.

            In the third service :
            My first action do the enrichement, I then open a connection with jdbc get the data I need, enrich the message and disconnect from the database.
            My second action do the translation from xml to sql.
            My third action do the insertion, I then transform my message which is in xml format into an sql statement and I reconnect to the same database to execute the statement, eventually I diconnect from the database.

            Actually I would like to do the connection only one time to the database, and then have something as describe below in the service:
            connect
            action1 : do enrichment (use the connection above)
            action3 : do transformation xml -> sql
            action2 : do insert (use the connection above)
            disconnect

            I could of course do all in one action, but I would like to let the enrichement apart in order to reuse it in another service.

            My second question was for action 3 (insertion), is there an existing action that would take and execute an sql statement.

            If it is not clear enough I don't mind to explain more.

            Thanks for your help.

            • 3. Re: Actions and jdbc
              Tom Fennelly Master

              Which version of the ESB are you using?

              We will soon have these capabilities in the ESB - hopefully in the first 4.2.1 FP (Feature Pack). Part of this will require an upgrade of the ESB to v1.0 of Smooks.

              As a relevant example of this type of ETL + Enrichment capability, see the following Smooks tutorial: http://milyn.codehaus.org/Smooks+Example+-+db-edi-etl. This particular tutorial is based on a usecase where you have an EDI message containing many "order" records that need to be extracted from the EDI message and inserted into a database. It shows how the SQL statements are dynamically constructed based on templates etc. It shows how they can conditionally executed etc. It's not an exact match for the usecase you outlined, but is relevant I think and shows some of the key capabilities.

              You can download and try it standalone, but it won't work in the ESB (v4.2.x) at this time (but will soon).

              • 4. Re: Actions and jdbc
                Olivier Lemaitre Newbie

                Hello,

                Thanks for your answer.

                I am using 4.2.1GA.
                I had a look at the tutorial. I think it wil be an interresting feature for the companies I work with because they use a lot of etl integration style with many accesses to the database.

                Unfortunatly it may not fulfill all my requirements except maybe for my second question. I think indeed that ( if I well understand ) I could map an xml message to an insert statement in a smooks configurator.

                For my first question I did some search.
                I think I can reformulate it in :

                How can I manage a jdbc connection pool with JBoss ESB ?

                I hope it will solve my problem. I am looking for a good tutorial on this. If you know one, let me know.

                Olivier.

                • 5. Re: Actions and jdbc
                  Mark Little Master

                  If you deploy JBESB into an application server, then that will be able to do the connection pooling for you.

                  • 6. Re: Actions and jdbc
                    Tom Fennelly Master

                    Just to add to Mark's comment...

                    When you're using the container managed connections via the datasource, don't go caching statements or connections... close them. Keeping them open over a short period is fine, but don't tie their lifecycle into e.g. the lifecycle of your action. There's no need to and it's dangerous. Prepared statements will be cached on the server, so don't be thinking there's a huge overhead in recreating them and (as Mark said) the datasource will pool the connections for you so close them once your done (the close really just returns them to the pool).

                    • 7. Re: Actions and jdbc
                      Tom Fennelly Master

                      Sorry, forgot to mention... caching the Datasource itself is fine i.e. so as to avoiding having to look that up every time.

                      • 8. Re: Actions and jdbc
                        Olivier Lemaitre Newbie

                        Thanks for these answers.

                        However I would like to use my services in standalone mode. One on the machine of the source database , and one on the machine of target database. I have not planned to use an application server, just a JBoss ESB server on another machine to manage messaging and xml transformations.

                        Maybe it is not a proper use or I have misunderstood something. If so tell me.

                        I think anyway that I will keep connect and disconnect into my actions for the moment.



                        • 9. Re: Actions and jdbc
                          Tom Fennelly Master

                          If you're using the ESB Server, then you should be able to use container managed Datasources there too (AFAIK).

                          If yer running the ESB in standalone mode... then you'll need to manage the connection etc manually. My understanding is that standalone is really only intended for demo use.

                          • 10. Re: Actions and jdbc
                            Kurt Stam Master

                            Actually we packaged up c3p0 for the standalone messageStore. You could use that. -K

                            • 11. Re: Actions and jdbc
                              Olivier Lemaitre Newbie

                              Thank you very much I think that I now have the keys to sort this out.

                              Just one thing I wanted to point out about the stand alone mode.

                              It is very important for me to understand this mode.
                              I have read David Chappell's book about ESBs. He says that an esb can be seen as many service containers (abstract enpoints) which can run independently. This allows integration to be ubiquitous/pervasive.

                              I see the jboos-esb.xml as the description of the service container. And if I want to use it independently I can use the standalone mode.

                              Am I wrong?

                              This point if very important for my understanding of an ESB.


                              • 12. Re: Actions and jdbc
                                Mark Little Master

                                The jboss-esb.xml is the description of a service. It's actually pretty independent of how that service is housed, e.g., JEE container or lightweight stand-alone.

                                The standalone deployment is meant for those people who don't want all of the capabilities of an application server. When we move to OSGi the difference will be a lot clearer.