[fdo-users] ODBC provider gives error when used together withInformix

[Gavin Cramer]

Hello.  So far, Informix is not a supported data source for ODBC
Provider.  I do not think that it is on anyone's short-term list for
adding support either.  I wish that it were as simple as just plugging
in a new driver when trying each new data source.  So far, pretty much
every ODBC driver that is now supported has required code changes to the
provider for at least 2 or 3 issues to make it work well.  This is due
to unexpected behavior, misfeatures or plain old defects in each driver.

I have done most of these fixes.  Right I am swamped and cannot
volunteer significant time right now.  I can help you past any early
stumbling blocks to getting things downloaded and built though.  Plus,
we can discuss tips on unit testing and making specific adaptations for
a new driver if you want to tackle this.

As for quoting of outgoing SQL, that particular block of code is not
what deals with it -- that code deals with incoming column names that
would have required quoting earlier.  Quoting of outgoing SQL is handled
mainly in two places:

1. Earlier in col_.c, where it internally initialises "formatStr".
There is special handling for Sybase to avoid quotes.

2. In FdoSmPhOdbcMgr::SupportsAnsiQuotes().  It senses Sybase and tells
the Schema Manager to not use quotes at all.  This flag is used in many
places.

An ideal solution for quoting would be to call SQLGetInfo/SQLGetInfoW
and check the SQL_IDENTIFIER_QUOTE_CHAR attribute value.  However, that
opens up the option of any quote character, and the Schema Manager only
handles double-quote (the SQL92 standard) or no quoting.  Plus, there
isn't an RDBI interface to pass up a specific quote character from a
driver call.  It was a more conservative code change to just suppress
quotes entirely for Sybase specifically, since that can be detected in
both levels of code.  In other places (e.g. for connecting to MySQL),
provider code make a call to set a driver attribute to get the data
source to use standard double-quotes.

Gavin


-----Original Message-----
From: fdo-users-bounces at lists.osgeo.org
[mailto:fdo-users-bounces at lists.osgeo.org] On Behalf Of Ismael Cams
Sent: Friday, June 01, 2007 8:07 AM
To: fdo-users at lists.osgeo.org
Subject: [fdo-users] ODBC provider gives error when used together
withInformix


Hello all,

it seems to be there is a problem with the ODBC provider when trying to
connect to an Informix database. The problem is related to the manner
the
SQL statements are sent. When sniffing on the SQL statements that are
coming
in on the database we get for example:

SELECT H."x", H."y" FROM "cmplx_xy" H

This is causing a syntax error in the Informix database. I have looked
somewhat in the code and noticed these quotes have been added to support
the
Oracle database (in col_.c):

/ Oracle allows a dot '.' character in a column name if 
                // it is double-quoted during an SQL "create table" or
"alter table"
                // command.  E.g.:
                //     alter table table2 add ( "name.withdot"
varchar2(40));
                // This is actually used in Oracle system tables in some
                // non-default installations (see defect 654283).  It
conflicts
                // with FDO's identifier rules.  Rather than reject the
entire
                // schema or table, we'll just skip the offending
column.

The Sybase seems to suffer the same problem as the Informix database
(which
makes sense as they are close connected to eachother), but for the
Sybase
this has been managed in the code (no quotes are added in case of
Sybase).

Because all my spatial data is stored in Informix this is really
blocking
for me. Can it be solved without adapting the code and build the FDO
myself
(I am not familiar with that stuff) ? Is there a chance it will be
solved in
the near future in a release of the ODBC provider ?

Thanks in advance.
-- 
View this message in context:
http://www.nabble.com/ODBC-provider-gives-error-when-used-together-with-
Informix-tf3851905s18162.html#a10911634
Sent from the fdo-users mailing list archive at Nabble.com.

_______________________________________________
fdo-users mailing list
fdo-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/fdo-users