Try OpenEdge Now
skip to main content
Managed Adapters Guide
Database Managed Adapter : Tutorial 1: Using the Database Adapter : Step 8: Defining the SQL statement
 

Step 8: Defining the SQL statement

To define SQL statement:
1. In the SQL Statement dialog box, enter the statement name. For example, FindCustomerInfo.
2. Click the SQL Statement tab.
a. Choose the SQL statement type (for information on types, see Defining SQL statements). Since you plan to query the database, and are not doing any modification, choose Select. A button named Choose Tables is enabled in the Clauses panel. Click this button to open the Choose Table(s) dialog box.
b. Select the table CUSTOMER_INFO from the list (we created this table in Step 1: Creating the customer database table), and click OK. The SQL Statement tab in the SQL Statement dialog box now displays the CUSTOMER_INFO details.
You have already selected the table, and now need to select the columns and the lookup condition.
c. Click Choose Columns. A list of all available columns in the table appears in the Choose Columns dialog box.
d. Select the columns you need to get the information from — in this example, CUSTOMER_INFO.NAME and CUSTOMER_INFO.PHONE.
e. Click OK. The selected columns now displayed in the SQL Statement dialog box.
3. In the SQL Statement dialog box, click Add Conditions to display the Compose a Condition Clause dialog box.
a. Since you will be selecting records from the table based on customer ID, choose CUSTOMER_INFO.ID from the Database Columns drop-down list.
b. Click Append a Column, to add the selected column to the Where clause.
c. From the SQL Operators drop-down list, select "=" and click Append an Operator, adding the selected operator to the Where clause. After you have completed the composition, the condition clause appears.
You can select the Enable Edit check box to edit the condition directly. Once you are familiar with the Database Adapter operation, you may prefer to use the expert mode.
Note that the condition is incomplete. It reads:
    CUSTOMER_INFO.ID =
You need to define the right side of the equation to complete it. Since the value will not be constant, you can put a variable at this place.
4. Click Append a Variable. A list of the existing variables (if any) are displayed in the Variable Definitions dialog box.
a. Click Add to create a new variable. The Variable Editor dialog box appears
b. Enter ID for the variable name, as it will store the customer ID number.
c. Select Integer from the Type list for the data type.
d. From the Access list, select Input, as it will be passed as input to the Database Adapter. Enter 0 (zero) for the default value.
e. Click OK. The ID variable now appears in the variable list in the Variable Definitions dialog box.
5. Select the "ID" variable and click OK. The condition is now complete:
    CUSTOMER_INFO.ID = @ID
6. Click OK to close the condition editor. The SQL statement dialog box displays the complete statement.