Connecting to MySql database and fetching records using JDBC connection in TIBCO BusinessWorks

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:-
clip_image002

Save the project with the directory path :-
clip_image004
Your project pane would look something like this :-
clip_image006
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.

clip_image008

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

clip_image010

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.

clip_image012

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”.

clip_image013

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.

clip_image014

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

clip_image016

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

clip_image018

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 clip_image019 (“Create transition” button) as shown below.

clip_image021

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

clip_image023

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.

clip_image025

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

clip_image027

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

clip_image029

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.

Advertisements

,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Random Thoughts

The World as I see it

Simple Programmer

Making The Complex Simple

Ionic Solutions

Random thoughts on software construction, design patterns and optimization.

Long (Way) Off

A tragic's view from the cricket hinterlands

%d bloggers like this: