Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Variable Arg Lists to PreparedStatements
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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=