Support for Stored Procedure OUT Parameters in PBE Mode

Availability

This feature is available since MogDB 5.0.8.

Introduction

When using JDBC and other PBE connection methods, support is provided for returning values from the left side of expressions in anonymous blocks (including OUT/INOUT type data in stored procedure parameter lists) to the corresponding driver side. To use this feature, set the proc_outparam_override option in the behavior_compat_options parameter. The method is as follows:

set behavior_compat_options = 'proc_outparam_override';

Or set this parameter in the postgres.conf file and restart the database.

Benefits

Improves compatibility, aligning with Oracle's corresponding usage. Enhances usability, facilitating the writing of JDBC and other programs to connect to and operate databases.

Description

When using JDBC and other PBE connection methods, support is provided for returning values from the left side of expressions in anonymous blocks (including OUT/INOUT type data in stored procedure parameter lists) to the corresponding driver side. Taking JDBC as an example, users may wish to return the OUT parameters of expressions to the Java program side when writing and executing anonymous block programs, and then process these data on the Java program side. Replace the corresponding position of the variable in the anonymous block with “?”, and then register the data type correctly in the subsequent program, and you can use the corresponding get method to retrieve the data.

The supported data range includes basic data types supported by JDBC mapping, such as tableof, object, array, refcursor, composite, etc.

Reference for JDBC default data type mapping relationship:

get：java.sql.CallableStatement

set：java.sql.PreparedStatement

Supported scenarios include direct execution of anonymous blocks, calling functions, stored procedures, and packages within anonymous blocks, as well as internal calls to immutable execute, select into, bulk into, execute immediate, fetch into, and other scenarios within anonymous blocks.

Constraints

This feature can only be used when the database compatibility mode is Oracle (i.e., not specified when creating the DB, or DBCOMPATIBILITY='A'), and cannot be used in other database compatibility modes.
The JDBC version should be greater than or equal to 5.0.0.4, and the JDBC end should be correctly connected to the database.
It is only applicable to the left side of expressions (including OUT/INOUT type data in the stored procedure parameter list).
In the anonymous block executed by the user, variables should not be named with a “$” prefix, such as “$1”, as this may cause inaccurate data issues.
The immutable execute scenario does not support the direct use of anonymous block OUT parameters, as the “?” in the string is not calculated as a variable that needs to be replaced, but it can be used in conjunction with using.

Example

public static void test_case_0001_output_mutil(Connection conn) throws Exception {
    String baseSQLStrings = "set behavior_compat_options='proc_outparam_override';";
    String baseSQLString = "DECLARE" +  
                            "baselen integer:= 199;" +  
                            "BEGIN" +  
                            "? := baselen;" +  
                            "? := baselen*2;" +  
                            "END;";
    try {
        CallableStatement pstmt = conn.prepareCall(baseSQLStrings);
        pstmt.execute();
        pstmt.close();

         pstmt = conn.prepareCall(baseSQLString);
        System.out.println("Prepare param out SQL succeed!");

        pstmt.registerOutParameter(1, Types.INTEGER);
        System.out.println("Register succeed!");

        pstmt.registerOutParameter(2, Types.INTEGER);
        System.out.println("Register succeed!");

        pstmt.execute();
        System.out.println("Execute succeed!");

        
        if (199 == pstmt.getInt(1)) {
            System.out.println("answer true");
        } else {
            System.out.println("answer false");
        }

        if (398 == pstmt.getInt(2)) {
            System.out.println("answer true");
        } else {
            System.out.println("answer false");
        }

        System.out.println("Get succeed!");

        pstmt.close();
        System.out.println("Run succeed!");
        } 
    catch (Exception e) {
        String exceptionStr = e.toString();
        System.out.println(exceptionStr);
        }
}

In the above example, we use the anonymous block OUT parameter feature in baseSQLString, which involves the return of two expression left values. The results can be obtained and processed on the Java side.

public static void t02_base_test(Connection conn) throws Exception {
    String createPackageHead =
                        "CREATE OR REPLACE PACKAGE testuser.pck2 AS" +  
                        "  PROCEDURE get_IN_OUT(output1 OUT varchar(26), output2 OUT bool, output3 OUT TINYINT, output4 OUT smallint, ret1 IN OUT DOUBLE PRECISION);" + 
                        "END pck2;";

    String createPackageBody =  
                        "CREATE OR REPLACE PACKAGE BODY testuser.pck2 AS" +  
                        "  PROCEDURE get_IN_OUT(output1 OUT varchar(26), output2 OUT bool, output3 OUT TINYINT, output4 OUT smallint, ret1 IN OUT DOUBLE PRECISION) IS" +  
                        "   BEGIN" +
                        "      output1 := 'abcdefghigklmnopqrstuvwxyz';" +
                        "      output2 := false;" +
                        "      output3 := 2;" +
                        "      output4 := 12;" +
                        "      ret1    := ret1 + 10;" +
                        "  END get_IN_OUT;" +
                        "END pck2;";
                                
    String baseSQLString =  
                        "BEGIN" +  
                        "  testuser.pck2.get_IN_OUT(?, ?, ?, ?, ?);" +  
                        "END;";
    try {
        CallableStatement pstmt = conn.prepareCall(createPackageHead);
        pstmt.execute();
        pstmt.close();
        System.out.println("HEAD Prepare succeed!");

        pstmt = conn.prepareCall(createPackageBody);
        pstmt.execute();
        pstmt.close();
        System.out.println("BODY Prepare succeed!");

        pstmt = conn.prepareCall(baseSQLString);
        
        pstmt.setDouble(5, 99.99999999);

        pstmt.registerOutParameter(1, Types.VARCHAR);
        pstmt.registerOutParameter(2, Types.BOOLEAN);
        pstmt.registerOutParameter(3, Types.TINYINT);
        pstmt.registerOutParameter(4, Types.SMALLINT);
        pstmt.registerOutParameter(5, Types.DOUBLE);
        System.out.println("Register succeed!");

        pstmt.execute();
        System.out.println("Execute succeed!");

        System.out.println(pstmt.getString(1));
        System.out.println(pstmt.getBoolean(2));
        System.out.println(pstmt.getByte(3));
        System.out.println(pstmt.getShort(4));
        System.out.println(pstmt.getDouble(5));

        System.out.println("Get succeed!");

        pstmt.close();
        System.out.println("Run succeed!");
        } 
    catch (Exception e) {
        String exceptionStr = e.toString();
        System.out.println(exceptionStr);
        }
}

In the above example, we use the anonymous block OUT parameter feature in baseSQLString, which involves the return of five basic types and the calling of a package, as well as the use of OUT and INOUT types. The results can be obtained and processed on the Java side.

behavior_compat_options, Development Based on JDBC