Re: Array paramiters

I would like to query data based on the values in a java int array ( int[]
), using a prepared statement similar to:

"SELECT * FROM items WHERE id IN (?)"

can anybody advise in have to set the prepared in the statement?

On Monday 04 June 2007 15:29:32 ben sagal wrote:
> I would like to query data based on the values in a java int array ( int[]
> ), using a prepared statement similar to:
>
> "SELECT * FROM items WHERE id IN (?)"
>
> can anybody advise in have to set the prepared in the statement?

You must generate the query manually, by iterating over your array and add '?'
for each element. Then you need to pass each value of the array to
setInt(index, array[i]);

--
Andreas Joseph Krogh <andreak(at)officenet(dot)no>
Senior Software Developer / Manager
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Karenslyst Allé 11 | know how to do a thing and to watch |
PO. Box 529 Skøyen | somebody else doing it wrong, without |
0214 Oslo | comment. |
NORWAY | |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+

is there any way to do that with a variable amount of elements in the array?

Ben

On 6/5/07, Andreas Joseph Krogh <andreak(at)officenet(dot)no> wrote:
>
> On Monday 04 June 2007 15:29:32 ben sagal wrote:
> > I would like to query data based on the values in a java int array (
> int[]
> > ), using a prepared statement similar to:
> >
> > "SELECT * FROM items WHERE id IN (?)"
> >
> > can anybody advise in have to set the prepared in the statement?
>
> You must generate the query manually, by iterating over your array and add
> '?'
> for each element. Then you need to pass each value of the array to
> setInt(index, array[i]);
>
> --
> Andreas Joseph Krogh <andreak(at)officenet(dot)no>
> Senior Software Developer / Manager
> ------------------------+---------------------------------------------+
> OfficeNet AS | The most difficult thing in the world is to |
> Karenslyst Allé 11 | know how to do a thing and to watch |
> PO. Box 529 Skøyen | somebody else doing it wrong, without |
> 0214 Oslo | comment. |
> NORWAY | |
> Tlf: +47 24 15 38 90 | |
> Fax: +47 24 15 38 91 | |
> Mobile: +47 909 56 963 | |
> ------------------------+---------------------------------------------+
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

On Tuesday 05 June 2007 16:23:29 ben sagal wrote:
> is there any way to do that with a variable amount of elements in the
> array?

I'm not quite sure what you mean?
Say you have the following:

int[] num = new int[]{1,2,3};
StringBuffer sb = new StringBuffer();
sb.append("SELECT * FROM items WHERE id IN(").append(generateCSV("?",
num.length)).append(")");
for (int i = 0; i < num.length; i++) {
ps.setInt(i, num[i]);
}
ps.executeQuery();

The generateCSV-method must return a comma-separated String, in this case the
String "?,?,?", because num-array has length==3.

then num-array can be of any lenth you want.

--
Andreas Joseph Krogh <andreak(at)officenet(dot)no>
Senior Software Developer / Manager
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Karenslyst Allé 11 | know how to do a thing and to watch |
PO. Box 529 Skøyen | somebody else doing it wrong, without |
0214 Oslo | comment. |
NORWAY | |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+

I would ideally like to use a prepared statement, in which i can on each
execution insert a different array? the method u suggest will not allow use
of a prepared statement.

thanks Ben

On 6/5/07, Andreas Joseph Krogh <andreak(at)officenet(dot)no> wrote:
>
> On Tuesday 05 June 2007 16:23:29 ben sagal wrote:
> > is there any way to do that with a variable amount of elements in the
> > array?
>
> I'm not quite sure what you mean?
> Say you have the following:
>
> int[] num = new int[]{1,2,3};
> StringBuffer sb = new StringBuffer();
> sb.append("SELECT * FROM items WHERE id IN(").append(generateCSV("?",
> num.length)).append(")");
> for (int i = 0; i < num.length; i++) {
> ps.setInt(i, num[i]);
> }
> ps.executeQuery();
>
> The generateCSV-method must return a comma-separated String, in this case
> the
> String "?,?,?", because num-array has length==3.
>
> then num-array can be of any lenth you want.
>
> --
> Andreas Joseph Krogh <andreak(at)officenet(dot)no>
> Senior Software Developer / Manager
> ------------------------+---------------------------------------------+
> OfficeNet AS | The most difficult thing in the world is to |
> Karenslyst Allé 11 | know how to do a thing and to watch |
> PO. Box 529 Skøyen | somebody else doing it wrong, without |
> 0214 Oslo | comment. |
> NORWAY | |
> Tlf: +47 24 15 38 90 | |
> Fax: +47 24 15 38 91 | |
> Mobile: +47 909 56 963 | |
> ------------------------+---------------------------------------------+
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

