Обсуждение: 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.
On Tue, 16 Nov 2004, enishiseki@juno.com wrote: > 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}') > > int [] intArray = {1,2,3}; > stmt.setObject(2, intArray ); > > When I try to run the program, I get the error, > "missing dimension value". > The postgresql jdbc driver does not have support for using setOjbect with array types. If you enable statement logging you will see something like the following command on the backend side: SELECT testarrfunc('[I@fd13b5') If you use the 8.0 driver you will get a better error message: Exception in thread "main" org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of [I. Use setObject() with an explicit Types value to specify the type to use. at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1594) Using the 7.4 driver you can do a setString(x, "{1,2,3}"), but this won't work with the 8.0 driver, for that you need a real java.sql.Array implementation. Kris Jurka