Creating a Data Access Service Connector

• •Databricks
• •IBM DB2
• •Microsoft SQL Server
• •MySQL
• •Oracle
• •PostgreSQL

1. 1In Application Integration, click New > Service Connectors.
2. 2In the New Asset dialog box, click Data Access Service Connector using Form, and then click Create.

Defining Properties

1. 1In Application Integration, click New > Service Connectors > Data Access Service Connector using Form > Create.
The data access service connector editor appears.
The following image shows the data access service connector editor:

2. 2On the Definition tab, define the following basic properties for the data access service connector:
• - Name: The name by which the data access service connector is made available for processes. The name must start with a letter or number, and can contain only alphanumeric characters, multibyte characters, underscores (_), and hyphens (-). The name must not exceed 128 characters. This is a required field.
• - Location: Specify the project or folder to save the data access service connector. This is a required field.
• - Description: Optionally, enter a description for the data access service connector.
• - Agent Only: Selected by default because a data access service connector can only run on a Secure Agent.
• - Use OData: Select this option to access data from a web service that uses OData. After you enable the Use OData option in a data access service connector, you can enable OData and specify the allowed users and groups for OData in the app connection that uses the data access service connections.
For more information about properties related to app connections, see Design
After you publish the app connection, you can view the OData Service URL and OData Swagger URL in the Properties Detail page of the app connection.
3. 3In the Connection Properties section, define the following general properties to connect to an external database:
• - JDBC Driver: The fully qualified Java class name of the JDBC driver.
Based on the database, you can specify one of the following driver class names:
• ▪ Databricks: com.databricks.client.jdbc.Driver
• ▪ IBM DB2: com.ibm.db2.jcc.DB2Driver
• ▪ Microsoft SQL Server: com.microsoft.sqlserver.jdbc.SQLServerDriver
• ▪ MySQL: com.mysql.jdbc.Driver
• ▪ Oracle: oracle.jdbc.OracleDriver
• ▪ PostgreSQL: org.postgresql.Driver
This is a required field.
• - JDBC URL: The connection URL to connect to an external database. This is a required field.
Based on the database, you can specify one of the following URLs:
• ▪ Databricks: jdbc:databricks://<host>:443/default;transportMode=http;ssl=1;AuthMech=3;httpPath=<http-path>
• ▪ IBM DB2: jdbc:db2://<server>:<port>/<database>
• ▪ Microsoft SQL Server: jdbc:sqlserver://<host>:<port>;databaseName=<database>
• ▪ MySQL: jdbc:mysql://<host>:<port>/<database>
• ▪ Oracle: jdbc:oracle:thin:@//<host>:<port>/<service>
• ▪ PostgreSQL: jdbc:postgresql://<host>:<port>/<database>
Note: You can only use a personal access token (PAT) authentication method to connect to the Databricks database.
• - User Name: The user name to connect to the database. This is a required field.
• - Password: The password to connect to the database. This is a required field. The value entered in this field is encrypted by default. In the Developer Console, the password that you enter is visible when you save the connector. After you save, close, and reopen the connector, the password appears encrypted.
Note: When you connect to the Databricks database, enter token in the User Name field and the PAT value in the Password field.
• - Schema: Enter the name of the schema that contains the tables that you want to include or exclude in the metadata. To view the appropriate results, you must also enter a schema name in the app connection that uses the data access service connector.
• - Include Tables: Enter the names of the tables that you want to include in the metadata. To include a list of tables, use a comma to separate multiple table names. You can also use '.*' for pattern matching. To view the appropriate results, you must also mention the included table names in the app connection that uses the data access service connector.
• - Exclude Tables: Enter the names of the tables that you want to exclude from the metadata. To exclude a list of tables, use a comma to separate multiple table names. You can also use '.*' for pattern matching. To view the appropriate results, you must also mention the excluded table names in the app connection that uses the data access service connector.
4. 4Optionally, in the Connection Properties section, define the following advanced properties :
• - Check SQL Injection: Set this field to Yes to prevent or mitigate an SQL injection attack that can happen through malicious input parameters. Default is No. You must also configure this field in the app connection that uses the data access service connector.
Note: Although you can use this option to prevent SQL injection attacks, it might not apply to all conditions.
• - Initial Connection Pool Size: The initial number of connections created when the pool is started. Default is 5.
• - Max Active: The maximum number of active connections that can be allocated from the pool simultaneously. If the value is negative, there is no limit. Default is 10.
• - Min Idle: The minimum number of connections that can remain idle in the pool without additional connections being created. A value of 0 means that none will be created. Default is 1.
• - Max Idle: The maximum number of connections that can remain idle in the pool without additional connections being released. If the value is negative, there is no limit. Default is 5.
• - Max Wait: The maximum number of milliseconds that the pool waits for a connection if there are none available. A value of -1 means to wait indefinitely.
• - Transaction Isolation Level: The default state of connections created by the pool, which is one of the following states:
• ▪ 0 - None
• ▪ 1 - Read Uncommitted
• ▪ 2 - Read Committed
• ▪ 4 - Repeatable Read
• ▪ 8 - Serializable
• - Min Evictable Idle Time: The minimum amount of time an object may sit idle in the pool before it is evicted (if an evictor exists).
• - Num Tests per Eviction Run: The number of objects to test during each run of the idle object evictor thread (if an evictor exists).
• - Test on Borrow: When selected, objects are validated before being borrowed from the pool. If the object cannot be validated, it is dropped from the pool, and an attempt is made to borrow another. Default is false.
• - Test on Return: When selected, the borrowed objects are validated before being returned to the pool. Default is false.
• - Test while Idle: When selected, the borrowed objects are validated by the idle object evictor (if one exists). If the object cannot be validated, it is dropped from the pool. Default is false.
• - Validation Query: The SQL query that validates connections from the pool before returning them to the caller. If specified, the query must be an SQL SELECT statement that returns at least one row.

