Re: Variable Arg Lists to PreparedStatements

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 20 Sep 2004 13:03:09 -0500
Message-ID: <uacvkes7a.fsf@standardandpoors.com>

On Mon, 20 Sep 2004, 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.

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

Received on Mon Sep 20 2004 - 13:03:09 CDT