Variable Arg Lists to PreparedStatements

From: Robert Brown (robertbrown1971_at_yahoo.com)
Date: 09/20/04 

Date: 20 Sep 2004 09:52:35 -0700

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

Relevant Pages

  • Variable Arg Lists to PreparedStatements
    ... Is there a way to use PreparedStatements with SQL ... have a PreparedStatement retrieve all of them in a single SQL call. ... SELECT FROM employees WHERE employee_type =? ... one option where you first insert your array of IDs into a Temp table ...
    (comp.lang.java.programmer)
  • Re: Variable Arg Lists to PreparedStatements
    ... :> Is there a way to use PreparedStatements with SQL ... :> have a PreparedStatement retrieve all of them in a single SQL call. ... I heard that Temp tables is ... SELECT FROM employees WHERE employee_type =? ...
    (comp.lang.java.databases)
  • Re: Variable Arg Lists to PreparedStatements
    ... :> Is there a way to use PreparedStatements with SQL ... :> have a PreparedStatement retrieve all of them in a single SQL call. ... I heard that Temp tables is ... SELECT FROM employees WHERE employee_type =? ...
    (comp.lang.java.programmer)
  • Re: Skipping detail lines in a report
    ... SELECT Employees.* ... any of the filter words are located anywhere in the Address field. ... No code, no muss, just SQL. ... I spin through the entire array of filter-words (it's ...
    (microsoft.public.access.reports)
  • Re: JDBC PreparedStatements, Java Data Objects/O-R mapping, and SQL Injection
    ... "PreparedStatement objects represent SQL statements that can be prepared, ... the MySQL JDBC driver, since it's open source and widely used. ... protection against SQL injection gained by using PreparedStatements, ...
    (SecProg)