Variable Arg Lists to PreparedStatements

R

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
 
J

Joe Weinstein

Robert said:
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?
Click to expand...

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
 
G

Galen Boyer

This is a good reason to use a stored proc.

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?
Click to expand...
Click to expand...

You could do the following:

WHERE employee_type = ?
AND (
employee_id = ?
OR
employee_id = ?
OR
employee_id = ?
)
Sorry. JDBC doesn't offer this.
Click to expand...

Oracle offers array support, and I would guess others do as well,
but then you have to use their drivers.
 
C

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.prepareStatement(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 said:
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
Click to expand...



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

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 said:
**** 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.prepareStatement(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 said:
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
Click to expand...



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



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

Malcolm Dew-Jones

Joe Weinstein ([email protected]) 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.
 
P

Prem K Mehrotra

Christian Antognini said:
**** 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 said:
**** 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.prepareStatement(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 said:
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
Click to expand...



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



-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.usenet.com
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Click to expand...
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
 
Y

Yakov

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?
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

PreparedStatement 10
Connected SQLite to my java program but information are not submitted 2
PreparedStatement problem 6
generating random argument lists 8
Tuples vs. variable-length argument lists 2
how to pass variable arg list to a function pointer 3
Please, I am going insane: First element access causing ArrayIndexOutOfBoundsException:0 4
using JSP and JavaBeans to enter data in MySQL database 2

Members online

Total: 64 (members: 3, guests: 61)
Robots: 402

Forum statistics

Threads
473,994
Messages
2,570,222
Members
46,810
Latest member
Kassie0918

Latest Threads

Top