JDBC calling PL/pgSQL with array parameter

Hello,

I'm trying to call a PL/pgSQL function from a Java program using
JDBC. The function takes an integer array as an input parameter,
sums the elements of the array, and returns the sum.

I can successfully call the function from psql as:
  SELECT testit('{1,2,3}')

However, I cannot get it to run successfully when called from Java.

Can anyone give me an example of the JDBC to use?

I'm running PostgreSQL 8.0 beta2 on Windows XP.

My PL/pgSQL is the following:

===============================================================
CREATE OR REPLACE FUNCTION testit(_INT4) RETURNS INTEGER AS $$
  -------------------------------------------------------------
  DECLARE
  -------------------------------------------------------------
    sum_of_ints   INTEGER := 0;
    ints          ALIAS FOR $1;
  -------------------------------------------------------------
  BEGIN
  -------------------------------------------------------------
     FOR i IN 1..3 LOOP
       sum_of_ints := sum_of_ints + ints[i];
     END LOOP;
     RETURN sum_of_ints;
  END;
$$ LANGUAGE 'plpgsql';
===============================================================

The following is my Java code:

===============================================================
import java.sql.*;

public class PlSqlTest {
  public static void main(String args[]) {
    CallableStatement stmt = null;
    int result = 0;
    int [] intArray = {1,2,3};

    System.out.println("\nArray Parameter Test\n");
    try {
      Class.forName("org.postgresql.Driver");
      Connection conn = DriverManager.getConnection
        ("jdbc:postgresql://localhost/test1", "postgres",
         "2adfw2d");
      conn.setAutoCommit(false);
      stmt = conn.prepareCall("{?= call testit(?)}");
      stmt.registerOutParameter(1, Types.INTEGER);
      stmt.setObject(2, intArray );
      stmt.execute();
      result = stmt.getInt(1);

      System.out.println("\nThe result is " + result);

      conn.close();
    }
    catch (SQLException se) {
      System.out.println("\nA SQL exception occurred.");
      System.out.println("\nError code: " + se.getErrorCode());
      System.out.println("\nSQL state: " + se.getSQLState());
      se.printStackTrace();
    }
    catch (Exception e) {
      System.out.println("\nSome other exception occurred.\n");
      e.printStackTrace();
    }
  }
}
=============================================================


When I try to run the program, I get the error,
"missing dimension value".

See below:

============================================================
C:\dev\pl\PLpgSQL>java PlSqlTest

C:\dev\pl\PLpgSQL>"C:\java\sdk\jrockit-1.4.2_04"\bin\java -classpath ".;c:\dev\j
TPCC\lib\pg74.215.jdbc3.jar" PlSqlTest

Array Parameter Test


A SQL exception occurred.

Error code: 0

SQL state: 22P02
org.postgresql.util.PSQLException: ERROR: missing dimension value

        at org.postgresql.util.PSQLException.parseServerError(Ljava.lang.String;
)Lorg.postgresql.util.PSQLException;(PSQLException.java:139)
        at org.postgresql.core.QueryExecutor.executeV3()Lorg.postgresql.core.Bas
eResultSet;(QueryExecutor.java:152)
        at org.postgresql.core.QueryExecutor.execute()Lorg.postgresql.core.BaseR
esultSet;(QueryExecutor.java:100)
        at org.postgresql.core.QueryExecutor.execute([Ljava.lang.String;[Ljava.l
ang.Object;Lorg.postgresql.core.BaseStatement;)Lorg.postgresql.core.BaseResultSe
t;(QueryExecutor.java:43)
        at org.postgresql.jdbc1.AbstractJdbc1Statement.execute()Z(AbstractJdbc1S
tatement.java:517)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute()Z(AbstractJdbc2S
tatement.java:50)
        at PlSqlTest.main([Ljava.lang.String;)V(PlSqlTest.java:18)

C:\dev\pl\PLpgSQL>
==============================================================

Thanks,

Ernie Nishiseki







________________________________________________________________
Juno Platinum $9.95. Juno SpeedBand $14.95.
Sign up for Juno Today at http://www.juno.com!
Look for special offers at Best Buy stores.