DB query to get records from a database table
Create a database query to get records from a database table.
On the DB query action dialog box of a form, click
For a Data access activity, click Configure in the properties panel of the activity.The <ActionName> dialog box is displayed.
To specify the database connection string that holds the details of the relational database, on the
Database connection list, select a global variable or click
to manually enter the connection string.
For a DAS activity, select the process or server variable or click to manually enter the connection string.
By default, the Type list displays Tables and all tables within the selected database appear on the Tables list.
Tables list, select the table to get records.
All columns within the selected table appear under Available columns.Record list displays Get.By default, the
Select the columns and click
to move the columns from
Available columns to
Use and on the column header to reorder the columns.
- To get the records for the selected columns, map the form control, variable (form/global variable) or field on the Output value to list for each column.
To specify the fields and sort order in your result:
- Select the column.
- Select the sort order as None, ascending or descending, against each column.
To get the count of rows within a table, on the
Return count to
list, select the form control, variable (form/global) or field that holds the count of rows.
You must map all the output parameters with the relevant variables. ODBC and OLEDB do not support XML data type.
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.
To retrieve the records of ContactName, Address and Phone for the cities of London and Berlin:
Click under Search criteria.
Specify the search criteria as Column: City(String), Operator: Equal To, Criteria: Berlin..
To retrieve records of customers whose surname starts with 'M', specify the condition as: Column: LastName, Operator: Begins with, Criteria: "M".
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:
Specify the search criteria as Column: ContactName(String), Operator: begins with, Criteria: A, Logic: OR.
Specify another search criteria as Operator: ends with, Criteria: A.
- Click OK.
To test the database query, click
The Preview query dialog box is displayed with the records for the selected columns.
The <ActionName> dialog box is displayed.
The DB query action dialog box displays the Query.
- Click Save.