Re: PreparedStatement for set membership (The IN operator)

On 05.04.2011 15:39, Daron Ryan wrote:
> Thanks Heikki. I have tried using the setArray method but I am still
> running into an error.
>
> Exception in thread "main" org.postgresql.util.PSQLException: Unknown
> type _INTEGER.
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.setArray(AbstractJdbc2Statement.java:2800)
>
> at dictionary.test.Main.main(Main.java:85)
>
> This is the Array implementation I have created.
> http://pastebin.com/tkzPRL4A

Starting with JDBC4, you can use conn.createArrayOf() function. No need
to create a custom Array class anymore. This is what we have in the test
suite:

   public void testCreateArrayOfInt() throws SQLException {
         PreparedStatement pstmt = _conn.prepareStatement("SELECT
?::int[]");
         Integer in[] = new Integer[3];
         in[0] = 0;
         in[1] = -1;
         in[2] = 2;
         pstmt.setArray(1, _conn.createArrayOf("int4", in));

         ResultSet rs = pstmt.executeQuery();
         assertTrue(rs.next());
         Array arr = rs.getArray(1);
         Integer out[] = (Integer [])arr.getArray();

         assertEquals(3, out.length);
         assertEquals(0, out[0].intValue());
         assertEquals(-1, out[1].intValue());
         assertEquals(2, out[2].intValue());
     }

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com