Re: Connecting to Unidata through HS

"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