HomePowerServer Web | PowerServer MobileAppeon Documentation Center

Dynamic SQL

Supported

  • Dynamic SQL Format 1: executing a SQL statement does not produce a result set and does not require input parameters.

    Example code:

    EXECUTE IMMEDIATE :strSQL USING trans_obj;
/*Executing a SQL statement does not produce a result set and 
does not require input parameters*/

    Notes:

    1. In EXECUTE IMMEDIATE SQL statement, if the number of fetched row(s) is 0, the SQLCODE in the transaction object is 0 in PowerBuilder while it is 100 in PowerServer.

    2. Using the syntax EXECUTE IMMEDIATE "set transaction isolation level n" is unsupported.

    3. (.NET* only) Input parameters are unsupported.

  • Dynamic SQL Format 2: executing a SQL statement that does not produce a result set but does require input parameters.

    Example code:

    INT emp_id = 56
String fname = "jack";
PREPARE sqlsa FROM "Delete From employee Where emp_id=? And fname=?"
EXECUTE sqlsa USING :emp_id, :fname;
/*Executing a SQL statement that does not produce a result set 
but does require input parameters*/

  • Dynamic SQL Format 3: 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.

    Example code:

    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 ;
/*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*/

    Note: The default Transaction object name SQLCA is supported.

  • Dynamic SQL Format 4: executing a SQL statement that produces a result set in which the number of input parameters, result set columns, or both, are unknown at compile time.

    Example code:

    DECLARE Cursor | Procedure DYNAMIC CURSOR | PROCEDURE
FOR DynamicStagingArea ;
PREPARE DynamicStagingArea FROM SQLStatement { USING TransactionObject} ;
DESCRIBE DynamicStagingArea INTO DynamicDescriptionArea ;
OPEN DYNAMIC Cursor | Procedure USING DESCRIPTOR DynamicDescriptionArea ;
EXECUTE DYNAMIC Cursor | Procedure USING DESCRIPTOR DynamicDescriptionArea ;
FETCH Cursor | Procedure USING DESCRIPTOR DynamicDescriptionArea ;
CLOSE Cursor | Procedure ;
/ *Use this format to execute a SQL statement 
that produces a result set in which the input 
parameters and result set columns are unknown 
at compile time*/

  • Notes:

    1. MERGE statement is supported.

    2. The default transaction object name SQLDA is supported.

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

    4. (.NET only) Because of the .NET driver for Informix, Web application differs from PowerBuilder application in the following aspect:

      1. The Time data type will be returned as DateTime on the Web.

      2. The Money and Float data type will be returned as Decimal data type.

    5. In Appeon PowerServer, when executing a dynamic embedded SQL in Dynamic SQL Format 4 (example code as shown below), ASE database cannot get the output parameters from the stored procedure.

      This problem is caused by ASE database driver. If there are similar codes described below causing problems in ASE database, please turn to relevant technicians of SAP for support. 

      String ls_execute_sql = 'begin transaction apb ' + '~r~n' +&
'declare @myparm numeric(4) ' + '~r~n' +&
'exec appeon_inout @parin = 4, @parout = @myparm output' + '~r~n' +&
'SELECT @myparm ' + '~r~n' +&
'commit transaction apb '
prepare sqlsa from :ls_execute_sql using sqlca;
describe sqlsa into sqlda;
DECLARE my_cursor DYNAMIC procedure FOR SQLSA ;
execute DYNAMIC my_cursor using descriptor sqlda ;
if sqlca.sqlcode < 0 then
messagebox ('1',sqlca.sqlerrtext)
end if
FETCH my_cursor using descriptor sqlda ;
if sqlca.sqlcode < 0 then
messagebox ('2',sqlca.sqlerrtext)
else
ls_Value = String(SQLDA.GetDynamicNumber(1))
If IsNull(ls_Value) Then
ls_value = 'Null'
ElseIf ls_value = '' then
ls_value = "Empty string('')"
End If
messagebox ('3',ls_Value)
end if
close my_cursor;

Difference

  • Calling Fetch Prior when the cursor position is on the first row or calling Fetch Next when the cursor position is on the last row returns a different sqldbcode than in PowerBuilder and on the Web.

  • It is suggested to use "colname is null" instead of "colname = null".

Prev  Up  Next
Database stored procedures  Home  Functions