Re: Connecting to Unidata through HS

The trace shows this:

SELECT
  A1.NAME AS c0001,
  A1.AREA AS c0000
FROM ADMINISTRATORS.DEMO_WELL_INFO_NF A1 I run this in Powerbuilder using ODBC connection to Unidata and it fails. If I change it to:

SELECT
  A1.NAME c0001,
  A1.AREA c0000
FROM ADMINISTRATORS.DEMO_WELL_INFO_NF A1 it works! So somehow I need to prevent HS from adding the AS in the SQL statement. I can't find anything in the configuration parameters for Unidata ODBC that has the passthrough option.

-- 
Terry Dykstra
Canadian Forest Oil Ltd.
"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message
news:1a75df45.0311132217.1ccc2139_at_posting.google.com...


> "Terry Dykstra" <dontreply_tddykstra_at_forestoil.ca.x> wrote

>

> > I'm trying to use Heterogeneous Services on 8174 SE to connect to a

Unidata


> > 5.2 database.

> > Unfortunately, somewhere the SQL statement gets translated into invalid

> > syntax.

>

> Terry, I do not see the invalid syntax...

>

> You enter this in SQL*Plus:

> select

>   DEMO_WELL_INFO_NF.AREA THEAREA,

>   DEMO_WELL_INFO_NF.NAME THENAME

> from Administrators.DEMO_WELL_INFO_NF_at_DAVEHS

>

> It is translated to this - which seems very valid to me:

> SELECT

>   A1."AREA",

>   A1."NAME"

> FROM "ADMINISTRATORS"."DEMO_WELL_INFO_NF" A1

>

> The column labels you have specified have been stripped (which is

> correct bevahiour across the gateway AFAIK). And the table name has

> been given a synonym name and that is used in the SELECT clause -

> again SOP. Even within Oracle itself this is done when dealing with

> its own SQL.

>

> Finally you gave the following SQL from the trace. Again it seems fine

> to me.

> SELECT

>   A1.NAME AS c0001,

>   A1.AREA AS c0000

> FROM  ADMINISTRATORS.DEMO_WELL_INFO_NF A1

>

> The column labels that Unidata/ODBC assign is not relevant - that is

> ignored (I think) on the Oracle side.

>

> > The problem is that Unidata doesn't like the AS.  If I connect through

> > PowerBuilder using ODBC to the Unidata database, I have no problem using

the


> > original SQL statement.

>

> Different set of equations.

>

> > If I use the translated SQL statement, it will fail unless I remove the

AS.


> > Anybody have any idea how to tell Oracle /  Unidata to not mangle my

SQL?


>

> Is Unidata case sensitive? Informix for example is. If I do the

> following via a HS Informix Gateway, it will fail:

> SELECT count(*) FROM node_at_informix;

>

> The reason is that the table name node is translated by Oracle to

> uppercase and passed to Informix. The table name on the Informix side

> is lowercase. Thus, I need to do the following:

> SELECT count(*) FROM "node"@informix;

>

> The quotes around the table name tell Oracle to keep the case as is.

>

> You will see that Oracle in your case makes column and table names

> uppercase and put quotes around it to enforce uppercase. Could that be

> the problem?

>

> Do you have anything specific in the HS log that says exactly why the

> SQL is rejected as invalid? Have you enabled the ODBC tracing and

> looked at what the ODBC log says?

>

> Also, when using an ODBC driver with Oracle HS, you can configure ODBC

> parameters for that HS connection. Try and see if you can set

> something called ODBC_PASSTHROUGH (or ODBC_PASSTHRU or whatever) to

> TRUE. This will prevent the ODBC side from mangling and intefering

> with the SQL - something for which ODBC is well known for.

>

> --

> Billy