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

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 directory/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:

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:

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

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:

 

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:

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:

 

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:

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