i have a problem with the datetime format of rails. i am unable to save a dataset to the db. here''s the error message -- the original error message was in german, so i translated it -- i get: DBI::DatabaseError: Execute OLE error code:80040E07 in Microsoft OLE DB Provider for SQL Server Couldn''t convert a char-Datatype to datetime HRESULT error code:0x80020009 Exception occured.: INSERT INTO projects ([updated_at], [title], [starts_at], [description], [ends_at], [created_at]) VALUES(''2006-01-20 15:23:40'', ''title'', ''2006-01-20 15:23:00'', ''description'', ''2006-01-20 15:23:00'', ''2006-01-20 15:23:00'') -- Posted via http://www.ruby-forum.com/.
i''m using Rails 1.0, Ruby 1.8.2 and SQL Server. -- Posted via http://www.ruby-forum.com/.
I had a similar problem when using DateTime instead of Time objects. Could this solve your problem? On Jan 20, 2006, at 10:02 AM, NoobSaibot wrote:> i have a problem with the datetime format of rails. i am unable to > save > a dataset to the db. here''s the error message -- the original error > message was in german, so i translated it -- i get: > > DBI::DatabaseError: Execute > OLE error code:80040E07 in Microsoft OLE DB Provider for SQL > Server > Couldn''t convert a char-Datatype to datetime > HRESULT error code:0x80020009 > Exception occured.: INSERT INTO projects ([updated_at], [title], > [starts_at], [description], [ends_at], [created_at]) VALUES > (''2006-01-20 > 15:23:40'', ''title'', ''2006-01-20 15:23:00'', ''description'', ''2006-01-20 > 15:23:00'', ''2006-01-20 15:23:00'') > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails
Jamie Orchard-Hays wrote:> I had a similar problem when using DateTime instead of Time objects. > Could this solve your problem?Right now i''m using scaffold, so i didn''t define the datatype of this attribute in the object. -- Posted via http://www.ruby-forum.com/.
Well, i''d like to use Ruby (on Rails) in our company. But this stupid little problem prevents me from developing/presenting a usable application. -- Posted via http://www.ruby-forum.com/.
If you can pinpoint the problem, be sure to file a bug report on the Rails website. Jamie On Jan 20, 2006, at 12:20 PM, NoobSaibot wrote:> Well, i''d like to use Ruby (on Rails) in our company. But this stupid > little problem prevents me from developing/presenting a usable > application. > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails
SQL Server has some weird problems with dates and internationalization. Some drivers (e.g. the Spanish one) even has bugs that will not be solved. Anyway, you might have more success with the following date formats: dates: {d ''yyyy-mm-dd''}, for example {d ''2001-12-31''} timestamps: {ts ''yyyy-mm-dd hh:mm:ss''}, for example {ts ''2001-12-31 00:00:00''} times: {t ''hh:mm:ss''}, etc. See also http://www.bigbold.com/snippets/posts/show/756 Regards, Erik.
Erik van Oosten wrote:> SQL Server has some weird problems with dates and internationalization. > Some drivers (e.g. the Spanish one) even has bugs that will not be > solved. Anyway, you might have more success with the following date > formats: > > dates: {d ''yyyy-mm-dd''}, for example {d ''2001-12-31''} > timestamps: {ts ''yyyy-mm-dd hh:mm:ss''}, for example {ts ''2001-12-31 > 00:00:00''} > times: {t ''hh:mm:ss''}, etc. > > See also http://www.bigbold.com/snippets/posts/show/756 > > Regards, > Erik.As you can see in my very first post -- especially the error message -- you''ll see that sql server does have problems with the format yyyy-mm-dd. However, i tried to reformat the date before it gets stored in the db. for that i used the before_save/before_create callback methods. def before_save // first try @created_at = @created_at.strftime("%d-%m-%y") // second try self[:created_at] = self[:created_at].strftime("%d-%m-%y") // third try write_attribute(:created_at) = read_attribute(:created_at).strftime("%d-%m-%y") end another attempt to solve this problem was to redefine the getter method def created_at // see before_save end both attempts failed as i saw no changes in the date format. some of them even resulted in another error: "you tried to ... nil.strftime" -- Posted via http://www.ruby-forum.com/.
No solution in sight?! Please help me, since i''m doomed to use ColdFusion right now :) -- Posted via http://www.ruby-forum.com/.
Hi you might want to have a look over there : http://www.karaszi.com/SQLServer/info_datetime.asp#DtFormatsInput Just like the authour, I tend to use (although not with rails specifically as I don''t use sql server with it) the unseparated dateformat (''20051127 14:23:05'') which is language neutral. I have a trading station written in .Net which is deployed also in Spain and rely on that format, so I guess it should do the job there to. hope this helps Thibaut -- [blog] http://www.dotnetguru2.org/tbarrere -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060124/e25d7494/attachment.html
Thibaut Barr?re wrote:> Hi > > you might want to have a look over there : > http://www.karaszi.com/SQLServer/info_datetime.asp#DtFormatsInput > > Just like the authour, I tend to use (although not with rails > specifically > as I don''t use sql server with it) the unseparated dateformat (''20051127 > 14:23:05'') which is language neutral. > > I have a trading station written in .Net which is deployed also in Spain > and > rely on that format, so I guess it should do the job there to. > > hope this helps > > ThibautThanks for the pointer. However, i wouldn''t be in the situation if i''d be able to format the date to whatever i want. in my last post i described my approach to this problem. Every approach failed, as i see no change in the datetime format which is used to actually save the data. Every hook (callback) i tried didn''t do the job. I''m unable to say if the fault''s on my end. However, i hope to find a way. -- Posted via http://www.ruby-forum.com/.
> > Thanks for the pointer. However, i wouldn''t be in the situation if i''d > be able to format the date to whatever i want. in my last post i > described my approach to this problem.Sounds right :-) Every approach failed, as i see no change in the datetime format which> is used to actually save the data. Every hook (callback) i tried didn''t > do the job. I''m unable to say if the fault''s on my end.Did you try putting a breakpoint into before_save to inspect what''s available there ? Or another hint : I just browsed the doc and it seems that you miss a parameter to before_save (but I never actually used it, so I may be wrong!) http://api.rubyonrails.com/classes/ActiveRecord/Callbacks.html#M000652 def before_save(record) record.credit_card_number = encrypt(record.credit_card_number) end Thibaut -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060124/84b7278a/attachment.html
Thibaut Barr?re wrote:> Did you try putting a breakpoint into before_save to inspect what''s > available there ?i did. the result was always nil.> Or another hint : I just browsed the doc and it seems that you miss a > parameter to before_save (but I never actually used it, so I may be > wrong!) > > http://api.rubyonrails.com/classes/ActiveRecord/Callbacks.html#M000652 > > def before_save(record) > record.credit_card_number = encrypt(record.credit_card_number) > endhmmm!! i''ll try that, thanks. -- Posted via http://www.ruby-forum.com/.
there''s no parameter to the before_save callback, so it failed :( i set a breakpoint in the before_save function and "puts" out the attributes in the object. *all* of them were nil, even the self object! what''s wrong?! -- Posted via http://www.ruby-forum.com/.
Did you try the various type of callback registrations as seen in the doc ? (I never tried myself, but clearly I would start here - heck, I *will* start here in a couple of days :-) On 24/01/06, NoobSaibot <wuseldusel@yahoo.de> wrote:> > there''s no parameter to the before_save callback, so it failed :( > > i set a breakpoint in the before_save function and "puts" out the > attributes in the object. *all* of them were nil, even the self object! > > what''s wrong?! >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060124/cf8e5f5b/attachment.html
Thibaut Barr?re wrote:> Did you try the various type of callback registrations as seen in the > doc ?the callbacks i''ve already mentioned in my previous posts and the one with a block: class MyClass < BaseClass ... before_save { |record| record.created_at = record.created_at.strftime("dd.mm.yyyy") } end> (I never tried myself, but clearly I would start here - heck, I *will* > start > here in a couple of days :-)i wish you good luck :) -- Posted via http://www.ruby-forum.com/.
btw. the breakpointer seems to be broken, as i get nil as output regardless what i do. puts self[:created_at] => nil puts self => nil puts "not nil" => nil am i doing something wrong? i set a breakpoint() in my controller. after this point is reached by the server/interpreter the web application is on hold. that''s where i do the following (in the app directory of course): ruby script/breakpointer -- Posted via http://www.ruby-forum.com/.
NoobSaibot wrote:> btw. the breakpointer seems to be broken, as i get nil as output > regardless what i do. > > puts self[:created_at] => nil > puts self => nil > puts "not nil" => nilWhat do you get if you type ''local_variables'' and ''instance_variables''? (no puts)... -- Alex
NoobSaibot
2006-Jan-24 13:02 UTC
[Rails] Re: Re: Re: Re: Re: Re: SQL Server datetime error
Alex Young wrote:> NoobSaibot wrote: >> btw. the breakpointer seems to be broken, as i get nil as output >> regardless what i do. >> >> puts self[:created_at] => nil >> puts self => nil >> puts "not nil" => nil > What do you get if you type ''local_variables'' and ''instance_variables''? > (no puts)...@project is in the list of instance_variables -- Posted via http://www.ruby-forum.com/.
Alex Young
2006-Jan-24 13:06 UTC
[Rails] Re: Re: Re: Re: Re: Re: SQL Server datetime error
NoobSaibot wrote:> Alex Young wrote: >> NoobSaibot wrote: >>> btw. the breakpointer seems to be broken, as i get nil as output >>> regardless what i do. >>> >>> puts self[:created_at] => nil >>> puts self => nil >>> puts "not nil" => nil >> What do you get if you type ''local_variables'' and ''instance_variables''? >> (no puts)... > > @project is in the list of instance_variablesWhat if you type ''@project''? Sorry if this is going over old ground, but if I remember correctly (and I''ll be glad to be proved wrong here), the puts method dumps the data out to the original console the server''s running on, *not* the breakpointer. The return value of puts is nil. -- Alex
NoobSaibot
2006-Jan-24 13:17 UTC
[Rails] Re: Re: Re: Re: Re: Re: Re: SQL Server datetime error
Alex Young wrote:> NoobSaibot wrote: >> >> @project is in the list of instance_variables > > What if you type ''@project''? Sorry if this is going over old ground, > but if I remember correctly (and I''ll be glad to be proved wrong here), > the puts method dumps the data out to the original console the server''s > running on, *not* the breakpointer. The return value of puts is nil.you''re absolutely write. what i had to do is ....> [@project] which would print out the object (and its attributes) Allright, thanks Alex. Still i''m unable to format the dates. -- Posted via http://www.ruby-forum.com/.
NoobSaibot
2006-Jan-25 12:56 UTC
[Rails] Re: Re: Re: Re: Re: Re: Re: SQL Server datetime error
i wonder if i''m the only one with this kind of problems? -- Posted via http://www.ruby-forum.com/.
Tom Ward
2006-Jan-25 13:46 UTC
[Rails] Re: Re: Re: Re: Re: Re: Re: SQL Server datetime error
Noob, Could you run the following on a fresh schema (using SQL Query Analyser or similar), and tell me which insert statements work and which do not? If they all work, could you provide me with the schema that''s causing you problems. Tom CREATE TABLE [date_test] ( id int NOT NULL IDENTITY(1, 1) PRIMARY KEY, [date] datetime default NULL, ); INSERT INTO date_test ([date]) VALUES (''2006-05-04 03:21:00'') INSERT INTO date_test ([date]) VALUES (''20060504 03:21:00'') INSERT INTO date_test ([date]) VALUES (''2006-05-04T03:21:00'') INSERT INTO date_test ([date]) VALUES (convert(datetime,''2006-05-04 03:21:00'')) INSERT INTO date_test ([date]) VALUES (convert(datetime,''20060504 03:21:00'')) INSERT INTO date_test ([date]) VALUES (convert(datetime,''2006-05-04T03:21:00''))
NoobSaibot
2006-Jan-25 14:32 UTC
[Rails] Re: Re: Re: Re: Re: Re: Re: Re: SQL Server datetime error
Here''s the schema -- as all of your stuff worked: CREATE TABLE [dbo].[projects] ( [id] uniqueidentifier ROWGUIDCOL NOT NULL , [created_at] [datetime] NULL , [title] [varchar] (500) COLLATE Latin1_General_CI_AS NOT NULL , [description] [text] COLLATE Latin1_General_CI_AS NULL , [starts_at] [datetime] NULL , [ends_at] [datetime] NULL , [updated_at] [datetime] NULL ) -- Posted via http://www.ruby-forum.com/.
NoobSaibot wrote:> Here''s the schema -- as all of your stuff worked: > > CREATE TABLE [dbo].[projects] ( > [id] uniqueidentifier ROWGUIDCOL NOT NULL , > [created_at] [datetime] NULL , > [title] [varchar] (500) COLLATE Latin1_General_CI_AS NOT NULL , > [description] [text] COLLATE Latin1_General_CI_AS NULL , > [starts_at] [datetime] NULL , > [ends_at] [datetime] NULL , > [updated_at] [datetime] NULL > )The funny thing is that the same sql insert statement -- which is claimed to be wrong by ror -- does work if i put it in the query analyzer. maybe it''s the encoding or something? -- Posted via http://www.ruby-forum.com/.
> The funny thing is that the same sql insert statement -- which is > claimed to be wrong by ror -- does work if i put it in the query > analyzer. > > maybe it''s the encoding or something?I guess that''s a possibility. There''s nothing in the schema that seems strange, the failing SQL works fine on both our systems through query analyser. Can I ask how you connect to SQL Server (through ODBC or ADO) and which version of SQL Server you connect to. Tom
Tom Ward wrote:> Can I ask how you connect to SQL Server (through ODBC or ADO)i''m using ADO to connect to db> which version of SQL Server you connect to.2000 sp3 And here''s the encoding i''m using for my html files (if that matters) <meta http-equiv="Content-type" content="text/html; charset=utf-8" /> -- Posted via http://www.ruby-forum.com/.
On 1/25/06, NoobSaibot <wuseldusel@yahoo.de> wrote:> Tom Ward wrote: > > Can I ask how you connect to SQL Server (through ODBC or ADO) > > i''m using ADO to connect to dbCan you try connecting through ODBC instead. Either using a dsn configured via control panel, or with a connection string. Examples below (obviously change values to match your configuration): development: adapter: sqlserver mode: ODBC dsn: Driver={SQL Server};Server=(local);Database=rails;Uid=sa;Pwd username: sa password: development: adapter: sqlserver mode: ODBC dsn: my_datasource_name username: sa password:
unfortunately i get the same error message -- Posted via http://www.ruby-forum.com/.
On 1/25/06, NoobSaibot <wuseldusel@yahoo.de> wrote:> unfortunately i get the same error messageRunning out of ideas here, unfortunately. When you added a breakpoint and inspected the project object, what output did you get? Tom -- email : tom at popdog.net
Tom Ward wrote:> On 1/25/06, NoobSaibot <wuseldusel@yahoo.de> wrote: >> unfortunately i get the same error message > > Running out of ideas here, unfortunately. When you added a breakpoint > and inspected the project object, what output did you get? > > TomHere''s the output for [@project] => [#<Project:0x37f48d8 @new_record=true, @attributes={ "updated_at"=>nil, "title"=>"Reisekosten", "starts_at"=>Wed Jan 25 17:25:00 Westeurop?ische Normalzeit 2006, "description"=>"Reisekosten", "ends_at"=>Wed Jan 25 17:25:00 Westeurop?ische Normalzeit 2006, "created_at"=>nil }> ] -- Posted via http://www.ruby-forum.com/.
On 1/25/06, NoobSaibot <wuseldusel@yahoo.de> wrote:> > Tom Ward wrote: > > On 1/25/06, NoobSaibot <wuseldusel@yahoo.de> wrote: > >> unfortunately i get the same error message > > > > Running out of ideas here, unfortunately. When you added a breakpoint > > and inspected the project object, what output did you get? > > > > Tom > > Here''s the output for [@project] > > => [#<Project:0x37f48d8 > @new_record=true, > @attributes={ > "updated_at"=>nil, > "title"=>"Reisekosten", > "starts_at"=>Wed Jan 25 17:25:00 Westeurop?ische Normalzeit 2006, > "description"=>"Reisekosten", > "ends_at"=>Wed Jan 25 17:25:00 Westeurop?ische Normalzeit 2006, > "created_at"=>nil > }> > ] > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >Funny, I thought we just covered this? There is a bug in teh German-Language pack of SQL Server that boggles up datetime conversion to/from string. I googled it but couldn''t find my reference back. -- ------------------------------ Forget the icing. Bake the Cake! - the epi-centered developer ------------------------------ Peter Fitzgibbons -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060125/bdc8de90/attachment.html
> Funny, I thought we just covered this? > There is a bug in teh German-Language pack of SQL Server that boggles up > datetime conversion to/from string. > > I googled it but couldn''t find my reference back.i''m sorry if i missed that one. i did *lots* of research before i decided to bore you with my problem :) so it''s a known bug! was there a workaround, or do i have to wait for a patch or something? -- Posted via http://www.ruby-forum.com/.
I wrote a not so little walkthrough on setting up Rails on the LiteSpeed Webserver. I discovered LiteSpeed when they posted their SAPI interface on PHP Internals for inclusion in core a few weeks back. Although it''s a commercial offering, their standard version is free and works great with Rails and for you guys that still have legacy PHP code, well, you''ll never go back to Apache. They have their own set of benchmarks, but who ever trusts those. I did my own testing (as should you if you are interested in speed) and this combo worked better for me than the others I tried. The walkthrough can be found here: http://www.railtie.net/articles/2006/01/21/up-and-running-in-the-speed-of-li ght BTW, I''m a programmer, not an author, so don''t expect this tutorial to be on the NYT Best-Sellers list. Bob Silva http://www.railtie.net/> -----Original Message----- > From: rails-bounces@lists.rubyonrails.org [mailto:rails- > bounces@lists.rubyonrails.org] On Behalf Of NoobSaibot > Sent: Thursday, January 26, 2006 12:05 AM > To: rails@lists.rubyonrails.org > Subject: [Rails] Re: Re: Re: Re: Re: SQL Server datetime error > > > Funny, I thought we just covered this? > > There is a bug in teh German-Language pack of SQL Server that boggles up > > datetime conversion to/from string. > > > > I googled it but couldn''t find my reference back. > > i''m sorry if i missed that one. i did *lots* of research before i > decided to bore you with my problem :) > > so it''s a known bug! was there a workaround, or do i have to wait for a > patch or something? > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails
If anyone has any more information about this bug, it''d be great to hear about it. There may be a workaround we can include in the sqlserver adapter. Tom On 1/26/06, NoobSaibot <wuseldusel@yahoo.de> wrote:> > Funny, I thought we just covered this? > > There is a bug in teh German-Language pack of SQL Server that boggles up > > datetime conversion to/from string. > > > > I googled it but couldn''t find my reference back. > > i''m sorry if i missed that one. i did *lots* of research before i > decided to bore you with my problem :) > > so it''s a known bug! was there a workaround, or do i have to wait for a > patch or something? > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- email : tom at popdog.net
NoobSaibot
2006-Jan-26 12:28 UTC
[Rails] Re: Re: Re: Re: Re: Re: SQL Server datetime error
Tom Ward wrote:> If anyone has any more information about this bug, it''d be great to > hear about it. There may be a workaround we can include in the > sqlserver adapter. > > TomThe same goes for the ODBC adapter, methinks. -- Posted via http://www.ruby-forum.com/.
As I wrote earlier, when talking to SQL Server, it is safest to use the SQL Server specific date/time formats. dates: {d ''yyyy-mm-dd''}, for example {d ''2001-12-31''} timestamps: {ts ''yyyy-mm-dd hh:mm:ss''}, for example {ts ''2001-12-31 00:00:00''} times: {t ''hh:mm:ss''}, etc. I copied these from http://www.bigbold.com/snippets/posts/show/756 BTW, the bug I mentioned in my earlier post was in the ODBC driver for Sql Server 5 when 7 was about to be released, so that was quite some time ago. Regards, Erik. Tom Ward schreef:>If anyone has any more information about this bug, it''d be great to >hear about it. There may be a workaround we can include in the >sqlserver adapter. > >Tom > >
Well, i hope there''ll be a patch to correct this bug, so i can demonstrate the power of rails :) -- Posted via http://www.ruby-forum.com/.
Noob, One more attempt to try and fix this issue: Could you replace the quote method in sqlserver_adapter.rb with this and see if it helps at all: def quote(value, column = nil) case value when String if column && column.type == :binary "''#{quote_string(column.class.string_to_binary(value))}''" else "''#{quote_string(value)}''" end when NilClass then "NULL" when TrueClass then ''1'' when FalseClass then ''0'' when Float, Fixnum, Bignum then value.to_s when Date, Time, DateTime then "convert(datetime, ''#{value.strftime("%Y-%m-%d %H:%M:%S")}'', 121)" else "''#{quote_string(value.to_yaml)}''" end end
Tom Ward wrote:> Noob, > > One more attempt to try and fix this issue: > > Could you replace the quote method in sqlserver_adapter.rb with this > and see if it helps at all: > > ...Nope, what i get now is an infinite load of the page. -- Posted via http://www.ruby-forum.com/.
On 1/30/06, NoobSaibot <wuseldusel@yahoo.de> wrote:> Tom Ward wrote: > > Noob, > > > > One more attempt to try and fix this issue: > > > > Could you replace the quote method in sqlserver_adapter.rb with this > > and see if it helps at all: > > > > ... > > Nope, what i get now is an infinite load of the page.Curiouser and curiouser. I''ll try changing locale on my own sql server and recreating the issue, but i wont have time to do this for a couple of days. Sorry I can''t help more. Tom
Tom Ward wrote:> On 1/30/06, NoobSaibot <wuseldusel@yahoo.de> wrote: >> Nope, what i get now is an infinite load of the page. > Curiouser and curiouser. I''ll try changing locale on my own sql > server and recreating the issue, but i wont have time to do this for a > couple of days. Sorry I can''t help more. > > TomYour help is very much appreciated. And thanks a lot for not giving me up :) -- Posted via http://www.ruby-forum.com/.
bumping this thread in hope to get some new infos -- Posted via http://www.ruby-forum.com/.
No news yet? Should i file a bug report or something? -- Posted via http://www.ruby-forum.com/.
NoobSaibot wrote:> bumping this thread in hope to get some new infosIs there any news ? -- Posted via http://www.ruby-forum.com/.
dodik wrote:> NoobSaibot wrote: >> bumping this thread in hope to get some new infos > > Is there any news ?no, as it seems. i can do nothing until this bug is fixed :( -- Posted via http://www.ruby-forum.com/.
i''ve filed a bug report now. http://dev.rubyonrails.org/ticket/4006 -- Posted via http://www.ruby-forum.com/.
NoobSaibot wrote:> i''ve filed a bug report now. http://dev.rubyonrails.org/ticket/4006i think i got a workaround. i changed the language role for the db user from "german" to "english". now it seems to work. got to test it more thouroughly thou. -- Posted via http://www.ruby-forum.com/.