Advanced PowerBuilder

Dynamic SQL

Dynamic SQL consists of a set of embedded SQL facilities, that are specially provided for the construction of a generalized, on-line (and possibly interactive) applications. PowerBuilder doesn't support certain SQL statements, either through DataWindow or embedded SQL. These include:

• Data Definition Language (DDL), for example, CREATE TABLE.
• Certain forms of SELECT statements, such as, SELECT * from #Temp1, which selects from a temporary table - a table that doesn't exist at compile time.
• SET commands, for example, SET ROWCOUNT 100.
• Commands to GRANT and REVOKE privileges, for example, GRANT SELECT on product_master TO PUBLIC.

You can get around these using dynamic SQL. Since PowerBuilder doesn't check for SQL syntax errors at compile time, it is up to the database to take care of it.

We can broadly divide dynamic SQL statements into 4 categories:

• Non-result set statements with no input parameters.
• Non-result set statements with input parameters.
• Result set statements in which the input parameters and result set columns are known at compile time.
• Result set statements in which the input parameters, the result set columns or both are unknown at compile time.

You need to use different formats in order to execute each of these statements. Dynamic SQL introduces two new terms, dynamic Staging Area and dynamic Description Area.

Home	Previous Lesson: Retrieving the Value Of OUTPUT Variable Next Lesson: Dynamic Staging Area