John Stanton wrote:
As you postulated, Sqlite's approach is indeed more rational.
Most SQL implementations use fixed-size records so it makes more sense for them to enforce each column's data type exactly - inserting a string into a column which can only hold a single char isn't very useful. SQLite's flexibility makes life much easier. I now see it as A Good Thing. Switching to a SQL implementation without it must be quite traumatic.
As for what you are doing, the way I did a similar thing for compatibility was to make a function which looks at the declared type and the actual type and makes the appropriate conversion if necessary to match the destination requirements.
Seeing as I'm designing a wrapper it makes no difference - I simply moved the type info from the column list into the results. If I ever support (eg.)MySQL I figure it won't be much of a problem to emulate this behavior.