How can I set a String[] parameter to a native query?
eclipselink, java, jpql, postgresql
Solution
Testing by passing a Java array of type String[] to `PreparedStatement.setObject(...)` results in the behaviour you report. It appears that PgJDBC doesn't accept a Java array as an argument to `PreparedStatement.setObject()`, with or without a `Types.ARRAY` parameter.
Compliance
The JDBC spec, 16.5 "Array Objects", suggests that the JDBC `Array` exists partly so the client doesn't have to copy big arrays into memory, they can be used by reference. I'm not too sure whether the JDBC driver is required to accept raw Java arrays as parameters. All the spec code refers to `java.sql.Array` and the spec makes it clear that arrays are mapped via the `Array` interface in Appendix B and elsewhere. In a quick search/reading I could find no mention of passing raw Java arrays other than `byte[]` as parameters or returning them as results.
However, in §16.5.4 the JDBC4.2 draft spec reads:
A Java array may be passed as an input parameter by calling the method
PreparedSatement.setObject.
though all the rest of the code there refers to `Array` objects. Do they mean `Array` by "a Java array"? Or do they mean a raw native Java array like `String[]`?
It looks to me like clients are supposed to use the `java.sql.Array` interface via `Connection.createArrayOf(...)`, so EclipseLink is probably doing the wrong thing.
What do do about it
Try updating EclipseLink to 2.4 in the hopes it uses the commonly specified method of passing arrays to JDBC via a java.sql.Array object.
You may also need to annotate the mapping with `@Array`, an EclipseLink extension. See also this forum thread re 2.3 and bug 361701.
It appears you may have to implement your own type handler for EclipseLink to override its behaviour. To correctly set an array parameter via PgJDBC you must use:
Array sqlArray = conn.createArrayOf("text", strArray);
pstmt.setArray(1, sqlArray);
pstmt.executeUpdate();
... where `conn` and `pstmt` are a `java.sql.Connection` and a `PreparedStatement` respectively, and `strArray` is a `String[]` instance.
See Custom data types in the eclipselink wiki.
On a side note, the use of a string type name to specify the array's data type in `createArrayOf` seems kind of insane given the existence of `java.sql.Types`. It makes portability much harder; the above code won't run on (say) Oracle, because Oracle wants `VARCHAR` not `text` as a type name.
Note: The unit test `org/postgresql/test/jdbc2/ArrayTest.java` has `ArrayTest.testSetArray()`, which at line 166 tests:
pstmt.setObject(1, arr);
pstmt.executeUpdate();
... however the type of `arr` is `java.sql.Array`, not `int[]`. It's a JDBC array type, not a regular Java array.
Problem
This is my PostgreSQL function: ``` salvarArquivoGeometricoCasoZeroPOINT (dimensao text,tableName text,tuplas text[],srid text) ``` It has a `text[]` parameter, and I want to pass a Java `String[]` to it from my JPQL: ``` public String salvarGeometriaCaso0(String[] tuplas,FileDto arquivo){ Query query = em().createNativeQuery("select salvarArquivoGeometricoCasoZeroPOINT(?1,?2,?3,?4)"); query.setParameter(1,arquivo.getGeo());//String query.setParameter(2,arquivo.getTable());/String query.setParameter(3,tuplas);//String[] query.setParameter(4,arquivo.getSrid());//String return (String) query.getSingleResult();//function returns a Text, so cast to String } ``` The above code fails with the exception: ``` ERROR] Internal Exception: org.postgresql.util.PSQLException: Can not infer a SQL type to use for an instance of [Ljava.lang.String;. Use setObject () with an explicit Types value to specify the type to use. ``` so I'm not sure how to call my function from EclipseLink.