[FDW] Table vide avec odbc_fdw

Bonjour à tous,

J’espère ne pas faire un doublon sur ce sujet, mais après avoir cherché un
peu partout, je ne trouve pas de solution à mon problème.

Je travaille actuellement sur une plateforme postgresql contenant de la
donnée propre, et j’aimerais pouvoir y utiliser en lecture seule des données
en provenance d’une base de données MS Access. Pour cela je me suis tourné
vers odbc_fdw qui semble capable de répondre à mon besoin.

Actuellement :

- J’ai préparé une connexion ODBC à mon fichier Access que j’ai
appelé « TESTACCESS ». J’ai testé cette connexion via Excel en réalisant un
lien vers des donénes externe et j’arrive bien à utiliser les tables de
données depuis Excel, en passant par le lien ODBC. Cette partie me semble
donc OK

- J’ai créé un serveur distant dans mon wrapper odbc_fdw, avec la
description suivante :
CREATE SERVER access FOREIGN DATA WRAPPER odbc_fdw OPTIONS (dsn
'TESTACCESS');

- J’ai créé une correspondance utilisateur avec mon compte admin
postgres :
CREATE USER MAPPING FOR postgres SERVER access OPTIONS (username
'Administrateur',password '');
J’ai essayé pas mal d’option concernant le nom d’utilisateur et le mot de
passe, mais ça ne change pas grand-chose (mon fichier access n’est pas
protégé, mais à un moment il est indiqué un utilisateur « Administrateur »,
donc…)

- J’ai finalement créé ma table de donnée distante :

CREATE FOREIGN TABLE super_store.test (db_station text ) SERVER access
OPTIONS (sql_query 'SELECT IPR.`Station` AS db_station FROM `IPR` IPR;',
schema '', sql_count 'SELECT count(IPR.`Station`) FROM `IPR` IPR;', database
'', db_station 'db_station');

