|
|
News: Xmarter TIBCO Consultants begin work for a large gambling company to implement an enterprise solution using TIBCO BusinessEvents and the TIBCO ActiveMatrix products.
Xmarter Thiking: "Why stand we here idle? What is it that gentlemen wish? What would they have? Is life so dear, or peace so sweet, as to be purchased at the price of chains and slavery? Forbid it, Almighty God! I know not what course others may take; but as for me, give me TIBCO
or give me death!"
|
|
Home  TIBCO Training Center TIBCO Designer Tutorial - Query Database Table with Pa...
|
TIBCO Designer Tutorial - Query Database Table
with Parameters
This TIBCO tutorial shows you how to use TIBCO Designer to connect to a database and query a table with a WHERE clause containing a parameter. Instead of just selecting all the records from the database table, we will select only the ones that meet a certain criteria. The target audience are TIBCO developers or those interested in becoming one.
The prerequisite to this tutorial is the tutorial titled TIBCO Designer Tutorial - Query Database Table. Make sure you complete that prerequisite before proceeding with this tutorial.
In this tutorial, we will continue to use the Oracle database and the BOOK table in that database. Instead of selecting all the book records like we did in the prerequisite tutorial, here we will only select the books published after the year 2000. We will use a WHERE clause with a parameter in our SQL statement to accomplish this.
Open up TIBCO Designer, and follow these steps:
- Open the existing project called XmarterTIBCOTutorials.
-
In the GetBooks process definition, click on the JDBC Query activity. Change the JDBC Query activity as follows:
-
In the Configuration tab of the activity:
-
At the SQL Statement field, add a WHERE clause to the existing SQL statement. The final statement should look like this:
SELECT * FROM BOOK WHERE YEAR > ?
The ? represents a parameter that can accept data passed into it. You will notice on the right-hand side of the SQL Statement field that there is now a warning (an exclamation point). This means that you must create a corresponding parameter element in the Prepared Parameters section.
- In the Prepared Parameters section, click on the plus button to add a new parameter. Give the parameter the same name as the field. For our case, name the parameter YEAR.
- Go to the Input tab of the activity. In the Activity Input section, expand the jdbcQueryActivityInput node, and you will notice that there is a YEAR element that is colored red. This means that it is a required field that you must now fill out. Type 2000 into the field.
- Click on the Fetch button below. This will fetch the resultSet structure of the SQL statement.
- Click on the Apply button on the activity, and then save the project.
-
Validate the resource and make sure there are no errors before testing the process.
- To do this, deselect all the activities by clicking on a white space inside the process definition. Then click on the Validate Resource button on the top section of TIBCO Designer.
-
Go to the Tester tab on the left-hand side of TIBCO Designer. Click on the Start testing button. In the dialog box that comes up, click on the Load & Start Current button.
- The process should now execute.
- To check the output, click on the JDBC Query activity, and go to the Output tab. Unlike in the prerequisite tutorial, here you should see only two book records instead of three in the Output tab. This is because one of the three records in the BOOK table has a year that is older than 2000.
Once again, we have another demonstration of how easy it is to do database application programming with TIBCO. I hope this tutorial has been helpful to you.
[Printer-Friendly Version]
|
|
|
©2010 Xmarter, Inc. All Rights Reserved.
|
|
|
