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
on 2006-04-19 18:45
on 2006-04-19 19:00
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 2006-04-19 19:06
Thanks David, Does this mean that you cannot use Bind variables with Oracle + ROR? Thanks again, Chris
on 2006-04-19 19:10
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.
on 2006-04-19 19:55
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 2006-04-19 22:00
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 M.