Là aussi j’ai essayé pas mal de chose : Précisez ou pas un nom de schéma, le
mettre dans la requête. J’ai également fait des test avec des requête sans
table (simplement un « SELECT 'TEST'; » par exemple pour essayer.

Dans tous les cas, mon problème est que la table obtenue est bien créée,
mais reste desésperement vide. Au départ j’avais des problème de crash de
mon serveur postgreSQL (dues visiblement à des « OPTIONS » non présentes),
mais là j’ai simplement une table qui m’apparait comme vide.

J’ai également testé les 2 requêtes (sql_query et sql_count) directement via
MS Access et celles-ci renvoient bien les résultats attendus (245 lignes
pour la première et une ligne contenant le champ ‘245’ pour la seconde)…

J’avoue que là, je sèche un peu donc si quelqu’un avait une idée, une piste,
une remarque…

En vous remerciant par avance pour votre aide.

Julien Perrot

julien(dot)perrot(at)peche33(dot)com

----------------------------------------------------------

Chargé de mission

Développement et gestion de bases de données géographiques

----------------------------------------------------------------------------
-----------------------------------------------------

FDAAPPMA33

PS : Au niveau des logs du serveur, en mode « debug3 », j’obtiens lors de la
requête sur la table via pgadmin :

2014-09-02 17:18:14 CEST DEBUG: forked new backend, pid=6896 socket=1228

2014-09-02 17:18:14 CEST DEBUG: postgres child[6896]: starting with (

2014-09-02 17:18:14 CEST DEBUG: postgres

2014-09-02 17:18:14 CEST DEBUG: )

2014-09-02 17:18:14 CEST DEBUG: InitPostgres

2014-09-02 17:18:14 CEST DEBUG: my backend ID is 3

2014-09-02 17:18:14 CEST DEBUG: StartTransaction

2014-09-02 17:18:14 CEST DEBUG: name: unnamed; blockState: DEFAULT;
state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:

2014-09-02 17:18:14 CEST DEBUG: CommitTransaction

2014-09-02 17:18:14 CEST DEBUG: name: unnamed; blockState: STARTED;
state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:

2014-09-02 17:18:14 CEST DEBUG: StartTransactionCommand

2014-09-02 17:18:14 CEST INSTRUCTION : SELECT version();

2014-09-02 17:18:14 CEST DEBUG: StartTransaction

2014-09-02 17:18:14 CEST INSTRUCTION : SELECT version();

2014-09-02 17:18:14 CEST DEBUG: name: unnamed; blockState: DEFAULT;
state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:

2014-09-02 17:18:14 CEST INSTRUCTION : SELECT version();

2014-09-02 17:18:14 CEST DEBUG: CommitTransactionCommand

2014-09-02 17:18:14 CEST INSTRUCTION : SELECT version();

2014-09-02 17:18:14 CEST DEBUG: CommitTransaction

2014-09-02 17:18:14 CEST INSTRUCTION : SELECT version();

2014-09-02 17:18:14 CEST DEBUG: name: unnamed; blockState: STARTED;
state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:

2014-09-02 17:18:14 CEST INSTRUCTION : SELECT version();

2014-09-02 17:18:14 CEST DEBUG: StartTransactionCommand

2014-09-02 17:18:14 CEST INSTRUCTION : SET DateStyle=ISO;

SET client_min_messages=notice;

SET bytea_output=escape;

SELECT oid, pg_encoding_to_char(encoding) AS encoding,
datlastsysoid

FROM pg_database WHERE oid = 12029

2014-09-02 17:18:14 CEST DEBUG: StartTransaction

2014-09-02 17:18:14 CEST INSTRUCTION : SET DateStyle=ISO;

SET client_min_messages=notice;

SET bytea_output=escape;

SELECT oid, pg_encoding_to_char(encoding) AS encoding,
datlastsysoid

FROM pg_database WHERE oid = 12029

2014-09-02 17:18:14 CEST DEBUG: name: unnamed; blockState: DEFAULT;
state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:

2014-09-02 17:18:14 CEST INSTRUCTION : SET DateStyle=ISO;

SET client_min_messages=notice;

SET bytea_output=escape;

SELECT oid, pg_encoding_to_char(encoding) AS encoding,
datlastsysoid

FROM pg_database WHERE oid = 12029

2014-09-02 17:18:14 CEST DEBUG: ProcessUtility

2014-09-02 17:18:14 CEST INSTRUCTION : SET DateStyle=ISO;

SET client_min_messages=notice;

SET bytea_output=escape;

SELECT oid, pg_encoding_to_char(encoding) AS encoding,
datlastsysoid

FROM pg_database WHERE oid = 12029

2014-09-02 17:18:14 CEST DEBUG: ProcessUtility

2014-09-02 17:18:14 CEST INSTRUCTION : SET DateStyle=ISO;

SET client_min_messages=notice;

SET bytea_output=escape;

SELECT oid, pg_encoding_to_char(encoding) AS encoding,
datlastsysoid

FROM pg_database WHERE oid = 12029

2014-09-02 17:18:14 CEST DEBUG: ProcessUtility

2014-09-02 17:18:14 CEST INSTRUCTION : SET DateStyle=ISO;

SET client_min_messages=notice;

SET bytea_output=escape;

SELECT oid, pg_encoding_to_char(encoding) AS encoding,
datlastsysoid

FROM pg_database WHERE oid = 12029

2014-09-02 17:18:14 CEST DEBUG: CommitTransactionCommand

2014-09-02 17:18:14 CEST INSTRUCTION : SET DateStyle=ISO;

SET client_min_messages=notice;

SET bytea_output=escape;

SELECT oid, pg_encoding_to_char(encoding) AS encoding,
datlastsysoid

FROM pg_database WHERE oid = 12029

2014-09-02 17:18:14 CEST DEBUG: CommitTransaction

2014-09-02 17:18:14 CEST INSTRUCTION : SET DateStyle=ISO;

SET client_min_messages=notice;

SET bytea_output=escape;

SELECT oid, pg_encoding_to_char(encoding) AS encoding,
datlastsysoid

FROM pg_database WHERE oid = 12029

2014-09-02 17:18:14 CEST DEBUG: name: unnamed; blockState: STARTED;
state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:

2014-09-02 17:18:14 CEST INSTRUCTION : SET DateStyle=ISO;

SET client_min_messages=notice;

SET bytea_output=escape;

SELECT oid, pg_encoding_to_char(encoding) AS encoding,
datlastsysoid

FROM pg_database WHERE oid = 12029

2014-09-02 17:18:14 CEST DEBUG: StartTransactionCommand

2014-09-02 17:18:14 CEST INSTRUCTION : set client_encoding to 'UNICODE'

2014-09-02 17:18:14 CEST DEBUG: StartTransaction

2014-09-02 17:18:14 CEST INSTRUCTION : set client_encoding to 'UNICODE'

2014-09-02 17:18:14 CEST DEBUG: name: unnamed; blockState: DEFAULT;
state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:

2014-09-02 17:18:14 CEST INSTRUCTION : set client_encoding to 'UNICODE'

2014-09-02 17:18:14 CEST DEBUG: ProcessUtility

2014-09-02 17:18:14 CEST INSTRUCTION : set client_encoding to 'UNICODE'

2014-09-02 17:18:14 CEST DEBUG: CommitTransactionCommand

2014-09-02 17:18:14 CEST INSTRUCTION : set client_encoding to 'UNICODE'

2014-09-02 17:18:14 CEST DEBUG: CommitTransaction

2014-09-02 17:18:14 CEST INSTRUCTION : set client_encoding to 'UNICODE'

2014-09-02 17:18:14 CEST DEBUG: name: unnamed; blockState: STARTED;
state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:

2014-09-02 17:18:14 CEST INSTRUCTION : set client_encoding to 'UNICODE'

2014-09-02 17:18:14 CEST DEBUG: StartTransactionCommand

2014-09-02 17:18:14 CEST INSTRUCTION : SELECT count(*) FROM
super_store.test WHERE false

2014-09-02 17:18:14 CEST DEBUG: StartTransaction

2014-09-02 17:18:14 CEST INSTRUCTION : SELECT count(*) FROM
super_store.test WHERE false

2014-09-02 17:18:14 CEST DEBUG: name: unnamed; blockState: DEFAULT;
state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:

2014-09-02 17:18:14 CEST INSTRUCTION : SELECT count(*) FROM
super_store.test WHERE false

2014-09-02 17:18:15 CEST NOTICE: Opps!

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT count(*) FROM
super_store.test WHERE false

2014-09-02 17:18:15 CEST NOTICE: Opps!

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT count(*) FROM
super_store.test WHERE false

2014-09-02 17:18:15 CEST DEBUG: CommitTransactionCommand

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT count(*) FROM
super_store.test WHERE false

2014-09-02 17:18:15 CEST DEBUG: CommitTransaction

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT count(*) FROM
super_store.test WHERE false

2014-09-02 17:18:15 CEST DEBUG: name: unnamed; blockState: STARTED;
state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT count(*) FROM
super_store.test WHERE false

2014-09-02 17:18:15 CEST DEBUG: StartTransactionCommand

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT * FROM super_store.test

2014-09-02 17:18:15 CEST DEBUG: StartTransaction

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT * FROM super_store.test

2014-09-02 17:18:15 CEST DEBUG: name: unnamed; blockState: DEFAULT;
state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT * FROM super_store.test

2014-09-02 17:18:15 CEST NOTICE: Opps!

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT * FROM super_store.test

2014-09-02 17:18:15 CEST NOTICE: Opps!

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT * FROM super_store.test

2014-09-02 17:18:15 CEST DEBUG: CommitTransactionCommand

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT * FROM super_store.test

2014-09-02 17:18:15 CEST DEBUG: CommitTransaction

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT * FROM super_store.test

2014-09-02 17:18:15 CEST DEBUG: name: unnamed; blockState: STARTED;
state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT * FROM super_store.test

2014-09-02 17:18:15 CEST DEBUG: StartTransactionCommand

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT attisdropped FROM
pg_attribute WHERE attnum > 0 AND attrelid=43286::oid

ORDER BY attnum

2014-09-02 17:18:15 CEST DEBUG: StartTransaction

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT attisdropped FROM
pg_attribute WHERE attnum > 0 AND attrelid=43286::oid

ORDER BY attnum

2014-09-02 17:18:15 CEST DEBUG: name: unnamed; blockState: DEFAULT;
state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT attisdropped FROM
pg_attribute WHERE attnum > 0 AND attrelid=43286::oid

ORDER BY attnum

2014-09-02 17:18:15 CEST DEBUG: CommitTransactionCommand

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT attisdropped FROM
pg_attribute WHERE attnum > 0 AND attrelid=43286::oid

ORDER BY attnum

2014-09-02 17:18:15 CEST DEBUG: CommitTransaction

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT attisdropped FROM
pg_attribute WHERE attnum > 0 AND attrelid=43286::oid

ORDER BY attnum

2014-09-02 17:18:15 CEST DEBUG: name: unnamed; blockState: STARTED;
state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT attisdropped FROM
pg_attribute WHERE attnum > 0 AND attrelid=43286::oid

ORDER BY attnum

2014-09-02 17:18:15 CEST DEBUG: StartTransactionCommand

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT n.nspname AS nspname,
relname, format_type(t.oid,NULL) AS typname, format_type(t.oid,
att.atttypmod) AS displaytypname, nt.nspname AS typnspname, attname, attnum,
COALESCE(b.oid, t.oid) AS basetype, atthasdef, adsrc,

CASE WHEN t.typbasetype::oid=0 THEN att.atttypmod
else t.typtypmod END AS typmod,

CASE WHEN t.typbasetype::oid=0 THEN att.attlen else
t.typlen END AS typlen

FROM pg_attribute att

JOIN pg_type t ON t.oid=att.atttypid

JOIN pg_namespace nt ON nt.oid=t.typnamespace

JOIN pg_class c ON c.oid=attrelid

JOIN pg_namespace n ON n.oid=relnamespace

LEFT OUTER JOIN pg_type b ON b.oid=t.typbasetype

LEFT OUTER JOIN pg_attrdef def ON adrelid=attrelid AND
adnum=attnum

WHERE attnum > 0 AND NOT attisdropped AND
attrelid=43286::oid

ORDER BY attnum

2014-09-02 17:18:15 CEST DEBUG: StartTransaction

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT n.nspname AS nspname,
relname, format_type(t.oid,NULL) AS typname, format_type(t.oid,
att.atttypmod) AS displaytypname, nt.nspname AS typnspname, attname, attnum,
COALESCE(b.oid, t.oid) AS basetype, atthasdef, adsrc,

CASE WHEN t.typbasetype::oid=0 THEN att.atttypmod
else t.typtypmod END AS typmod,

CASE WHEN t.typbasetype::oid=0 THEN att.attlen else
t.typlen END AS typlen

FROM pg_attribute att

JOIN pg_type t ON t.oid=att.atttypid

JOIN pg_namespace nt ON nt.oid=t.typnamespace

JOIN pg_class c ON c.oid=attrelid

JOIN pg_namespace n ON n.oid=relnamespace

LEFT OUTER JOIN pg_type b ON b.oid=t.typbasetype

LEFT OUTER JOIN pg_attrdef def ON adrelid=attrelid AND
adnum=attnum

WHERE attnum > 0 AND NOT attisdropped AND
attrelid=43286::oid

ORDER BY attnum

2014-09-02 17:18:15 CEST DEBUG: name: unnamed; blockState: DEFAULT;
state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT n.nspname AS nspname,
relname, format_type(t.oid,NULL) AS typname, format_type(t.oid,
att.atttypmod) AS displaytypname, nt.nspname AS typnspname, attname, attnum,
COALESCE(b.oid, t.oid) AS basetype, atthasdef, adsrc,

CASE WHEN t.typbasetype::oid=0 THEN att.atttypmod
else t.typtypmod END AS typmod,

CASE WHEN t.typbasetype::oid=0 THEN att.attlen else
t.typlen END AS typlen

FROM pg_attribute att

JOIN pg_type t ON t.oid=att.atttypid

JOIN pg_namespace nt ON nt.oid=t.typnamespace

JOIN pg_class c ON c.oid=attrelid

JOIN pg_namespace n ON n.oid=relnamespace

LEFT OUTER JOIN pg_type b ON b.oid=t.typbasetype

LEFT OUTER JOIN pg_attrdef def ON adrelid=attrelid AND
adnum=attnum

WHERE attnum > 0 AND NOT attisdropped AND
attrelid=43286::oid

ORDER BY attnum

2014-09-02 17:18:15 CEST DEBUG: CommitTransactionCommand

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT n.nspname AS nspname,
relname, format_type(t.oid,NULL) AS typname, format_type(t.oid,
att.atttypmod) AS displaytypname, nt.nspname AS typnspname, attname, attnum,
COALESCE(b.oid, t.oid) AS basetype, atthasdef, adsrc,

CASE WHEN t.typbasetype::oid=0 THEN att.atttypmod
else t.typtypmod END AS typmod,

CASE WHEN t.typbasetype::oid=0 THEN att.attlen else
t.typlen END AS typlen

FROM pg_attribute att

JOIN pg_type t ON t.oid=att.atttypid

JOIN pg_namespace nt ON nt.oid=t.typnamespace

JOIN pg_class c ON c.oid=attrelid

JOIN pg_namespace n ON n.oid=relnamespace

LEFT OUTER JOIN pg_type b ON b.oid=t.typbasetype

LEFT OUTER JOIN pg_attrdef def ON adrelid=attrelid AND
adnum=attnum

WHERE attnum > 0 AND NOT attisdropped AND
attrelid=43286::oid

ORDER BY attnum

2014-09-02 17:18:15 CEST DEBUG: CommitTransaction

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT n.nspname AS nspname,
relname, format_type(t.oid,NULL) AS typname, format_type(t.oid,
att.atttypmod) AS displaytypname, nt.nspname AS typnspname, attname, attnum,
COALESCE(b.oid, t.oid) AS basetype, atthasdef, adsrc,

CASE WHEN t.typbasetype::oid=0 THEN att.atttypmod
else t.typtypmod END AS typmod,

CASE WHEN t.typbasetype::oid=0 THEN att.attlen else
t.typlen END AS typlen

FROM pg_attribute att

JOIN pg_type t ON t.oid=att.atttypid

JOIN pg_namespace nt ON nt.oid=t.typnamespace

JOIN pg_class c ON c.oid=attrelid

JOIN pg_namespace n ON n.oid=relnamespace

LEFT OUTER JOIN pg_type b ON b.oid=t.typbasetype

LEFT OUTER JOIN pg_attrdef def ON adrelid=attrelid AND
adnum=attnum

WHERE attnum > 0 AND NOT attisdropped AND
attrelid=43286::oid

ORDER BY attnum

2014-09-02 17:18:15 CEST DEBUG: name: unnamed; blockState: STARTED;
state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:

2014-09-02 17:18:15 CEST INSTRUCTION : SELECT n.nspname AS nspname,
relname, format_type(t.oid,NULL) AS typname, format_type(t.oid,
att.atttypmod) AS displaytypname, nt.nspname AS typnspname, attname, attnum,
COALESCE(b.oid, t.oid) AS basetype, atthasdef, adsrc,

CASE WHEN t.typbasetype::oid=0 THEN att.atttypmod
else t.typtypmod END AS typmod,

CASE WHEN t.typbasetype::oid=0 THEN att.attlen else
t.typlen END AS typlen

FROM pg_attribute att

JOIN pg_type t ON t.oid=att.atttypid

JOIN pg_namespace nt ON nt.oid=t.typnamespace

JOIN pg_class c ON c.oid=attrelid

JOIN pg_namespace n ON n.oid=relnamespace

LEFT OUTER JOIN pg_type b ON b.oid=t.typbasetype

LEFT OUTER JOIN pg_attrdef def ON adrelid=attrelid AND
adnum=attnum

WHERE attnum > 0 AND NOT attisdropped AND
attrelid=43286::oid

ORDER BY attnum

2014-09-02 17:18:16 CEST DEBUG: shmem_exit(0): 7 callbacks to make

2014-09-02 17:18:16 CEST DEBUG: proc_exit(0): 3 callbacks to make

2014-09-02 17:18:16 CEST DEBUG: exit(0)

2014-09-02 17:18:16 CEST DEBUG: shmem_exit(-1): 0 callbacks to make

2014-09-02 17:18:16 CEST DEBUG: proc_exit(-1): 0 callbacks to make

2014-09-02 17:18:16 CEST DEBUG: reaping dead processes

2014-09-02 17:18:16 CEST DEBUG: processus serveur (PID 6896) quitte avec le
code de sortie 0

---
Ce courrier électronique ne contient aucun virus ou logiciel malveillant parce que la protection avast! Antivirus est active.
http://www.avast.com