Get records from a database table

Create a database query to get records from a database table.

1. On the properties panel of the Data access activity, click Configure.
   The Data access configuration - <Activity name> dialog box is displayed.
2. You can select one of the following for Database connection.

   • Local (Default): Select the process or server variable to specify the database connection string that holds the details of the relational database.

   • External: To use the database connection from external storage, enter the secret name for the connection secret stored in the configured external storage.

   By default, the Tables option is selected and all tables within the selected database are available on the Tables list.

3. On the Tables list, select the table to get records. All columns within the selected table appear.
   By default, the Get option is selected.
4. On the Available columns list, select the columns to retrieve the records. For example, for a Customer table, you can get the records for the ContactName, Address, and Phone columns.
5. To get the records for the selected columns, select one of the following options on the Return list:

   Option	Description
   Multiple rows	Retrieves multiple rows. Map the Return result set to output parameter to the dynamic complex variable. Map the Return row count to output parameter to the long variable. See Create process variables.
   Single row	Returns a single row. (Default: Single row)
   Use aggregate	Retrieves the minimum and maximum values and the row count. To retrieve the minimum value of a column, select Min value for the column, and on the Output min value to list, select the variable that holds the minimum value. To retrieve the maximum value of a column, select the Max value for that column, and on the Output max value to list, select the variable that holds the maximum value. To get the count of rows within a table, select Count rows, and on the Return row count to list select the variable that holds the count of rows.

   You must map all the output parameters with the relevant variables. Note that ODBC and OLEDB do not support xml data type.

6. To specify the fields and sort order in your result:
   1. Select the columns.
   2. Select the Sort order as Ascending or Descending against each column.
7. To reorder the columns, select the columns, and then use the  icons.
8. Configure your query based on specific conditions. For example, you can search for records with similar values. Conditions are helpful and enhance your search result when you do not know the exact value of an item.

   Example 1

   To retrieve the records of ContactName, Address, and Phone for the cities of London and Berlin.

   1. Click  under Search criteria.

   2. Specify the search criteria as Column: City(String), Operator: Equal To, Criteria: Berlin.

   3. Click Add.

   Example 2

   To retrieve records of customers whose surname starts with 'M', specify the condition as follows: Column: LastName, Operator: Begins with, Criteria: "M".

   Example 3

   To retrieve the records of FirstName, LastName, Date of Birth, and Address for first names starting with 'A' such as Andrew, Avril, and Anthony; first names containing the letters 'or' such as Trevor, Florence, and Victoria; or first names ending in 'a' such as Martina, Katrina, and Sabrina, specify the following condition:

   1. Click .

   2. Specify the search criteria as Column: ContactName(String), Operator: begins with, Criteria: A, Logic: OR.

   3. Click Add.

   4. Specify another search criteria as Operator: ends with, Criteria: A.

   5. Click Add.

9. Click OK.