Show TOC

Example: Using the JDBCLookupPipe ComponentLocate this document in the navigation structure

AsapSource reads data from Event Stream Processor and passes it to the JDBC lookup pipe. If required, the JDBC lookup pipe modifies the values of the 'charfield' column by using 'replaceValue1', and passes that data to FileSource, which then outputs that data to the file out.txt file.

Prerequisites

The cluster database stores configuration information. Ensure that the cluster is running, so that the adapter can interact with the projects on the cluster.

Procedure

  1. Create a table and then fill the table with data. For example, for a DB2 database, run the createTable_DB2.sql script.
    Modify this script to use it for any other databases.
  2. Update the DB properties in the JdbcLookupPipe.props file to point to the required database instance.
  3. Update the JdbcLookupPipe.bat or JdbcLookupPipe.sh script, and add JDBC driver JARs in the class path.
  4. Set the user name and password in the example environment:
    Operating System Step
    Windows
    1. Edit the set_example_env.bat script
    2. Set the ADAPTER_EXAMPLE_USERNAME and ADAPTER_EXAMPLE_PASSWORD variables to the desired user name/password combination.
    3. If SSL is enabled, set ADAPTER_EXAMPLE_CLUSTER_NODE_PROTOCOL to esps. If it is disabled, set it to esp.
    UNIX
    1. Edit the set_example_env.sh script
    2. Set the ADAPTER_EXAMPLE_USERNAME and ADAPTER_EXAMPLE_PASSWORD variables to the desired user name/password combination.
    3. If SSL is enabled, set ADAPTER_EXAMPLE_CLUSTER_NODE_PROTOCOL to esps. If it is disabled, set it to esp.
  5. Start SAP Event Stream Processor.
    Operating System Step
    Windows Open a command window:
    1. Start the cluster:

      start_server_cluster.bat

    2. Add project to the cluster, and start it on the cluster:

      start_project.bat
    UNIX Open a terminal window:
    1. Start the cluster:

      ./start_server_cluster.sh

    2. Start the project on the cluster:

      ./start_project.sh
  6. Start streamingsubscribe to subscribe to the project that is running on the cluster.
    Operating System Step
    Windows Open a command window and enter:

    streamingsubscribe.bat
    UNIX Open a terminal window and enter:

    ./streamingsubscribe.sh
  7. Start the AsapSource, FileSink, and JDBCLookupPipe components.
    Operating System Step
    UNIX Open a terminal window and enter:

    ./JdbcLookupPipe.sh
    Windows Open a command window and enter:

    JdbcLookupPipe.bat
  8. Upload data to the Server.
    Operating System Step
    UNIX Open a terminal window and enter:

    ./streamingupload.sh
    Windows Open a command window and enter:

    streamingupload.bat
    AsapSource reads this data (records) and passes it on to JDBCLookupPipe, which modifies the records according to data available and reference data from the database tables. JDBCLookupPipe then passes that data to FileSink, which then writes the records to file.
    • Table "test1" contains data "col1='AttributeKey'" and "col2='replaceValue1'". 'KeyDbCol1' is col1 in the props file, therefore, col1 column contains attribute keys.
    • These attribute keys are present in the incoming record column 'textfield'.
    • To replace the 'charfield' column value of a record to 'replaceValue1', include 'AttributeKey' as a value in 'textfield' column of a record
    See the esp_insert.txt file for more details. Records that do not have 'AttributeKey' as the 'textfield' column value are not modified.
  9. See contents of the out.txt file.
    Charfield data for some of the records is updated to 'replaceValue1' value.
Related Information