Hello
If I''m using an SQL-based finder method (such as find_by_sql), and I
want to use some vendor-specific DB funtions like MySQL''s NOW(), that
may not be supported by other platforms... what''s the best way to do
this?
Is there any way I can do something like:
"SELECT #{current_db_driver.func_current_time}"
to have this generate the appropriate SQL in a db-independent way?
This sort of thing can be readily done with JDBC (java) and ADODB
(php)... does Rails have any neat solutions, I wonder...
thanks a lot for any help
Arc
On 8/9/05, arcturus nine <arcturus9-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hello > > If I''m using an SQL-based finder method (such as find_by_sql), and I > want to use some vendor-specific DB funtions like MySQL''s NOW(), that > may not be supported by other platforms... what''s the best way to do > this? > > Is there any way I can do something like: > > "SELECT #{current_db_driver.func_current_time}" > > to have this generate the appropriate SQL in a db-independent way? > > This sort of thing can be readily done with JDBC (java) and ADODB > (php)... does Rails have any neat solutions, I wonder... > > thanks a lot for any help > ArcThere are rumors of a new find() option called :select that will let you do just that. http://dev.rubyonrails.com/changeset/1830 Example from the changeset: find(:all, :select => "first_name, last_name") If you''re running off the svn trunk, you''re all set, otherwise use the beta rails gems. I''m not sure what revision they''re up to. IIRC, the beta gem version should be something like 0.13.1.xxxx, where xxxx is > 1830. -- rick http://techno-weenie.net
On 8/10/05, arcturus nine <arcturus9-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hello > > If I''m using an SQL-based finder method (such as find_by_sql), and I > want to use some vendor-specific DB funtions like MySQL''s NOW(), that > may not be supported by other platforms... what''s the best way to do > this? > > Is there any way I can do something like: > > "SELECT #{current_db_driver.func_current_time}"Why are you selecting the time from the database? Just use Time.now? If you want to add the current time to predicates, simply do something like this Whatever.find(:all, :conditions=>["time_thingy < ?", Time.now]) -- Cheers Koz
Hi Yeah - sorry. It was a bit of a contrived example and I didn''t explain myself very well. It''s not necessarily the time I''m going to be selecting from the database - it could be _any_ database function - for example any one mentioned on this page: http://dev.mysql.com/doc/mysql/en/string-functions.html The problem is, I don''t want to tie myself down to MySQL - So, going back to time again (bear with me...) if I''m working with MySQL I use "NOW()", if I''m working with SQL Server I use "GETDATE()", and who knows what else for Postgres, etc, etc. If there''s no such DB-abstraction layer for Ruby/Rails then I''ll have to manually check which database system is being used and manually generate the appropriate function calls appropriate to the vendor... not ideal... unless anyone has the magic answer? Thanks again for any help and to those who have replied so far, Arc. On 09/08/05, Michael Koziarski <koziarski-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 8/10/05, arcturus nine <arcturus9-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > Hello > > > > If I''m using an SQL-based finder method (such as find_by_sql), and I > > want to use some vendor-specific DB funtions like MySQL''s NOW(), that > > may not be supported by other platforms... what''s the best way to do > > this? > > > > Is there any way I can do something like: > > > > "SELECT #{current_db_driver.func_current_time}" > > Why are you selecting the time from the database? Just use Time.now? > > If you want to add the current time to predicates, simply do > something like this > > Whatever.find(:all, :conditions=>["time_thingy < ?", Time.now]) > > -- > Cheers > > Koz > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Oh, I didn''t fully understand your entry. There is a bit of a DB
abstraction layer:
http://rails.rubyonrails.com/classes/ActiveRecord/ConnectionAdapters/AbstractAdapter.html.
Methods aren''t defined for what you want, but with the mighty power
of ruby, you can add them.
class ActiveRecord::ConnectionAdapters::MysqlAdapter.class_eval do
def current_time
"NOW()"
end
end
class ActiveRecord::ConnectionAdapters::SQLServerAdapter.class_eval do
def current_time
"GETDATE()"
end
end
Post.connection.current_time to access it.
On 8/9/05, arcturus nine
<arcturus9-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> Hi
>
> Yeah - sorry. It was a bit of a contrived example and I didn''t
> explain myself very well. It''s not necessarily the time
I''m going to
> be selecting from the database - it could be _any_ database function -
> for example any one mentioned on this page:
>
> http://dev.mysql.com/doc/mysql/en/string-functions.html
>
> The problem is, I don''t want to tie myself down to MySQL - So,
going
> back to time again (bear with me...) if I''m working with MySQL I
use
> "NOW()", if I''m working with SQL Server I use
"GETDATE()", and who
> knows what else for Postgres, etc, etc.
>
> If there''s no such DB-abstraction layer for Ruby/Rails then
I''ll have
> to manually check which database system is being used and manually
> generate the appropriate function calls appropriate to the vendor...
> not ideal... unless anyone has the magic answer?
>
> Thanks again for any help and to those who have replied so far,
> Arc.
>
> On 09/08/05, Michael Koziarski
<koziarski-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > On 8/10/05, arcturus nine
<arcturus9-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > > Hello
> > >
> > > If I''m using an SQL-based finder method (such as
find_by_sql), and I
> > > want to use some vendor-specific DB funtions like
MySQL''s NOW(), that
> > > may not be supported by other platforms... what''s the
best way to do
> > > this?
> > >
> > > Is there any way I can do something like:
> > >
> > > "SELECT #{current_db_driver.func_current_time}"
> >
> > Why are you selecting the time from the database? Just use Time.now?
> >
> > If you want to add the current time to predicates, simply do
> > something like this
> >
> > Whatever.find(:all, :conditions=>["time_thingy < ?",
Time.now])
> >
> > --
> > Cheers
> >
> > Koz
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
--
rick
http://techno-weenie.net
> If you want to add the current time to predicates, simply do > something like this > > Whatever.find(:all, :conditions=>["time_thingy < ?", Time.now])There''s a problem with this approach if you''ve got several app boxes, and there''s even a remote chance that their clocks aren''t perfectly synchronized. For that reason, I''ve always used the time on the database box as the "one true time" for any persistent data.
> There''s a problem with this approach if you''ve got several app boxes, > and there''s even a remote chance that their clocks aren''t perfectly > synchronized. > > For that reason, I''ve always used the time on the database box as the > "one true time" for any persistent data.I second that. I have some auction data that I need to query, and I''d feel a lot better basing the data off what''s in the database, not what time my app server said. Now that I think about it, I ran into this very issue already, but I solved it a different way. I reimplemented UTC_TIMESTAMP() in Postgresql as a stored procedure (a daily WTF submission?). I think I''ll try extending the adapters instead... -- rick http://techno-weenie.net
On 8/10/05, Michael Schoen <schoenm-ihVZJaRskl1bRRN4PJnoQQ@public.gmane.org> wrote:> > If you want to add the current time to predicates, simply do > > something like this > > > > Whatever.find(:all, :conditions=>["time_thingy < ?", Time.now]) > > There''s a problem with this approach if you''ve got several app boxes, > and there''s even a remote chance that their clocks aren''t perfectly > synchronized.Yeah, this is true. However we had to ensure our servers were syncronised as part of our sarbanes-oxley compliance, so most sites should be investing in this now.> For that reason, I''ve always used the time on the database box as the > "one true time" for any persistent data.You''re talking a couple of seconds, I wouldn''t worry about it. But If you are still concerned, Rick''s suggestion will work. -- Cheers Koz
> The problem is, I don''t want to tie myself down to MySQL - So, going > back to time again (bear with me...) if I''m working with MySQL I use > "NOW()", if I''m working with SQL Server I use "GETDATE()", and who > knows what else for Postgres, etc, etc.How often will you change database software? Why worry about this now? I''m asking this because I really don''t know, not because I''m trolling. I''ve always been just the UI guy on web apps I''ve worked on, but I''ve never seen anyone swap databases like that. I''ve certainly been on projects where additional support was added (like Oracle in addition to SQL Server), but with the knowledge that the work involved would be significant and would merit a dot-release of the product, at least. In other words, it was an issue only when it was *really* an issue.
A SQL phrasebook can help this if a db change is anticipated. I''ve seen it happen, especially in the NGO/NPO sector when I was working in DC. On Aug 9, 2005, at 5:04 PM, Andrew Otwell wrote:>> The problem is, I don''t want to tie myself down to MySQL - So, going >> back to time again (bear with me...) if I''m working with MySQL I use >> "NOW()", if I''m working with SQL Server I use "GETDATE()", and who >> knows what else for Postgres, etc, etc. >> > > How often will you change database software? Why worry about this now? > > I''m asking this because I really don''t know, not because I''m > trolling. I''ve always been just the UI guy on web apps I''ve worked > on, but I''ve never seen anyone swap databases like that. I''ve > certainly been on projects where additional support was added (like > Oracle in addition to SQL Server), but with the knowledge that the > work involved would be significant and would merit a dot-release of > the product, at least. In other words, it was an issue only when it > was *really* an issue. > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Andrew Otwell wrote:>> The problem is, I don''t want to tie myself down to MySQL - So, going >> back to time again (bear with me...) if I''m working with MySQL I use >> "NOW()", if I''m working with SQL Server I use "GETDATE()", and who >> knows what else for Postgres, etc, etc. > > > How often will you change database software? Why worry about this now? > > I''m asking this because I really don''t know, not because I''m trolling. > I''ve always been just the UI guy on web apps I''ve worked on, but I''ve > never seen anyone swap databases like that. I''ve certainly been on > projects where additional support was added (like Oracle in addition > to SQL Server), but with the knowledge that the work involved would be > significant and would merit a dot-release of the product, at least. In > other words, it was an issue only when it was *really* an issue. > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/railsYou usually don''t need to swap databases. Maybe, when you have a product and each customer requires the use of their chosen dbms. Adding some *ideas* to this thread: Michael Schoen has told about the issue of having several app boxes and one database server, having some chance to have some difference between clocks. Well, you can have one server w/ a "ntpd" and the other boxes acting as ntp clients. Also, put a sync command in cron. The ntpd server can also act as ntp client, and query an ''official'' ntp server. IMHO, the database shouldn''t know about the ''data'' itself at all (ie: setting the date for records), but on ''how to store data''. regards! Juraci Krohling Costa http://jkcosta.info juca at jkcosta dot info
Wow - nearly missed this... my Gmail had stacked up all replies so it looked like there were only one or two that I''d already read... lucky I figured it out - that''s pretty close to what I was looking for! Yes, it''d be better if the Ruby/Rails database ''drivers'' provided such methods as standard, but once again the power of Ruby saves the day! Thanks a lot for that! On 09/08/05, Rick Olson <technoweenie-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Oh, I didn''t fully understand your entry. There is a bit of a DB > abstraction layer: > http://rails.rubyonrails.com/classes/ActiveRecord/ConnectionAdapters/AbstractAdapter.html. > Methods aren''t defined for what you want, but with the mighty power > of ruby, you can add them. > > class ActiveRecord::ConnectionAdapters::MysqlAdapter.class_eval do > def current_time > "NOW()" > end > end > > class ActiveRecord::ConnectionAdapters::SQLServerAdapter.class_eval do > def current_time > "GETDATE()" > end > end > > Post.connection.current_time to access it. > > On 8/9/05, arcturus nine <arcturus9-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > Hi > > > > Yeah - sorry. It was a bit of a contrived example and I didn''t > > explain myself very well. It''s not necessarily the time I''m going to > > be selecting from the database - it could be _any_ database function - > > for example any one mentioned on this page: > > > > http://dev.mysql.com/doc/mysql/en/string-functions.html > > > > The problem is, I don''t want to tie myself down to MySQL - So, going > > back to time again (bear with me...) if I''m working with MySQL I use > > "NOW()", if I''m working with SQL Server I use "GETDATE()", and who > > knows what else for Postgres, etc, etc. > > > > If there''s no such DB-abstraction layer for Ruby/Rails then I''ll have > > to manually check which database system is being used and manually > > generate the appropriate function calls appropriate to the vendor... > > not ideal... unless anyone has the magic answer? > > > > Thanks again for any help and to those who have replied so far, > > Arc. > > > > On 09/08/05, Michael Koziarski <koziarski-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > On 8/10/05, arcturus nine <arcturus9-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > Hello > > > > > > > > If I''m using an SQL-based finder method (such as find_by_sql), and I > > > > want to use some vendor-specific DB funtions like MySQL''s NOW(), that > > > > may not be supported by other platforms... what''s the best way to do > > > > this? > > > > > > > > Is there any way I can do something like: > > > > > > > > "SELECT #{current_db_driver.func_current_time}" > > > > > > Why are you selecting the time from the database? Just use Time.now? > > > > > > If you want to add the current time to predicates, simply do > > > something like this > > > > > > Whatever.find(:all, :conditions=>["time_thingy < ?", Time.now]) > > > > > > -- > > > Cheers > > > > > > Koz > > > _______________________________________________ > > > Rails mailing list > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > -- > rick > http://techno-weenie.net > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >