Skip to content

JDBC STATEMENT

Description

The JDBC STATEMENT processor is designed to execute SQL statements in a database using JDBC. This processor is particularly useful for scenarios where database operations, such as data insertion, update, or complex queries, need to be performed as part of a data processing pipeline.

Config Location

To configure the JDBC STATEMENT processor, you need to define it within the schemaAppliedProcessors section of your schema configuration. This involves specifying the processor name and entity.

Supported Data Types

  • String
  • Double
  • Decimal
  • Integer
  • Json
  • BLOB
  • Boolean

Config Requirements

Config ({}) options are required for the JDBC STATEMENT processor. If no configuration is provided or if the configuration is improperly set up, a block violation will be thrown. For more information on Apiro violations and their appearance in the Apiro UI or logs, refer to the violations section.

Example Config

Below is an example of how to configure the JDBC STATEMENT processor in XML format:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<schemaAppliedProcessors>
    <dataBlockProcessors>
        <dataBlockProcessor name="BLOCK_STATEMENT" entity="JDBC_STATEMENT">
            <config>
                <![CDATA[
                    {
                        "dbDriverClass": "org.postgresql.Driver",
                        "dbUrl": "jdbc:postgresql://nexus.auspacific.net/db",
                        "dbUsername": "postgres",
                        "dbPassword": "password",
                        "statement": "select sp_composer_update_request(?,?,?,?,?,?)",
                        "update" : false,
                        "bindings": {
                            "dp1": "#GRV{ ''+CTX['DP1'] }",
                            "dp2": "#GRV{ ''+CTX['DP2'] }",
                            "dp3": "#GRV{ ''+CTX['DP3'] }",
                            "dp3": "#GRV{ ''+CTX['DP4'] }",
                            "dp4": "#GRV{ ''+CTX['DP5'] }"
                        },
                        "completionExpression" : "${LOCAL:COMPLETE_EXPR}"
                    }
                ]]>
            </config>
        </dataBlockProcessor>
    </dataBlockProcessors>
</schemaAppliedProcessors>

Example Result

Upon successful configuration and execution, the JDBC STATEMENT processor will execute the specified SQL statement in the database. The SQL statement select sp_composer_update_request(?,?,?,?,?,?) will be executed with the parameters dynamically populated from the context (CTX). The processor will not perform an update operation as indicated by the "update": false configuration.

Config Parameters

name acceptable values comment
dbDriverClass The fully qualified class name of the JDBC driver
dbUrl The JDBC URL for the database connection
dbUsername The username for the database connection
dbPassword The password for the database connection
statement The SQL statement to execute
update A boolean indicating whether the processor should perform an update operation
bindings A JSON object mapping parameter names to their values, which can be dynamically populated from the context (CTX)
completionExpression An expression to evaluate upon completion of the processor's execution, allowing for further processing or decision-making based on the outcome

Common Mistakes

  • Ensure that the processor name and entity are correctly defined in the configuration to ensure that the processor is correctly identified and applied during the data processing pipeline.
  • Verify that the dbDriverClass, dbUrl, dbUsername, and dbPassword are correctly set up for your database environment.
  • Ensure that the statement is correctly formatted and matches the SQL statement you intend to execute in your database.
  • Remember that the configuration for the JDBC STATEMENT processor is required. If no configuration, or improper configuration is provided then a block violation will be thrown.