Re: 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(at)juno(dot)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(at)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