Re: Variable Arg Lists to PreparedStatements

• Post for FREE via your newsreader at post.usenet.com ****

Mhmm... just noticed you multi-posted... (not the best thing to do!) My solution works only with Oracle...

"Christian Antognini" <christian.antognini_at_trivadis.com> wrote in message news:414f20a0$1_at_post.usenet.com...
> **** Post for FREE via your newsreader at 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...
>
> - on the database
>
> CREATE TYPE t_collection IS TABLE OF NUMBER;
>
> - on the JDBC client (excerpt)
>
> 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
>
>
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> *** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
> http://www.usenet.com
> Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

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

• Usenet.com - The #1 Usenet Newsgroup Service on The Planet! *** http://www.usenet.com Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=