Configure a database statement for a SQL service provider

After creating a database connection, you can configure database statement for a SQL service provider.

Procedure

  1. In the navigator, click Proficy System > Proficy System > Databases > Database Statement.
  2. Click Add Statement.
  3. In the Name and Description fields, enter a name and description for the resource.
  4. In the Displays panel, click Database Editor.
  5. Click the Designer tab.
  6. From the Statement Type drop-down list, select one of the following actions for a statement.
    Select...To...
    Query select
    Update insert, update, or delete
  7. From the Provider drop-down list, select SQL Data Provider.
  8. In the Statement section, enter a valid SQL string, and then click Create Parameters to generate automatically any parameters used in the statement.
  9. Optional: To add other parameters, in the data grid, click Add.
  10. Optional: To configure the data grid properties:
    • In the Name column, select a cell, and then enter a new name.
    • In the DataType column, select a cell, and then from the drop-down list, select a data type.
    Note: The parameters must match those used in the statement string.
  11. To remove a parameter, in the data grid, select a row, and then click Delete.
  12. Click Save.
  13. To test the statement:
    1. Click the Test tab.
    2. In the Inputs section, in the Parameter Value field, enter a value for each parameter.
    3. In the Connection section, click Browse.
      The Universal Browser appears.
    4. Select a database connection to run the statement against, and then click OK.
    5. In the Test section, click the Test button.
      CAUTION: Statements are executed against a specified connection, which may result in loss of data.
      The Outputs section displays the test results.
  14. To execute a statement, open a workflow, and then insert and configure a Call Method activity.
  15. From the call method activity configuration panel:
    1. Click Configure Method.
    2. Expand IDatabaseService, select ExecuteStatement, and then click Next.
    3. In the Statement section, click Browse next to the Use Existing radio button.
      The Universal Browser appears.
    4. Select a statement, and then click Finish.
    5. On the call method activity configuration panel, in the Connection field, click the ellipsis button.
      The Universal Browser appears.
    6. Select a database connection, and then click OK.
    7. To bind the connection to a workflow or subprocess property, click the drop-down arrow, and then select Bind.
    8. From the Call Method activity panel, in the Inputs section, you can configure any required input parameters.
      Note: The Statement field is read only.
    9. Click Save.
  16. Optional: You can also execute a procedure from WPF and Silverlight forms by invoking a server method. For more information see Bind a return value to a DataGrid using from server methods.