Wiebe Cazemier
2006-Mar-06 16:51 UTC
[Rails] Executing SQL in rails, and the types of the data returned
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 -- Posted via http://www.ruby-forum.com/.
Lugovoi Nikolai
2006-Mar-06 17:21 UTC
[Rails] Executing SQL in rails, and the types of the data returned
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 On 3/6/06, Wiebe Cazemier <halfgaar@gmail.com> wrote:> 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 > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Wiebe Cazemier
2006-Mar-06 22:39 UTC
[Rails] Re: Executing SQL in rails, and the types of the data return
Lugovoi Nikolai 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 :) -- Posted via http://www.ruby-forum.com/.
Lugovoi Nikolai
2006-Mar-07 06:13 UTC
[Rails] Re: Executing SQL in rails, and the types of the data return
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 On 3/7/06, Wiebe Cazemier <halfgaar@gmail.com> wrote:> Lugovoi Nikolai 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 :) > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Wiebe Cazemier
2006-Mar-07 12:19 UTC
[Rails] Re: Re: Executing SQL in rails, and the types of the data re
Lugovoi Nikolai 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 > > HTHAh, so "ruby-postgres" is kind of the developtment version of "postgres". I''ll try the new type stuff when 0.8.0 is released. -- Posted via http://www.ruby-forum.com/.
BigSmoke
2006-Mar-07 12:53 UTC
[Rails] Executing SQL in rails, and the types of the data returned
On 3/6/06, Wiebe Cazemier <halfgaar@gmail.com> 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 -- Morality is usually taught by the immoral.
Wiebe Cazemier
2006-Mar-07 13:08 UTC
[Rails] Re: Executing SQL in rails, and the types of the data return
Wiebe Cazemier 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). -- Posted via http://www.ruby-forum.com/.