Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Variable Arg Lists to PreparedStatements

Re: Variable Arg Lists to PreparedStatements

From: Christian Antognini <christian.antognini_at_trivadis.com>
Date: Mon, 20 Sep 2004 20:25:42 +0200
Message-ID: <414f20a0$1@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...

      CREATE TYPE t_collection IS TABLE OF NUMBER;

      String sql = "SELECT ename FROM emp WHERE empno IN (SELECT value(v) FROM table(?) v)";

      OraclePreparedStatement statement = (OraclePreparedStatement)connection.prepareStatement(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" <robertbrown1971_at_yahoo.com> wrote in message news:240a4d09.0409200852.1711430a_at_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?
>
> 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

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Received on Mon Sep 20 2004 - 13:25:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US