PowerScript Reference
SQL Statements

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} ;

Examples

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

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 ;

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} ;

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 ;

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 ;

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 editor 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 editor, 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 ;

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 ;

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

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:

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:

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 ;