JDBC calling PL/pgSQL with array parameter - Mailing list pgsql-jdbc
From | enishiseki@juno.com |
---|---|
Subject | JDBC calling PL/pgSQL with array parameter |
Date | |
Msg-id | 20041116.093007.42.454@webmail25.nyc.untd.com Whole thread Raw |
Responses |
Re: JDBC calling PL/pgSQL with array parameter
|
List | pgsql-jdbc |
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.
pgsql-jdbc by date: