Using dynamic SQL

General information

Because database applications usually perform a specific activity, you usually know the complete SQL statement when you write and compile the script. When PowerBuilder does not support the statement in embedded SQL (as with a DDL statement) or when the parameters or the format of the statements are unknown at compile time, the application must build the SQL statements at runtime. This is called dynamic SQL. The parameters used in dynamic SQL statements can change each time the program is executed.

Using SQL Anywhere

For information about using dynamic SQL with SQL Anywhere, see the SQL Anywhere documentation.

Four formats

PowerBuilder has four dynamic SQL formats. Each format handles one of the following situations at compile time:

Format

When used

Format 1

Non-result-set statements with no input parameters

Format 2

Non-result-set statements with input parameters

Format 3

Result-set statements in which the input parameters and result-set columns are known at compile time

Format 4

Result-set statements in which the input parameters, the result-set columns or both are unknown at compile time


  • To handle these situations, you use:

    • The PowerBuilder dynamic SQL statements

    • The dynamic versions of CLOSE, DECLARE, FETCH, OPEN, and EXECUTE

    • The PowerBuilder datatypes DynamicStagingArea and DynamicDescriptionArea

About the examples

The examples assume that the default transaction object (SQLCA) has been assigned valid values and that a successful CONNECT has been executed. Although the examples do not show error checking, you should check the SQLCode after each SQL statement.

Dynamic SQL statements

The PowerBuilder dynamic SQL statements are:

DESCRIBE DynamicStagingArea
   INTO DynamicDescriptionArea ;EXECUTE {IMMEDIATE} SQLStatement
   {USING TransactionObject} ;EXECUTE DynamicStagingArea
   USING ParameterList ;EXECUTE DYNAMIC Cursor | Procedure
   USING ParameterList ;OPEN DYNAMIC Cursor | Procedure
   USING ParameterList ;EXECUTE DYNAMIC Cursor | Procedure
   USING DESCRIPTOR DynamicDescriptionArea ;OPEN DYNAMIC Cursor | Procedure
   USING DESCRIPTOR DynamicDescriptionArea ;PREPARE DynamicStagingArea
   FROM SQLStatement {USING TransactionObject} ;

Two datatypes

DynamicStagingArea

DynamicStagingArea is a PowerBuilder datatype. PowerBuilder uses a variable of this type to store information for use in subsequent statements.

The DynamicStagingArea is the only connection between the execution of a statement and a transaction object and is used internally by PowerBuilder; you cannot access information in the DynamicStagingArea.

PowerBuilder provides a global DynamicStagingArea variable named SQLSA that you can use when you need a DynamicStagingArea variable.

If necessary, you can declare and create additional object variables of the type DynamicStagingArea. These statements declare and create the variable, which must be done before referring to it in a dynamic SQL statement:

DynamicStagingArea dsa_stage1
dsa_stage1 = CREATE DynamicStagingArea

After the EXECUTE statement is completed, SQLSA is no longer referenced.

DynamicDescriptionArea

DynamicDescriptionArea is a PowerBuilder datatype. PowerBuilder uses a variable of this type to store information about the input and output parameters used in Format 4 of dynamic SQL.

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:

DynamicDescriptionArea dda_desc1
dda_desc1 = CREATE DynamicDescriptionArea

For more information about SQLDA, see Dynamic SQL Format 4.

Preparing to use dynamic SQL

When you use dynamic SQL, you must:

  • Prepare the DynamicStagingArea in all formats except Format 1

  • Describe the DynamicDescriptionArea in Format 4

  • Execute the statements in the appropriate order

Preparing and describing the datatypes

Since the SQLSA staging area is the only connection between the execution of a SQL statement and a transaction object, an execution error will occur if you do not prepare the SQL statement correctly.

In addition to SQLSA and SQLDA, you can declare other variables of the DynamicStagingArea and DynamicDescriptionArea datatypes. However, this is required only when your script requires simultaneous access to two or more dynamically prepared statements.

This is a valid dynamic cursor:

DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;PREPARE SQLSA FROM "SELECT emp_id FROM employee" ;OPEN DYNAMIC my_cursor ;

This is an invalid dynamic cursor. There is no PREPARE, and therefore an execution error will occur:

DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;OPEN DYNAMIC my_cursor ;

Statement order

Where you place the dynamic SQL statements in your scripts is unimportant, but the order of execution is important in Formats 2, 3, and 4. You must execute:

  1. The DECLARE and the PREPARE before you execute any other dynamic SQL statements

  2. The OPEN in Formats 3 and 4 before the FETCH

  3. The CLOSE at the end

If you have multiple PREPARE statements, the order affects the contents of SQLSA.

