Bind variables

ruby-talk
1

I am extremely new to Ruby, my company is in the process of evaluating it for a large new report system and administration system. One concern with Ruby is the use of bind variables, we need to use bind variables for performance concerns, I am sure I am getting different concepts mixed up. It seems the default SQL library with Ruby on Rails does not support bind variables, there does seem to be at least one other SQL library for SQL Sequel, which can perform parameterized queries, is this true bind variables or are they just turning into a string underneath for us, the fact that it is documented as to protection against SQL injection. Seems to suggest it is true bind variable.

Another feature which is not critical but would be nice, I haven't been able to find information about this for Sequel, but sqlite-ruby has this though is of no use to use since we need to support oracle, postsql initially and more latter on.

2

AFAIK, all the database connection libraries support parameterized queries if the underlying database supports it (mysql, postgres, oracle, etc). Not every ORM mapper uses bind variables inside (like ActiveRecord).

···

On Jun 4, 2008, at 22:47 PM, Nathan Day wrote:

I am extremely new to Ruby, my company is in the process of evaluating it for a large new report system and administration system. One concern with Ruby is the use of bind variables, we need to use bind variables for performance concerns, I am sure I am getting different concepts mixed up. It seems the default SQL library with Ruby on Rails does not support bind variables, there does seem to be at least one other SQL library for SQL Sequel, which can perform parameterized queries, is this true bind variables or are they just turning into a string underneath for us, the fact that it is documented as to protection against SQL injection. Seems to suggest it is true bind variable.

3

One
concern with Ruby is the use of bind variables, we need to use bind
variables for performance concerns, I am sure I am getting different
concepts mixed up.

Probably prepared statements is what would make the difference in performance.

It seems the default SQL library with Ruby on Rails
does not support bind variables,

That would be the default ORM. ActiveRecord does not support bind variables at
the SQL adapter level. It does support

The individual SQL libraries, such as mysql, postgres, etc, do support bind
variables. This won't help you much if you're using Rails in its entirety --
but if you really want to, you can always swap out ActiveRecord for something
else, like DataMapper or Sequel. Of course, by then, you might consider using
another framework altogether, like Merb.

Also: There have been some lively discussions about this in the Rails
community. (ruby-talk is a Ruby discussion list, and contrary to popular
opinion, Ruby is more than just Rails.)

there does seem to be at least one
other SQL library for SQL Sequel, which can perform parameterized
queries, is this true bind variables or are they just turning into a
string underneath for us, the fact that it is documented as to
protection against SQL injection. Seems to suggest it is true bind
variable.

I can't speak for Sequel, but ActiveRecord does support fake bind variables in
a few places, to guard against SQL injection. Maybe someday it will do actual
bind variables, but for now, it just turns it into a string under the hood.

Another feature which is not critical but would be nice, I haven't
been able to find information about this for Sequel, but sqlite-ruby
has this though is of no use to use since we need to support oracle,
postsql initially and more latter on.

What feature is this?

And by the way, Google for information about optimizing Rails on Oracle.
People have done tricks to make Oracle work reasonably well even without real
bind variables.

···

On Thursday 05 June 2008 00:47:13 Nathan Day wrote:

4

One
concern with Ruby is the use of bind variables, we need to use bind
variables for performance concerns, I am sure I am getting different
concepts mixed up.

Probably prepared statements is what would make the difference in performance.

Would there be in point in using a prepared statement if you don't have bind variables, every change to a variable would result in an complete new statement. There is also an issue with Oracle 9 and earlier which resulting in bad performance without bind variable, oracle caches compiled statements and without bind variables every query is treated as a new statement. We have even had extreme case where the database would collect so many compiled queries that it was clear out that the database failed. Oracle issue has a compromise fix, oracle 11 can use bind variables to make decisions about how to perform the query.

It seems the default SQL library with Ruby on Rails
does not support bind variables,

That would be the default ORM. ActiveRecord does not support bind variables at
the SQL adapter level. It does support

The individual SQL libraries, such as mysql, postgres, etc, do support bind
variables. This won't help you much if you're using Rails in its entirety --
but if you really want to, you can always swap out ActiveRecord for something
else, like DataMapper or Sequel. Of course, by then, you might consider using
another framework altogether, like Merb.

Also: There have been some lively discussions about this in the Rails
community. (ruby-talk is a Ruby discussion list, and contrary to popular
opinion, Ruby is more than just Rails.)

there does seem to be at least one
other SQL library for SQL Sequel, which can perform parameterized
queries, is this true bind variables or are they just turning into a
string underneath for us, the fact that it is documented as to
protection against SQL injection. Seems to suggest it is true bind
variable.

I can't speak for Sequel, but ActiveRecord does support fake bind variables in
a few places, to guard against SQL injection. Maybe someday it will do actual
bind variables, but for now, it just turns it into a string under the hood.

Another feature which is not critical but would be nice, I haven't
been able to find information about this for Sequel, but sqlite-ruby
has this though is of no use to use since we need to support oracle,
postsql initially and more latter on.

What feature is this?

Ok that was not vary clear, I was referring to query metadata.

···

On 05/06/2008, at 4:11 PM, David Masover wrote:

On Thursday 05 June 2008 00:47:13 Nathan Day wrote:

And by the way, Google for information about optimizing Rails on Oracle.
People have done tricks to make Oracle work reasonably well even without real
bind variables.

5

OK once again in english

We have even had extreme case where the database would collect so many compiled queries that it wasn't clearing out that the database failed. Oracle 10 has a compromise fix where you get Oracle to convert all values to bind variables, oracle 11 can use bind variables to make decisions about how to perform the query.

···

On 07/06/2008, at 12:18 AM, Nathan Day wrote:

One
concern with Ruby is the use of bind variables, we need to use bind
variables for performance concerns, I am sure I am getting different
concepts mixed up.

Probably prepared statements is what would make the difference in performance.

Would there be in point in using a prepared statement if you don't have bind variables, every change to a variable would result in an complete new statement. There is also an issue with Oracle 9 and earlier which resulting in bad performance without bind variable, oracle caches compiled statements and without bind variables every query is treated as a new statement. We have even had extreme case where the database would collect so many compiled queries that it was clear out that the database failed. Oracle issue has a compromise fix, oracle 11 can use bind variables to make decisions about how to perform the query.