On Saturday 14 August 2004 09:45 am, you wrote:
> I find I have to do that anyway for lots of other reasons - SQL is dreadful
> if you're trying to write platform-independent code. It's arguable whether
> DBI should be attempting to eliminate all differences between these
> databases, because ultimately it means you wouldn't be able to pass in a
> SQL command as a string of text.
Well in that case I don't think DBI is desirable. DBI is useful when only the
common subset of SQL is required. Then you don't have to worry about which
database is actually in the background. I mean, that's the main point, right?
> Here's one of many differences which plague me: to concatenate columns, for
> some databases you must write a||b; for others you must write concat(a,b).
> Some let you do both, but in most cases you must use the right one.
>
> So what do I do? I have a wrapper class with my own 'concat' method to
> synthesise the string I need:
>
> def sqconcat(a,b)
> "concat(#{a},#{b})"
> end
>
> subclass this for other databases where needed:
>
> def sqconcat(a,b)
> "((#{a})||(#{b}))"
> end
>
> and call this method when building a SQL command which needs to concatenate
> two columns. Yuk.
>
> And of course I need my own factory method, which looks at the first
> argument to 'connect' and builds the appropriate object depending on
> whether it matches /^dbi:sqlite:/i, /^dbi:mysql:/i etc.
Here too might be a case for better abstraction in DBI. The DBD spec need only
support the sqconcat method. --As long as enough backends support such
functionality I think it's a good idea.
> I am not convinced that all databases have the concept of "the unique ID of
> the last row inserted". Maybe mysql and sqlite do, but I've not come across
> this in Oracle. Since the primary key can be composed of multiple columns,
> it could be an array of values.
Hmm... well there's got to be some way to identify a newly inserted record,
that can be abstracted across the board. Even if it means returning a hash of
the fields that uniqely identify the record. Ironically, I think there is an
standard SQL statement for this, but its not well supported :(
--
T.
|
