[Mono-dev] Sybase ASA Odbc, timestamp, fractional seconds, possible bug in mono Odbc stack?

Sun May 27 16:47:29 EDT 2007

fredag 25 maj 2007 11:30 skrev A Nagappan:
> Hi,
> 
> Fixed the issue in SVN trunk - revision 77937.

Thanks a lot.

I have two quibbs with the code though:

if (outsize!=-1) {// This means SQL_NULL_DATA
  /* Sybase returns the fraction values in nano seconds,
     but DateTime expects the value in milliseconds,
     so the conversion. */
  int fraction = Convert.ToInt32 (ts_data.fraction > 999 ? ts_data.fraction / 
1000000 : ts_data.fraction);
  DataValue = new 
DateTime(ts_data.year,ts_data.month,ts_data.day,ts_data.hour,
			   ts_data.minute,ts_data.second, fraction);
}

Now, if a database server with a precision _higher_ than ms, say ns, has two 
datetimes, where the fractional value is 375 ns and 1050 ns, 1050 is later in 
time than 375. However, when converted to TDateTime values in the Odbc 
datareader, they will become 375 ms and 0 ms, effectively swapping order. 
Apart from the wrongness - 375 ns should have become 0 ms, not 375 ms - the 
ordering is also quite inconvenient...

As you can read here, both db2 and oracle goes higher than ms sql (and sybase, 
it seems): http://www.dbazine.com/db2/db2-disarticles/pelzer2

The other quibb is about updating the database. Clearly, when I hold a 
timestamp in the Mono C# stack, and wish to update a row with it, the 
conversion the other way is important too. I can't figure out where this 
happens? Otherwise, i would reckon all timestamp updates against a odbc 
datasource that uses (which I believe it should) nanoseconds as the 
fractional, will get values between 0 and 999 nanoseconds, effectively 
destroying the precision.

I am still quite confused about this. My knowledge of ODBC is quite limited. 
However, from various sources, e.g. 
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcc_data_types.asp
I do get the impression that fractional values _should_ be in nanoseconds.

In other words: shouldn't we _always_ divide with 1000000? And, shouldn't we 
somewhere else always multiply with 1000000?

I do not have access to other ODBC sources at the moment, unfortunately, but 
the code tends to suggest that there must exists at least one odbc source 
that uses ms for its fractional value. I wonder if _that_ is not the error.

Regards,

Mads

> 
> Thanks
> Nagappan
> 
> Nagappan A <anagappan at novell.com>
> Linux Desktop Testing Project - http://ldtp.freedesktop.org
> http://nagappanal.blogspot.com
> 
> Novell, Inc.
> SUSE® Linux Enterprise 10
> Your Linux is ready™
> http://www.novell.com/linux
> 
> 
> >>> Mads Bondo Dydensborg <mbd at dbc.dk> 05/16/07 2:56 PM >>>
> onsdag 16 maj 2007 11:08 skrev A Nagappan:
> > Hi,
> >   From the stack trace it looks like the issue is in System.DateTime,
> 
> How did you determine this? My impression was that the ODBC stack hands
> over a 
> frational second value in nanoseconds, and DateTime expects the
> fractional 
> value to be in milliseconds?
> 
> If that is the case, I can hardly blame DateTime for expecting a
> millisecond 
> value, can I?
> 
> Please advise.
> 
> Regards,
> 
> Mads
> 
> 
> > could you please file a bug on that ? With a generic test-case to
> > reproduce this bug.
> > 
> >
> http://bugzilla.ximian.com/enter_bug.cgi?product=Mono%3A%20Class%20Libraries
> > and Component as System.
> > 
> > Thanks
> > Nagappan
> > 
> > 
> > Nagappan A <anagappan at novell.com>
> > Linux Desktop Testing Project - http://ldtp.freedesktop.org
> > http://nagappanal.blogspot.com
> > 
> > Novell, Inc.
> > SUSE* Linux Enterprise 10
> > Your Linux is ready*
> > http://www.novell.com/linux
> > 
> > 
> > 
> > >>> On Fri, May 4, 2007 at  6:15 PM, in message
> > <200705041445.27847.mbd at dbc.dk>,
> > Mads Bondo Dydensborg <mbd at dbc.dk> wrote: 
> > > fredag 04 maj 2007 10:56 skrev A Nagappan:
> > >> Hi,
> > >> 
> > >> Could you please send me a test.cs file to reproduce this bug ?
> > > 
> > > Please find it attached.
> > > 
> > > Running "select PublishedTime from PublishingJob" from isql returns:
> > > 2007- 05- 03 10:57:15.000
> > > 2007- 05- 03 10:57:15.920
> > > 2007- 05- 03 10:57:15.000
> > > 2007- 05- 03 10:57:15.000
> > > 
> > > And, the output from the test.cs program is:
> > > 
> > > $ mono -- debug test.exe
> > > Creating connection
> > > Opening connection
> > > Executing command
> > > Reading data
> > > Column 0: 5/3/2007 10:57:15 AM
> > > 
> > > Unhandled Exception: System.ArgumentOutOfRangeException: Argument is
> > out of 
> > > range.
> > > Parameter name: Parameters describe an unrepresentable DateTime.
> > >   at System.DateTime..ctor (Int32 year, Int32 month, Int32 day,
> Int32
> > hour, 
> > > Int32 minute, Int32 second, Int32 millisecond) [0x00000]
> > >   at System.Data.Odbc.OdbcDataReader.GetValue (Int32 ordinal)
> > [0x00415] 
> > > in 
> > >
> >
> /home/compile/Compile/Mono/mcs/class/System.Data/System.Data.Odbc/OdbcDataRea
> > > der.cs:730
> > >   at System.Data.Odbc.OdbcDataReader.get_Item (Int32 index)
> [0x00000]
> > 
> > > in 
> > >
> >
> /home/compile/Compile/Mono/mcs/class/System.Data/System.Data.Odbc/OdbcDataRea
> > > der.cs:153
> > >   at Test.OdbcTest.OdbcTest.Main (System.String[] args) [0x00057] 
> > > in /home/madsdyd/Tests/OdbcDateTime/test.cs:26
> > > 
> > > So, the first row is correctly made into a DateTime, the next is
> not.
> > 
> > > Stripping the fractional of all dates in the database works, but is
> > not 
> > > really what I want :- )
> > > 
> > > Regards & thanks in advance for your help.
> > > 
> > > Mads
> > 
> > 
> > 
> > 
> 
> -- 
> Med venlig hilsen/Regards
> 
> Systemudvikler/Systemsdeveloper cand.scient.dat, Ph.d., Mads Bondo
> Dydensborg
> Dansk BiblioteksCenter A/S, Tempovej 7-11, 2750 Ballerup, Tlf. +45 44 86
> 77 34
> 
> 
> 
> 

-- 
Med venlig hilsen/Regards

Systemudvikler/Systemsdeveloper cand.scient.dat, Ph.d., Mads Bondo Dydensborg
Dansk BiblioteksCenter A/S, Tempovej 7-11, 2750 Ballerup, Tlf. +45 44 86 77 34