9 Replies Latest reply on May 8, 2017 11:20 AM by Martien van den Akker

    How to use db adapter for multiple table update

    3340980

      Hi,

      I am trying to use db adapter using Oracle BPM 12C to insert/update data from multiple tables (external oracle db tables). The scenario is like -

      Suppose I am having 7 tables in db. Out of 7 tables 5 are having relations and 2 are not having any relations. And the requirement is to insert or sometimes update the existing data in these tables in my BPM process. Although this might be a small job to do but as I am new to this technology, can anyone of you please suggest me the best way to do this with steps if possible.

        • 1. Re: How to use db adapter for multiple table update
          Carlos Cortez

          Hello,

           

          If you are going to insert or update on multiple tables, then you could use a BPEL component and expose it as a web service to consume it from BPM, then you pass the arguments to the web service.

           

          Inside BPEL you will call several db adapters as your needs, each inserting or updating in the righ table, something like this:

           

           

          This is an approach that you can use if you don't want to have a lot of db adapters in your BPM Composite, also in a future if you need to change something related to the inserts, updates etc then you only have to modify the BPEL, not the BPM process.

           

          Regards

          Carlos

          • 2. Re: How to use db adapter for multiple table update
            Martien van den Akker

            Hi User and Carlos,

             

            Indeed, BPEL orchestration is one (good) approach. Although I'm a BPEL adept from the first hour (at least in the Netherlands) I often felt that I was somehow 'programming Pl/Sql in BPEL', put otherwise doing in BPEL what could be done in Pl/Sql. That why I one wrote http://www.darwin-it.nl/uploads/media/CodeGenerationWithXSLT.pdf . And a few years ago expanded this into a framework that also supported relationships.

             

            The BPEL DB adapter perfectly supports a large hierarchy of Object Types. Since 5 of your tables have relations ships, I would consider doing the insert/updates in a Pl/Sql procedure with the object hierarchy as a parameter. Even better: implement the particular insert/update statements as methods on the particular objects. Then from BPEL you only have to implement one DB Adapter in stead of one per table insert/update.

             

            If there is enough interest, I'd be interested to put my framework on github. Mind that I did not document this already, because lack of time. If you'd like to help me with that, it could be a nice and helpful project.

             

            Regards,
            Martien

            • 3. Re: How to use db adapter for multiple table update
              Vladimir Zhilyaev

              Hi, Martien

               

              I think your framework will be usefully for community. Show your work to the people =)

               

              Vladimir

              • 4. Re: How to use db adapter for multiple table update
                Carlos Cortez

                Hi Martien,

                 

                I would be nice if you show with us the project and we could help you with it.

                 

                Regards

                Carlos

                • 5. Re: How to use db adapter for multiple table update
                  3340980

                  Hi Martien,

                   

                  Vladimir & Carlos are absolutely correct. It would be good and helpful to put your framework for Oracle BPM community. Wherever, it needs our help, we will always be there.

                   

                  Sanjay

                  • 6. Re: How to use db adapter for multiple table update
                    Martien van den Akker

                    Hi,

                     

                    Nice. I proposed this to my management. I thought it would be best to ask approval.

                    If they go with it, I'll post it to GitHub. Keep you posted.

                     

                    Regards,
                    Martien

                    • 7. Re: How to use db adapter for multiple table update
                      Martien van den Akker

                      Hi all,

                      I need to show the current state of the framework to my manager. But he seems to be willing to have it put on Github.

                       

                      It mostly needs documentation on how to set it up initially. And a sample setup. For that we have an example datamodel.

                       

                      regards,
                      Martien

                      • 8. Re: How to use db adapter for multiple table update
                        Martien van den Akker

                        Hi All,

                         

                        I'm going to post our framework to GitHub. I have to make up a good name.  I called it Darwin Sabbatial as  a wink to Hibernate. But that wink might not be too obvious for the public.

                        I'll keep you posted.


                        Regards,
                        Martien

                        • 9. Re: How to use db adapter for multiple table update
                          Martien van den Akker

                          Hi All,

                           

                          A moment ago I put my source on Github: https://github.com/makker-nl/Dotacc

                          I renamed it to Darwin Oracle Type accelerator.  As said, I did not have change to create elaborate documentation.

                          But in short

                          • In $GitHub/Dotacc/Source/Dotacc/ddl/owner\ you'll find setup scripts for the framework. With setupTables.sql and setupPlsql.sql you create a bunch of tables and pl/sql with XXX_ as a prefix to support the generation of types.
                          • insertXslNL2.0.sql or insertXslEN2.0.sql creates the xsl in the xxx_xmldocuments table.
                          • $GitHub/Dotacc/Source/Dotacc/Config contains a set of insert scripts to define what tables, foreignkeys etc. to handle:
                            • XXX_TABLES: defines the tables for which you want to generate the types. The column generation_order defines in which order the types are created or dropped at recreation.
                            • XXX_FK_DEFINITIONS: defines the foreignkeys to consider. The FK_TYPE column defines if it should be considered as a child table ('DETAIL') or Lookup ('LOOKUP').
                            • XXX_DERIVED_COLUMNS: defines virtual columns that can be looked up from another table. You can define a method that is added to do the actual lookup based on a lookup value from a key column.
                            • XXX_CUSTOM_METHODS: can be used to add custom methods to the object type.

                           

                          I added two datamodels (Doe_owner and hbc_owner under ddl). It would be nice to come up with a sample filling for named tables.

                           

                          To do a recreate of the types, execute  xxx_gen_objects.recreate_objects;

                           

                          I'll add this in short to our blog (blog.darwin-it.nl).

                           

                          If you have remarks, post a comment on the blog and/or do a request to contribute on github or send me a PM.

                           

                          Regards,
                          Martien