Thank you for your feedback.
Form temporarily unavailable. Please try again or contact docfeedback@servicenow.com to submit your comments.

JDBC step

Log in to subscribe to topics and get notified when content changes.

JDBC step

Create a reusable action to send SQL commands to a relational database.

Note:
  • The JDBC step is not available in the base system and requires the subscription to IntegrationHub Standard Pack Installer (com.glide.hub.integrations.standard) or later. For more information about the ServiceNow®IntegrationHub subscription packages, see IntegrationHub usage and subscription. After the required plugin is activated, the step is visible under Integrations.
  • The JDBC step runs only on a ServiceNow® MID Server with JDBC step capabilities. Activate the plugin, IntegrationHub Standard Pack Installer (com.glide.hub.integrations.standard) or later to use the JDBC capability for the MID Server.

Roles and availability

The JDBC step is available as an Action Designer action step. Users with the action_designer role can create a custom action with one or more action steps.

Sanitizing inputs using the escape functions

Escape all user inputs to eliminate the possibility of a malicious user from executing malicious SQL statements, resulting in SQL injection on your target database. When you use data pills in JDBC step SQL statements, sanitize them first using a preprocessing Script step.

In the Script step, use the escape APIs, escapeSQLIdentifier and escapeSQLValue. These APIs are available as static methods of the server-side script class, SNC.GlideSQLEscapeUtils. For example,
var escapedValue = SNC.GlideSQLEscapeUtils.escapeSQLValue("MYSQL", "Georgi's Birthday");
The escaped values from the escape APIs are the output of the Script step. Use this escaped output in the SQL statements of the JDBC step.
Table 1. Escape APIs
Escape API Description
escapeSQLIdentifier
  • Escapes special characters and injected values in the SQL identifiers.
  • Validates input for a period character. Throws an SQLEscapeException error when a period character is encountered.
Note: To join SQL identifiers using a period, use two pills, which are ID escaped, joined by the period.
escapeSQLValue Escapes special characters and injected values in SQL values by wrapping the input in database-specific quotes.

Whitelisting SQL operations

By default, you can run the following SQL operations.
  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • SHOW
  • DESCRIBE
To enable only some of these SQL operations that the JDBC step can perform, create a MID Server property, mid.property.jdbc_operations and enter the whitelisted SQL operations, separated by comma. To learn more about MID Server properties, see MID Server properties.

Fields

Field Description
Connection Details
Connection Type of connection to use.
  • Define Connection Inline: Define connection information within the action step.
  • Use Connection Alias: Define connection information using the Connection Alias table. Using an alias eliminates the need to configure multiple credentials and connection information profiles when using an action in multiple environments. Likewise, if the connection information changes, you don't need to update your custom action.

To learn more about connections and credentials, see credentials, connections, and aliases.

Connection Alias Connection & Credential alias record the system uses to run the action step. Users with the flow_designer or admin role can create or select an associated Connection record. Using an alias eliminates the need to configure multiple credentials and connection information profiles when using an action in multiple environments. Likewise, if the connection information changes, you don't need to update your custom action. To learn more about connections and credentials, see credentials, connections, and aliases. The credential value is displayed as a Password (2 Way Encrypted) data pill on the data pane. This field is available when Use Connection Alias is selected from the Connection list.
Credential Alias Credential alias the system uses to run the action step. Users with the flow_designer or admin role can create or select an associated Connection record. Using an alias eliminates the need to configure multiple credentials when using an action in multiple environments. Likewise, if the credential information changes, you don't need to update your custom action. To learn more about connections and credentials, see credentials, connections, and aliases. The credential value is displayed as a Password (2 Way Encrypted) data pill on the data pane. This field is available when Define Connection Inline is selected from the Connection list.
Database Type Database type for this connection. The choices are:
  • MySQL
  • Oracle
  • SQLServer
  • Custom
The default choice is Custom. This field is available when Define Connection Inline is selected from the Connection list.
JDBC Driver Driver to use for this connection when it's not a default database type such as DB2 Universal and Sybase. The database Type is Custom. This field is available when Define Connection Inline is selected from the Connection list and Custom is selected from the Database Type list.
Connection URL URL that the MID Server uses to connect to the specified database. The URL is created automatically when you save the form, and is read-only for the default databases. This field is available when Define Connection Inline is selected from the Connection list and Custom is selected from the Database Type list.
MID Application Application that the MID Server must support to be eligible for selection. This field is available when Define Connection Inline is selected from the Connection list.
Capabilities Capability of the MID Server. Select JDBC. This field is available when Define Connection Inline is selected from the Connection list.
Connection Timeout Maximum elapsed time, in seconds, for the activity to wait while attempting to connect to the target database. This field is available when Define Connection Inline is selected from the Connection list.
Query Timeout Maximum elapsed time, in seconds, that the query is allowed to run without a response. This field is available when Define Connection Inline is selected from the Connection list.
JDBC Configuration
SQL Statement SQL statement that the step executes.
Note: When you use data pills in step SQL statements, sanitize them first using a preprocessing Script step. For more information, see Sanitizing inputs using the escape functions.
Maximum Rows Maximum number of rows to be returned from the SQL statement. The default value is 1000.
Maximum Payload Size (KB) Maximum allowable payload size, in KB, to be returned from the SQL statement. The default payload size is 5120 KB. The maximum payload size is 5 MB.
Retry Policy
Enable Retry Policy Option to enable the retry policy. For more information, see Retry policy.
Override Default Policy for Alias Option to override the default retry policy. This option is not applicable when Define Connection Inline is selected from the Connection list.
Retry Policy Default retry policy associated with Connection Alias. If Override Default Policy for Alias is selected, you can override the default retry policy and select another existing retry policy based on your requirement.

Test JDBC step

Test the JDBC step before testing or publishing an action that contains the JDBC step.

About this task

It is mandatory that you test the JDBC step before testing the action. Testing ensures that the relevant complex object output schema is created from table columns, which can be used as data pills in subsequent steps.

Procedure

  1. Navigate to Flow Designer > Designer.
  2. Create an action with a JDBC step.
  3. Click Test JDBC Step.
    The Test JDBC Step pop-up window is displayed.
  4. If the JDBC step takes an action input or output of the previous step as its input, provide required input values in theStep input pills field to test the JDBC step.
    Note: Input values in the Step input pills fields are not needed when records are updated, inserted, or deleted.
  5. Click Run Test.
    • When a SELECT query is executed, Sample Result is displayed in the Test JDBC Step pop-up window. Sample Result includes column names, columns types, and the values of the first row.
    • When an UPDATE, INSERT, or DELETE query is executed, a message is displayed mentioning the number of rows affected.
  6. To use the sample result as the JDBC step output, click Use Result.
    Note: Use Result is not displayed when records are updated, inserted, or deleted.
  7. To retrieve schema of a different table when a SELECT query is executed in the JDBC step, enter the required value in the Step input pills field and click Run Test.

Result

When a SELECT query is executed in the JDBC step, ResultSet is displayed under Outputs. The relevant complex object output is populated. To learn more about complex objects, see Complex data.

What to do next

Test and publish the action.
Feedback