If I undestand you correctly you're looking for something like (in
pseudo-non-working-code):

int[] num = new int[]{1,2,3};
PreparedStatement ps = new PreparedStatement(
"SELECT * FROM items WHERE id IN ?");
ps.setArray(num);
ps.executeQuery();

If this is the case, then I'm pretty sure your answer's no. I've wanted
this a few times and haven't found anything. Your best answer is what
Andreas suggested.

cheers, Pete

ben sagal wrote:
> I would ideally like to use a prepared statement, in which i can on each
> execution insert a different array? the method u suggest will not allow
> use of a prepared statement.
>
> thanks Ben
>
>
> On 6/5/07, *Andreas Joseph Krogh* <andreak(at)officenet(dot)no
> <mailto:andreak(at)officenet(dot)no>> wrote:
>
> On Tuesday 05 June 2007 16:23:29 ben sagal wrote:
> > is there any way to do that with a variable amount of elements in
> the
> > array?
>
> I'm not quite sure what you mean?
> Say you have the following:
>
> int[] num = new int[]{1,2,3};
> StringBuffer sb = new StringBuffer();
> sb.append("SELECT * FROM items WHERE id IN(").append(generateCSV("?",
> num.length)).append(")");
> for (int i = 0; i < num.length; i++) {
> ps.setInt(i, num[i]);
> }
> ps.executeQuery();
>
> The generateCSV-method must return a comma-separated String, in this
> case the
> String "?,?,?", because num-array has length==3.
>
> then num-array can be of any lenth you want.
>
> --
> Andreas Joseph Krogh <andreak(at)officenet(dot)no
> <mailto:andreak(at)officenet(dot)no>>
> Senior Software Developer / Manager
> ------------------------+---------------------------------------------+
> OfficeNet AS | The most difficult thing in the world is to |
> Karenslyst Allé 11 | know how to do a thing and to watch |
> PO. Box 529 Skøyen | somebody else doing it wrong, without |
> 0214 Oslo | comment. |
> NORWAY | |
> Tlf: +47 24 15 38 90 | |
> Fax: +47 24 15 38 91 | |
> Mobile: +47 909 56 963 | |
> ------------------------+---------------------------------------------+
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org
> <mailto:majordomo(at)postgresql(dot)org> so that your
> message can get through to the mailing list cleanly
>
>

--
Pete Lewin-Harris
Senior Software Engineer
Tel: 020 7788 4388
Email: pete(at)kinomi(dot)com

On Tuesday 05 June 2007 16:46:48 ben sagal wrote:
> I would ideally like to use a prepared statement, in which i can on each
> execution insert a different array? the method u suggest will not allow use
> of a prepared statement.

What do you think the variable "ps" is?
Most databases have a cache of preparedstatements and will manage to reuse one
based on the query's hash-value. If the int-array varies then the IN-clause
also differs, which might cause the planner to switch plan, and that might be
a good thing.

--
Andreas Joseph Krogh <andreak(at)officenet(dot)no>
Senior Software Developer / Manager
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Karenslyst Allé 11 | know how to do a thing and to watch |
PO. Box 529 Skøyen | somebody else doing it wrong, without |
0214 Oslo | comment. |
NORWAY | |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+

"ben sagal" <bsagal(at)gmail(dot)com> writes:

> I would ideally like to use a prepared statement, in which i can on each
> execution insert a different array? the method u suggest will not allow use
> of a prepared statement.

The alternative is building up a string form of an array of your list of
parameters and passing it as a single parameter.

Something like

$sth = $dbh->prepare("select * from foo where bar =ANY(?::int[])")
$sth->execute('{' . join(@array, ',') . '}');

This works better with something like integers for which you don't have to
worry about escaping. The method the other poster suggested would be lower
overhead I think but this only gives you one prepared query if you have a wide
variety of lengths of lists.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com