Welcome!

PowerBuilder Authors: Chris Pollach, Yakov Fain, RealWire News Distribution, Al Soucy, Elizabeth White

Related Topics: PowerBuilder

PowerBuilder: Article

Dynamic SQL

Dynamic SQL

From the beginning, the DataWindow has been a powerful client/server control. What has set it apart from competing products is its ability to create SQL.

In reality, the DataWindow is a SQL-generating machine. By keeping track of row and column statuses, the DataWindow is able to generate the correct SQL statement to UPDATE, DELETE, or INSERT rows into a database. In most instances, the developer is oblivious to the SQL generation; all that's needed to unleash all this functionality is to issue a simple update function. However, DataWindows can take SQL only so far, as their use of SQL has its limitations, for example:

  • In certain instances, a SELECT statement, DataWindow result sets, or retrieval arguments can't be changed easily.
  • A DataWindow cannot issue DDL commands such as CREATE, DROP, GRANT, and REVOKE.

    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.

  • Format 1 or 2 dynamic SQL statements must be used for all forms of DDL (CREATE, GRANT, etc.).
  • Format 3 or 4 dynamic SQL statements are handy when the SQL statement generates a result set.

    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:

  • Declare the dynamic cursor for the DynamicStagingArea.
  • Prepare the DynamicStagingArea using the SQL statement to be executed.
  • Open the cursor.
  • Fetch the cursor into a variable list.
  • Close the cursor.

    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:

  • Prepare the cursor for the DynamicStagingArea.
  • Prepare the DynamicStagingArea from the SQL statement.
  • Describe the DynamicStagingArea into the DynamicDescriptionArea.
  • Open the cursor using the DynamicDescriptionArea.
  • Execute the cursor.
  • Fetch the cursor into PowerBuilder variables.
  • Close the cursor.

    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()
    Other SQL Considerations
    The previous examples illustrated how SQL is supported outside the DataWindow technology. This next section will get back to the DataWindow and discuss how SQL can be dynamically changed.

    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:

  • If the user is a "MANAGER", show all records in the employee table.
  • If the user is an "EMPLOYEE", show only records where the salary is less than $75,000.00.

    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!
    There are important PowerBuilder arguments that are passed to the SQLPreview Event. They are:
  • Request: A number identifying the function that initiated the database activity
  • SQLType: A number identifying the type of SQL statement being sent to the database
  • SQLSyntax: The SQL that is about to be executed
  • DwBuffer: A number signifying which DataWindow buffer the data is coming from
  • Row: The row number in the DataWindow that's being UPDATED/DELETED or INSERTED

    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:

  • 0 - Continue processing
  • 1 - Stop processing
  • 2 - Skip this request and go on to the next request

    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:

  • It's much faster. PowerBuilder does not validate the statement until retrieval.
  • The data source of the DataWindow can be changed, e.g., from SQL SELECT to stored procedures.
  • Table.Select allows the use of none or any of the arguments defined for the DataWindow object in the SQL Select clause. In SetSQLSelect, arguments that were not previously defined cannot be used.

    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 will read a SQL statement entered by the user (am I brave). The code will then create the DataWindow object syntax. The DataWindow object will then be created and associated to a DataWindow control. Three assumptions are being made:
    1.   The DataWindow control must exist at runtime. When we talk about dynamic DataWindows, we're talking about the object and not the control.
    2.   The dynamic DataWindow object that we create cannot be saved. Its scope is limited to the instance of the window.
    3.   The SQL is valid - in this example we are assuming that the user-entered SQL is well formed and correct.

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

    SyntaxFromSQL

  • A string representing a valid SQL statement
  • The presentation style of the dynamic DataWindow
  • A string passed by reference that will contain any error messages in case the function fails

    Create

  • A string containing the syntax of the dynamic DataWindow
  • A string passed by reference that will contain any error messages in case the function fails

    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.

  • More Stories By Bob Hendry

    Bob Hendry is a PowerBuilder instructor for Envision Software Systems and a frequent speaker at national and international PowerBuilder conferences. He specializes in PFC development and has written two books on the subject, including Programming with the PFC 6.0.

    Comments (0)

    Share your thoughts on this story.

    Add your comment
    You must be signed in to add a comment. Sign-in | Register

    In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.