Formating Statement problem (agregation problem)

Thread view:

Grzegorz Stasica - 02 Dec 2003 12:18 GMT

Hi,

How should I prepare PreparedStatement if:
PreparedStatement stmt=conn.prepareStatement("select bit_or(cright) from
codeaccess where cid=? and agid in (?) group by cid")

Both parameters are to be numbers but in second one I have list of
numbers which looks like that 10,20

The problem is that although I set first parameter as setInt(1,10) I
encounter problem with second one. I have to write it as string but if I
do that the second part of the query looks like this: "and agid in
('10,20')" which is incorrect since field agid is numeric

Reply to this Message

Robert Klemme - 02 Dec 2003 15:24 GMT

> Hi,
>
[quoted text clipped - 9 lines]
> do that the second part of the query looks like this: "and agid in
> ('10,20')" which is incorrect since field agid is numeric

That's because a single "?" can't reveive a list of values. You'll have
to provide at least as much question marks as you have values. If the
number has a fixed upper limit and you want to avoid creating
PreparedStatements with different amounts of "?" over and over again, then
you can use that upper limit and simply repeat the last value untill all
parameters are set.

Regards

robert

Reply to this Message

Grzegorz Stasica - 02 Dec 2003 19:21 GMT

Hi',

OK but the problem is that when I build string for example like this:
StringBuffer s=new StringBuffer();
s.append("10").append(",").append("20")

the jdbc will convert it to something like this: ('10,20') which is
incorrect. Even if I quote the numbers like that:
s.append("'10'").append(",").append("'20'") it still doesn't help since
jdbc driver will convert it to: ('\'10\',\'20\''). Please notice escape
characters.

Of course I set the parameter via: setString method

Does anybody have any idea how to overcome this problem?

Rgs.

Reply to this Message

Frank Brouwer - 03 Dec 2003 12:08 GMT

Hi,

Here is an example:

Fixed number of parameters:
// Prepare sql.
String sql = "select * from example where id in (?,?,?);

// Create PreparedStatement
PreparedStatement ps = connection.PreparedStatement(sql);

// Set parameters as int values.
ps.setInt(1, 10);
ps.setInt(2, 20);
ps.setInt(3, 30);

// Get resultset.
ResultSet rs = ps.executeQuery();

Flexible number of parameters:
String sql = "select * from example where id in (";

// Add as many paremeters as needed.
for (int i = 0; i<numberOfParams; i++)
{
sql+="?,";
}

// Remove last "," and add ")" for correct syntax.
sql = sql.substring (0, sql.length() - 1) + ")";

PreparedStatement ps = connection.PreparedStatement(sql);

// Add as many parameters as needed, start from index 1.
for (int i = 1; i<=numberOfParams; i++)
{
ps.setInt(i, value of param);
}

ResultSet rs = ps.executeQuery();

Maybe this will help.....

Rgds,

Frank.

> Hi',
>
[quoted text clipped - 13 lines]
>
> Rgs.

Reply to this Message

Robert Klemme - 04 Dec 2003 16:21 GMT

> Hi',
>
[quoted text clipped - 11 lines]
>
> Does anybody have any idea how to overcome this problem?

As I said, you can only use as much parameters to a PreparedStatement as
there are question marks in the SQL. If you want to have a differing
number of values you either have to generate statements on the fly or use
a prepared statement that has enough question marks in there.

robert

Reply to this Message

Frank Brouwer - 02 Dec 2003 15:34 GMT

Hi,

When you know the number of list values in advance then you use ... in
(?,?,?....) where there are ? times number of values. Then you use setInt
(n, value) where n goes from 1... number of values.

If you do not know the number of values in advance you need to construct
(build in a string) the sqlStatement, then you have the choice between using
prepared statement (like above) or direct statement (like ... in (20, 10,
11, ...). A prepared statement is more effective if you need to use it
multiple times with different values as parameters.

Hope it helps....

Regards,

Frank.

> Hi,
>
[quoted text clipped - 9 lines]
> do that the second part of the query looks like this: "and agid in
> ('10,20')" which is incorrect since field agid is numeric

Reply to this Message

Amol - 04 Dec 2003 19:40 GMT

Hi,

PreparedStatements canot handle unknown number of parameters. The most
recommended way to achieve what you are trying to do is in the
following way:

1. Create a table IN_CLAUSE_TABLE as follows:

SESSION_ID, VARCHAR2_DATA, NUMBER_DATA {add subsequent columns for any
other
data types that may require a similar search bsed on WHERE COLUMN IN
(?)}

2. The original select is now split into two statements:

INSERT INTO IN_CLAUSE_TABLE (SESSION_ID, NUMBER_DATA) VALUES
(?, ?);
-- This above insert will be PreparedStatment and will be called in a
loop
-- to set all the AGID values that would go in your preparedstatement
-- (Session id will be your application's session id or a randomly
generated
-- unique value)

Followed by:

SELECT BIT_OR(CA.CRIGHT) FROM CODEACCESS CA, IN_CLAUSE_TABLE ICT
WHERE CA.CID=? AND CA.AGID=ICT.VARCHAR2_DATA AND ICT.SESSION_ID=?
GROUP BY CID

-- Above PreparedStatement will be fired just once to get all the
values
-- you need.

This technique is especially useful if the number of values that go in
the in
clause could be potentially large which would overload the DB. Putting
that
data in the IN_CLAUSE_TABLE would make it a join operation which would
have
better performance. Ofcourse, there is the problem of inserting the
data in
the IN_CLAUSE_TABLE, but even that can be avoided depending on the
application
logic.

If the number of items in the IN CLAUSE is guaranteed to be low by
application
logic and the query is to be executed only a few times (<5 times) then
you might be better off using a dynamically constructed Statement
object by
using simple string concatenation.

Hope that helps,

Regards,
~ amol

> Hi,
>
[quoted text clipped - 9 lines]
> do that the second part of the query looks like this: "and agid in
> ('10,20')" which is incorrect since field agid is numeric

Reply to this Message

Robert Klemme - 05 Dec 2003 12:19 GMT

> Hi,
>
> PreparedStatements canot handle unknown number of parameters. The most
> recommended way to achieve what you are trying to do is in the
> following way:

s/The most recommended/A feasible/

> 1. Create a table IN_CLAUSE_TABLE as follows:
>
[quoted text clipped - 23 lines]
> values
> -- you need.

Where's the cleanup of IN_CLAUSE_TABLE? What about multiple concurrent
requests and locking on the IN_CLAUSE_TABLE? What about a database
connected in read only mode?

I doubt your solution is efficient especially if the set of parameters
changes often over time. With this scenario at hand, I'd prefer to
generate the SQL for the PreparedStatement dynamically. This can also
employ caching.

> This technique is especially useful if the number of values that go in
> the in
[quoted text clipped - 7 lines]
> application
> logic.

If the parameter sets are fixed, a database view is the better
alternative. Why manually join on each requst if you can hard code the
joins into a view?

> If the number of items in the IN CLAUSE is guaranteed to be low by
> application
> logic and the query is to be executed only a few times (<5 times) then
> you might be better off using a dynamically constructed Statement
> object by
> using simple string concatenation.

Definitely.

robert

Reply to this Message

Formating Statement problem (agregation problem)

Free Magazines