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
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:
-
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]
-
And I really like this PgProc snippet found at
BigBold - Informasi Tentang Bisnis dan Marketing
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
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:
- 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.
- And I really like this PgProc snippet found at
BigBold - Informasi Tentang Bisnis dan Marketing
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. 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.
On 3/6/06, Wiebe C. [email protected] 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
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).