Like others who have come here support, I also am having problems getting a database link in Oracle to successfully connect to a PosgreSQL database. I can successfully isql to the PostgreSQL database from the Oracle server.
Oracle installed on 64 bit version of Centos 6.2
PostgreSQL database also on a 64 bit version of Centos 6.2
Contents of odbcinst.ini:
# Example driver definitions
# Driver from the postgresql-odbc package
# Setup from the unixODBC package
Description = ODBC for PostgreSQL
Driver = /usr/lib/psqlodbc.so
Setup = /usr/lib/libodbcpsqlS.so
Driver64 = /usr/lib64/psqlodbc.so
Setup64 = /usr/lib64/libodbcpsqlS.so
FileUsage = 1
Trace = yes
Tracefile = /u01/oracle/odbc_errors/odbc.log
Contents of .odbc.ini:
Driver = PostgreSQL
#Driver = /usr/lib64/psqlodbc.so
DATABASE = p316
PORT = 5432
#SERVER = 10.34.160.10
ServerName = 10.34.160.10
UID = sa
PWD = amdocs
#charset = utf8
#ConnSettings =SET CLIENT_ENCODING to 'UNICODE'
results of odbcinst -j command:
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /u01/oracle/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
contents of tnsnames.ora:
dg4odbc = (Description =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA = (SID = dg4odbc))
(HS = OK)
contents of listener.ora:
(ORACLE_HOME = /u01/oracle/prod/db_22.214.171.124)
(SID_NAME = dg4odbc)
(PROGRAM = dg4odbc)
contents of initdg4odbc.ora (gateway init.ora file):
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
# HS init parameters
HS_FDS_CONNECT_INFO = dg4odbc
#HS_FDS_TRACE_LEVEL = 255
HS_FDS_TRACE_LEVEL = DEBUG
#HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so
HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbc.so
#HS_LANGUAGE = american_america.utf8
HS_LANGUAGE = american_america.we8msin1252
#HS_NLS_NCHAR = UCS2
# ODBC specific environment variables
# Environment variables required for the non-Oracle system
Error from gateway trace file:
Entered hgopoer at 2014/01/14-16:57:42
hgopoer, line 231: got native error 202 and sqlstate 0; message follows...
Exiting hgopoer, rc=0 at 2014/01/14-16:57:42
hgocont, line 2754: calling SqlDriverConnect got sqlstate 0
Exiting hgocont, rc=28500 at 2014/01/14-16:57:42 with error ptr FILE:hgocont.c LINE:2774 FUNCTION:hgocont() ID:Something other than invalid authorization
Exiting hgolgon, rc=28500 at 2014/01/14-16:57:42 with error ptr FILE:hgolgon.c LINE:801 FUNCTION:hgolgon() ID:Calling hgocont
Entered hgoexit at 2014/01/14-16:57:42
Exiting hgoexit, rc=0
I can get an ODBC trace file to generate when i use the isql command. However, I can't seem to generate one when I try accessing the PostgreSQL database from Oracle. Does anyone have an idea as to how I can get and ODBC trace to generate? In the .odbc.ini file under the [ODBC] heading I tried setting Trace=1 and TraceFile=/path/of /file but I didn't know what to set for the TraceDll setting.
Thanks for your patience with the length of this posting.
The PostGreSQL ODBC driver does not contain generic ODBC functions. So you need to use a driver manager in your gateway init file referenced by HS_FDS_SHAREABLE_NAME.
HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbc.so
on Unix a common ODBC driver is unixODBC and its library is called libodbc just as you had it in the commented line. But please make sure that the ODBC driver is a 64bit ODBC driver as your ODBC driver itself is a 64bit ODBC driver library and DG4ODBC on 64bit OS is also 64bit.
So check if /usr/local/lib/libodbc.so is a 64bit lib, else install it from UnixODBC.org - then set correctly: HS_FDS_SHAREABLE_NAME = <path>/libodbc.so
Thank you. That helped a lot. After making the change, plus changing the value of HS_LANGUAGE to american_america.we8iso8859P1 I was able to successfully select from the PostgreSQL database.
I'm now determined to learn the difference between an odbc driver and an odbc driver manager.
I have one parting question if you don't mind. I know why I had to set the HS_LANGUAGE setting to a Western European. I ended up with the same problem that you have replied to others about. The PostgreSQL database is actually a UTF8 database. Are you saying that we can't retrieve the non-Western European characters with this setup; or, is there an additional setting I can use in the gateway init file to allow that?
Let me start with the difference between ODBC Driver and Driver Manager.
The ODBC driver MANAGER is responsible for generic ODBC functions and it knows how to interpret the odbc.ini. In addition it is loading the real ODBC driver. The driver manager is generic and required for almost all drivers.The ODBC driver itself is designed for a dedicated database and very often shipped by the database vendor.
On Windows for example Microsoft ships the driver manager and you only need to install the ODBC driver.
Regarding your UTF-8 Postgres database. It depends on the ODBC Driver Manager version. Older ODBC Driver Managers from unixODBC expect the ODBC connect string in double byte when you specify an UTF8 charset. As all applications send the connect string in single bytes the older driver managers fails and doesn't load the ODBC driver.
Newer driver managers (2.3 onwards) are working correctly. So when you want to use UTF8 please make sure you have a 2.3.x ODBC Driver manager installed.
The release installed in your env can be checked using "odbcinst" -j after setting the PATH and LD_LIBRARY_PATH correctly.