Specifying Variables

Built-in Variables

Defining Actions

• •Action Name: Enter the name that appears in lists when referencing this action in data access service connectors and connections. Do not use spaces or special characters in the action name. This is a required field.
• •Category: If you have many data access service connectors, each with multiple actions, you can create categories to help users navigate within processes.
• •Description: Enter a description or notes about this action.

Input Tab

• • Name: Required. The name of the input data item.
Note: If you use a space character in the input field name and use the input field in the SQL query on the SQL Binding tab, you might see an error.
• •Label: The name of the item to display within a process. If not specified, the name is displayed.
• •Type: Select the data type of the item from the list. If you select the Reference or Object List type, you can also select a Process Object.
• •Required: Select if a value must be set for this parameter.
• •Description: Enter a description of the parameter.
• •Test with: Enter a value that Process Designer will use to test the action. If the input is an attachment, you can also upload a sample attachment file.

SQL Binding Tab

SQL Name

Name of the SQL query.

SQL Text

A valid SQL statement that returns at least one row.

Column Case

Specify whether the characters in the expression must be converted into lowercase or uppercase, or remain unchanged. Default is Lowercase.

Type

Select one of the following values:

• - Data Manipulation. Select this value to query information and apply logic to it to generate a completely different set of data.
• - Stored Procedure. Select this value to use a prepared SQL code that you can save and reuse.

Has Result Set

Reserved for future use.

Maximum Rows

The maximum number of rows returned by the SQL query that can be displayed in the SQL response payload. Default is 100 rows. The maximum number of rows can't exceed 10,000 rows.

Max Wait (Seconds)

The maximum length of time in seconds that a database client can wait for a response from the database before timing out the session.

Configuring multiple SQL queries

