Forum: Ruby on Rails Executing SQL in rails, and the types of the data returned

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Wiebe C. (Guest)
on 2006-03-06 18:51
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
Lugovoi N. (Guest)
on 2006-03-06 19:22
(Received via mailing list)
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
Wiebe C. (Guest)
on 2006-03-07 00:39
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.

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

Hey, this is something I can use very well. I've been wondering if
calling DB functions would be possible. Thanks :)
Lugovoi N. (Guest)
on 2006-03-07 08:16
(Received via mailing list)
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/brow...
http://groups.google.com/group/comp.lang.ruby/brow...

HTH
Wiebe C. (Guest)
on 2006-03-07 14:19
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/brow...
> 
http://groups.google.com/group/comp.lang.ruby/brow...
>
> 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.
BigSmoke (Guest)
on 2006-03-07 14:54
(Received via mailing list)
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/Co...


Good luck,

Rowan
Wiebe C. (Guest)
on 2006-03-07 15:08
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).
This topic is locked and can not be replied to.