These statements illustrate the correct ordering:

DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA
string sql1, sql2
sql1 = "SELECT emp_id FROM department "&
WHERE salary > 90000"
sql2 = "SELECT emp_id FROM department "&
WHERE salary > 20000" 
IF deptId = 200 then
      PREPARE SQLSA FROM :sql1 USING SQLCA ;
ELSE
      PREPARE SQLSA FROM :sql2 USING SQLCA ;
END IF
OPEN DYNAMIC my_cursor ;   // my_cursor maps to the
                           // SELECT that has been
                           // prepared.

Declaring a procedure with the SQL Native Client database interface

When you connect to Microsoft SQL Server using the PowerBuilder SQL Native Client (SNC) database interface, the syntax for declaring a procedure is:

DECLARE logical_procedure_name PROCEDURE FOR 
   [@rc=]procedure_name
   {@param1 = value1 [OUTPUT], @param2 = value2 [OUTPUT], ...}
   {USING transaction_object};

[@rc=] indicates that you want to get the procedure's return value.

Use the keyword OUTPUT or OUT to indicate an output parameter if you want to get the output parameter's value.

If the BindSPInput database parameter is 0, value1, value2,... can be either PowerBuilder script variables or literal values. If BindSPInput is 1, value1, value2, ... must be PowerBuilder script variables. If you specify literal values, the SNC interface returns a runtime error.

When you declare a dynamic SQL statement with a procedure, enter a question mark (?) for each IN/OUT parameter in the statement. Value substitution is positional. For examples, see Dynamic SQL Format 3 and 4.

Dynamic SQL Format 1

Description

Use this format to execute a SQL statement that does not produce a result set and does not require input parameters. You can use this format to execute all forms of Data Definition Language (DDL).

Syntax

EXECUTE IMMEDIATE SQLStatement {USING TransactionObject} ;

Parameter

Description

SQLStatement

A string containing a valid SQL statement. The string can be a string constant or a PowerBuilder variable preceded by a colon (such as :mysql). The string must be contained on one line and cannot contain expressions.

TransactionObject (optional)

The name of the transaction object that identifies the database.


Examples

These statements create a database table named Trainees. The statements use the string Mysql to store the CREATE statement.

For SAP ASE users

If you are connected to an ASE database, set AUTOCOMMIT to true before executing the CREATE.

string      MyASE
 MyASE = "CREATE TABLE Trainees "&
       +"(emp_id integer not null,"&
       +"emp_fname char(10) not null, "&
       +"emp_lname char(20) not null)"
 EXECUTE IMMEDIATE :MyASE ;

These statements assume a transaction object named My_trans exists and is connected:

string      MyASE
 MyASE="INSERT INTO department Values (1234,"& 
        +"'Purchasing',1234)" 
 EXECUTE IMMEDIATE :MyASE USING My_trans ;

Dynamic SQL Format 2

Description

Use this format to execute a SQL statement that does not produce a result set but does require input parameters. You can use this format to execute all forms of Data Definition Language (DDL).

Syntax

PREPARE DynamicStagingArea FROM SQLStatement 
   {USING TransactionObject} ;
 EXECUTE DynamicStagingArea USING {ParameterList} ;

Parameter

Description

DynamicStagingArea

The name of the DynamicStagingArea (usually SQLSA).

If you need a DynamicStagingArea variable other than SQLSA, you must declare it and instantiate it with the CREATE statement before using it.

SQLStatement

A string containing a valid SQL statement. The string can be a string constant or a PowerBuilder variable preceded by a colon (such as :mysql). The string must be contained on one line and cannot contain expressions.

Enter a question mark (?) for each parameter in the statement. Value substitution is positional; reserved word substitution is not allowed.

TransactionObject (optional)

The name of the transaction object that identifies the database.

ParameterList (optional)

A comma-separated list of PowerScript variables. Note that PowerScript variables are preceded by a colon (:).


Usage

To specify a null value, use the SetNull function.

Examples

These statements prepare a DELETE statement with one parameter in SQLSA and then execute it using the value of the PowerScript variable Emp_id_var:

INT      Emp_id_var = 56
 PREPARE SQLSA 
       FROM "DELETE FROM employee WHERE emp_id=?" ;
 EXECUTE SQLSA USING :Emp_id_var ;

These statements prepare an INSERT statement with three parameters in SQLSA and then execute it using the value of the PowerScript variables Dept_id_var, Dept_name_var, and Mgr_id_var (note that Mgr_id_var is null):

