Re: Fwd: Carto ODBC_FDW issue "ERROR: Connecting to driver" from PG to DB2

On Mon, Apr 9, 2018 at 7:52 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 04/09/2018 02:37 PM, karthik kumar wrote:
>
>>
>>
>> On Mon, Apr 9, 2018 at 5:13 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>>
>> On 04/09/2018 02:10 PM, karthik kumar wrote:
>>
>
> Assuming using this:
>>
>> https://github.com/ZhengYang/odbc_fdw
>>
>
>
>> I believe that should be username and password per above link:
>>
>> "CREATE USER MAPPING FOR postgres
>> SERVER odbc_server
>> OPTIONS (username 'root', password '');
>> "
>>
>> Yes.
>>
>>
>> Does 'yes' mean you changed this:
>>
>> CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (odbc_UID
>> 'db2inst1', odbc_PWD 'db2inst1');
>>
>> to this?:
>>
>> CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (username
>> 'db2inst1', password 'db2inst1');
>>
>>
>> If i try to use username it throws error as invalid option
>>
>> postgres=# CREATE SERVER driver_db5 FOREIGN DATA WRAPPER odbc_fdw OPTIONS
>> (dsn 'sample');
>> CREATE SERVER
>>
>> postgres=# CREATE USER MAPPING FOR pg SERVER driver_db5 OPTIONS
>> (username 'db2inst1', password 'db2inst1');
>> ERROR: invalid option "username"
>> HINT: Valid options in this context are: <none>
>>
>
> You are using the extension from below, correct?:
>
> https://github.com/ZhengYang/odbc_fdw
>
> In psql what does the below show?:
>
> \dx
>
>
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
----------+---------+------------+----------------------------------------------------------------
odbc_fdw | 0.3.0 | public | Foreign data wrapper for accessing
remote databases using ODBC
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)

What happens if you do not include the OPTIONS?
>
>
>> postgres=# CREATE USER MAPPING FOR pg SERVER driver_db5 OPTIONS
>> (odbc_UID 'db2inst1', odbc_PWD 'db2inst1');
>> CREATE USER MAPPING
>>
>>
>>
postgres=# CREATE USER MAPPING FOR pg SERVER driver_db5;
CREATE USER MAPPING

postgres=# CREATE FOREIGN TABLE
postgres-# driver_db5 (
postgres(# id integer
postgres(# )
postgres-# SERVER driver_db5
postgres-# OPTIONS (
postgres(# odbc_DATABASE 'sample',odbc_SCHEMA 'db2inst1'
postgres(# );
CREATE FOREIGN TABLE

postgres=# select * from driver_db5;
ERROR: Connecting to driver

>
>> Thanks,
>> Karthik.
>>
>>
>>
>> -- Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>