Re: Variable Arg Lists to PreparedStatements

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
Received on Mon Sep 20 2004 - 11:58:26 CDT