You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by Raul Acevedo <ra...@cantara.com> on 2008/03/06 03:53:59 UTC
using prepared statements for "in"
When you use Criteria.addIn, Torque does not generate a prepared
statement. (Or at least it doesn't use bind variables for the "in"
part, even if it does for other parts of the query.)
Is there a way to get Torque to use bind variables for this? Without
having to convert the query into a bunch of "or"s?
Raul
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: using prepared statements for "in"
Posted by Raul Acevedo <ra...@cantara.com>.
In most cases N is a small number, so most times it would be cached.
Also, it is desirable to use prepared statements to aid in preventing
SQL injection attacks, since with prepared statements the JDBC driver
makes sure the arguments are properly protected.
Raul
On Mon, 2008-03-10 at 15:19 -0400, Greg Monroe wrote:
> Just curious why you want/need to do this?
>
> Since the number of items in the "in set"(N) is variable, and
> the prepared statement rules want a syntax like:
>
> select * from foo where id in ( ?, ?, ?, ?...n)
>
> (At least MySQL 5.0 does...)
>
> The only optimization that I can see is if you're doing a bunch
> of queries with a fairly static number of items in the set.
> Otherwise, you're probably spending more CPU cycles trying
> to match a cached already prepared statement with the same N,
> then failing the match, generating and executing the prepared
> statement.
>
> IMHO, it's more efficient for the general case NOT to create
> the "in set" as part of a prepared statement. And if you have
> the need for doing a set of queries with a static N, use the
> OR condition.
>
> But I haven't spent a lot of time thinking this thru so...
> > -----Original Message-----
> > From: Thomas Fischer [mailto:fischer@seitenbau.net]
> > Sent: Thursday, March 06, 2008 5:05 AM
> > To: Apache Torque Users List
> > Subject: RE: using prepared statements for "in"
> >
> > I assume you are using XXPeer.doPSSelect ?
> > Looking at the source code, I cannot see a way. Not even
> > Criteria.CUSTOM
> > will help :-(
> >
> > Do jdbc drivers understand binding of a collection to a prepared
> > statement
> > ? I never tried it.
> >
> > Thomas
> >
> > Raul Acevedo <ra...@cantara.com> schrieb am 06.03.2008 03:53:59:
> >
> > > When you use Criteria.addIn, Torque does not generate a prepared
> > > statement. (Or at least it doesn't use bind variables for the "in"
> > > part, even if it does for other parts of the query.)
> > >
> > > Is there a way to get Torque to use bind variables for this?
> Without
> > > having to convert the query into a bunch of "or"s?
> > >
> > > Raul
> > >
> > >
> > >
> ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> > > For additional commands, e-mail: torque-user-help@db.apache.org
> > >
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: torque-user-help@db.apache.org
>
> DukeCE Privacy Statement:
> Please be advised that this e-mail and any files transmitted with
> it are confidential communication or may otherwise be privileged or
> confidential and are intended solely for the individual or entity
> to whom they are addressed. If you are not the intended recipient
> you may not rely on the contents of this email or any attachments,
> and we ask that you please not read, copy or retransmit this
> communication, but reply to the sender and destroy the email, its
> contents, and all copies thereof immediately. Any unauthorized
> dissemination, distribution or copying of this communication is
> strictly prohibited.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: using prepared statements for "in"
Posted by Greg Monroe <Gr...@DukeCE.com>.
Just curious why you want/need to do this?
Since the number of items in the "in set"(N) is variable, and
the prepared statement rules want a syntax like:
select * from foo where id in ( ?, ?, ?, ?...n)
(At least MySQL 5.0 does...)
The only optimization that I can see is if you're doing a bunch
of queries with a fairly static number of items in the set.
Otherwise, you're probably spending more CPU cycles trying
to match a cached already prepared statement with the same N,
then failing the match, generating and executing the prepared
statement.
IMHO, it's more efficient for the general case NOT to create
the "in set" as part of a prepared statement. And if you have
the need for doing a set of queries with a static N, use the
OR condition.
But I haven't spent a lot of time thinking this thru so...
> -----Original Message-----
> From: Thomas Fischer [mailto:fischer@seitenbau.net]
> Sent: Thursday, March 06, 2008 5:05 AM
> To: Apache Torque Users List
> Subject: RE: using prepared statements for "in"
>
> I assume you are using XXPeer.doPSSelect ?
> Looking at the source code, I cannot see a way. Not even
> Criteria.CUSTOM
> will help :-(
>
> Do jdbc drivers understand binding of a collection to a prepared
> statement
> ? I never tried it.
>
> Thomas
>
> Raul Acevedo <ra...@cantara.com> schrieb am 06.03.2008 03:53:59:
>
> > When you use Criteria.addIn, Torque does not generate a prepared
> > statement. (Or at least it doesn't use bind variables for the "in"
> > part, even if it does for other parts of the query.)
> >
> > Is there a way to get Torque to use bind variables for this?
Without
> > having to convert the query into a bunch of "or"s?
> >
> > Raul
> >
> >
> >
---------------------------------------------------------------------
> > To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: torque-user-help@db.apache.org
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
DukeCE Privacy Statement:
Please be advised that this e-mail and any files transmitted with
it are confidential communication or may otherwise be privileged or
confidential and are intended solely for the individual or entity
to whom they are addressed. If you are not the intended recipient
you may not rely on the contents of this email or any attachments,
and we ask that you please not read, copy or retransmit this
communication, but reply to the sender and destroy the email, its
contents, and all copies thereof immediately. Any unauthorized
dissemination, distribution or copying of this communication is
strictly prohibited.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: using prepared statements for "in"
Posted by Thomas Fischer <fi...@seitenbau.net>.
I assume you are using XXPeer.doPSSelect ?
Looking at the source code, I cannot see a way. Not even Criteria.CUSTOM
will help :-(
Do jdbc drivers understand binding of a collection to a prepared statement
? I never tried it.
Thomas
Raul Acevedo <ra...@cantara.com> schrieb am 06.03.2008 03:53:59:
> When you use Criteria.addIn, Torque does not generate a prepared
> statement. (Or at least it doesn't use bind variables for the "in"
> part, even if it does for other parts of the query.)
>
> Is there a way to get Torque to use bind variables for this? Without
> having to convert the query into a bunch of "or"s?
>
> Raul
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org