DB query to get records from a database table

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

1. On the DB query action dialog box of a form, click Configure.
   The DBQuery dialog box is displayed.
2. To specify the database connection string that holds the details of the relational database, select either Database connection.

   • Local (Default): Select a global variable or click  to manually enter the connection string.

   • 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 Type list displays Tables and all tables within the selected database appear on the Tables list.

3. On the Tables list, select the table to get records.
   All columns within the selected table appear under Available columns.

   By default, the Record list displays Get.

4. Select the columns and click to move the columns from Available columns to Selected columns.

   Use  and  on the column header to reorder the columns.

5. 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.
6. To specify the fields and sort order in your result:
   1. Select the column.
   2. Select the sort order as None, ascending or descending, against each column.
7. To get the count of rows within a table, on the Return row 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.

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, do the following:

   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 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.
10. To test the database query, click Test.

    The Preview query dialog box is displayed with the records for the selected columns.

11. Click Close.
12. Click OK.

    The DB query action dialog box displays the query.

13. Click Save.