[egenix-users] db2 related question

[M.-A. Lemburg]

Fernando wrote:
> On Feb 25, 2010 09:52 "M.-A. Lemburg" <mal at egenix.com> wrote:
> 
>> mxODBC itself does not parse the SQL you pass to it. The ODBC driver
>> and/or database do such parsing and thus it's possible that the ODBC
>> driver can implement such conversion between mixed case and all upper
>> case identifiers.
>>
>> However, I wonder why that's a problem in your case:
>>
>> DB2 adheres to the SQL standard which mandates to be case insensitive
>> regarding identifiers, unless those identifiers are quoted.
>>
>>
>>
> Thanks for your detailed answer. DB2-ExpressC doesn't seem to be
> compliant, though.
> 
> I have this test table in DB2: lktBuildings(BUILDINGID, BuildCode)
> 
> Using MS Access SQL PassThrough, SQL statements are tunnelled through
> ODBC without SQL parsing. If I send
> 
> SELECT "lktBuildings".BUILDINGID, "lktBuildings"."BuildCode"
> FROM "lktBuildings";
> 
> it works fine, but if I send
> 
> SELECT "lktBuildings".BUILDINGID, "lktBuildings".BuildCode
> FROM "lktBuildings";
> 
> (no quotes around BuildCode), I get the error
>
> [IBM][CLI Driver][DB2/LINUXX8664] SQL0206N "lktBuildings.BUILDCODE" is
> not valid in the context where it is used. SQLSTATE=42703 (#-206)

For the above to work, you have to create the tables, views,
etc. without using quoted identifiers.

In the above case, you seem to have a field created as "BuildCode"
(with quotes). If you create this as BuildCode (without the
quotes), the first example will fail, but the second will work.

My suggestion is to not use quoted identifiers at all in your
application or the schema definition. That way you get a more
portable application.

You can still use CamelCase in the definitions,
queries, etc. to improve readability, but at the database kernel
level, this is normally not needed.

If you still need those CamelCase columns or table names for
some reason, I'd suggest to use "SELECT CamelCase as "CamelCase", ..."
and/or views to rebind the column names using quoted
versions.

> And the documentation states that "For a SELECT or DELETE statement, the
> specified column is not a column of any of the tables or views
> identified in a FROM clause in the statement."
> 
> I'll check the links you provided, thanks,

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Feb 25 2010)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/