HomePowerServer Web | PowerServer MobileAppeon Documentation Center

Database stored procedures

SQL statements

Supported

The following statements are supported:

  • DECLARE

    DECLARE lproc_1 PROCEDURE FOR StoreProcedure ​@f1 = :p1 IN, @f2 = :p2 OUT, ... @fn = :pn USING trans_obj;

  • EXECUTE

    EXECUTE lproc_1;

  • FETCH

    FETCH lproc_1 INTO :v1, :v2, :v3, ...;

  • FETCH FIRST

    FETCH FIRST lcur_1 INTO :v1, :v2, :v3, ...;

  • FETCH LAST

    FETCH LAST lcur_1 INTO :v1, :v2, :v3, ...;

  • FETCH NEXT

    FETCH NEXT lcur_1 INTO :v1, :v2, :v3, ...;

  • FETCH PRIOR

    FETCH PRIOR lcur_1 INTO :v1, :v2, :v3, ...;

  • CLOSE

    CLOSE lproc_1;

Notes:

Input & output parameters are supported, except that the output parameter for a stored procedure that performs DataWindow update is unsupported.

Return value for stored procedure is supported.

Declaration syntax

All supported, except this:

Placing the stored procedure declaration syntax in a statement block that may not be executed at runtime is unsupported.

In PowerBuilder, stored procedure declaration syntax is treated the same way as variable declaration, so the syntax will not be skipped although the statement block is not executed. However, in the Web application, the syntax may be skipped and cause errors.

For example:

if li_length = 10 then
     DECLARE proc_empl PROCEDURE FOR 
dbo.java_debug_request
         debugger = a1,
         request = a2,
         out_request = a3 ;
...
End if
OPEN proc_empl;
FETCH proc_empl INTO :ls_emplid;
...

In a Web application with the above syntax, if the li_length is not 10, the cursor declaration syntax cannot be read, and errors occur.

Arguments of stored procedures

Supported

Input, output & inout parameters is supported. The data type of the parameters should match the corresponding data type in database when you declare a stored procedure.

Multiple result sets for a stored procedure are supported.

Unsupported

It is unsupported to call Oracle stored procedure with array parameters.

User-defined data types

Unsupported

For the Appeon .NET edition, you can work around for SQL Server database with the following instructions.

To use the user-defined data type of SQL Server for stored procedure, you need to take the following steps to modify the configuration file:

  1. Open the configuration file named "user2systemdbtype.config" in the <powerserver>/AEM/config directory.

  2. Add data type mappings in the section named "sqlserver". For example, if you have created a user-defined data type named "mydatetime" that is derived from the system data type "datetime", you would need to set the value of the "userdefine-type" attribute to "mydatetime" and then set the value of the "system-type" attribute to "datetime" in the section named "datatype". If you have more than one user-defined data type, you should add all of them to the configuration file, as shown in the example below. 

    <database name="sqlserver">
       <datatype userdefine-type="mydatetime" system-type="datetime" />
       <datatype userdefine-type="myvarchar" system-type="varchar" />
</database>

  3. Restart IIS after you make changes to the configuration file.

Calling stored procedures

Supported

Appeon PowerServer provides nearly full support for calling stored procedures, except for the following requirements and unsupported features.

Requirements (For Informix database)

The sequence of input parameters must stay the same as that in the Informix database. For example: 

Create procedure ProcName (Variable1 int, Variable2 varchar(10), ...)
return varchar (10)
End procedure ProcName
Call ProcName (10, "Appeon", ...)

Unsupported

PowerServer does not support using an expression as a parameter for calling the stored procedure.

PowerServer does not support using default values for parameters in an Oracle stored procedure.

PowerServer does not support calling an overloading stored procedure.

(.NET only) If the parameter is null, dynamically calling stored procedure in Informix is unsupported.


Prev  Up  Next
Cursor statements  Home  Dynamic SQL