INT Dept_id_var = 156
 INT Mgr_id_var    
 String      Dept_name_var
 Dept_name_var = "Department"
 SetNull(Mgr_id_var)
 PREPARE SQLSA 
       FROM "INSERT INTO department VALUES (?,?,?)" ;
 EXECUTE SQLSA 
        USING :Dept_id_var,:Dept_name_var,:Mgr_id_var ;

Dynamic SQL Format 3

Description

Use this format to execute a SQL statement that produces a result set in which the input parameters and result set columns are known at compile time.

Syntax

DECLARE Cursor | Procedure 
   DYNAMIC CURSOR | PROCEDURE 
   FOR DynamicStagingArea ;
 PREPARE DynamicStagingArea FROM SQLStatement 
   {USING TransactionObject} ;
 OPEN DYNAMIC Cursor 
   {USING ParameterList} ;
 EXECUTE DYNAMIC Procedure
   {USING ParameterList} ;
 FETCH Cursor | Procedure
   INTO HostVariableList ;
 CLOSE Cursor | Procedure ;

Parameter

Description

Cursor or Procedure

The name of the cursor or procedure you want to use.

DynamicStagingArea

The name of the DynamicStagingArea (usually SQLSA).

If you need a DynamicStagingArea variable other than SQLSA, you must declare it and instantiate it with the CREATE statement before using it.

SQLStatement

A string containing a valid SQL SELECT statement The string can be a string constant or a PowerBuilder variable preceded by a colon (such as :mysql). The string must be contained on one line and cannot contain expressions.

Enter a question mark (?) for each parameter in the statement. Value substitution is positional; reserved word substitution is not allowed.

TransactionObject (optional)

The name of the transaction object that identifies the database.

ParameterList (optional)

A comma-separated list of PowerScript variables. Note that PowerScript variables are preceded by a colon (:).

HostVariableList

The list of PowerScript variables into which the data values will be retrieved.


Usage

To specify a null value, use the SetNull function.

The DECLARE statement is not executable and can be declared globally.

If your DBMS supports formats of FETCH other than the customary (and default) FETCH NEXT, you can specify FETCH FIRST, FETCH PRIOR, or FETCH LAST.

The FETCH and CLOSE statements in Format 3 are the same as in standard embedded SQL.

To declare a local cursor or procedure, open the script in the Script view and select Paste SQL from the PainterBar or the Edit>Paste Special menu. To declare a global, instance, or shared cursor or procedure, select Declare from the first drop-down list in the Script view, and select Global Variables, Instance Variables, or Shared Variables from the second drop-down list. Then, select Paste SQL.

For information about global, instance, shared, and local scope, see Where to declare variables.

Examples

Example 1

These statements associate a cursor named my_cursor with SQLSA, prepare a SELECT statement in SQLSA, open the cursor, and return the employee ID in the current row into the PowerScript variable Emp_id_var:

integer Emp_id_var
 DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;
 PREPARE SQLSA FROM "SELECT emp_id FROM employee" ;
 OPEN DYNAMIC my_cursor ;
 FETCH my_cursor INTO :Emp_id_var ;
 CLOSE my_cursor ;

You can loop through the cursor as you can in embedded static SQL.

Example 2

These statements associate a cursor named my_cursor with SQLSA, prepare a SELECT statement with one parameter in SQLSA, open the cursor, and substitute the value of the variable Emp_state_var for the parameter in the SELECT statement. The employee ID in the active row is returned into the PowerBuilder variable Emp_id_var:

DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;
 integer Emp_id_var
 string Emp_state_var = "MA"
 string sqlstatement
  
 sqlstatement = "SELECT emp_id FROM employee "&
       +"WHERE state = ?"
 PREPARE SQLSA FROM :sqlstatement ;
 OPEN DYNAMIC my_cursor using :Emp_state_var ;
 FETCH my_cursor INTO :Emp_id_var ;
 CLOSE my_cursor ;

Example 3

These statements perform the same processing as the preceding example but use a database stored procedure called Emp_select:

// The syntax of emp_select is:
 // create procedure emp_select (@stateparm char(2)) as 
 // SELECT emp_id FROM employee WHERE state=@stateparm.
 DECLARE my_proc DYNAMIC PROCEDURE FOR SQLSA ;
 integer Emp_id_var
 string Emp_state_var
  
 PREPARE SQLSA FROM "execute emp_select @stateparm=?" ;
 Emp_state_var = "MA"
 EXECUTE DYNAMIC my_proc USING :Emp_state_var ;
 FETCH my_proc INTO :Emp_id_var ;
 CLOSE my_proc ;

Example 4

These statements are for a stored procedure with a return value for a SQL Native Client (SNC) connection:

integer var1, ReturnVal
string var2

