> -----Original Message----- > From: John Stanton [mailto:[EMAIL PROTECTED] > Sent: Monday, May 28, 2007 4:21 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Re: CAST > > We actually do that with our Sqlite interfaces. We use the declared > type to specify the type and perform a conversion when necessary. For > example if the declared type of a column is DATE we know that we > actually have a FLOAT so when we call a Javascript SQL function for > example the Sqlite user function transforms the floating point number > declared as a DATE to be a Javascript Date object. > > A FLOAT with declared type DATE may be transformed into a date string > in > accordance with the rules of the chosen locale when being embedded in > an > HTML page. > > Our applications use DECIMAL arithmetic for accuracy so when a column > is > declared as DECIMAL its actual type is TEXT but arithmetic rules are > applied. If Sqlite has decided to make it an INTEGER or a FLOAT the > correct conversion is made with the declared precision and scale.
I use the declared type as well (where possible) in the SQLite .NET provider. Still, it'd be better if we could have an extensible type system built into the engine itself so one could perform literal to column comparisons and other SQL statements that are beyond the scope of a wrapper to provide type adjustments for. Your date example is a perfect example of a place where an extensible type system would be ideal. A SQL statement against a DATE field such as ... SELECT * FROM FOO WHERE [TheDate] < '2007/01/01' ...is pretty much impossible to fix in a wrapper -- but if we had an extensible type system we could provide our own comparison func that takes [TheDate]'s value and the literal value and figures out how to interpret and compare the two values. Robert ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
