TIBCO JDBC Batch Update Tutorial: Insert Multiple Records With One JDBC Update Activity

JDBC Update activity from JDBC Palette is used to update database records or insert new records. In the configuration tab of JDBC Update activity, you can write SQL query to insert new record. What if you want to insert multiple records to a database table in a batch?
In this tutorial, I will explain how you can use TIBCO JDBC Batch Update feature  to insert multiple records at a time in the table.

Step 1: Create JDBC Connection

This is always the first step that you need to perform for any projects involving database interactions. From the JDBC Palette, add JDBC Connection resource in your project and configure it according to your own database configurations.

In my case configurations of JDBC Connection are shown in below screenshot:

jdbc batch update jdbc connection

Using the Test Connection option, confirm that your JDBC Connection is successful.

 

Step 2: Create Process for TIBCO JDBC Batch Update to Insert Multiple Records

Create a new Process and add JDBC Update Activity in the process.

We want to add multiple Records into the table TBL_FILES which is already created in my database schema. The table has two columns (FILE_NAME and FILE_SIZE). It was created using below SQL Query:

create table TBL_FILES
(
FILE_NAME VARCHAR2(50) not null,
FILE_SIZE NUMBER
)

In the process, configure JDBC Update Activity by specifying the connection in the configuration tab. Also, add sql query in the SQL Statement field and add two parameters in the Prepared Parameters section as shown below:

tibco jdbc batch update configuration screenshot

In order to enable Batch JDBC Update, we need to check Batch Update checkbox in the Advanced tab which is unchecked by default:

tibco jdbc batch update advanced tab

After checking this Batch Update option, now if you go to Input tab, you will see that there is a * with Records element which means that you can add multiple Records.

By Right Clicking on Record and using Duplicate option from the Statement, you can add multiple Records as shown below:

tibco statement duplicate

You can add as many records as you want by this method. I am adding two records here in this example scenario as you can see in below screenshot:

tibco jdbc multiple records batch updateThis completes input mappings for our Batch Update process.

Validate the project to make sure everything is fine. Once validated successfully, proceed to next step in which we will test JDBC Batch Update process in designer tester.

 

Step 3: Test JDBC Batch Update Process

Load the process in designer tester so that Its job is created. As you can see in below screenshot, process has run successfully and JDBC Update Activity output shows that 2 Records have been updated:

designer tester jdbc batch update

 

Now you can verify the results by going to database table and querying that. As you can see below, two records in a batch have been inserted successfully in database table:

database records batch update screenshot

I hope this Tutorial will be helpful for you. Contact me for any guidance or help needed.

 

Ajmal Abbasi

Ajmal Hussain Abbasi is Integration Consultant By Profession with 13+ years experience in Integration domain mainly with TIBCO products. He has extensive practical knowledge of TIBCO Business Works, TIBCO Cloud, TIBCO Flogo, TIBCO Mashery, TIBCO Spotfire, EMS and TIBCO ActiveSpaces. He has worked on a number of highly critical integration projects in various sectors by using his skills in TIBCO Flogo, TIBCO API Management (Mashery), TCI, Tibco Designer, TIBCO Business Studio, Adapters, TIBCO EMS, RV, Administrator, TIBCO BE, TIBCO ActiveSpaces etc. Ajmal Abbasi has experience with MuleSoft ESB as well. Ajmal Abbasi is also experienced in the area of API Management particularly with WSO2 API management platforms. Ajmal Abbasi is also experienced in developing solutions using Core Java and J2EE Technologies. You can contact Ajmal Abbasi for Consultancy, Technical Assistance and Technical Discussions.

More Posts - Website - Facebook - LinkedIn - YouTube

14 thoughts on “TIBCO JDBC Batch Update Tutorial: Insert Multiple Records With One JDBC Update Activity

  1. syahidah

    Tutorials give yours a very nice and clear .
    I want to ask how to insert data an array in TIBCO,please answer my question at all soon .
    Thank’s

    Reply
  2. Y VENKATA VARA PRASAD

    Sir,my requirement is for every updation of records in DB,these records and updated time should have to write into one file.For this i have taken Timer activity and JDBC Update and Write file.i am able to write the records but i am not able to write the time of update of records time…..
    please tell me the steps….the above requirement

    Reply
  3. Raj Basha

    hi,,i have 20 record of csv file am insert into DB but i want to insert 5 record each time in DB using JDBC UPDATE…But Batch Update is insert all 20 values at time..but i want to insert 5 records for one batch……pls give reply…thanks in advance

    Reply
  4. sayyad

    hello,i have total 30 records in a csv file format and i want to update them in database and 6 records per each time.till now i felt JDBCUPDATE is the activity which we have to use to update that. but i have no idea how to do the process please help me to to get my solution as soon as possible.

    thanking u,
    hopefully.

    Reply
    1. Abhishek

      How to insert bulk records through SQL direct palette..? i tried with Insert all statement but it is giving exception like sql command not properly ended , but when run on oracle, it worked.

      Reply
      1. Deepa

        This issur normally comes when we put semicolon after statement while writing query into pallete Bcoz in Oracle we use semicolon after every query . You might have copied the same.

        Reply
  5. kiran kumar

    Ajmal nice to see your above multiple create processes.Suppose if we get the records from a parse data .How should we add to this jdbc update?

    Reply
  6. basha

    HI Ajmal,i have 10000 record of csv file am insert into DB but i want to insert 1000 record each time in DB using JDBC UPDATE…But Batch Update is try to insert all 10000 values at a time.while this time it throws a heap size error, i need to insert 1000 records for one batch……pls give reply…thanks in advance

    Reply
  7. Ananda Dayyala

    mention numberrecords =1000 in the Parser configuration and put in Jdbc in group try

    Reply
  8. anjali shevadkar

    hi , I’ve to read the data from SQL table and store in other sql table (SQL to SQL data ingestion). How can I do this.
    How to add row seperator.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *