SQL Statements in Database Resources

SQL statements executed by a Database Resource can be supplied in two ways:

Assisted the SQL is constructed from four sections in the Database Resource: Select columns, Select from tables, Where clause, Additional SQL statement clauses. They are used to build SQL statements to peform fetch, update, insert and delete operations.
Native the full SQL statements for the fetch, update, insert and delete operations are configured.

You can create dynamic SQL statements by using && field substitution to insert values of Resource Fields into your SQL statements. For example, you could choose to fetch data where the userid column is equal to the value in the userid Resource Field by putting the following in the where clause:

userid=&&userid

Steps

Here we will create a Database Resource to connect to a users table and fetch a user's name and age.

The users table has 3 columns: name, age and user_id.

Create a Database Resource with Resource Fields named name, age and user_id. Make age and user_id integers.

Using the Assisted SQL option:

Enter * for the Select columns property, to retrieve all available columns from the database.

Enter users for the Select from tables property.

Enter user_id=&&user_id for the Where clause property.

In a Form you can import these Resource Fields as new Form Fields, by clicking on the Import Fields from an external Resource button in the Fields View, or manually map the Resource Fields to existing Form Fields by clicking on the Show Mappings for Resource button in the Resources View.