Rails doesn''t seem to be building my mysql requests properly, and I''m completely lost as to why. My code to select the next event for the team is: @next_event = Team.find(@session[''team_id'']).events.find (:first, :conditions => [ "event_date >= ''?''", current_time_in_mysql_format ], :order => "event_date ASC") And the error given is: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2005-06-08 14:01:55 ORDER BY event_date ASC LIMIT 1'' at line 1: SELECT * FROM events WHERE events.team_id = 1 AND event_date >= ''?''2005-06-08 14:01:55 ORDER BY event_date ASC LIMIT 1 It seems that the ''?'' isn''t getting replaced properly and is causing the error. If I pull the ?, leaving the conditions at : "event_date >= " the current_time_in_mysql_format is added to the string anyways, which almost works, but fails because it isn''t quoted. I''ve read over the api on find() 50 times, and have tried dozens of syntax changes, to no avail. I am stumped. Other finds that I''m using (eg: @team_name = Player.find(@session [''player_id'']).team.name ) are working just fine. Rails: 0.12.1 MySQL: 4.1.12 Any suggestions? --Ethan _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Don''t put ? character in quotes: @next_event = Team.find(@session[''team_id'']).events.find (:first, :conditions => [ "event_date >= ? ", current_time_in_mysql_format ], :order => "event_date ASC") Kent. On 6/8/05, E <e@bigethan.com> wrote:> Rails doesn''t seem to be building my mysql requests properly, and I''m > completely lost as to why. > > My code to select the next event for the team is: > > @next_event = Team.find(@session[''team_id'']).events.find > (:first, :conditions => [ "event_date >= ''?''", > current_time_in_mysql_format ], :order => "event_date ASC") > > And the error given is: > > You have an error in your SQL syntax; check the manual that > corresponds to your MySQL server version for the right syntax to use > near ''2005-06-08 14:01:55 ORDER BY event_date ASC LIMIT 1'' at line 1: > SELECT * FROM events WHERE events.team_id = 1 AND event_date >> ''?''2005-06-08 14:01:55 ORDER BY event_date ASC LIMIT 1 > > It seems that the ''?'' isn''t getting replaced properly and is causing > the error. If I pull the ?, leaving the conditions at : "event_date > >= " the current_time_in_mysql_format is added to the string > anyways, which almost works, but fails because it isn''t quoted. > > I''ve read over the api on find() 50 times, and have tried dozens of > syntax changes, to no avail. I am stumped. Other finds that I''m > using (eg: @team_name = Player.find(@session > [''player_id'']).team.name ) are working just fine. > > Rails: 0.12.1 > MySQL: 4.1.12 > > Any suggestions? > > --Ethan > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > > >
not quoting the ?: @next_event = Team.find(@session[''team_id'']).events.find (:first, :conditions => [ "event_date >= ? ", current_time_in_mysql_format ], :order => "event_date ASC") yields a similar error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''? 2005-06-08 14:28:24 ORDER BY event_date ASC LIMIT 1'' at line 1: SELECT * FROM events WHERE events.team_id = 1 AND event_date >= ? 2005-06-08 14:28:24 ORDER BY event_date ASC LIMIT 1 I''ve tried as many syntax options as I can think of, quotes, no quotes, using the %s, skipping the ? and trying the var right in there. all to no avail. It seems buggish, but I''d wager that I''ve got a problem rather than rails. I just can''t find it. --Ethan On Jun 8, 2005, at 2:21 PM, Kent Sibilev wrote:> Don''t put ? character in quotes: > > @next_event = Team.find(@session[''team_id'']).events.find > (:first, :conditions => [ "event_date >= ? ", > current_time_in_mysql_format ], :order => "event_date ASC") > > Kent. > > On 6/8/05, E <e@bigethan.com> wrote: > >> Rails doesn''t seem to be building my mysql requests properly, and I''m >> completely lost as to why. >> >> My code to select the next event for the team is: >> >> @next_event = Team.find(@session[''team_id'']).events.find >> (:first, :conditions => [ "event_date >= ''?''", >> current_time_in_mysql_format ], :order => "event_date ASC") >> >> And the error given is: >> >> You have an error in your SQL syntax; check the manual that >> corresponds to your MySQL server version for the right syntax to use >> near ''2005-06-08 14:01:55 ORDER BY event_date ASC LIMIT 1'' at >> line 1: >> SELECT * FROM events WHERE events.team_id = 1 AND event_date >>> ''?''2005-06-08 14:01:55 ORDER BY event_date ASC LIMIT 1 >> >> It seems that the ''?'' isn''t getting replaced properly and is causing >> the error. If I pull the ?, leaving the conditions at : "event_date >> >>> = " the current_time_in_mysql_format is added to the string >>> >> anyways, which almost works, but fails because it isn''t quoted. >> >> I''ve read over the api on find() 50 times, and have tried dozens of >> syntax changes, to no avail. I am stumped. Other finds that I''m >> using (eg: @team_name = Player.find(@session >> [''player_id'']).team.name ) are working just fine. >> >> Rails: 0.12.1 >> MySQL: 4.1.12 >> >> Any suggestions? >> >> --Ethan >> >> _______________________________________________ >> 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 >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
> I''ve tried as many syntax options as I can think of, quotes, no > quotes, using the %s, skipping the ? and trying the var right in > there. all to no avail. It seems buggish, but I''d wager that I''ve > got a problem rather than rails. I just can''t find it.Perhaps current_time_in_mysql_format isn''t being set? Where does that come from? When you look in your log, what query is being executed? Ben
it comes from here:
current_time_in_mysql_format = Time.now.strftime("%Y-%m-%d %H:%M:%S")
@next_event = Team.find(@session[''team_id'']).events.find
(:first, :conditions => [ "event_date >= ? ",
current_time_in_mysql_format ], :order => "event_date ASC)
the Time.now.strftime() bit formerly was in the @next_event line, but
I pulled it out to see if that was causing problems. It seems to be
properly set, as the date is in the MySQL error. The query getting
passed to MySQL is:
SELECT * FROM events WHERE events.team_id = 1 AND event_date >= ?
2005-06-08 14:28:24 ORDER BY event_date ASC LIMIT 1
--Ethan
On Jun 8, 2005, at 3:14 PM, Ben Bleything wrote:
>> I''ve tried as many syntax options as I can think of, quotes,
no
>> quotes, using the %s, skipping the ? and trying the var right in
>> there. all to no avail. It seems buggish, but I''d wager that
I''ve
>> got a problem rather than rails. I just can''t find it.
>>
>
> Perhaps current_time_in_mysql_format isn''t being set? Where does
that
> come from? When you look in your log, what query is being executed?
>
> Ben
> _______________________________________________
> 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
On Wed, 2005-06-08 at 16:24 -0700, E wrote:> it comes from here: > > current_time_in_mysql_format = Time.now.strftime("%Y-%m-%d %H:%M:%S") > @next_event = Team.find(@session[''team_id'']).events.find > (:first, :conditions => [ "event_date >= ? ", > current_time_in_mysql_format ], :order => "event_date ASC) > > the Time.now.strftime() bit formerly was in the @next_event line, but > I pulled it out to see if that was causing problems. It seems to be > properly set, as the date is in the MySQL error. The query getting > passed to MySQL is: > > SELECT * FROM events WHERE events.team_id = 1 AND event_date >= ? > 2005-06-08 14:28:24 ORDER BY event_date ASC LIMIT 1 > > --Ethan >It appears that you don''t need the question mark at all. Clearly it''s just concatentating the two pieces and passing them through. So how about :conditions => [ "event_date >= ", quote_string(current_time_in_mysql_format) ] That should do the trick for you. If it complains about the quote_string try using "adapter".quote_string where adapter is whatever the variable is that holds your connection to the database (quote_string is found within the connection_adapter). Sorry my head is a little out of it so the exact syntax is failing me...... But it does look like you can do without the question mark. John W Higgins wishdev-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
On Wed, Jun 08, 2005, E wrote:> it comes from here: > > current_time_in_mysql_format = Time.now.strftime("%Y-%m-%d %H:%M:%S") > @next_event = Team.find(@session[''team_id'']).events.find > (:first, :conditions => [ "event_date >= ? ", > current_time_in_mysql_format ], :order => "event_date ASC) > > the Time.now.strftime() bit formerly was in the @next_event line, but > I pulled it out to see if that was causing problems. It seems to be > properly set, as the date is in the MySQL error. The query getting > passed to MySQL is: > > SELECT * FROM events WHERE events.team_id = 1 AND event_date >= ? > 2005-06-08 14:28:24 ORDER BY event_date ASC LIMIT 1Maybe (as was suggested by the next poster), you don''t need the ? unless you''re doing multiple substitution. I''m 90% sure that''s worked for me in the past, though... Weird.
> It appears that you don''t need the question mark at all. Clearly it''s > just concatentating the two pieces and passing them through. So how > aboutYou guys have found a prexisting bug http://dev.rubyonrails.org/ticket/1407. It will be fixed before 1.0. (this is heinous guys) Ethan, What you *should* be able to do is: @next_event = Team.find(@session[''team_id'']).events.find (:first, :conditions => [ "event_date >= ? ", Time.now ], :order => "event_date ASC") No need to format your dates, AR knows how to do that. Just as an aside, have you thought about putting a team method in your application controller like this? def team Team.find(@session[''team_id'']) end It''ll simplify your code -- Cheers Koz
quote_string doesn''t work as it seems to be unknown, but the
following does:
current_time_in_mysql_format = "''" +
Time.now.strftime("%Y-%m-%d %H:%
M:%S").to_s + "''"
@next_event = Team.find(@session[''team_id'']).events.find
(:first, :conditions => [ "event_date > ",
current_time_in_mysql_format ], :order => "event_date ASC")
So I''m happy it works. thank you very much. Seriously Though
I''m
still curious why the ? replacement wasn''t working as it
"should"
--Ethan
On Jun 8, 2005, at 4:33 PM, John W Higgins wrote:
> On Wed, 2005-06-08 at 16:24 -0700, E wrote:
>
>> it comes from here:
>>
>> current_time_in_mysql_format = Time.now.strftime("%Y-%m-%d
%H:%M:%S")
>> @next_event =
Team.find(@session[''team_id'']).events.find
>> (:first, :conditions => [ "event_date >= ? ",
>> current_time_in_mysql_format ], :order => "event_date ASC)
>>
>> the Time.now.strftime() bit formerly was in the @next_event line, but
>> I pulled it out to see if that was causing problems. It seems to be
>> properly set, as the date is in the MySQL error. The query getting
>> passed to MySQL is:
>>
>> SELECT * FROM events WHERE events.team_id = 1 AND event_date >= ?
>> 2005-06-08 14:28:24 ORDER BY event_date ASC LIMIT 1
>>
>> --Ethan
>>
>>
>
> It appears that you don''t need the question mark at all. Clearly
it''s
> just concatentating the two pieces and passing them through. So how
> about
>
> :conditions => [ "event_date >= ",
> quote_string(current_time_in_mysql_format) ]
>
> That should do the trick for you. If it complains about the
> quote_string
> try using "adapter".quote_string where adapter is whatever the
> variable
> is that holds your connection to the database (quote_string is found
> within the connection_adapter). Sorry my head is a little out of it so
> the exact syntax is failing me...... But it does look like you can do
> without the question mark.
>
> John W Higgins
> wishdev-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
>
> _______________________________________________
> 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
Ahhhhhhh........ It''s sorta nice to know I''m not crazy :-) I hadn''t been formatting the dates, but in my attempts to remove all variables in trying to fix this, well, they got formatted. Thanks for the tip about the application controller. I''m just a youngling, but I''m getting there. --Ethan On Jun 8, 2005, at 4:45 PM, Michael Koziarski wrote:>> It appears that you don''t need the question mark at all. Clearly it''s >> just concatentating the two pieces and passing them through. So how >> about >> > > You guys have found a prexisting bug > http://dev.rubyonrails.org/ticket/1407. It will be fixed before 1.0. > (this is heinous guys) > > Ethan, What you *should* be able to do is: > > @next_event = Team.find(@session[''team_id'']).events.find > (:first, :conditions => [ "event_date >= ? ", > Time.now ], :order => "event_date ASC") > > No need to format your dates, AR knows how to do that. > > Just as an aside, have you thought about putting a team method in > your application controller like this? > > def team > Team.find(@session[''team_id'']) > end > > It''ll simplify your code > -- > 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
Michael Koziarski wrote:>>It appears that you don''t need the question mark at all. Clearly it''s >>just concatentating the two pieces and passing them through. So how >>about >> >> > >You guys have found a prexisting bug >http://dev.rubyonrails.org/ticket/1407. It will be fixed before 1.0. >(this is heinous guys) > >Isn''t that even a dup of http://dev.rubyonrails.org/ticket/1281 ? Jack