TIBCO Process to List All Files in a Folder and Insert Into Database

By | October 24, 2014

While working on some file based tibco projects, I came across a situation where I need to put a large number of files names from a directory to a database table so that another application can fetch the file names from the database and perform its own actions for the files. In this post, I am going to explain step by step how you can use List Files activity of the File Palette to list all the files in a drectory/folder and then iterate through the list using Grouping feature and then insert the file details to a database table.

Step 1: Create table in Database and Add JDBC Connection to the Project

For this example case, I am creating a very simple database table TBL_FILES which has only two columns to store File Name and File Size (in bytes).

Below query is used to create the table:

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

After creating the table in the database (I am using Oracle database), add JDBC Connection to your project from the JDBC Palette.

Configure JDBC Connection with information specific to your database. For me configuration of JDBC Connection is shown below:

tibco list files database connection

Step 2: Create TIBCO Process for Listing Files and Inserting to database

In this step, we will design our process with all the logic inside. Create a new process (I named it as FilesListDBInsertion Process.

In the process, add List Files activity from the Files Palette. Files that I want to list and place in the database are in the path D:\files\ in my local machine as you can see below:

tibco list files

In the Input tab of List Files activity, specify the name of the above directory from which we want to list the files:

tibco list files input

The List File activity will give details of all the files in the given directory including File Name, File type, File Size etc.

Since we want to iterate through all the files listed as output of List Files activity and insert the file names and size in the database table, we will add JDBC Update Activity inside Iteration group for this purpose.

For the Group, choose Iterate as Group Action and select List Files activity output in the Variables List. Give name to the Iteration Element. I named it as Files in my case. Below screenshot shows all details of iterate group configuration for this example scenario:

tibco list files iterate group

 

Indie the group, for JDBC Update activity, choose the database connection in the configuration and add insert query along with two parameters as shown in below screenshot:

tibco jdbc insert with parameters

In the Input tab of JDBC Update activity, map the input values for the two parameters. Use the Iteration Element of the group for this purpose as shown below:
tibco add file details to database

 

We are now done with the process design phase. Validate the project to ensure that there are no errors. Once validated successfully, proceed towards the next step in which we will test our process.

Step 3: Test List Files and Database Update Process in Designer Tester

Load the process in designer tester so that a new job is created for the process. As you can see below, process has run successfully:

tibco list files process testWe can verify in the database table that records have been added successfully for all the files that were there in the given folder:

tibco list files database update

 

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

2 thoughts on “TIBCO Process to List All Files in a Folder and Insert Into Database

  1. Abdelhak

    Hi Sir Ajmal
    Thank you for this tutorial ,i have a question please
    can we store into a database multiple files content using one JDBC UPDATE Activity !!?

    Reply
    1. Ajmal Abbasi Post author

      Dear Abdhelhak,

      Yes we can iterate through the content of the files and store the content into the database table using JDBC UPDATE activity. However, make sure that your database column is of correct type to store lengthy contnets (e.g. Clob). Thanks

      Reply

Leave a Reply

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