Re: Variable Arg Lists to PreparedStatements

Joe Weinstein (joeNOSPAM_at_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. Received on Mon Sep 20 2004 - 14:15:39 CDT