Executing SQL in rails, and the types of the data returned


#1

Hi,

Every once in while I have to run SQL by hand, in my models mostly. But
I’m wondering if there is a better way of doing it than I do. And, I
have a question concerning types of the fields in the result the query
produces.

First, about the types. In a certain model, to get and return the value
of a boolean function, I have to do:

result = InvestmentProductProvider.find_by_sql(“SELECT
rating.investment_product_provider_has_complete_rating(#{id})”).at(0)
return result.investment_product_provider_has_complete_rating == “t”

Whereas in a unittest, I can/must do:

result = InvestmentProductProviderScore.find_by_sql(“SELECT
rating.provider_score_is_current(#{first_sub_of_first_root_score.id})”).at(0)
assert result.provider_score_is_current

I really don’t see the difference between those calls, yet in the latter
the field in the result is a boolean, like I would expect, but in the
former, it is a string which contains either “t” or “f”. Both
databasefunctions (postgres) return the type boolean. So what’s the
deal?

And, I don’t like this way of executing SQL. Running find_by_sql on
ActiveRecord::Base doesn’t seem to work. At least, it does at first, but
when I want to do something with “result”, it says:

ActiveRecord::ActiveRecordError: ActiveRecord::Base doesn’t belong in a
hierarchy descending from ActiveRecord

Isn’t there any abstract and clean way of running SQL-queries, that
doesn’t have to make use one of your model classes?

Thanks in advance


#2

What version of postgres driver do you use?
ruby-postgres (libpq biniding) or postgres-pr ?

I prefer latest ruby-postgres-20051221, as it has built-in type
conversion for results and provides access to values of tuple columns
by name for resulting rows.

So I occasionaly have few options for raw SQL:

  1. Use ActiveRecord::Base.connection method :
    r = ActiveRecord::Base.connection.query("select true as a, false as b,
    3 as c ").first
    [ r[‘a’], r[‘b’], r[‘c’]]
    => [true, false, 3]

  2. And I really like this PgProc snippet found at
    http://bigbold.com/snippets/posts/show/1008
    so one can write somthing like that to call stored proc:

assert PgProc.provider_score_is_current(
first_sub_of_first_root_score.id )

HTH


#3

Lugovoi N. wrote:

What version of postgres driver do you use?
ruby-postgres (libpq biniding) or postgres-pr ?

Postgres-pr? I can only find two, postgres-ruby and postgres. I have the
latter, version 0.7.1

I prefer latest ruby-postgres-20051221, as it has built-in type
conversion for results and provides access to values of tuple columns
by name for resulting rows.

Are you saying that only the postgres-ruby driver has support for
built-in type conversion, or do they both have it from a certain version
and newer?

What exactly are the differences anyway? I mean, why would one either
choose the ruby or the c(++?) version?

So I occasionaly have few options for raw SQL:

  1. Use ActiveRecord::Base.connection method :
    r = ActiveRecord::Base.connection.query("select true as a, false as b,
    3 as c ").first
    [ r[‘a’], r[‘b’], r[‘c’]]
    => [true, false, 3]

Hmm, that doesn’t seem to work here. I can do r[0][0,1,2], but it only
returns strings.

  1. And I really like this PgProc snippet found at
    http://bigbold.com/snippets/posts/show/1008
    so one can write somthing like that to call stored proc:

assert PgProc.provider_score_is_current(
first_sub_of_first_root_score.id )

Hey, this is something I can use very well. I’ve been wondering if
calling DB functions would be possible. Thanks :slight_smile:


#4

Lugovoi N. wrote:

postgres-pr is pure ruby driver, at
http://rubyforge.org/projects/ruby-dbi/ , I didn’t use it.

About about ruby-postgres there were few threads on ruby-lang ML:

http://groups.google.com/group/comp.lang.ruby/browse_thread/thread/20c8f05ecbe88dd4/dbd26ddd499f26d6
http://groups.google.com/group/comp.lang.ruby/browse_thread/thread/c53b6ace754e8d26/9b2588b7228f96a7

HTH

Ah, so “ruby-postgres” is kind of the developtment version of
“postgres”. I’ll try the new type stuff when 0.8.0 is released.


#5

On 3/6/06, Wiebe C. removed_email_address@domain.invalid wrote:

And, I don’t like this way of executing SQL. Running find_by_sql on

ActiveRecord::Base doesn’t seem to work. At least, it does at first, but
when I want to do something with “result”, it says:

ActiveRecord::ActiveRecordError: ActiveRecord::Base doesn’t belong in a
hierarchy descending from ActiveRecord

Isn’t there any abstract and clean way of running SQL-queries, that
doesn’t have to make use one of your model classes?

You’re looking for one of the methods documented at [1]. These are
available through ActiveRecord::Base.connection.

[1]
http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/DatabaseStatements.html

Good luck,

Rowan


#6

postgres-pr is pure ruby driver, at
http://rubyforge.org/projects/ruby-dbi/ , I didn’t use it.

About about ruby-postgres there were few threads on ruby-lang ML:

http://groups.google.com/group/comp.lang.ruby/browse_thread/thread/20c8f05ecbe88dd4/dbd26ddd499f26d6
http://groups.google.com/group/comp.lang.ruby/browse_thread/thread/c53b6ace754e8d26/9b2588b7228f96a7

HTH


#7

Wiebe C. wrote:

First, about the types. In a certain model, to get and return the value
of a boolean function, I have to do:

result = InvestmentProductProvider.find_by_sql(“SELECT
rating.investment_product_provider_has_complete_rating(#{id})”).at(0)
return result.investment_product_provider_has_complete_rating == “t”

Whereas in a unittest, I can/must do:

result = InvestmentProductProviderScore.find_by_sql(“SELECT
rating.provider_score_is_current(#{first_sub_of_first_root_score.id})”).at(0)
assert result.provider_score_is_current

I really don’t see the difference between those calls, yet in the latter
the field in the result is a boolean, like I would expect, but in the
former, it is a string which contains either “t” or “f”. Both
databasefunctions (postgres) return the type boolean. So what’s the
deal?

About those types again; what exactly happens here? Why is the result in
the first call a string, and in the latter a boolean? (I tested if the
latter is really a boolean, it is. It does not just pass the assert
because any string with charachters in it is true).