Re: Re: need advice about out parameter settings while calling stored procedure in Java code

Alex Wang wrote:
> Hi Vladimir/Dave,
>
> Thanks for your kind reply. I am migrating a huge platform from Oracle (11g) to
> Postgresql (Postgres Plus Advanced Server 9.3.1.3). Here are the details about
> the issue I encountered for your reference which I hope they are clear enough
> for your analysis.
>
> I have a stored procedure inside a package (/myPackage)/  whose SQL snippet  is:
>
> /PROCEDURE p_myprocedure(a character varying, b character varying, c character
> varying, d character varying, OUT o1 text, OUT o2 text) IS/
>
 > ~
 > ~
> ~
 >
 > Thanks & regards,
 > Alex

Hello Alex,

    I'm not sure and as John indicated that this report and fix is required
in this forum. The code example provided below works fine when used directly
with a 9.4.1 server installation and 9.4-1200.jdbc4. This was just recently
demostrated in this forum for meta data on CallableStatements.

The only difference is the FUNCTION rather then your PROCEDURE definition/
call.

danap.

Complete Workable Code Sample Method.

public void test(Connection connection)
    {
       // Method Instances
       String sqlStatementString;
       Statement sqlStatement;
       CallableStatement cstmt;

       try
       {
          sqlStatement = connection.createStatement();

          sqlStatementString = "CREATE OR REPLACE FUNCTION sum_n_product(x int,
y int, OUT sum int, OUT prod int) AS $$"
                + " SELECT (x + y) AS sum, (x * y) AS prod; $$ LANGUAGE SQL;";
          System.out.println(sqlStatement);
          sqlStatement.execute(sqlStatementString);

          // Execute Function.

          cstmt = connection.prepareCall("{call sum_n_product(?,?,?,?)}");

          cstmt.registerOutParameter(3, Types.INTEGER);
          cstmt.registerOutParameter(4, Types.INTEGER);

          cstmt.setInt(1, 2);
          cstmt.setInt(2, 5);

          cstmt.execute();

          int x1 = cstmt.getInt(3);
          int x2 = cstmt.getInt(4);

          System.out.println("x1: " + x1);
          System.out.println("x2: " + x2);

          // Drop Function.
          sqlStatementString = "DROP FUNCTION sum_n_product(int, int, OUT int,
OUT int);";
          System.out.println(sqlStatementString);
          sqlStatement.execute(sqlStatementString);

          cstmt.close();
          sqlStatement.close();
       }
       catch (SQLException sqle)
       {
          System.out.println("SQL Exeception" + sqle);
       }
    }