PreparedStatement for set membership (The IN operator)

Hello,

I need to use a SELECT statement with varying data for set membership. IE.

SELECT link_id
FROM links
WHERE heading_id IN (?, ?, ?)

It won't always be the same number of members in the set. I am using an
ordinary statement with an executeQuery call but would prefer to use a
PreparedStatement. Is this possible? If so how do I set the values in
the set?

Regards,
Daron.

On 05.04.2011 10:17, Daron Ryan wrote:
> Hello,
>
> I need to use a SELECT statement with varying data for set membership. IE.
>
> SELECT link_id
> FROM links
> WHERE heading_id IN (?, ?, ?)
>
> It won't always be the same number of members in the set. I am using an
> ordinary statement with an executeQuery call but would prefer to use a
> PreparedStatement. Is this possible? If so how do I set the values in
> the set?

You can do "WHERE heading_id = ANY (?)", and pass an array of the ids
for the parameter.

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

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

For baseTypeName I have also tried "int" and received the same error
referring to type _int instead.

This is the code I used to test the implementation
calling code
http://pastebin.com/T1mvADaF

Any idea what I need to do?

On 5/04/2011 5:57 PM, Heikki Linnakangas wrote:
> On 05.04.2011 10:17, Daron Ryan wrote:
>> Hello,
>>
>> I need to use a SELECT statement with varying data for set
>> membership. IE.
>>
>> SELECT link_id
>> FROM links
>> WHERE heading_id IN (?, ?, ?)
>>
>> It won't always be the same number of members in the set. I am using an
>> ordinary statement with an executeQuery call but would prefer to use a
>> PreparedStatement. Is this possible? If so how do I set the values in
>> the set?
>
> You can do "WHERE heading_id = ANY (?)", and pass an array of the ids
> for the parameter.
>

hi,

try

select unnest(ARRAY[?,?,?])

regards
Thomas

Am 05.04.2011 09:17, schrieb Daron Ryan:
> Hello,
>
> I need to use a SELECT statement with varying data for set membership.
> IE.
>
> SELECT link_id
> FROM links
> WHERE heading_id IN (?, ?, ?)
>
> It won't always be the same number of members in the set. I am using
> an ordinary statement with an executeQuery call but would prefer to
> use a PreparedStatement. Is this possible? If so how do I set the
> values in the set?
>
> Regards,
> Daron.
>

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

On 5/04/2011 10:26 PM, Heikki Linnakangas wrote:
> 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());
> }
>
Thanks, my code is working now. I changed my baseTypeName to int4 and
after a few more fixes my code worked. Then I tried the
Connection.createArrayOf method and that worked too.

Thanks to your help before I made ANY work with integers. I now need
to work with strings. Any idea where I can find a list of the
type names used by postgres for the Connection.createArray method?

On 6/04/2011 8:58 PM, Daron Ryan wrote:
> Thanks to your help before I made ANY work with integers. I now need
> to work with strings. Any idea where I can find a list of the
> type names used by postgres for the Connection.createArray method?
Just found it at http://db.apache.org/ojb/docu/guides/jdbc-types.html.
Sorry.