Working with Database Operations > Using the Database Operation File Editor > SQL Query Tab
	SQL Query Tab

The SQL Query tab shows the query code to be sent to the SQL server when this database operation file is executed. This is helpful to verify that the database operation does what you expect it to do. (This assumes that you are familiar with SQL query commands and syntax.) By testing the SQL query before actually running the application, the designer can find problems in the logic earlier in the design process.

The query can be tested by clicking the Execute button, with the query results being returned in the grid below the button. Notice that the columns in the result grid match the returned columns in the select statement.

By plugging in different values for variables (through simple or compound logic defined in the Predicate tab, the value(s) is displayed in the Criteria Input field on the SQL Query tab), and upon executing, you can test the query and see the results.

In general, if there is an error when executing a Database operation, a message box is displayed with the error message.

Examples of supported SQL database operations follow.

• Select SQL Query

• Insert SQL Query

• Delete SQL Query

• Update SQL Query.

• Stored Procedures SQL Query

With the combination of these supported operations, the database data can be easily manipulated without the need for an external tool:

Select SQL Query

For example:

SELECT Customer.Cust_ID, Customer.First_Name, Customer.Last_Name  FROM dbo.Customer  WHERE ( Customer.Cust_ID = $[CustomerSQL:Cust_ID} )

Criteria Input Example:

• Cust_ID: 1

Click Execute and the results grid indicates the First and Last Name for customer with the ID of 1 (John is the first name, and Jacobs is the last name in this example.)

Insert SQL Query

For example:

FROM dbo.Customer  ( Cust_ID, First_Name, Last_Name )  VALUES( $[AddCustomerSQL:Cust_ID], $[AddCustomerSQL:First_Name], $[AddCustomerSQL:Last_Name} )

Criteria Input Example:

• Cust_ID: 3

• First_Name: Jackie

• Last_Name: Snow

Click Execute and the results grid indicates the number of rows added (1, in this example).

Delete SQL Query

For example:

DELETE FROM dbo.Customer  WHERE ( Customer.Cust_ID = ${CustomerSQL:Cust_ID} )

Criteria Input Example:

• Cust_ID: 2

Click Execute and the results grid indicates the number of rows deleted (1, in this example).

Update SQL Query.

For example:

UPDATE dbo.Customer  SET First_Name = ${UpdateCustomerSQL:First_Name}  WHERE ( Customer, Cust_ID = ${CustomerSQL:Cust_ID} )

Criteria Input Example:

• First_Name: Jackie

• Cust_ID: 3

Click Execute and the results grid indicates the number of rows updated (1, in this example).

Stored Procedures SQL Query

Example 1:

{ ${SPSQL:_RETURN_VALUE} = call dbo.getCustomerById;   1 ( ${SPSQL:_Param1) }

Criteria Input Example 1:

• @Param1: 1

Click Execute and the results grid indicates the result of the query.

Example 2:

{ call DDTEST,GETCUSTOMER( ${ExecuteOracleDB:PARAM1) ${ExecuteOracleDB:PARAM2} )

Criteria Input Example 2:

• PARAM1: 2

Output Parameters:

• Tom

Click Execute and the results grid indicates the result of the query.

©2009, Avaya Inc. All rights reserved.