{"revision":"c10276e953456835cdbb1f3a60d58ae1980be353","document":{"id":4239,"path":"build-workflows/workflow-studio/jdbc-step.md","title":"JDBC step","bundle":null,"doc_type":null,"release":"australia","body":"\n# JDBC step\n\nCreate a reusable action to send SQL commands to a relational database.\n\n**Note:**\n\n- This step requires an Integration Hub subscription. For more information, see [Legal schedules - Integration Hub overview](https://www.servicenow.com/content/dam/servicenow-assets/public/en-us/doc-type/legal/snc-addendum-integrationhub.pdf). After the required plugin is activated, the step is visible under Integrations.\n- The JDBC step runs only on a ServiceNow® MID Server with JDBC step capabilities.\n\n## Roles and availability\n\nThe JDBC step is available as an Workflow Studio action step. Users with the action\\_designer role can create a custom action with one or more action steps.\n\n## JDBC operations and MID Server timeouts\n\nFor JDBC operations, execute the Data Stream Action asynchronously and poll the Attachments \\[sys\\_attachment\\] table for results.\n\nThe MID Server processes the SQL statement, while the instance/main thread waits for context payloads to be inserted into the attachment table to query the next record.\n\nYou can adjust timeout values for JDBC operations with the following properties.\n\n- **__com.snc.process\\_flow.datastream.payload.timeout.seconds__**\n\n The amount of time the instance waits for the payload to be available from the JDBC execution in the MID Server. A bounded property with a minimum value of 0 seconds and a maximum value of 7200 seconds. The default time is 600 seconds.\n\n- **__com.snc.process\\_flow.datastream.async\\_child.timeout.seconds__**\n\n The amount of time allocated for the execution of a child plan in the MID Server.A bounded property with a minimum value of 0 seconds and a maximum value of 7200 seconds. The default time is 60 seconds.\n\n\n## Sanitizing inputs\n\nEscape all user inputs to eliminate the possibility of a malicious user from executing malicious SQL statements that can result in SQL injection on your target database. When you use data pills in JDBC step SQL statements, sanitize them first using [Sanitize SQL transform functions](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/build-workflows/workflow-studio/sanitize-sql-transform-functions.md). This transform function category automatically appears when a data pill is dropped into the SQL Statement input.\n\n## SQL operations inclusion list\n\nBy default, you can run the following SQL operations.\n\n- SELECT\n- INSERT\n- UPDATE\n- DELETE\n- SHOW\n- DESCRIBE\n\nTo 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 SQL operations, separated by comma. To learn more about MID Server properties, see [MID Server properties](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/servicenow-platform/r_MIDServerProperties.md).\n\n**Note:** Multiple SQL statements are not allowed. Stored procedures with output parameters are not supported.\n\n## Fields\n\n\n
\n\nField\n\n\n\nDescription\n\n
\n\nConnection Details\n\n
\n\nConnection\n\n\n\nType of connection to use.- **Define Connection Inline**: Define connection information within the action step.\n- **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.\n\n To learn more about connections and credentials, see [Introduction to credentials, connections, and aliases](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-security/credentials-connections-alias.md).\n\n
\n\nConnection Alias\n\n\n\nConnection & Credential alias record that 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](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-security/credentials-connections-alias.md). The credential value is displayed as a Password \\(2 Way Encrypted\\) data pill on the data panel. This field is available when **Use Connection Alias** is selected from the Connection list.\n\n
\n\nCredential Alias\n\n\n\nCredential alias that 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](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-security/credentials-connections-alias.md). The credential value is displayed as a Password \\(2 Way Encrypted\\) data pill on the data panel. This field is available when **Define Connection Inline** is selected from the Connection list.\n\n
\n\nMID Selection\n\n\n\nOption to select a specific MID Server or MID Cluster. Choose any one of the following options. - **Auto-Select MID Server**: Your ServiceNow instance selects the MID Server without manual input.\n- **Specific MID Server**: Your ServiceNow instance uses MID Server that you specify.\n- **Specific MID Cluster**: Your ServiceNow instance uses the MID Cluster that you specify.\n\nA MID Cluster is a group of MID Servers that enables your ServiceNow instance to handle multiple integrations, and improve integration speed. For more information, see [Configure a MID Server cluster](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/servicenow-platform/t_ConfigureAMIDServerCluster.md).\n\n\n This field is available when **Define Connection Inline** is selected from the Connection list.\n\n
\n\nMID Cluster\n\n\n\nData pill for the MID Cluster you want to use. This field is available when **Define Connection Inline** is selected from the Connection list, and **Specific MID Cluster** is selected from the MID Selection list.\n\n
\n\nDatabase Type\n\n\n\nDatabase type for this connection. The choices are:- **MySQL**\n- **Oracle**\n- **SQLServer**\n- **Custom**\n\nThe default choice is **Custom**. This field is available when **Define Connection Inline** is selected from the Connection list.\n\n
\n\nJDBC Driver\n\n\n\nDriver 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.\n\n
\n\nConnection URL\n\n\n\nURL 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.\n\n
\n\nMID Application\n\n\n\nApplication 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.\n\n
\n\nCapabilities\n\n\n\nCapability of the MID Server. Select**JDBC**. This field is available when **Define Connection Inline** is selected from the Connection list.\n\n
\n\nConnection Timeout\n\n\n\nMaximum 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.**Note:** Avoid setting the **Connection Timeout** value to zero, as this may cause a stale connection.\n\n
\n\nQuery Timeout\n\n\n\nMaximum 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.\n\n
\n\nJDBC Configuration\n\n
\n\nSQL Statement\n\n\n\nSQL statement that the step executes.**Note:** When you use data pills in step SQL statements, sanitize them first using a preprocessing [Script step](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/build-workflows/workflow-studio/javascript-step-action-designer.md). For more information, see [Sanitizing inputs using the escape functions](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/build-workflows/workflow-studio/jdbc-step.md).\n\n
\n\nMaximum Rows\n\n\n\nMaximum number of rows to be returned from the SQL statement. The default value is **1000**.\n\n
\n\nMaximum Payload Size \\(KB\\)\n\n\n\nMaximum allowable payload size, in KB, to be returned from the SQL statement. The default payload size is **5120 KB**. The maximum payload size is **10 MB**.\n\n
\n\nTest JDBC Step\n\n\n\nTest the JDBC step. View test results directly in the Test JDBC Step window. For more information, see [Test JDBC step](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/build-workflows/workflow-studio/test-jdbc-step.md).\n\n
\n\nRetry Policy\n\n
\n\nEnable Retry Policy\n\n\n\nOption to enable the retry policy. For more information, see [Retry policy](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/build-workflows/workflow-studio/retry-policy.md).\n\n
\n\nOverride Default Policy for Alias\n\n\n\nOption to override the default retry policy. This option is not applicable when **Define Connection Inline** is selected from the Connection list.\n\n
\n\nRetry Policy\n\n\n\nDefault 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.\n\n
## Action error evaluation\n\n- **If this step fails**\n\n Data type: **Choice**\n\n Option to continue running the next step or go to error evaluation. To use the step status code or message for a custom action error condition, see [Action error evaluation](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/build-workflows/workflow-studio/action-error-evaluation.md).\n\n\n- **[Test JDBC step](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/build-workflows/workflow-studio/test-jdbc-step.md)** \nTest the JDBC step before testing or publishing an action that contains the JDBC step.\n\n**Parent Topic:**[Workflow Studio steps](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/build-workflows/workflow-studio/steps.md)\n\n"}}