Advanced PowerBuilder

Home	Previous Lesson: Dynamic DataWindows Next Lesson: The DW Syntax Tool

An Overview of Modifying Data Source Definitions

This section discusses various methods that can be used for modifying the data source definition, i.e., SELECT statement in the DataWindow.

GetSqlPreview() & SetSqlPreview()

These statements allow you to see and change the SQL statements dynamically at run-time. In a DataWindow control, executing the Retrieve() function triggers RetrieveStart event, and executing the Update() function triggers an UpdateStart event. Both these events, RetrieveStart and UpdateStart then go on to trigger SqlPreview event.

The actual SQL statements are executed only if the SqlPreview script is successfully completed. To see the UPDATE, INSERT or DELETE statements generated by PowerBuilder, or the SELECT statement defined at the painting time, call GetSQLPreview() from the SqlPreview event. In version 5.0, the SQL statement comes as a parameter, "SQLSyntax" to the event and GetSQLPreview() function is obsolete from v5.0.

We can then use the SetSQLPreview() function to change the SQL statement. Typically, these commands are used to dynamically build WHERE and HAVING clauses. You can also change the list of the columns in SELECT statement, but, the number of columns and their data types should match the ones that are already present in the DataWindow object.

You can access statements such as UPDATE, SELECT, INSERT and DELETE by calling this command. For example, if 10 rows were modified and two rows were deleted, calling MessageBox("SQL", SQLSyntax ) would display 10 UPDATE statements and 2 DELETE statements.

If you change the table name in the SELECT statement using SetSQLPreview() function, it wouldn't change the UPDATE characteristics. For example, if you change the table name from 'product_master' to 'product_master_history', PowerBuilder would still generate the data manipulation commands pointing to product_master.

GetSQLSelect() and SetSQLSelect()

Unlike GetSqlPreview(), GetSQLSelect() and SetSQLSelect() functions can be called from other events. When PowerBuilder validates the SELECT statement against the database, SetSqlPreview() is called - provided the DataWindow is updateable.

If the SELECT list doesn't match the previously defined version, it returns an error and the new list of columns won't take effect. If the DataWindow is not updateable, PowerBuilder doesn't check for the validity of the SELECT statement.

If the SELECT statement contains computed columns, or if the FROM clause contains more than one table, PowerBuilder sets the DataWindow as not updateable, which makes Update() function call to fail. If this happens, you need to change the UPDATE characteristics by calling Modify(), which is explained next. Typically, these commands are called when a WHERE or HAVING clause has to be changed dynamically.

Modify() function

We've already seen how to use Modify() function to change most of the attributes of a DataWindow, and in the same way we can use it to change the SQL statements. The attribute you need to modify, in order to change the SQL statement, is table.select. For example:

String lArg1, lResult
lArg1 = "datawindow.table.select='select product_no, " + &
        "product_description," + " product_balance from " + &
        "product_master where product_balance < " + &
        "product_reorder_leval'"
lResult = dwc_1.Modify( lArg1)
If lResult = "" Then
   dwc_1.Retrieve()
Else
   MessageBox( "Error in changing the SQL statement", lResult )
   Return -1
End If

When Modify() is used, it doesn't check SQL statement against the database;  this makes it faster, but obviously prone to errors.

We would recommend that you thoroughly test any SQL statements associated with this function.

Home	Previous Lesson: Dynamic DataWindows Next Lesson: The DW Syntax Tool