bind variables

[Nathan Day]

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.

 

[Eric Hodel]

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.

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).

 

[David Masover]

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.

 

[Nathan Day]

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.

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.)


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.


What feature is this?

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

 

[Nathan Day]

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.

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.