Forum: Ruby on Rails SQL Server datetime error

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
NoobSaibot (Guest)
on 2006-01-20 17:02
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')
NoobSaibot (Guest)
on 2006-01-20 17:12
i'm using Rails 1.0, Ruby 1.8.2 and SQL Server.
Jamie O. (Guest)
on 2006-01-20 18:17
(Received via mailing list)
I had a similar problem when using DateTime instead of Time objects.
Could this solve your problem?
NoobSaibot (Guest)
on 2006-01-20 18:29
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.
NoobSaibot (Guest)
on 2006-01-20 19:20
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.
Jamie O. (Guest)
on 2006-01-20 19:29
(Received via mailing list)
If you can pinpoint the problem, be sure to file a bug report on the
Rails website.

Jamie
Erik van Oosten (Guest)
on 2006-01-20 21:18
(Received via mailing list)
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.
NoobSaibot (Guest)
on 2006-01-21 00:48
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"
NoobSaibot (Guest)
on 2006-01-24 11:13
No solution in sight?! Please help me, since i'm doomed to use
ColdFusion right now :)
Thibaut Barrère (Guest)
on 2006-01-24 12:06
(Received via mailing list)
Hi

you might want to have a look over there :
http://www.karaszi.com/SQLServer/info_datetime.asp...

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
NoobSaibot (Guest)
on 2006-01-24 12:20
Thibaut Barrère wrote:
> Hi
>
> you might want to have a look over there :
> http://www.karaszi.com/SQLServer/info_datetime.asp...
>
> 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

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.

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.
Thibaut Barrère (Guest)
on 2006-01-24 12:36
(Received via mailing list)
>
> 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/Ca...

def before_save(record)
      record.credit_card_number = encrypt(record.credit_card_number)
end


Thibaut
NoobSaibot (Guest)
on 2006-01-24 12:43
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/Ca...
>
> def before_save(record)
>       record.credit_card_number = encrypt(record.credit_card_number)
> end

hmmm!! i'll try that, thanks.
NoobSaibot (Guest)
on 2006-01-24 12:57
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?!
Thibaut Barrère (Guest)
on 2006-01-24 14:13
(Received via mailing list)
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 :-)
NoobSaibot (Guest)
on 2006-01-24 14:26
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 :)
NoobSaibot (Guest)
on 2006-01-24 14:39
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
Alex Y. (Guest)
on 2006-01-24 14:53
(Received via mailing list)
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)...
NoobSaibot (Guest)
on 2006-01-24 15:01
Alex Y. 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
Alex Y. (Guest)
on 2006-01-24 15:08
(Received via mailing list)
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.
NoobSaibot (Guest)
on 2006-01-24 15:17
Alex Y. 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.
NoobSaibot (Guest)
on 2006-01-25 14:56
i wonder if i'm the only one with this kind of problems?
Tom W. (Guest)
on 2006-01-25 15:49
(Received via mailing list)
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 (Guest)
on 2006-01-25 16:32
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
)
NoobSaibot (Guest)
on 2006-01-25 16:43
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?
Tom W. (Guest)
on 2006-01-25 17:05
(Received via mailing list)
> 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
NoobSaibot (Guest)
on 2006-01-25 17:16
Tom W. 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" />
Tom W. (Guest)
on 2006-01-25 17:41
(Received via mailing list)
On 1/25/06, NoobSaibot <removed_email_address@domain.invalid> wrote:
> Tom W. wrote:
> > Can I ask how you connect to SQL Server (through ODBC or ADO)
>
> i'm using ADO to connect to db

Can 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:
NoobSaibot (Guest)
on 2006-01-25 17:56
unfortunately i get the same error message
Tom W. (Guest)
on 2006-01-25 18:15
(Received via mailing list)
On 1/25/06, NoobSaibot <removed_email_address@domain.invalid> 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
NoobSaibot (Guest)
on 2006-01-25 18:29
Tom W. wrote:
> On 1/25/06, NoobSaibot <removed_email_address@domain.invalid> 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
  }>
]
Peter F. (Guest)
on 2006-01-25 21:14
(Received via mailing list)
On 1/25/06, NoobSaibot <removed_email_address@domain.invalid> wrote:
> Here's the output for [@project]
>   }>
> ]
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> removed_email_address@domain.invalid
> 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.




--
NoobSaibot (Guest)
on 2006-01-26 10:05
> 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?
Bob S. (Guest)
on 2006-01-26 11:21
(Received via mailing list)
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-...
ght


BTW, I'm a programmer, not an author, so don't expect this tutorial to
be on
the NYT Best-Sellers list.


Bob S.
http://www.railtie.net/
Tom W. (Guest)
on 2006-01-26 13:00
(Received via mailing list)
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
NoobSaibot (Guest)
on 2006-01-26 14:28
Tom W. 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.
>
> Tom

The same goes for the ODBC adapter, methinks.
Erik van Oosten (Guest)
on 2006-01-26 16:08
(Received via mailing list)
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 W. schreef:
NoobSaibot (Guest)
on 2006-01-30 11:56
Well, i hope there'll be a patch to correct this bug, so i can
demonstrate the power of rails :)
Tom W. (Guest)
on 2006-01-30 14:59
(Received via mailing list)
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
NoobSaibot (Guest)
on 2006-01-30 17:55
Tom W. 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.
Tom W. (Guest)
on 2006-01-30 18:46
(Received via mailing list)
On 1/30/06, NoobSaibot <removed_email_address@domain.invalid> 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.

Tom
NoobSaibot (Guest)
on 2006-01-30 22:21
Tom W. wrote:
> On 1/30/06, NoobSaibot <removed_email_address@domain.invalid> 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.
>
> Tom

Your help is very much appreciated. And thanks a lot for not giving me
up :)
NoobSaibot (Guest)
on 2006-02-14 11:35
bumping this thread in hope to get some new infos
NoobSaibot (Guest)
on 2006-02-16 11:57
No news yet? Should i file a bug report or something?
dodik (Guest)
on 2006-02-23 16:49
NoobSaibot wrote:
> bumping this thread in hope to get some new infos

Is there any news ?
NoobSaibot (Guest)
on 2006-02-27 13:32
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 :(
NoobSaibot (Guest)
on 2006-03-07 15:35
i've filed a bug report now. http://dev.rubyonrails.org/ticket/4006
NoobSaibot (Guest)
on 2006-03-12 12:39
NoobSaibot wrote:
> i've filed a bug report now. http://dev.rubyonrails.org/ticket/4006

i 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.
This topic is locked and can not be replied to.