[Mono-list] Mono.Data.SqliteClient always report text as DataTypeName

[Aaron Bockover]

Do you have a test case of this I can look at? If this is a bug, it
should be filed.

--Aaron

On Wed, 2005-12-14 at 10:11 -0600, Duncan McQueen wrote:
> You are correct.  However, a DataTable returned with a valid DateTime
> column in it as a result of using the SQLiteDataAdaptor had DateTIme
> fields that were being characterized incorrectly.  The runtime didn't
> think that the fields themselves could be converted into DateTime
> values (unlike the Finisar SQLite implementation), and subsequently
> would fail when passed into NPlot as DateTime values.
> 
> 
> 
> On 12/14/05, Aaron Bockover <abockover at novell.com> wrote:
> > Dates in Sqlite are stored as strings, in the format "YYYY-MM-DD
> > HH:MM:SS". If you read them in and use some variation of
> > System.DateTime.Parse() to convert the string to a System.DateTime, the
> > conversion may be incorrect if the current locale does not support this
> > format. If you must parse it, be sure to manually specify the US to the
> > Parse method.
> >
> > However... System.DateTime.Parse is extremely inefficient, and I
> > recommend avoiding it altogether. Instead, store a unix expoch timestamp
> > in the database, and use
> > Mono.Unix.Native.NativeConvert.ToDateTime/FromDateTime to do the
> > conversions to/from.
> >
> > If you must store it as a formatted date string in the database, you can
> > also use this SQL to do the conversion to epoch time:
> >
> > SELECT strftime("%s", "2005-12-14 10:32:55");
> >
> > This will return the time in UTC. If you need it for the current
> > timezone:
> >
> > SELECT strftime("%s", "2005-12-14 10:32:55", "localtime");
> >
> > I've never tested this method in practice, but I would imagine it's much
> > faster than using System.DateTime.Parse() on the string variant.
> >
> > To give you a rough idea of how slow System.DateTime.Parse is:
> >
> > In Banshee, the Tracks table had two "DATETIME" columns. There were 4000
> > rows in the table. Loading all 4000 rows meant System.DateTime.Parse()
> > was called 8000 times. This load operation took about 20 seconds. When I
> > changed the value being stored from a formatted date/time string to an
> > epoch int, using ToDateTime to do the conversion took only about 1.5
> > seconds to load all 4000 rows.
> >
> > --Aaron
> >
> >
> > On Wed, 2005-12-14 at 08:05 -0600, Duncan McQueen wrote:
> > > Will this error be related to DateTime values not being reported correctly?
> > >
> > > On 12/14/05, Julien Sobrier <julien at sobrier.net> wrote:
> > > > Aaron Bockover wrote:
> > > > > Internally in sqlite, everything is stored as a string, regardless of
> > > > > what type a column was assigned during table creation.
> > > > >
> > > > > The only way to know the "type" is to parse the table definition from
> > > > > the sqlite_master table. This should probably be done for convenience
> > > > > inside Mono.Data.SqliteClient.
> > > > >
> > > > > For instance, run this query:
> > > > >
> > > > > SELECT sql FROM sqlite_master WHERE name="Tracks";
> > > > >
> > > > > That will return the SQL used to create the "Tracks" table. You can then
> > > > > parse that result to build a map of column names->types.
> > > > >
> > > > > There is some example code for this in Banshee:
> > > > > http://cvs.gnome.org/viewcvs/*checkout*/banshee/src/Database.cs
> > > > >
> > > > > --Aaron
> > > >
> > > > Thanks a lot, I'll look at it.
> > > >
> > > > _______________________________________________
> > > > Mono-list maillist  -  Mono-list at lists.ximian.com
> > > > http://lists.ximian.com/mailman/listinfo/mono-list
> > > >
> > > _______________________________________________
> > > Mono-list maillist  -  Mono-list at lists.ximian.com
> > > http://lists.ximian.com/mailman/listinfo/mono-list
> >
> >