Variable Arg Lists to PreparedStatements

[Robert Brown]

Is there a way to use PreparedStatements (or bind variables) with SQL
statements that have a variable number of arguments. For example, I
have an array of IDs for employees of a certain type and I want to
have a PreparedStatement retrieve all of them in a single SQL call.

SELECT FROM employees WHERE employee_type = ? employee_id in
(?,?,?,...,?)

It seems at least in Java that PreparedStatements can only take a
fixed number of arguments?

PreparedStatement pstmt = con.prepareStatement("SELECT FROM
employees WHERE employee_type = ? AND employee_id = ?");
pstmt.setLong(1, employeeType);
pstmt.setInt(2, employeeID);

Is there a way to have a PreparedStatement for a SQL "IN" clause or
similar variable argument length clauses? I heard that Temp tables is
one option where you first insert your array of IDs into a Temp table
and then use a JOIN. But that seems to defeat the purpose as it
requires multiple queries. Is there anyway to something like this:

int[] employeeIDArray = getEmployees();
PreparedStatement pstmt = con.prepareStatement("SELECT FROM
employees WHERE employee_type = ? AND employee_id IN (?)");
pstmt.setLong(1, employeeType);
pstmt.setIntArray(2, employeeIDArray); <--- How can I do this?

Thanks,

- Robert

[Joe Weinstein]

Robert Brown wrote:

Is there a way to use PreparedStatements (or bind variables) with SQL
statements that have a variable number of arguments. For example, I
have an array of IDs for employees of a certain type and I want to
have a PreparedStatement retrieve all of them in a single SQL call.

SELECT FROM employees WHERE employee_type = ? employee_id in
(?,?,?,...,?)

It seems at least in Java that PreparedStatements can only take a
fixed number of arguments?

PreparedStatement pstmt = con.prepareStatement("SELECT FROM
employees WHERE employee_type = ? AND employee_id = ?");
pstmt.setLong(1, employeeType);
pstmt.setInt(2, employeeID);

Is there a way to have a PreparedStatement for a SQL "IN" clause or
similar variable argument length clauses? I heard that Temp tables is
one option where you first insert your array of IDs into a Temp table
and then use a JOIN. But that seems to defeat the purpose as it
requires multiple queries. Is there anyway to something like this:

int[] employeeIDArray = getEmployees();
PreparedStatement pstmt = con.prepareStatement("SELECT FROM
employees WHERE employee_type = ? AND employee_id IN (?)");
pstmt.setLong(1, employeeType);
pstmt.setIntArray(2, employeeIDArray); <--- How can I do this?
Sorry. JDBC doesn't offer this. The SQL you send for preparation has to be
fairly fixed and complete so the DBMS can fully parse it and make a
query plan for it. Typically DBMSes only provide for the ability to
plug in a single simple parameter value in a single simple parameter
placemarker.
I would suggest a hack work-around of preparing a statement with
some maximum number of '?'s, and if your current query requires fewer,
fill in the rest with a non-qualifying or a repeat value.

Joe Weinstein at BEA
Thanks,

- Robert

[Galen Boyer]

On Mon, 20 Sep 2004, jo*******@bea.com wrote:

Robert Brown wrote:

Is there a way to use PreparedStatements (or bind variables)
with SQL statements that have a variable number of
arguments.
This is a good reason to use a stored proc.
For example, I have an array of IDs for employees of a certain
type and I want to have a PreparedStatement retrieve all of
them in a single SQL call. SELECT FROM employees WHERE
employee_type = ? employee_id in (?,?,?,...,?) It seems at
least in Java that PreparedStatements can only take a fixed
number of arguments? PreparedStatement pstmt =
con.prepareStatement("SELECT FROM employees WHERE
employee_type = ? AND employee_id = ?"); pstmt.setLong(1,
employeeType); pstmt.setInt(2, employeeID); Is there a way to
have a PreparedStatement for a SQL "IN" clause or similar
variable argument length clauses? I heard that Temp tables is
one option where you first insert your array of IDs into a
Temp table and then use a JOIN. But that seems to defeat the
purpose as it requires multiple queries.
Its better in the sense that the query would be alot faster than
single row lookups, but you have to weigh it with single value
inserts. But, with array inserts, its a fine way to go, but
since you aren't able to handle arrays, it doesn't seem to buy
you much.
Is there anyway to something like this: int[] employeeIDArray
= getEmployees(); PreparedStatement pstmt =
con.prepareStatement("SELECT FROM employees WHERE
employee_type = ? AND employee_id IN (?)"); pstmt.setLong(1,
employeeType); pstmt.setIntArray(2, employeeIDArray); <---

How can I do this?

You could do the following:

WHERE employee_type = ?
AND (
employee_id = ?
OR
employee_id = ?
OR
employee_id = ?
)
Sorry. JDBC doesn't offer this.
Oracle offers array support, and I would guess others do as well,
but then you have to use their drivers.
The SQL you send for preparation has to be fairly fixed and
complete so the DBMS can fully parse it and make a query plan
for it. Typically DBMSes only provide for the ability to plug
in a single simple parameter value in a single simple parameter
placemarker. I would suggest a hack work-around of preparing a
statement with some maximum number of '?'s, and if your current
query requires fewer, fill in the rest with a non-qualifying or
a repeat value.

Joe Weinstein at BEA

Thanks,
- Robert

--
Galen Boyer

[Malcolm Dew-Jones]

Joe Weinstein (jo*******@bea.com) wrote:
: Robert Brown wrote:

: > Is there a way to use PreparedStatements (or bind variables) with SQL
: > statements that have a variable number of arguments. For example, I
: > have an array of IDs for employees of a certain type and I want to
: > have a PreparedStatement retrieve all of them in a single SQL call.
: >
: > SELECT FROM employees WHERE employee_type = ? employee_id in
: > (?,?,?,...,?)
: >
: > It seems at least in Java that PreparedStatements can only take a
: > fixed number of arguments?
: >
: > PreparedStatement pstmt = con.prepareStatement("SELECT FROM
: > employees WHERE employee_type = ? AND employee_id = ?");
: > pstmt.setLong(1, employeeType);
: > pstmt.setInt(2, employeeID);
: >
: > Is there a way to have a PreparedStatement for a SQL "IN" clause or
: > similar variable argument length clauses? I heard that Temp tables is
: > one option where you first insert your array of IDs into a Temp table
: > and then use a JOIN. But that seems to defeat the purpose as it
: > requires multiple queries. Is there anyway to something like this:
: >
: > int[] employeeIDArray = getEmployees();
: > PreparedStatement pstmt = con.prepareStatement("SELECT FROM
: > employees WHERE employee_type = ? AND employee_id IN (?)");
: > pstmt.setLong(1, employeeType);
: > pstmt.setIntArray(2, employeeIDArray); <--- How can I do this?

: Sorry. JDBC doesn't offer this. The SQL you send for preparation has to be
: fairly fixed and complete so the DBMS can fully parse it and make a
: query plan for it. Typically DBMSes only provide for the ability to
: plug in a single simple parameter value in a single simple parameter
: placemarker.
: I would suggest a hack work-around of preparing a statement with
: some maximum number of '?'s, and if your current query requires fewer,
: fill in the rest with a non-qualifying or a repeat value.
Another option that would allow PreparedStatements is to put the varying
values into a table and select against that table, that way there is a
fixed number of variables in the query.

foreach employee_id in employee_ids
insert values (employee_id,context_value) into parameter_table

SELECT FROM employees WHERE employee_type = ? and employee_id in
select employee_id from parameter_table
where context_value = ?
Some systems allow process specific temp tables, which might make this
pretty simple. It that case a context_value would not be required, and
the old parameter data would be cleaned up automatically when the process
exits.

Otherwise you must assign a unique id (such as the sessionid in oracle) to
each process and index the parameter table using that, and then you must
also clean up old parameters after they have been used.

I might use the temp table method, I haven't had a chance or reason to
play with it.

I would avoid the context_value solution like the plague unless it was
absolutely essential.

The one perhaps non-obvious advantage to either of the above is that
queries can be built using views, and the views themselves can refer to
the parameters, which in rare occasions could be useful.

[Christian Antognini]

**** Post for FREE via your newsreader at post.usenet.com ****

Hi Robert

You could solve the problem by using an array. Personally I don't like this
method too much... anyway here an example...

- on the database

CREATE TYPE t_collection IS TABLE OF NUMBER;

- on the JDBC client (excerpt)

String sql = "SELECT ename FROM emp WHERE empno IN (SELECT value(v)
FROM table(?) v)";
OraclePreparedStatement statement =
(OraclePreparedStatement)connection.prepareStateme nt(sql);
ArrayDescriptor descriptor =
ArrayDescriptor.createDescriptor("T_COLLECTION", connection);
long[] content = { 7521, 7654, 7698 };
ARRAY array = new ARRAY(descriptor, connection, content);
statement.setARRAY(1, array);
ResultSet resultSet = statement.executeQuery();
while (resultSet.next())
{
System.out.println("name: " + resultSet.getString("ename"));
}
Chris

"Robert Brown" <ro*************@yahoo.com> wrote in message
news:24**************************@posting.google.c om...

Is there a way to use PreparedStatements (or bind variables) with SQL
statements that have a variable number of arguments. For example, I
have an array of IDs for employees of a certain type and I want to
have a PreparedStatement retrieve all of them in a single SQL call.

SELECT FROM employees WHERE employee_type = ? employee_id in
(?,?,?,...,?)

It seems at least in Java that PreparedStatements can only take a
fixed number of arguments?

PreparedStatement pstmt = con.prepareStatement("SELECT FROM
employees WHERE employee_type = ? AND employee_id = ?");
pstmt.setLong(1, employeeType);
pstmt.setInt(2, employeeID);

Is there a way to have a PreparedStatement for a SQL "IN" clause or
similar variable argument length clauses? I heard that Temp tables is
one option where you first insert your array of IDs into a Temp table
and then use a JOIN. But that seems to defeat the purpose as it
requires multiple queries. Is there anyway to something like this:

int[] employeeIDArray = getEmployees();
PreparedStatement pstmt = con.prepareStatement("SELECT FROM
employees WHERE employee_type = ? AND employee_id IN (?)");
pstmt.setLong(1, employeeType);
pstmt.setIntArray(2, employeeIDArray); <--- How can I do this?

Thanks,

- Robert

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.usenet.com
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

[Christian Antognini]

**** Post for FREE via your newsreader at post.usenet.com ****

Mhmm... just noticed you multi-posted... (not the best thing to do!) My
solution works only with Oracle...

"Christian Antognini" <ch*****************@trivadis.com> wrote in message
news:41********@post.usenet.com...

**** Post for FREE via your newsreader at post.usenet.com ****

Hi Robert

You could solve the problem by using an array. Personally I don't like this method too much... anyway here an example...

- on the database

CREATE TYPE t_collection IS TABLE OF NUMBER;

- on the JDBC client (excerpt)

String sql = "SELECT ename FROM emp WHERE empno IN (SELECT value(v)
FROM table(?) v)";
OraclePreparedStatement statement =
(OraclePreparedStatement)connection.prepareStateme nt(sql);
ArrayDescriptor descriptor =
ArrayDescriptor.createDescriptor("T_COLLECTION", connection);
long[] content = { 7521, 7654, 7698 };
ARRAY array = new ARRAY(descriptor, connection, content);
statement.setARRAY(1, array);
ResultSet resultSet = statement.executeQuery();
while (resultSet.next())
{
System.out.println("name: " + resultSet.getString("ename"));
}
Chris

"Robert Brown" <ro*************@yahoo.com> wrote in message
news:24**************************@posting.google.c om...

Is there a way to use PreparedStatements (or bind variables) with SQL
statements that have a variable number of arguments. For example, I
have an array of IDs for employees of a certain type and I want to
have a PreparedStatement retrieve all of them in a single SQL call.

SELECT FROM employees WHERE employee_type = ? employee_id in
(?,?,?,...,?)

It seems at least in Java that PreparedStatements can only take a
fixed number of arguments?

PreparedStatement pstmt = con.prepareStatement("SELECT FROM
employees WHERE employee_type = ? AND employee_id = ?");
pstmt.setLong(1, employeeType);
pstmt.setInt(2, employeeID);

Is there a way to have a PreparedStatement for a SQL "IN" clause or
similar variable argument length clauses? I heard that Temp tables is
one option where you first insert your array of IDs into a Temp table
and then use a JOIN. But that seems to defeat the purpose as it
requires multiple queries. Is there anyway to something like this:

int[] employeeIDArray = getEmployees();
PreparedStatement pstmt = con.prepareStatement("SELECT FROM
employees WHERE employee_type = ? AND employee_id IN (?)");
pstmt.setLong(1, employeeType);
pstmt.setIntArray(2, employeeIDArray); <--- How can I do this?

Thanks,

- Robert

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.usenet.com
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.usenet.com
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

[Prem K Mehrotra]

"Christian Antognini" <ch*****************@trivadis.com> wrote in message news:<41********@post.usenet.com>...

**** Post for FREE via your newsreader at post.usenet.com ****

Mhmm... just noticed you multi-posted... (not the best thing to do!) My
solution works only with Oracle...

"Christian Antognini" <ch*****************@trivadis.com> wrote in message
news:41********@post.usenet.com...

**** Post for FREE via your newsreader at post.usenet.com ****

Hi Robert

You could solve the problem by using an array. Personally I don't like

this

method too much... anyway here an example...

- on the database

CREATE TYPE t_collection IS TABLE OF NUMBER;

- on the JDBC client (excerpt)

String sql = "SELECT ename FROM emp WHERE empno IN (SELECT value(v)
FROM table(?) v)";
OraclePreparedStatement statement =
(OraclePreparedStatement)connection.prepareStateme nt(sql);
ArrayDescriptor descriptor =
ArrayDescriptor.createDescriptor("T_COLLECTION", connection);
long[] content = { 7521, 7654, 7698 };
ARRAY array = new ARRAY(descriptor, connection, content);
statement.setARRAY(1, array);
ResultSet resultSet = statement.executeQuery();
while (resultSet.next())
{
System.out.println("name: " + resultSet.getString("ename"));
}
Chris

"Robert Brown" <ro*************@yahoo.com> wrote in message
news:24**************************@posting.google.c om...

Is there a way to use PreparedStatements (or bind variables) with SQL
statements that have a variable number of arguments. For example, I
have an array of IDs for employees of a certain type and I want to
have a PreparedStatement retrieve all of them in a single SQL call.

SELECT FROM employees WHERE employee_type = ? employee_id in
(?,?,?,...,?)

It seems at least in Java that PreparedStatements can only take a
fixed number of arguments?

PreparedStatement pstmt = con.prepareStatement("SELECT FROM
employees WHERE employee_type = ? AND employee_id = ?");
pstmt.setLong(1, employeeType);
pstmt.setInt(2, employeeID);

Is there a way to have a PreparedStatement for a SQL "IN" clause or
similar variable argument length clauses? I heard that Temp tables is
one option where you first insert your array of IDs into a Temp table
and then use a JOIN. But that seems to defeat the purpose as it
requires multiple queries. Is there anyway to something like this:

int[] employeeIDArray = getEmployees();
PreparedStatement pstmt = con.prepareStatement("SELECT FROM
employees WHERE employee_type = ? AND employee_id IN (?)");
pstmt.setLong(1, employeeType);
pstmt.setIntArray(2, employeeIDArray); <--- How can I do this?

Thanks,

- Robert

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.usenet.com
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.usenet.com
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Have you loooked in JDBC's dynamic query interface. If you cannot do
what you want using static prepared statement, dynamic query may be
your best option.
I have personnally not used dyanmic query in JDBC, but I have used in
other languages such as PRO*C.

Prem

[Yakov]

ro*************@yahoo.com (Robert Brown) wrote in message news:<24**************************@posting.google. com>...

Is there a way to use PreparedStatements (or bind variables) with SQL
statements that have a variable number of arguments. For example, I
have an array of IDs for employees of a certain type and I want to
have a PreparedStatement retrieve all of them in a single SQL call.

SELECT FROM employees WHERE employee_type = ? employee_id in
(?,?,?,...,?)

It seems at least in Java that PreparedStatements can only take a
fixed number of arguments?

Srting sql = "SELECT FROM employees WHERE employee_type = ?
employee_id in(";

When you have all your IDs in the array, concatenate a required number
of the question marks in a loop like for(int
i=0;i<myArray.length;i++)...

Set param values from the array in a similar loop

Regards,
Yakov