Setting Up and Executing JDBC Activities with Eclipse Using DTP

Using JDBC palette activities, you can setup JDBC connections and execute JDBC activities to run queries and display the results. This sample shows how to execute SQL calls and edit data with Eclipse DTP at Design time.

Prerequisites

Install the Sakila Sample Database.

Procedure

  1. In the samples directory, select palette > jdbc > EclipseDTPIntegration and double-click tibco.bw.sample.palette.jdbc.EclipseDTPIntegration. For more information, see Accessing Samples.
  2. In the Project Explorer view, expand the tibco.bw.sample.palette.jdbc.EclipseDTPIntegration project.
  3. Set the default ApplicationProfile to match the OS you are running on. For more information, see Setting the Default Application Profile.
  4. Verify your JDBC connection.
    1. Fully expand the Resources directory.
    2. Double-click JDBCConnectionResource.jdbcResource.
    3. In JDBC Driver, click Click here to set preferences.
    4. Set the JDBC driver folder directory preference and click Apply. Click OK.
    5. Click the Test Connection button to verify the connection.


  5. Fully expand the Processes directory and double-click EclipseDTP.bwp.
  6. In the Process Editor, click JDBCQuery and then click Properties > Statement tab.
  7. Click SQL. This launches the SQL Query Builder. In SQL Query Builder, update or edit the statement, execute it, and view the result.
    1. Modify the statement by removing "where title=?" and click Execute the query to execute the statement at Design time by using DTP.


      In the SQL Results tab, the history for all operations displays at the left and Status, Result, and Parameters display at the right.
    2. Click the Data Source Explorer tab to view the connected database, BWProfiletibco.bw.palette.jdbc.eclipsedtpintegration.JDBCConnectionResource. Right-click on the table and select Data > Edit to edit the data.


    3. Open the scrapbook to edit and run the SQL statements by clicking the icon available in the Data Source Explorer view.
    4. The entry for any SQL for default tables is available in the Sakila schema. Right-click and select Execute All. See the results in the SQL Results view.


    5. Double-click JDBCQuery. Click Statement in the Properties tab. Modify the statement by adding "where title=?" at the end in statement.
  8. Click File > Save to save the project.
  9. Click Run > Debug Configurations.
  10. At the left hand tree of Debug Configuration wizard, expand BusinessWorks Application and select BWApplication.
  11. Click the Applications tab and then click the Deselect All button if you have multiple applications. Select the check box next to tibco.bw.sample.palette.jdbc.EclipseDTPIntegration.application.
  12. Click Debug.
    This runs the sample in Debug mode.
  13. Click the Terminate icon to stop the process.

Result

The console log contains messages similar to: INFO c.t.b.p.g.L.t.b.s.p.j.E.Log - FILM_ID-10001: Inventory of film id [17] in store id = [1] is 2.

Understanding the Configuration

The EclipseDTP.bwp process uses the JDBC Connection in the Resources folder to establish a connection to the database and executes SELECT and STORED PROCEDURE queries.

It queries the table for a specific record using the JDBC Query activity and then returns the available copies of a specified film in stock using the JDBC Call Procedure activity.

Finally it displays a number of records with MessageID in console.

Both activities use prepared parameters as Input. This shows the ability to execute the same statement with multiple values by caching the statement at runtime.