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

[Duncan McQueen]

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
>
>