Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:

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?

share|improve this question
    
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) – Mark Rotteveel Dec 19 '14 at 7:33
    
Could you try with "int8"? "bigint" is just an alias of "int8" (but both should be used in this context). – pozs Dec 19 '14 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. – Martin Carney Dec 22 '14 at 18:01
    
Ok, but are you using the jdbc 4 build? Afaik, the jdbc 3 build doesn't have it. – Mark Rotteveel Dec 22 '14 at 18:28
    
@MarkRotteveel Yeah, JDBC 4 standard, build 1002. – Martin Carney Dec 22 '14 at 19:48

1 Answer 1

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.

share|improve this answer
    
What on Earth does this answer have to do with the question? – Martin Carney Jun 29 at 18:01
    
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? – RockyMM Jun 30 at 16:30
    
I have Tomcat 7.0.25. – Martin Carney Jun 30 at 18:20
    
Do try with latest tomcat-dbcp. Here is another suggestion that the problem is there: postgresql.nabble.com/… – RockyMM Jul 1 at 12:33
    
@MartinCarney Any luck with changing the connection pool? – RockyMM Sep 7 at 15:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.