db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Raul Acevedo <r...@cantara.com>
Subject RE: using prepared statements for "in"
Date Tue, 11 Mar 2008 00:52:15 GMT
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 <raul@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


Mime
View raw message