Robert Simpson wrote:
-----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
You have just given an excellent explanation of why the wrapper approach
is flawed. Think about it.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
