Setting Up a JDBC Connection to Query and Update Tables

After setting up a JDBC connection, a process executes the SELECT and UPDATE queries.

Prerequisites

Install the PostgreSQL database:

  1. Download and install PostgreSQL from http://www.postgresql.org/download/.
  2. Go to SQL Shell (psql) from the installed program.
  3. The PostgreSQL command line window starts.

Run JDBC.sql on the PostgreSQL database to create test tables. The script is located in the samples directory under the path TIBCO_HOME\bw\n.n\samples\palette\jdbc\Basic.

Procedure

  1. In the samples directory, select palette > jdbc > Basic and double-click tibco.bw.sample.palette.jdbc.Basic. For more information, see Accessing Samples .
  2. In the Project Explorer view, expand the tibco.bw.sample.palette.jdbc.Basic project.
  3. Set the default ApplicationProfile to match the OS you are running on. For more information, see Setting the Default Application Profile.
  4. Fully expand Module Descriptors under tibco.bw.sample.palette.jdbc.Basic and double-click Module Properties. The JDBC properties defined for the application are defined in the dialog. Provide a valid username, password, and database URL to connect to your PostgreSQL database.


  5. Fully expand the Processes directory and double-click JDBC_TEST.bwp.


  6. Verify your JDBC connection.
    1. Fully expand the Resources directory.
    2. Double-click JDBCConnection_PostgreSQL.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.


  7. Click File > Save to save the project.
  8. Click Run > Debug Configurations.
  9. At the left hand tree of Debug Configuration wizard, expand BusinessWorks Application and select BWApplication.
  10. 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.Basic.application.
  11. Click Debug.
    This runs the sample in Debug mode.
  12. Click the Terminate icon to stop the process.

Result

  • The order_description record in the sub_order table is updated to TESTING JDBC CONNECTION.
  • The JDBC_Basic.log output file at C:\tmp\JDBC_Basic shows that one record was written.

Understanding the Configuration

The JDBC_TEST.bwp process uses the JDBC Connection in the Resources folder to establish a connection to the database and execute SELECT and UPDATE queries. First it queries the table for a specific record using the JDBC_Query activity and then it updates another table using the JDBC_Update activity. Finally it updates a file indicating the number of records written.

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.