Unsupported use cases in Embedded SQLs

Embedded SQLs are supported, but there are a few unsupported use cases.

Unsupported use case #1

When executing a procedure in the cursor, only single result set is supported; the output parameter, return value, and multiple result sets are all unsupported.

declare lcs_test1 cursor for execute hr.synonyms_package.get_emp;
open  lcs_test1;
fetch lcs_test1 into :ls_name;
close lcs_test1;

Workaround

The code example below processes the result sets, return value and output parameter one at a time.

int li_intParam, li_retValue, li_bitResult
string ls_outVarParam, ls_outNvarParam, ls_varResult, ls_ncharResult

li_intParam = 1

declare lp_procName01 procedure for  @li_retValue = get_muiltResultset  @in_intParam = :li_intParam,  @out_varParam = :ls_outVarParam  output,  @out_nvarParam = :ls_outNvarParam  output;
execute  lp_procName01; 

//Handles the first result set
fetch lp_procName01 into :ls_varResult, :ls_ncharResult, :li_bitResult;
do while sqlca.sqlcode = 0
                fetch lp_procName01 into :ls_varResult, :ls_ncharResult, :li_bitResult;
loop

//Handles the second result set
fetch lp_procName01 into :ls_varResult, :ls_ncharResult;
do while sqlca.sqlcode = 0
                fetch lp_procName01 into :ls_varResult, :ls_ncharResult;
loop

//Handles the return value and output parameter
fetch lp_procName01 into :li_retValue, :ls_outVarParam, :ls_outNvarParam;
close lp_procName01;

Unsupported use case #2

Different transactions work on the same temp table. For example, after the first transaction is committed, the second transaction still accesses the temp table that is created in the first transaction. In this case, an "invalid object name #TEMPTABLE" error may occur.

Workaround

Make sure that all the operations related with one temp table are performed in the same transaction.

Unsupported use case #3

The DataWindows and/or embedded SQLs included in the PBD file cannot be parsed to the C# models (and the SetLibraryList and AddToLibraryList functions will not work properly with such PBD files as well).

Workaround

Manually convert the DataWindows and embedded SQLs from the corresponding PBL file through DataWindow Converter.