Rails - Apr 2006 - Does RoR + Oracle use bind variables?

Does ROR + Oracle use bind variables?

Using :conditions in a find statement gives the appearance that bind 
variables might be used :

header = "test header"
Posts.find(:all,:conditions=>["header = ?",header])

i.e. Does AR just pass the whole string to oracle or the SQL and the 
parameters?

I''m in an oracle shop and my boss wants to know, i''d be very
greatful if
you could tell me,

Thanks,
Chris

-- 
Posted via http://www.ruby-forum.com/.

Hi, Chris.

That form of the :conditions parameter causes the value of "header"  
to be sanitized (to prevent, for example, a SQL injection attack)  
before it replaces the question mark. The resulting condition is what  
gets built into the query that gets executed, without bind variables.  
If you''re running against an Oracle database for development, you can  
see the exact query in RAILS_ROOT/log/development.log.

There''s a good explanation of this form of the :conditions parameter,  
as well another one that uses named variables instead of question  
marks, at http://api.rubyonrails.com. Look for the find() method in  
the lower left frame, click on its link, and scroll down in the main  
frame to the section on "Conditions".

Hope this helps,
David

On Apr 19, 2006, at 8:45 AM, Chris wrote:
> Does ROR + Oracle use bind variables?
>
> Using :conditions in a find statement gives the appearance that bind
> variables might be used :
>
> header = "test header"
> Posts.find(:all,:conditions=>["header = ?",header])
>
> i.e. Does AR just pass the whole string to oracle or the SQL and the
> parameters?
>
> I''m in an oracle shop and my boss wants to know, i''d be
very
> greatful if
> you could tell me,
>
> Thanks,
> Chris
>
> -- 
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails

Thanks David,

Does this mean that you cannot use Bind variables with Oracle + ROR?

Thanks again,

Chris

-- 
Posted via http://www.ruby-forum.com/.

If you can''t use true oracle bind variables, and can only use dynamic 
SQL, then queries cannot be as optimized as they could be.  With oracle 
this means that no matter how many CPUs you add, you will still be 
hitting it hard.

-- 
Posted via http://www.ruby-forum.com/.

You can always use find_by_sql and provide the exact SQL yourself.  
That would make your code less portable, but more performant. Looks  
like we''ll always have that tradeoff. :-)

Can you do me a favor and trap one of these queries in your dev log?  
I want to make sure that what I told you is true. I''ve been doing  
some research (meet my assistant, Mr. Google), and I''m finding some  
discussions from the end of 2004 that imply that the functionality  
you want could be in place in the Oracle adapter. I''ll try to track  
it down from my end as well.

David

On Apr 19, 2006, at 9:10 AM, Chris wrote:
> If you can''t use true oracle bind variables, and can only use
dynamic
> SQL, then queries cannot be as optimized as they could be.  With  
> oracle
> this means that no matter how many CPUs you add, you will still be
> hitting it hard.
>
> -- 
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails

> -----Original Message-----
> From: rails-bounces@lists.rubyonrails.org 
> [mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of David Rupp
> Sent: Wednesday, April 19, 2006 9:54 AM
> To: rails@lists.rubyonrails.org
> Subject: Re: [Rails] Re: Does RoR + Oracle use bind variables?
> 
> 
> You can always use find_by_sql and provide the exact SQL yourself.  
> That would make your code less portable, but more performant. Looks  
> like we''ll always have that tradeoff. :-)
Yep.  You could also stick some cache or parallel hints in there as well
that way.
 
> Can you do me a favor and trap one of these queries in your dev log?  
> I want to make sure that what I told you is true. I''ve been doing
> some research (meet my assistant, Mr. Google), and I''m finding
some
> discussions from the end of 2004 that imply that the functionality  
> you want could be in place in the Oracle adapter. I''ll try to
track
> it down from my end as well.
The OCI8 driver definitely supports bind parameters, so I''m guessing
the
adapter would use them as well, though I''m not positive.

I actually don''t know how useful bind parameters are going to be in the
context of web development, since users are (presumably) going to be
bouncing around to different pages doing different queries, and thus the
previous queries would be bumped out of the cache.  I guess it depends
on your schema and SGA setup.

Regards,

Dan

I think Oracle is a clever beast,  If it recognises that the same query 
is being used then it wont have to compile it again.  It can only 
recognise that the same query is being used if you use bind variables.

Chris

-- 
Posted via http://www.ruby-forum.com/.

Hello,

I did a quick test and can confirm that neither of the forms  
presented in the Conditions section of the ActiveRecord::Base uses  
Oracle bind parameters. So, each statement is parsed by the Oracle  
parser.

What a shame!

-Anthony


On Apr 19, 2006, at 12:26 PM, Chris Richards wrote:
> I think Oracle is a clever beast,  If it recognises that the same  
> query
> is being used then it wont have to compile it again.  It can only
> recognise that the same query is being used if you use bind variables.
>
> Chris
>
> -- 
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails

This is because SQL generation is handled by ActiveRecord itself, not
by the specific connection adapters.  I''m not a giant fan of that
decision, but I do understand why it was made.
Still, you don''t need to bother with these optimizations until
you''ve
profiled your app.  If it isn''t fast enough for you, you can easily
use an Oracle-specific query to fetch the data you need.

On 4/19/06, Anthony Carlos <anthony@digitalphenom.com>
wrote:
> Hello,
>
> I did a quick test and can confirm that neither of the forms
> presented in the Conditions section of the ActiveRecord::Base uses
> Oracle bind parameters. So, each statement is parsed by the Oracle
> parser.
>
> What a shame!
>
> -Anthony
>
>
> On Apr 19, 2006, at 12:26 PM, Chris Richards wrote:
>
> > I think Oracle is a clever beast,  If it recognises that the same
> > query
> > is being used then it wont have to compile it again.  It can only
> > recognise that the same query is being used if you use bind variables.
> >
> > Chris
> >
> > --
> > Posted via http://www.ruby-forum.com/.
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

This isn''t completely true.

Since Oracle 9ish timeframe, Oracle has the option to
automatically convert common queries into placeholder
based queries.

Placeholders *would* be nice, and I''m sure we''ll get
there eventually.

Whenever this comes up, I hear people speaking of
SQL injection. I wonder if they know that using
true placeholders, SQL injection is not an issue?

--  
-- Tom Mornini

On Apr 19, 2006, at 8:10 AM, Chris wrote:
> If you can''t use true oracle bind variables, and can only use
dynamic
> SQL, then queries cannot be as optimized as they could be.  With  
> oracle
> this means that no matter how many CPUs you add, you will still be
> hitting it hard.
>
> -- 
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails