Обсуждение: Oracle Heterogenous Services Error

Hi List;

We've setup oracle HS-ODBC on Oracle 10g installed on a windowsXP box. We
also have Postgres installed on a redhat Linux box.

I've setup a user 'pgadmin' for testing.  I've added an entry in the
pg_hba.conf file as follows:

host    all         all         0.0.0.0/0  md5

When we try and connect we get this error:

(0) ORACLE GENERIC GATEWAY Log File Started at 2006-06-20T15:05:18
(0) hoainit (3): ; hoainit Exited with retcode = 0.
(0) hoalgon (7): ; hoalgon Entered. name = PGADMIN.
(0) DB_ODBC_INTERFACE (2085): ; FATAL: password authentication failed
for user
(0) "PGADMIN" (SQL State: 28000; SQL Code: 210

Anyone have any Ideas??

Thanks in advance for your help...

> I've setup a user 'pgadmin' for testing.  I've added an entry in the
> pg_hba.conf file as follows:

Here is username lowercase.

> When we try and connect we get this error:
>
> (0) ORACLE GENERIC GATEWAY Log File Started at 2006-06-20T15:05:18
> (0) hoainit (3): ; hoainit Exited with retcode = 0.
> (0) hoalgon (7): ; hoalgon Entered. name = PGADMIN.
> (0) DB_ODBC_INTERFACE (2085): ; FATAL: password authentication failed
> for user
> (0) "PGADMIN" (SQL State: 28000; SQL Code: 210

Here is username uppercase. I think postgresql is case-sensitive.
Could you connect via odbc the postgresql from another app?

Regards,

Luf

We are able to connect via sql server to postgres. Also, we are providing the username in lowercase, the Oracle error always returns it in uppercase.



----- Original Message -----
Subject: Re: [ODBC] Oracle Heterogenous Services Error
From: "Ludek Finstrle" <luf@pzkagis.cz>
Date: Wed, June 21, 2006 2:15

> I've setup a user 'pgadmin' for testing. I've added an entry in the > pg_hba.conf file as follows: Here is username lowercase. > When we try and connect we get this error: > > (0) ORACLE GENERIC GATEWAY Log File Started at 2006-06-20T15:05:18 > (0) hoainit (3): ; hoainit Exited with retcode = 0. > (0) hoalgon (7): ; hoalgon Entered. name = PGADMIN. > (0) DB_ODBC_INTERFACE (2085): ; FATAL: password authentication failed > for user > (0) "PGADMIN" (SQL State: 28000; SQL Code: 210 Here is username uppercase. I think postgresql is case-sensitive. Could you connect via odbc the postgresql from another app? Regards, Luf ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster

Wed, Jun 21, 2006 at 11:29:28AM -0500, LLC napsal(a):
> We are able to connect via sql server to postgres. Also, we are
> providing the username in lowercase, the Oracle error always
> returns it in uppercase.

Could you get the exact connection string?
Could you post here the mylog output?

Regards,

Luf

> ----- Original Message -----
>
>
> Subject: Re: [ODBC] Oracle Heterogenous Services Error
>
>
> From: "Ludek Finstrle" >;luf@pzkagis.cz>
>
>
> Date: Wed, June 21, 2006 2:15
>
>
>
>
>
> > I've setup a user 'pgadmin' for testing.  I've added an entry in the
> > pg_hba.conf file as follows:
>
> Here is username lowercase.
>
> > When we try and connect we get this error:
> >
> > (0) ORACLE GENERIC GATEWAY Log File Started at 2006-06-20T15:05:18
> > (0) hoainit (3): ; hoainit Exited with retcode = 0.
> > (0) hoalgon (7): ; hoalgon Entered. name = PGADMIN.
> > (0) DB_ODBC_INTERFACE (2085): ; FATAL: password authentication failed
> > for user
> > (0) "PGADMIN" (SQL State: 28000; SQL Code: 210
>
> Here is username uppercase. I think postgresql is case-sensitive.
> Could you connect via odbc the postgresql from another app?
>
> Regards,
>
> Luf
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

----- Original Message -----
Subject: Re: [ODBC] Oracle Heterogenous Services Error
From: "Ludek Finstrle" <luf@pzkagis.cz>
Date: Wed, June 21, 2006 10:45



Wed, Jun 21, 2006 at 11:29:28AM -0500, LLC napsal(a):
> We are able to connect via sql server to postgres. Also, we are
> providing the username in lowercase, the Oracle error always
> returns it in uppercase.

Could you get the exact connection string?
Could you post here the mylog output?

Regards,

Luf

> ----- Original Message -----
>
>
> Subject: Re: [ODBC] Oracle Heterogenous Services Error
>
>
> From: "Ludek Finstrle" >;luf@pzkagis.cz>
>
>
> Date: Wed, June 21, 2006 2:15
>
>
>
>
>
> > I've setup a user 'pgadmin' for testing.  I've added an entry in the
> > pg_hba.conf file as follows:
>
> Here is username lowercase.
>
> > When we try and connect we get this error:
> >
> > (0) ORACLE GENERIC GATEWAY Log File Started at 2006-06-20T15:05:18
> > (0) hoainit (3): ; hoainit Exited with retcode = 0.
> > (0) hoalgon (7): ; hoalgon Entered. name = PGADMIN.
> > (0) DB_ODBC_INTERFACE (2085): ; FATAL: password authentication failed
> > for user
> > (0) "PGADMIN" (SQL State: 28000; SQL Code: 210
>
> Here is username uppercase. I think postgresql is case-sensitive.
> Could you connect via odbc the postgresql from another app?
>
> Regards,
>
> Luf
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster


Here's the trace file from Oracle, does this help?

(0) hoagprd (2): ; hoagprd Entered.
(0) HOACONN.C (244): ; [Generic Connectivity Using ODBC] version:
4.6.1.0.0070
(0) HOACONN.C (295): ; Class version: 200
(0) hoagprd (2): ; hoagprd Exited with retcode = 0.
(0) hoainit (3): ; hoainit Entered.
(0)  (0): ; connect string is: defTdpName=hsodbc;SYNTAX=(ORACLE8_HOA,
(0) BASED_ON=ORACLE8, IDENTIFIER_QUOTE_CHAR="",
(0)
CASE_SENSITIVE=CASE_SENSITIVE_QUOTE);BINDING=<navobj><binding><datasourc
es><da-
(0) tasource name='hsodbc' type='ODBC'
(0)
connect='KAM_FINAL'><driverProperties/></datasource></datasources><remot
eMachi-
(0) nes/><environment><optimizer noFlattener='true'/><misc
year2000Policy='-1'
(0) consumerApi='1' sessionBehavior='4'/><queryProcessor
parserDepth='2000'
(0) tokenSize='1000' noInsertParameterization='true'
 noThreadedReadAhead='true'
(0) noCommandReuse='true'/><debug
(0) generalTrace='true'/></environment></binding></navobj>
(0) ORACLE GENERIC GATEWAY Log File Started at 2006-06-20T15:05:18
(0) hoainit (3): ; hoainit Exited with retcode = 0.
(0) hoalgon (7): ; hoalgon Entered. name = PGADMIN.
(0) DB_ODBC_INTERFACE (2085): ; FATAL: password authentication failed
for user
(0) "PGADMIN" (SQL State: 28000; SQL Code: 210)
(0) DRV_InitTdp: DB_ODBC_INTERFACE (2085): ; FATAL: password
authentication failed
(0) for user "PGADMIN" (SQL State: 28000; SQL Code: 210)
(0)
(0) nvRETURN (F:\Work\Connect_4_6_3\src\drv\DRV_BIND.C 356): -2220
(0) nvRETURN (F:\Work\Connect_4_6_3\src\nav\NAV_BIND.C 293): -2220
(0) hoalgon (7): ; hoalgon Exited with retcode = 28500.
(0) hoaexit (6): ; hoaexit Entered.
(0) hoaexit (6): ; hoaexit Exited with retcode = 0.
(0)  (0): ; Closing log file at TUE JUN 20 15:05:19 2006.

> > We are able to connect via sql server to postgres. Also, we are
> > providing the username in lowercase, the Oracle error always
> > returns it in uppercase.
>
> Could you get the exact connection string?
> Could you post here the mylog output?
>
> ----- End of original Message -----
>
> Here's the trace file from Oracle, does this help?

No, I see no connection string for psqlodbc. Please could you post
mylog output?

Regards,

Luf

----- Original Message -----


Subject: Re: [ODBC] Oracle Heterogenous Services Error


From: "Ludek Finstrle" <luf@pzkagis.cz>


Date: Wed, June 21, 2006 13:15





> > We are able to connect via sql server to postgres. Also, we are
> > providing the username in lowercase, the Oracle error always
> > returns it in uppercase.
>
> Could you get the exact connection string?
> Could you post here the mylog output?
>
> ----- End of original Message -----
>
> Here's the trace file from Oracle, does this help?

No, I see no connection string for psqlodbc. Please could you post
mylog output?

Regards,

Luf


The mylog output is listed below:

Thanks for your help with this...


{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0
Courier New;}{\f1\fswiss\fcharset0 Arial;}}
{\*\generator Msftedit 5.41.15.1503;}\viewkind4\uc1\pard\f0\fs20
[4168]globals.extra_systable_prefixes = 'dd_;'\par
[4168][[SQLAllocHandle]][4168]**** in PGAPI_AllocEnv ** \par
[4168]** exit PGAPI_AllocEnv: phenv = 13e1f90 **\par
[4168][[SQLSetEnvAttr]] att=200,2\par
[4168][[SQLAllocHandle]][4168]PGAPI_AllocConnect: entering...\par
[4168]**** PGAPI_AllocConnect: henv = 13e1f90, conn = 13e39d0\par
[4168]EN_add_connection: self = 13e1f90, conn = 13e39d0\par
[4168]       added at i =0, conn->henv = 13e1f90, conns[i]->henv =
13e1f90\par
[4168][SQLGetInfoW(30)][4168]PGAPI_GetInfo: entering...fInfoType=77\par
[4168]PGAPI_GetInfo: p='03.51', len=0, value=0, cbMax=12\par
[4168][SQLSetConnectAttrW][4168]PGAPI_SetConnectAttr 115 1\par
[4168]the application is ansi\par
[4168][SQLDriverConnectW][4168]PGAPI_DriverConnect: entering...\par
[4168]**** PGAPI_DriverConnect: fDriverCompletion=0,
connStrIn='DSN=KAM_FINAL;UID=PGADMIN;PWD=xxxxxxx;'\par
[4168]our_connect_string = 'DSN=KAM_FINAL;UID=PGADMIN;PWD=xxxxxxx;'\par
[4168]attribute = 'DSN', value = 'KAM_FINAL'\par
[4168]copyAttributes:

DSN='KAM_FINAL',server='',dbase='',user='',passwd='xxxxx',port='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)\par
[4168]attribute = 'UID', value = 'PGADMIN'\par
[4168]copyAttributes:

DSN='KAM_FINAL',server='',dbase='',user='PGADMIN',passwd='xxxxx',port='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)\par
[4168]attribute = 'PWD', value = 'xxxxx'\par
[4168]copyAttributes:

DSN='KAM_FINAL',server='',dbase='',user='PGADMIN',passwd='xxxxx',port='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)\par
[4168]getDSNinfo: DSN=KAM_FINAL overwrite=0\par
[4168]globals.extra_systable_prefixes = 'dd_;'\par
[4168]rollback_on_error=-1\par
[4168]globals.extra_systable_prefixes = 'dd_;'\par
[4168]our_connect_string = 'DSN=KAM_FINAL;UID=PGADMIN;PWD=xxxxxxx;'\par
[4168]attribute = 'DSN', value = 'KAM_FINAL'\par
[4168]CopyCommonAttributes:
A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[4168]attribute
= 'UID', value = 'PGADMIN'\par
[4168]CopyCommonAttributes:
A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[4168]attribute
= 'PWD', value = 'xxxxx'\par
[4168]CopyCommonAttributes:
A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[4168]calling
getDSNdefaults\par
[4168]CC_connect: entering...\par
[4168]sslmode=disable\par
[4168]original_CC_connect: entering...\par
[4168]original_CC_connect: DSN = 'KAM_FINAL', server = 'casanblol01', port
= '5433', database = 'testdb', username = 'PGADMIN', password='xxxxx'\par
[4168]connecting to the server socket...\par
[4168]connection to the server socket succeeded.\par
[4168]sizeof startup packet = 99\par
[4168]sent the authentication block.\par
[4168]sent the authentication block successfully.\par
[4168]gonna do authentication\par
[4168]read 13, global_socket_buffersize=4096\par
[4168]auth got 'R'\par
[4168]areq = 5\par
[4168]in AUTH_REQ_MD5\par
[4168]read 97, global_socket_buffersize=4096\par
[4168]auth got 'E'\par
[4168]original_CC_connect: 'E' - SFATAL\par
[4168]original_CC_connect: 'E' - C28000\par
[4168]original_CC_connect: 'E' - Mpassword authentication failed for user
"PGADMIN"\par
[4168]original_CC_connect: 'E' - Fauth.c\par
[4168]original_CC_connect: 'E' - L337\par
[4168]original_CC_connect: 'E' - Rauth_failed\par
[4168]CONN ERROR: func=original_CC_connect, desc='', errnum=210,
errmsg='FATAL: password authentication failed for user "PGADMIN"'\par
[4168]CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect',
errnum=210, errmsg='FATAL: password authentication failed for user
"PGADMIN"'\par
[4168][SQLGetDiagRecW][4168]PGAPI_GetDiagRec entering type=2 rec=1\par
[4168]**** PGAPI_ConnectError: hdbc=13e39d0 <0>\par
[4168]enter CC_get_error\par
[4168]enter CC_create_errormsg\par
[4168]msg = 'FATAL: password authentication failed for user "PGADMIN"'\par
[4168]exit CC_create_errormsg\par
[4168]exit CC_get_error\par
[4168]CC_get_error: status = 210, msg = #FATAL: password authentication
failed for user "PGADMIN"#\par
[4168]\tab      szSqlState = '28000',len=56, szError='(null)'\par
[4168]PGAPI_GetDiagRec exiting 1\par
[4168][SQLGetDiagRecW][4168]PGAPI_GetDiagRec entering type=2 rec=1\par
[4168]**** PGAPI_ConnectError: hdbc=13e39d0 <58>\par
[4168]enter CC_get_error\par
[4168]exit CC_get_error\par
[4168]CC_get_error: status = 210, msg = #FATAL: password authentication
failed for user "PGADMIN"#\par
[4168]\tab      szSqlState = '(null)',len=56, szError='FATAL: password
authentication failed for user "PGADMIN"'\par
[4168]PGAPI_GetDiagRec exiting 0\par
[4168][[SQLGetDiagFieldW]] Handle=(2,13e39d0) Rec=1 Id=8
info=(12d6f0,256)\par
[4168]PGAPI_GetDiagField entering rec=1[4168]PGAPI_GetDiagField exiting 0\par
[4168][[SQLGetDiagFieldW]] Handle=(2,13e39d0) Rec=1 Id=9
info=(12d6f0,256)\par
[4168]PGAPI_GetDiagField entering rec=1[4168]PGAPI_GetDiagField exiting 0\par
[4168][[SQLGetDiagFieldW]] Handle=(2,13e39d0) Rec=1 Id=10
info=(12d6f0,256)\par
[4168]PGAPI_GetDiagField entering rec=1[4168]PGAPI_GetDiagField exiting 0\par
[4168][[SQLGetDiagFieldW]] Handle=(2,13e39d0) Rec=1 Id=11
info=(12d6f0,256)\par
[4168]PGAPI_GetDiagField entering rec=1[4168]PGAPI_GetDiagField exiting 0\par
[4168][SQLGetDiagRecW][4168]PGAPI_GetDiagRec entering type=2 rec=2\par
[4168]**** PGAPI_ConnectError: hdbc=13e39d0 <0>\par
[4168]PGAPI_GetDiagRec exiting 100\par
[4168][[SQLFreeHandle]][4168]PGAPI_FreeConnect: entering...\par
[4168]**** in PGAPI_FreeConnect: hdbc=13e39d0\par
[4168]enter CC_Destructor, self=13e39d0\par
[4168]in CC_Cleanup, self=13e39d0\par
[4168]after CC_abort\par
[4168]SOCK_Destructor\par
[4168]after SOCK destructor\par
[4168]exit CC_Cleanup\par
[4168]after CC_Cleanup\par
[4168]after free statement holders\par
[4168]exit CC_Destructor\par
[4168]PGAPI_FreeConnect: returning...\par
[4168][[SQLFreeHandle]][4168]**** in PGAPI_FreeEnv: env = 13e1f90 ** \par
[4168]in EN_Destructor, self=13e1f90\par
[4168]exit EN_Destructor: rv = 1\par
[4168]   ok\par
[4168]DETACHING PROCESS\par
\par
\f1\par
}

> The mylog output is listed below:
>
> Thanks for your help with this...
>
>
> [4168][SQLDriverConnectW][4168]PGAPI_DriverConnect: entering...\par
> [4168]**** PGAPI_DriverConnect: fDriverCompletion=0,
> connStrIn='DSN=KAM_FINAL;UID=PGADMIN;PWD=xxxxxxx;'\par

As I said before. psqlODBC get PGADMIN instead of pgadmin.
There are two ways:
1) say Oracle to don't uppercase the username
2) create PGADMIN user in postgresql backend - to create this
   user you have to spcify the username in double quotes
   (somethink like this: create user "PGADMIN").

It isn't psqlODBC problem.

Regards,

Luf

Sorry to step into this so late, but when I saw that you were using oracle 10g I
did not think it relevant. Please see the attached set of emails exchanged a few
years back regarding this topic, and a TAR that Oracle says they have completed.
Since my initial effort I have not again attempted to use Oracle HS with
PostgreSQL.

-- dave

David Schleis
Wisconsin State Laboratory of Hygiene

email:        dave@mail.slh.wisc.edu
snail:        Madison WI 53718-6777
ups/fedx:    2811 Agriculture Dr
        Madison WI 53718
voice:         608.221.6288
fax:         608.221.6297


+++++++++++++++++++++++++++++++++++++++++++++++++++
Yes I do the TAR númber is: 13953728.6

We are in touch (I hope they release the patch soon, while, the systems area in
my company are going to develope a kind of non real-time "replication" program.

Greetings

Alejandro.

-----Mensaje original-----
De: Schleis, David [mailto:dave@mail.slh.wisc.edu]
Enviado el: Lunes, 04 de Agosto de 2003 06:51 a.m.
Para: 'Alejandro Fuentes González'
Asunto: RE: [ODBC] Problems with ORACLE


Thank you so much for forwarding this information. The fix for them is simple.
Have the ODBC translation layer either always use double quotes or never use
them. If you have a TAR number from oracle I would appreciate knowing it so I
can find out when this problem is resolved. As it turns out, the future project
I was exploring the Oracle to PostgreSQL communications link for is happening
now. ODBC would certainly make things easier.

Thanks again.
-- dave



-----Original Message-----
From: Alejandro Fuentes González [mailto:alejandro.fuentes@SSP.DF.GOB.MX]
Sent: Friday, August 01, 2003 2:33 PM
To: Schleis, David; 'pgsql-odbc@postgresql.org'
Subject: RE: [ODBC] Problems with ORACLE


David, I have talked with the people of Oracle and they told me that:

El problema radica en la diferencia del comportamiento default de los simbolos,
Oracle transforma todo a mayusculas, postgres a minusculas.
En el momento de resolver la query, esta se transforma en
select field from table@link
-> select "A1"."FIELD" from "TABLE" A1
Como el ultimo alias posgres lo transforma a minusculas este es equivalente a
-> select "A1"."FIELD" from "TABLE" "a1"

El cual no puede resolver.
TRANSLATING:

THE PROBLEM IS  THE DIFFERENT BEHAVIOR OF THE DEFAULT SIMBOLS, ORACLE TRANSFORMS
EVERYTHING TO UPPERCASES, POSTGRES TO LOWECASES.

WHEN THEY TRY TO RESOLVE THE QUERY ITS´ TRANSLATE FROM:
SELECT FIELD FROM TABLE@LINK
TO:
TO SELECT "A1"."FIELD" FROM "TABLE" A1

BECAUSE THE LAST ALIAS POSTGRES TRANSLATES TO LOWERCASE THIS IS EQUIVALENT TO:
SELECT "A1"."FIELD" FROM "TABLE"  "a1"

THE WICH ONE CANNOT BE RESOLVE.

THEY ARE ASKING ME IF THERE IS A WAY TO CHANGE THIS BEHAVIOR.

You have already told me that there is not a way around so they are going to
declare this as a bug.
-----Mensaje original-----
De: Schleis, David [mailto:dave@mail.slh.wisc.edu]
Enviado el: Martes, 29 de Julio de 2003 09:01 a.m.
Para: 'pgsql-odbc@postgresql.org'
Asunto: Re: [ODBC] Problems with ORACLE


Using Oracle 8.1.6 I was never successful in accessing a PostgreSQL database.
Examination of the log files revealed that the Oracle ODBC driver uses double
quotes "" inconsistently creating and then calling a table alias. There was no
way around this, so it was impossible to access the PostgreSQL database from
Oracle. I posted these findings on the OTN Heterogeneous Services discussion
group and was told I needed to contact Oracle support. I moved on to other
projects and I have not tried again with later versions of the Oracle ODBC
driver. If you find a way to get Oracle to talk to PostgreSQL please let me
know.
-- dave



-----Original Message-----
From: Alejandro Fuentes González [mailto:alejandro.fuentes@SSP.DF.GOB.MX]
Sent: Monday, July 28, 2003 7:08 PM
To: pgsql-odbc@postgresql.org
Subject: [ODBC] Problems with ORACLE


Hi, when I use the psqlODBC with MS-Access I´ve got not problems but when I try
to use it with Oracle the next message appears:


SELECT * FROM "ORACLE"."TABLITA"@POSGRES

*

ERROR at line 1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this

message:

[Generic Connectivity Using ODBC]Record &SQLREC has no fields. Loading failed

ORA-02063: preceding 2 lines from POSGRES

Altougth I´ve got already fields in the tables, I´ve tried with differnt kind of
datatypes varchar char int numeric....but the problem persist


Any Idea???


Thanks in advance

+++++++++++++++++++++++++++++++++++++++++++++




-----Original Message-----
From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org]
On Behalf Of Ludek Finstrle
Sent: Thursday, June 22, 2006 11:07 AM
To: LLC
Cc: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Oracle Heterogenous Services Error


> The mylog output is listed below:
>
> Thanks for your help with this...
>
>
> [4168][SQLDriverConnectW][4168]PGAPI_DriverConnect: entering...\par
> [4168]**** PGAPI_DriverConnect: fDriverCompletion=0,
> connStrIn='DSN=KAM_FINAL;UID=PGADMIN;PWD=xxxxxxx;'\par

As I said before. psqlODBC get PGADMIN instead of pgadmin. There are two ways:
1) say Oracle to don't uppercase the username
2) create PGADMIN user in postgresql backend - to create this
   user you have to spcify the username in double quotes
   (somethink like this: create user "PGADMIN").

It isn't psqlODBC problem.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend