473,395 Members | 1,702 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

Variable Arg Lists to PreparedStatements

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
Jul 20 '05 #1
7 2264


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


Jul 20 '05 #2
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
Jul 20 '05 #3
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.
Jul 20 '05 #4
**** 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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Jul 20 '05 #5
**** 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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Jul 20 '05 #6
"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
Jul 20 '05 #7
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
Jul 20 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Philip Rittenhouse | last post by:
I have discovered a couple of problems with the way the universal gateway code handles optional parameters and variable argument lists in COM servers. It appears to only be a problem when you...
14
by: pl | last post by:
Hi all, I followed the mails entitled 'How to turn a variable name into a string?' in march 2005 posts as I have a similar problem. I have to get some list variable names at some point in my...
7
by: bartek | last post by:
Hello, I've been pondering with this for quite some time now, and finally decided to ask here for suggestions. I'm kind of confused, actually... Maybe I'm thinking too much... Brain dump...
166
by: Graham | last post by:
This has to do with class variables and instances variables. Given the following: <code> class _class: var = 0 #rest of the class
3
by: Peteris Krumins | last post by:
Hello, Is it possible to use va lists if i prefare to define functions as following: (won't compile) void die(file, line, fn, fmt, ...) const char *file;
1
by: Peter Kirk | last post by:
Hi in Java I would more often than not use a PreparedStatement for database access (if I wasn't using an abstraction layer like Hibernate for example). Is there an equivalent construction to...
1
by: Vaughn Haybittle | last post by:
Hi, I am writing an ASP.Net application that uses a handful of dropdown lists. These lists appear on many of the .apx pages in the application. I would like to persist the data that is used to...
3
by: bennett.matthew | last post by:
Hello, Is there a good way to make PreparedStatements with a variable number of parameters? My queries look something like this: SET @rowid := null; SELECT * FROM ( SELECT
0
by: becksinthecity | last post by:
I'm trying to convert the below SQL into Oracle but am having some issues with the variable declarations. SQL commands declare @sqlstring varchar(500) select @sqlstring= (select ...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.