2

I am trying to insert a series of values into a sql query using Java's postgresql jdbc4.

java.sql.Connection specifies a method to convert Object[] into java.sql.Array: conn.createArrayOf(String typeName, Object[] elements)

The only problem is, no matter what I try, it always returns null.

public Array makeQueryBigintArray(Object[] values) throws SQLException {
    Array result = conn.createArrayOf("bigint", values);
    // "result" is null at this point, but shouldn't be
    return result;
}

conn is retrieved through a working javax.sql.DataSource via dataSource.getConnection(). conn works for all our other database uses, but its createArrayOf() method always returns null.

I have tried both upper case "BIGINT" and lower case "bigint" for typeName, as per this question, to no avail.

When I dig through the debugger, I find that conn is a org.apache.commons.dbcp.PoolableConnection wrapping a org.postgresql.jdbc4.Jdbc4Connection.

If the feature wasn't supported by postgres/JDBC4, I would expect calling it to throw a SQLException indicating it's unsupported. No exception is thrown.

Any clue as to why it's returning null?

How can I fix it, or else, how can I pass in an array or List of values to a PreparedStatement?

7
  • Are you using a recent JDBC 4(!) driver of PostgreSQL? It looks like it is implemented: github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/jdbc4/… (and has been for a few years now) Commented Dec 19, 2014 at 7:33
  • Could you try with "int8"? "bigint" is just an alias of "int8" (but both should be used in this context). Commented Dec 19, 2014 at 12:47
  • @MarkRotteveel We're using a build that is only 2 builds behind the most current PostgreSQL JDBC. That is, we are using build 1002, and build 1004 is the most current as of 12/19/14. I had similar thoughts that maybe it was a really old one, but it's recent. Commented Dec 22, 2014 at 18:01
  • Ok, but are you using the jdbc 4 build? Afaik, the jdbc 3 build doesn't have it. Commented Dec 22, 2014 at 18:28
  • @MarkRotteveel Yeah, JDBC 4 standard, build 1002. Commented Dec 22, 2014 at 19:48

1 Answer 1

0

If you have Tomcat6 installed using some Linux packaging system, then you probably have old versions of commons-jdbc and commons-dbcp in its /usr/share/tomcat6/lib directory. These old commons-jdbc and commons-dbcp libraries apparently have some bug when creating SQL arrays.

Apache team decided to stop maintaining commons-dbcp and moved on to tomcat-dbcp, due to code complexity and various perceived drawbacks.

What I did was I went to Maven Central, downloaded latest tomcat-jdbc and tomcat-dbcp and put those into /usr/share/tomcat6/lib and moved commons-jdbc and commons-dbcp JARs into /tmp. I also had to add to context file located in /etc/tomcat6/Catalina/localhost/my_app.xml following

factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"

What I think is happening is that there is a bug in handling exceptions in some of the JDBCConnection wrappers, which there are many many in commons-dbcp. There is part of the code which I suspect is the culprit but I'm not sure why and how. All in all, as soon as I moved from commons-dbcp, things started working again.

Sign up to request clarification or add additional context in comments.

5 Comments

What on Earth does this answer have to do with the question?
Let me quote myself: "As soon as I moved from commons-dbcp, things started working again" I have described to you what had worked for me. Do you have Tomcat 6 installed?
I have Tomcat 7.0.25.
Do try with latest tomcat-dbcp. Here is another suggestion that the problem is there: postgresql.nabble.com/…
@MartinCarney Any luck with changing the connection pool?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.