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 round 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:

1	Non-result set statements with no input parameters.
2	Non-result set statements with input parameters.
3	Result set statements in which the input parameters and result set columns are known at compile time.
4	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.

Dynamic Staging Area

Dynamic Staging Area is internally used by PowerBuilder and is the connection between execution of a statement and Transaction Object. You can't access the information in the dynamic Staging Area. PowerBuilder provides a global-level dynamic Staging Area named SQLSA. It contains the following information, for use, in subsequent statements:

	The SQL statement in the PREPARE statement.
	The number of parameters.
	The Transaction Object for use in subsequent statements.

Dynamic Description Area

The dynamic description area stores information about input and output parameters and is used with the fourth format of dynamic SQL. PowerBuilder provides a global-level dynamic description area named SQLDA, with the following structure:

The two principal statements of dynamic SQL are PREPARE and EXECUTE. The PREPARE statement takes the base SQL commands and places them in the SQLSA command buffer for execution. The EXECUTE statement passes these commands from the buffer to the back-end database and returns the feedback information to SQLCA as usual.

Home	Previous Lesson: Stored Procedures Next Lesson: Format 1