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

By | November 1, 2014

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 Batch Update feature of JDBC Update activity 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 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 a TIBCO Consultant By Profession with more than 6 years experience in TIBCO products. He has extensive practical knowledge of TIBCO Business Works, TIBCO Spotfire, TIBCO BE, EMS and TIBCO ActiveSpaces. He has worked on a number of highly critical integration projects in Telecom sector by using his skills in Tibco Designer, Adapters, TIBCO EMS, RV, Administrator, TIBCO BE, TIBCO ActiveSpaces etc. Ajmal Abbasi is also experienced in developing solutions using Oracle PL/Sql, Linux and Java. You can contact Ajmal Abbasi for Consultancy, Technical Assistance and Technical Discussions.

More Posts - Website - Facebook - LinkedIn

8 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
  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

Leave a Reply

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