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:
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:
In order to enable Batch JDBC Update, we need to check Batch Update checkbox in the Advanced tab which is unchecked by default:
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:
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:
This 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:
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:
I hope this Tutorial will be helpful for you. Contact me for any guidance or help needed.
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
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
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
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.
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.
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.
use parse data and write a condition over there.
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?
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
same question. Any answer please
Use ADB adapters.may be it will be easy for you
how to insert datetime realtime
mention numberrecords =1000 in the Parser configuration and put in Jdbc in group try
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.