> -----Original Message----- > From: John Stanton [mailto:[EMAIL PROTECTED] > Sent: Tuesday, May 29, 2007 6:18 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Re: CAST > > Your comments endorse the approach we took which was to avoid the > wrapper concept entirely with its inherent limitations We use Sqlite > as > an embedded database in an application server rather than trying to > integrate an API which wraps Sqlite. > > In the case of the date we implement date arithmetic and comparison > functions building upon the excellent date primitives in the Sqlite > source. However your case does not require any special functions as it > is handled simply by the regular Sqlite date functions. > > An example of the use of date arithmetic might be: > > SELECT * FROM mytab WHERE days_overdue(due_date) > 90; > > We implement the user functions either as native code or as Javascript. > The Javascript has the advantage that the text is stored in the > database so the functions can be state-driven.
Ok, I grant you that was a bad example -- I was in a hurry when I posted it. It also requires that datetimes are stored in the database as a string. If someone used a numeric type instead, you'd be forced into using a user-defined function for a comparison against a literal string. If all users were nice and tidy and used parameterized queries, the world would be a better place -- but try as you might to drill it into folks, they still throw their literals into a query and defeat your nice inline type adjusters. I've had to use a sturdy shoehorn and a lot of grease to squeeze SQLite into the ADO.NET vNext (Entity Framework) recently, so a lot of my type frustrations stem from that effort. MS's Entity Framework uses CAST() liberally in its SQL construction, and expects the returned values to match up to the type it was CAST() to -- which is currently impossible to do. I can't cast to a datetime, guid, int32, float, etc in SQLite and there's no way for me to tell what datatype was mentioned in the CAST statement for a given returned column. So when I'm queried for the type of a column returned from one of these SELECT's, there's no way for me to give back an accurate type. I've hacked it up and done it, but its a bit ugly. Fortunately users don't need to see the underlying SQL generated from the Entity Framework :) Robert ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
