What many PowerBuilder developers don't know is that SQL can be executed dynamically. The level and complexity of dynamic SQL is determined by what you are trying to accomplish. At any rate, the use of dynamic SQL falls into four categories and follows a repeatable process. It's especially helpful when SQL parameters or the column set of the statements are unknown at compile time or may need to change within the runtime environment. Dynamic SQL is broken down into four formats.

Dynamic SQL (Format 1)
If you want to issue statements that don't require input arguments and don't return a result set, use Format 1. It's a good choice when executing DDL statements. Use the DDL syntax for the Database Management System. These examples will be using DDL syntax for Sybase Adaptive Server Anywhere. The syntax of Format 1 is as follows:

EXECUTE IMMEDIATE <SQL> USING <TRANSACTION OBJECT>

The SQL statement has to be a valid string, either hard coded or a string variable. If a string variable is used, it must be bound with a colon. The Transaction object is optional and represents the database upon which this action is performed. If a Transaction object is not specified, SQLCA will be assumed. Listing 1 drops a table, re-creates it, inserts a row, then grants read permission.

Note that a commit is not being performed here. If you leave database commits out of your scripts, make sure the autocommit property is set to true in the database profile of the Transaction object.

Dynamic SQL (Format 2)
If you need to execute a SQL statement with a finite known number of input arguments and no result set, use Dynamic SQL Format 2. This format (as with Formats 3 and 4) uses the PowerBuilder DynamicStagingArea. Basically, the DynamicStagingArea provides the interface between the SQL statement and the Database Management System.

The DynamicStagingArea is a private PowerBuilder data type used to store information about the SQL statement. Internals of this data type are hidden from the programmer. PowerBuilder gave us a default DynamicStagingArea named SQLSA. Like the PowerBuilder default Transaction object SQLCA, additional DynamicStagingAreas can be created programmatically. Using Format 2 is a two-stage process. First the DynamicStagingArea must be prepared, then its associated SQL statement can be executed.

The following code prepares the DynamicStagingArea with one argument, denoted by the question mark. PowerBuilder stores this SQL information while waiting for it to be executed. The next line of code fires off the staged statement after it passes the retrieval argument.

string ls_part = "Scanner"
prepare SQLSA FROM "delete from inventory
where partdesc = ?" USING SQLCA;
Execute SQLSA USING :ls_part;

Dynamic SQL (Format 3)
Dynamic SQL Format 3 is used to execute a SQL statement that has a result set and a fixed number of arguments that are known at compile time. The steps are:

Listing 2 uses a cursor to read using this format and can dynamically fire off a result set into a variable list; although not demonstrated here, stored procedures can be used as well. The listing reads a list of employee last names into a ListBox.

Dynamic SQL (Format 4)
This most powerful format is used when the number of parameters and the result set are unknown at compile time. Several steps are required to execute Format 4 Dynamic SQL:

The DynamicDescriptionArea is a PowerBuilder data type. PowerBuilder uses a global variable of this type to hold information about the input and output arguments used in Dynamic SQL (Format 4). When the application starts, PowerBuilder creates a global DynamicDescriptionArea named SQLDA. Like the DynamicStagingArea (SQLSA), additional DynamicDescriptionAreas can be created within the code if needed.

PowerBuilder provides a global DynamicDescriptionArea named SQLDA that you can use when you need a DynamicDescriptionArea variable. If necessary, you can declare and create additional object variables of the type DynamicDescriptionArea. These statements declare and create the variable, which must be done before referring to it in a dynamic SQL statement.

If arguments are to be passed to the SQL statement, the SetDynamicParm function is used to fill the parameters in the input parameter descriptor array of the SQLDA before executing an OPEN or EXECUTE statement (see Listing 3).

The type and value for each output parameter in SQLDA is accessed via the following functions:

• GetDynamicDate()
• GetDynamicDateTime()
• GetDynamicNumber()
• GetDynamicString()
• GetDynamicTime()

SQLPreview Event
Immediately before a DataWindow attempts to execute a SQL statement, the SQLPreview event is fired. This event is useful in several ways. First and foremost, it allows programmers to determine which SQL statement the DataWindow is planning to execute. If, for example, the DataWindow is attempting to execute a SELECT statement, we may wish to modify it, perhaps adding a WHERE clause. This may be useful in the event that our retrieval arguments cannot be defined within the DataWindow painter. For example:

In our example, because the retrieval argument is contingent on the user, we can't define it at design time when we're creating the DataWindow. Clearly, the WHERE clause must be created dynamically. But how? We can use the four Dynamic SQL formats discussed earlier, then "push" the results into a DataWindow. A better bet would be to modify the DataWindow's SQL statement - adding a WHERE clause just before it issues a SELECT statement. The SQLPreview event is a great place to do this. Before we add code to the event, we must first determine what kind of SQL the DataWindow is planning on executing. This can be determined by using the following enumerated data types. Their meanings are intuitive so I won't elaborate on them:

