[lug] OT: CPP question

[Rob Nagler]

Tkil writes:
> 2. outer join syntax & capabilities.  oracle is the only db i've
>    worked with that allows you to an outer join on selected columns
>    out of a given table; all the others, you have to outer join the
>    whole table.  (but you could join it twice, i suppose, once as a
>    normal join, and again as an outer join?)

In Oracle and Sybase you can specify the outer join on columns.  What
http://openacs.org/doc/openacs/html/oracle-to-pg-porting.html
recommends for Oracle to Postgres is:

    Depending on the exact operation performed by the outer join, there
    are different approaches. For outer joins where specific, raw data is
    extracted from the outer-joined table (e.g. as above), it's best to
    use a UNION operation as follows:

    select a.field1, b.field2
    from a, b
    where a.item_id = b.item_id
    UNION
    select a.field1, NULL as field2
    from a
    where 0= (select count(*) from b where b.item_id=a.item_id)

> 3. NULL vs empty string.  informix distinguished the two, oracle
>    doesn't.  zany hilarity ensues.  (this is the origin of "VARCHAR2",
>    since the SQL standard specifies that VARCHAR must distinguish
>    them...)

You know.  I once asked a $200/hr Oracle consultant with the
difference between VARCHAR and VARCHAR2 was and he said there was
none.

> in one project i worked on, i used objects to represent all the
> application-level queries, then subclassed them for each personality
> of database.

I think any new software should implement an object-relational
mapping.  You don't want to be writing SQL inline if you can generate
it automatically.  This is not for portability.  I can't tell you how
much Java code I've seen that looks like:

     String statement = "select name from joe where name = '"
         + theName + "'";

This same mistake is repeated over and over and over and over and over
in the code (but it's object-oriented!).

An aside. What is awesome in Perl is that you can pass parameters as
anonymous arrays and convert them on-the-fly.  For example, if the
type layer passes a scalar reference to the database specific layer,
our platform knows that it is a BLOB and invokes the appropriate
incantation for the particular database it implements.  Another trick
we'd like to implement but haven't got around to it is mapping an
array to the appropriate number of ? parameters, e.g.

    $sql->execute("select bla from foo where bla in ?",
        [['a', 'b', 'c']]);

would get mapped to:

    $dbh->prepare('select bla from foo where bla in (?, ?, ?)'
        'a', 'b', 'c');

Rob