PREPARE SQLSA FROM "execute @rc = myproc @parm1=?, @parm2=? OUTPUT ";
DECLARE my_proc DYNAMIC PROCEDURE FOR SQLSA ;

EXECUTE DYNAMIC my_proc USING :var1, :var2 ;

//fetch result set
. . .

//fetch return value and output parameter
FETCH my_proc INTO : ReturnVal, :var2;

CLOSE my_proc ;

Dynamic SQL Format 4

Description

Use this format to execute a SQL statement that produces a result set in which the number of input parameters, or the number of result-set columns, or both, are unknown at compile time.

Syntax

DECLARE Cursor | Procedure 
   DYNAMIC CURSOR | PROCEDURE 
   FOR DynamicStagingArea ;
 PREPARE DynamicStagingArea FROM SQLStatement
   {USING TransactionObject} ;
 DESCRIBE DynamicStagingArea
   INTO DynamicDescriptionArea ;
 OPEN DYNAMIC Cursor
   USING DESCRIPTOR DynamicDescriptionArea ;
 EXECUTE DYNAMIC Procedure
   USING DESCRIPTOR DynamicDescriptionArea ;
 FETCH Cursor | Procedure 
   USING DESCRIPTOR DynamicDescriptionArea ;
 CLOSE Cursor | Procedure ;

Parameter

Description

Cursor or Procedure

The name of the cursor or procedure you want to use.

DynamicStagingArea

The name of the DynamicStagingArea (usually SQLSA).

If you need a DynamicStagingArea variable other than SQLSA, you must declare it and instantiate it with the CREATE statement before using it.

SQLStatement

A string containing a valid SQL SELECT statement. The string can be a string constant or a PowerBuilder variable preceded by a colon (such as :mysql). The string must be contained on one line and cannot contain expressions.

Enter a question mark (?) for each parameter in the statement. Value substitution is positional; reserved word substitution is not allowed.

TransactionObject (optional)

The name of the transaction object that identifies the database.

DynamicDescriptionArea

The name of the DynamicDescriptionArea (usually SQLDA).

If you need a DynamicDescriptionArea variable other than SQLDA, you must declare it and instantiate it with the CREATE statement before using it.


Usage

The DECLARE statement is not executable and can be defined globally.

If your DBMS supports formats of FETCH other than the customary (and default) FETCH NEXT, you can specify FETCH FIRST, FETCH PRIOR, or FETCH LAST.

To declare a local cursor or procedure, open the script in the Script view and select Paste SQL from the PainterBar or the Edit>Paste Special menu. To declare a global, instance, or shared cursor or procedure, select Declare from the first drop-down list in the Script view and Global Variables, Instance Variables, or Shared Variables from the second drop-down list, then select Paste SQL.

For information about global, instance, shared, and local scope, see Where to declare variables.

Accessing attribute information

When a statement is described into a DynamicDescriptionArea, this information is available to you in the attributes of that DynamicDescriptionArea variable:

Information

Attribute

Number of input parameters

NumInputs

Array of input parameter types

InParmType

Number of output parameters

NumOutputs

Array of output parameter types

OutParmType


Setting and accessing parameter values

The array of input parameter values and the array of output parameter values are also available. You can use the SetDynamicParm function to set the values of an input parameter and the following functions to obtain the value of an output parameter:

GetDynamicDate 

GetDynamicDateTime 

GetDynamicDecimal 

GetDynamicNumber 

GetDynamicString 

GetDynamicTime


For information about these functions, see

GetDynamicDate

GetDynamicDateTime

GetDynamicDecimal

GetDynamicNumber

GetDynamicString

GetDynamicTime.

Parameter values

The following enumerated datatypes are the valid values for the input and output parameter types:

TypeBoolean!

TypeByte!

TypeDate!

TypeDateTime!

TypeDecimal!

TypeDouble!

TypeInteger!

TypeLong!

TypeLongLong!

TypeReal!

TypeString!

TypeTime!

TypeUInt!

TypeULong!

TypeUnknown!


Input parameters

You can set the type and value of each input parameter found in the PREPARE statement. PowerBuilder populates the SQLDA attribute NumInputs when the DESCRIBE is executed. You can use this value with the SetDynamicParm function to set the type and value of a specific input parameter. The input parameters are optional; but if you use them, you should fill in all the values before executing the OPEN or EXECUTE statement.

Output parameters

You can access the type and value of each output parameter found in the PREPARE statement. If the database supports output parameter description, PowerBuilder populates the SQLDA attribute NumOutputs when the DESCRIBE is executed. If the database does not support output parameter description, PowerBuilder populates the SQLDA attribute NumOutputs when the FETCH statement is executed.

