Forms › The Form Designer › Form Element Functions › Form Elements › Data Sources › ca_pam_getSQLData(driverName,connectionURL,userName,password,query,callBack)

ca_pam_getSQLData(driverName,connectionURL,userName,password,query,callBack)

Directs SQL to retrieve data from an external data source and store that data in a variable (named callBack) that you define.

Note: In CA Process Automation releases before 04.1.00, this method was named ca_pam_getDataFromSQLQuery. The dataset expressions are supported in the ca_pam_getSQLData function.

Input Parameters

driverName (string)

Defines the class name for the installed SQL driver that you are using (for example, com.mysql.jdbc.driver).

connectionURL (string)

Defines the URL of the database application to query as in the following example:

jdbc:mysql://myPC-xp.myCompany.com:CA Portal/<path> or https://<server>:CA Portal/

userName (string)

Defines a user name or login credentials for a predefined user account with sufficient permissions to run the query.

password (string)

Defines the password that is associated with the specified user name.

query (string)

Defines a specific SQL query statement or a reference to an SQL query statement. The following code represents a sample SQL query:

"select * from employeedatatable where empName like '%"+ca_pam_getTextFieldValue('Form.empName')+"%'";

callBack (object)

Defines a reference to the function object to call after retrieving the data, including its onSuccess and onFailure methods.

Return Value

When the function retrieves the data, either the onSuccess(result) or onFailure(caught) callBack function runs. The function itself does not return any value.

Example 1

The following example queries an external data source and stores the resulting data in the callBack variable:

ca_pam_getSQLData(ca_fd.js.driverName (),ca_fd.js.connectionURL(),ca_fd.js.userName(),ca_fd.js.password(),ca_fd.js.queryEmployeeName(),callBack);

You can also find this function in the out-of-the-box content in CA Process Automation.

1. On the Home page, click Browse Out-of-the-Box Content.
2. Navigate to the User Interaction Forms folder, then 06 Populate Dropdown DB: Populate Dropdown from DB.

Example 2

The dataset expressions are supported in the ca_pam_getSQLData function as shown in the following example:

ca_pam_getSQLData('Datasets["/Forms/SQLDataset"].driverName','Datasets["/Forms/SQLDataset"].connectionURL','Datasets["/Forms/SQLDataset"].username','Datasets["/Forms/SQLDataset"].password','Datasets["/Forms/SQLDataset"].query',callBack);

This example queries and retrieves data from the database and the arguments of the function can be dataset variables as shown in the example.

Note: You need to provide an absolute path of the dataset.