This looks as naïve as connecting to a database from .NET using standard connection class. But, hey, that was interesting/exciting as well first time when you did that. In this example, we will connect to a MySql database from TIBCO Businessworks, fetch records and write the first record to a text file – as simple as that.

Create an empty project in TIBCO BusinessWorks designer:-

Save the project with the directory path :-

Your project pane would look something like this :-

Now, we need to add a JDBC connection. A JDBC Connection is a resource in TIBCO designer. Therefore for better organization, we create a “Resources” folder.

Add the JDBCConnection to the “Resources” folder as shown below.

JDBC Connection would be added as shown below and we need to select the driver and the connection string for the mysql database. Don’t forget the username and password. Click “Test Connection” to make sure the connection succeeds.

When you click “Test Connection”, you might see the below error :-

“BW-JDBC-100033 “Configuration Test Failed. Failed to find or load the JDBC driver. jdbc:mysql://localhost:3306/Research”

This really means BusinessWorks cannot see the MySql driver required for the connection. This driver is basically a .jar file [mysql-connector-java-5.1.23-bin.jar] which is located in the MySql installation directory. For my installation the directory is “C:\Program Files (x86)\MySQL\Connector J 5.1.23\mysql-connector-java-5.1.23-bin.jar”. All I needed to do is to copy that jar file to the TIBCO installation directory > Lib folder. For me, I needed to copy to “C:\tibco\bw\5.10\lib” directory. Save the project, close the designer and reopen the project. Now on the JDBC Connection, click “Test Connection”.

Voilà!!! Got connected this time. However, I am pretty sure there must be a better way (may be setting the classpath for the jar file) than copying the jar file itself.

Add a new folder named “Activities” to the root folder.

Add a “Process Definition” from the Palettes tab and double click on it to see the start and end points.

Add “JDBC Query” from the Palettes tab. In the configuration pane of the JDBC query, select the JDBC Connection as shown below :-

Add the Sql statement to get data from the mySql table. My sql statement was :-

SELECT ID, Name FROM Product

Writing the database query results to text file
Create a blank text file named “test.txt” in your project directory.

Add “Write File” from the Palettes tab and place it after the JDBC query. Connect them using the (“Create transition” button) as shown below.

In the “Input” tab of the “Write File”, enter the name of the blank text file we created earlier.

Now we need to set the contents of the file. The contents of the file would be the first record from the resultset returned by the JDBC query. Keep the cursor in the “textContent” textfield and click on the yellow pencil button.

XPath Formula Builder would open. In this, drag the Functions > String >Concat to the XPath Formula text field on the right.

Drag “Data > JDBC Query > resultSet > Record > Id” onto the <<string1>> and Drag “Data > JDBC Query > resultSet > Record > Name” onto the <<string2>>

Since we are getting only one record, change the following :-

concat($JDBC-Query/resultSet/Record/Id, $JDBC-Query/resultSet/Record/Name)

to this (index = 1 as XPath has starting index as 1):-

concat($JDBC-Query/resultSet/Record[1]/Id,”,”,$JDBC-Query/resultSet/Record[1]/Name)

Click “Apply” on the XPath Formula Builder and then on the Input pane of the “Write File”. Save the project. Run the project and you would see the file updated with the first record from the database.