You can use the number of output parameters in the NumOutputs attribute in functions to obtain the type of a specific parameter from the output parameter type array in the OutParmType attribute. When you have the type, you can call the appropriate function after the FETCH statement to retrieve the output value.

Examples

Example 1

These statements assume you know that there will be only one output descriptor and that it will be an integer. You can expand this example to support any number of output descriptors and any datatype by wrapping the CHOOSE CASE statement in a loop and expanding the CASE statements:

string Stringvar, Sqlstatement
 integer Intvar
 Long LongVar
 Sqlstatement = "SELECT emp_id FROM employee"
 PREPARE SQLSA FROM :Sqlstatement ;
 DESCRIBE SQLSA INTO SQLDA ;
 DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;
 OPEN DYNAMIC my_cursor USING DESCRIPTOR SQLDA ;
 FETCH my_cursor USING DESCRIPTOR SQLDA ;
 // If the FETCH is successful, the output 
 // descriptor array will contain returned 
 // values from the first row of the result set.
 // SQLDA.NumOutputs contains the number of 
 // output descriptors.
 // The SQLDA.OutParmType array will contain 
 // NumOutput entries and each entry will contain
 // a value of the enumerated datatype ParmType
 // (such as TypeInteger!, TypeLongLong!, or 
 // TypeString!).
 CHOOSE CASE SQLDA.OutParmType[1]
       CASE TypeString!
          Stringvar = GetDynamicString(SQLDA, 1)
       CASE TypeInteger!
          Intvar = GetDynamicNumber(SQLDA, 1)
        CASE TypeLongLong!
           Longvar = GetDynamicDecimal(SQLDA, 1)
 END CHOOSE
 CLOSE my_cursor ;

Example 2

These statements assume you know there is one string input descriptor and sets the parameter to MA:

string Sqlstatement, sValue
 Sqlstatement = "SELECT emp_fname, emp_lname " &
        + "FROM employee WHERE state = ?"
 PREPARE SQLSA FROM :Sqlstatement ;
  
 DESCRIBE SQLSA INTO SQLDA ;
  
 // If the DESCRIBE is successful, the input
 // descriptor array will contain one input
 // descriptor that you must fill prior to the OPEN
  
 DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;
 SetDynamicParm(SQLDA, 1, "MA")
  
 OPEN DYNAMIC my_cursor USING DESCRIPTOR SQLDA ;
  
 FETCH my_cursor USING DESCRIPTOR SQLDA ;
  
 // If the FETCH is successful, the output 
 // descriptor array will contain returned 
 // values from the first row of the result set
 // as in the first example.
  
 // To test and see the values:    
 sValue = SQLDA.GetDynamicString(1)
 //messagebox("",sValue)
 sValue = SQLDA.GetDynamicString(2)
 //messagebox("",sValue)
 Do While sqlca.sqlcode <> 100 
    FETCH my_cursor USING DESCRIPTOR SQLDA ;      
        sValue = SQLDA.GetDynamicString(1)
        //messagebox("",sValue)
        sValue = SQLDA.GetDynamicString(2)
       //messagebox("",sValue)
 Loop
  
 CLOSE my_cursor ;

Example 3

This example is for a stored procedure with a return value for a SQL Native Client (SNC) connection:

integer var1, ReturnVal
string var2

PREPARE SQLSA FROM  "execute @rc = myproc @parm1=?, @parm2=? OUTPUT ";

DESCRIBE SQLSA INTO SQLDA ;

DECLARE my_proc DYNAMIC PROCEDURE FOR SQLSA ;

SetDynamicParm(SQLDA, 1, var1)
SetDynamicParm(SQLDA, 2, var2)

EXECUTE DYNAMIC my_proc USING DESCRIPTOR SQLDA ;

//fetch result set
. . .

//fetch return value and output parameter
FETCH my_proc USING DESCRIPTOR SQLDA ;

//get return value
CHOOSE CASE SQLDA.OutParmType[1]
CASE TypeInteger!
   rc =  GetDynamicNumber(SQLDA, 1)
CASE TypeLong!
   rc =  GetDynamicNumber(SQLDA, 1)
CASE TypeString!
   Var2 = GetDynamicString(SQLDA, 1)
END CHOOSE

//get output value

CHOOSE CASE SQLDA.OutParmType[2]
CASE TypeString!
   Var2 = GetDynamicString(SQLDA, 2)
CASE TypeInteger!
   rc =  GetDynamicNumber(SQLDA, 2)
CASE TypeLong!
   rc =  GetDynamicNumber(SQLDA, 2)
END CHOOSE

CLOSE my_proc ;