Skip to content

JDBC CALLABLE

Description

The JDBC CALLABLE processor is designed to execute stored procedures 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 CALLABLE 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 CALLABLE 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 CALLABLE 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_JDBC_CALLABLE" entity="JDBC_CALLABLE">
            <config>
                <![CDATA[
                    {
                        "dbDriverClass": "org.postgresql.Driver",
                        "dbUrl": "jdbc:postgresql://nexus.auspacific.net/db",
                        "dbUsername": "postgres",
                        "dbPassword": "password",
                        "callableStatement": "select sp_composer_update_request(?,?,?,?,?,?)",
                        "update" : false,
                        "jdbcBindings": {
                            "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 CALLABLE processor will execute the specified stored procedure in the database. The stored procedure sp_composer_update_request will be called 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
callableStatement The SQL statement to execute, including the stored procedure call
update A boolean indicating whether the processor should perform an update operation
jdbcBindings 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 callableStatement is correctly formatted and matches the stored procedure's signature in your database.
  • Remember that the configuration for the JDBC CALLABLE processor is required. If no configuration, or improper configuration is provided then a block violation will be thrown.