1On the SQL Binding tab, click the + icon to add an SQL entry.
An SQL entry is added to the table.
2Click the Edit icon to edit the SQL query.
The Create SQL page appears. The following image shows the Create SQL page where you can configure multiple SQL queries:
3In the Create SQLpage, configure the SQL query properties such as the SQL name, SQL query, column case of the query, query type, maximum rows, and maximum wait time.
You can use SQL queries to perform various data manipulation operations such as SELECT, INSERT, DELETE, and UPDATE. You can parameterize an input field in an SQL query. Use the following syntax to parameterize an input field at run time:
'{$<input_field_name>}'
For example, if empId is the name of an input field, enter the following SQL query to parameterize the empId field:
select * from employee.contact where id = '{$empid}'
4Click Create.

SQL query response format

Output Tab

• •Name: The name of a field into which a returned value is placed.
• •Type: The data type of the value being written to the field. If the type is Object List or Reference, Process Designer displays a list of process objects from which you can choose one of the objects defined within the Process Objects tab.
• •Description: Enter a description of the output field.
• •Get From: Select one of the following options:
• - Column: To enter a column name (which is the column name in the database table used within the output returned by the database) to be placed within the output field.
• - Expression: To write an expression to parse the output returned by the database. Click f(x) to open the Expression Editor where you can type the expression.
• - Result Set: To assign the complete contents of the response from the database to the output field.
• - Entire Response As Attachment: To handle the entire response as an attachment.
• - Attachments: To work with multiple attachments and pass the entire list of attachments to the selected variable except the part used as the payload.

Creating Process Objects

1. 1Create or open an existing data access service connector.
2. 2Click the Process Objects tab.
3. 3Click + to add a new process object or select an existing process object from the list. Each process object appears as a line item on the tab.
The following image shows the Process Objects tab:

4. 4On the Properties tab, specify the following details for each process object:
• - Name: Enter a name that identifies the process object. This name appears in the lists where the process object is available for selection. This is a required field.
• - Description: Enter a description for the process object.
5. 5On the Fields tab, specify the following details for each process object:
• - Field Name: Enter a name for each field in the process object.
• - Type: Select the data type of the value being written to the field using one of the built-in data types such as Text, Integer, or Date Time. If the type is Object List, you also select a process object.
• - Required: Select this check box to set the field as a required field.
A required field must be included in the request payload. It can contain a value, be empty, or be null.
• - Nullable: Clear this check box if the field must not accept null values. By default, the Nullable check box is selected.

Testing the data access service connector

• •Generate Process Objects: Click to see the process objects that you have defined for the data access service connector.
• • Result: Displays the status of the test.
Note: Sometimes, a request made to the Databricks database might display a timeout error due to system latency or slow execution. Increase the value in the Max Wait (ms) field to avoid the request timeout.
• •Output and Value: Shows the data returned from the database that is assigned to the fields defined on the Output tab.
• •Response Payload: Shows the payload sent to Process Designer from the database (not the full response sent by the database).
• •SQL Request: Shows the data sent to the database from the data access service connector. The tab displays a list of SQL names on the left pane and the corresponding data on the right pane.
• • SQL Response: Shows all of the data sent back to the data access service connector from the database (whereas the response payload is only a portion of the returned data). The tab displays a list of SQL names on the left pane and the corresponding data on the right pane.
Note: The SQL Response section displays hexadecimal values for the characters that are not allowed in XML.

1. 1Click Generate Process Objects to show the process objects that you have defined for the data access service connector.
2. 2Select the process objects that you want to make reusable and then click Next.
Process Designer displays a list of generated process objects.
3. 3Click Finish.

Metadata parameters in response payload

The following sample snippet shows the format of the response payload:

OData expand parameter in response payload

Rules and guidelines for Databricks

• •You can't use stored procedures in a data access service connector that connects to a Databricks database.
• •Some requests to Databricks databases might time out randomly due to system latency or slow execution. Increase the value in the Max Wait (ms) field to avoid the request timeouts.