• PreviewSelect!
• PreviewInsert!
• PreviewDelete!
• PreviewUpdate!

SQLSyntax is especially helpful to us. Since it contains the current SQL to be executed, we can "intercept" it and change/append as needed. When we're finished tweaking it, we call the SetSQLPreview function. This function passes our SQL statement to the DataWindow, which then executes it (see Listing 4).

When a new SQL statement is applied to a DataWindow, it overrides the one that was defined for it in the DataWindow Painter, including any retrieval arguments. The SetSQLPreview function should never be called outside the SQLPreview event. In addition, it should be used as close to the end of the script as possible.

The SQLPreview event also has some handy return codes:

If, for example, certain users were not allowed to update the database, this rule could be enforced in the SQLPreview event with the following code:

If sqltype <> PreviewSelect! Then
If gs_user = "EMPLOYEE" Then
messagebox("Error","You are not allowed to make changes!")
Return 1
End If
End If

The TABLE.SELECT Property
Another way to modify the SQL statement for a DataWindow is to modify the TABLE.SELECT property. As when the SQL is changed via the SQLPreview event, changes made to the DataWindow via the TABLE.SELECT property completely override the SQL that was defined in the DataWindow Painter at design time. What makes the use of TABLE.SELECT different than that of the SetSQLPreview function is that changes made via TABLE.SELECT are permanent, at least while an instance of the DataWindow exists. On the other hand, changes made via SetSQLPreview exist only for that specific database transaction.

Listing 5 uses Describe and Modify to save/alter a DataWindow SQL statement.

Important Note About the Describe Function
The Describe function always attempts to return a SQL SELECT statement. If the database is not connected and the property's value is a PBSELECT statement (PBSELECT is the statement if the DataWindow was created in graphic mode), the Describe function will convert it to a SQL SELECT statement only if a SetTransObject function has already been executed for the DataWindow object. If you're using describe-less retrieval (the StaticBind DBParm parameter is set to 1), the SELECT property cannot be used.

SetSQLSelect Function
This DataWindow function is used to change the SQL statement of a DataWindow from within a script. PowerBuilder will validate the SQL if the DataWindow is updateable. When specifying a new SQL statement, the new column set's data types must match the old ones. Also, the Transaction object for the DataWindow needs to be set immediately (via SetTransObject) before SetSQLSelect.

The main limitation of the SetSQLSelect statement is that it can only be used for DataWindows that have a SQL SELECT data source, and there can be no retrieval arguments defined within the DataWindow. A good example of when to use SetSQLSelect is when small changes need to made to a WHERE clause. For example, if we needed to look up all employees who lived in "MA," and, if none existed, look up all employees who live in "CA":

string ls_cur_sql
string ls_new_sql

ls_cur_sql = "SELECT * FROM employee WHERE state = 'MA'"
ls_new_sql = "SELECT * FROM employee WHERE state = 'CA'"

IF dw_1.Retrieve() = 0 THEN
dw_1.SetSQLSelect(ls_new_sql)
dw_1.Retrieve()
END IF

Why TABLE.SELECT is Preferred over SetSQLSelect
Table.Select is generally considered to have several advantages over the SetSQLSelect function:

In addition to the SELECT property, other important TABLE properties exist. It's worth it to take a peek.

Dynamic DataWindows
If you need to get "very" dynamic, a DataWindow can be created based solely on a SQL statement. Although the SQL statement may be hard coded within a script, usually the SQL is built in a more dynamic fashion. A common usage is when the user is allowed to create reports, based on dragging and dropping columns - like a custom report generator. If you're really brave, you can allow the user to type in a SQL statement, then create a DataWindow based on it. Dynamic DataWindow can be one of the following presentation styles:

• (Default) Tabular
• Grid
• Form (for freeform)
• Graph
• Group
• Label
• N-up

Listing 6 uses two important functions. The following are the SyntaxFromSQL and Create functions, and the arguments they accept.

SyntaxFromSQL

Create

If all goes well, the DataWindow object will be created and the user will see it as the contents on the DataWindow control. At this point you can use Describe and Modify as you would on any other DataWindow.

Final Thoughts
That's the long and short of it. As you can see, the SQL that's generated upon DataWindow creation is just the tip of the iceberg. Indeed, to use SQL, a DataWindow need not be used at all. When using the SQL in the DataWindow, you should now know that this "static" SQL is more dynamic than you thought.