Advanced PowerBuilder

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 of these two 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.

Home	Previous Lesson: Modifying Data Source Definitions Next Lesson: GetSQLSelect() and SetSQLSelect()