"Christian Antognini" <christian.antognini_at_trivadis.com> wrote in message news:<414f21e8$1_at_post.usenet.com>...
> **** 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
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Have you loooked in JDBC's dynamic query interface. If you cannot do
what you want using static prepared statement, dynamic query may be
your best option.
I have personnally not used dyanmic query in JDBC, but I have used in
other languages such as PRO*C.
Prem
Received on Mon Sep 20 2004 - 20:24:11 CDT
