SQLCODE=-951

[JCTTTTTTT]

Hi,

I have constantly this error when i try to connect the informix_fdw ERROR: ERREUR: could not open connection to informix server: SQLCODE=-951 Yet, host, sqlhost and services seem to have a good configuration. If I try to connect from dbaccess, everything works perfectly.

CREATE EXTENSION informix_fdw;

CREATE SERVER my_srv
    FOREIGN DATA WRAPPER informix_fdw 
    OPTIONS (informixserver 'srv_tcp', informixdir '/opt/IBM/informix', database 'mybddl', user 'userid', password 'usermdp', client_locale 'fr_FR.utf8', db_locale 'fr_FR.utf8', enable_blobs '1');

CREATE USER MAPPING FOR user
    SERVER my_srv
    OPTIONS (user 'userid', password 'usermdp');

IMPORT FOREIGN SCHEMA informix
    FROM SERVER my_srv
    INTO public

An idea ?

[JCTTTTTTT]

the Server Environment is Informix SE 7.25 it's a problem ? If so, is there any way around this without upgrade?

[JCTTTTTTT]

No news is bad news.

[psoo]

ERROR -951 means that the Informix server wasn't able to authenticate you with the specified user, specifically the following errors are connected with this error code:

-951 User username is not known on the database server.

or

-951 User is not known on remote host.

Looking into your USER MAPPING definition, you should change "user" to "username", since that is the correct option to use for the Informix FDW. Also you don't need to set the user/password within the SERVER options.

Hope that helps.

[psoo]

Hmm looking at the code, there's seems to exist a bug within the correct handling of "user" and "username". While the ifx_valid_options array specifies "user" as the correct option for the database login, the code in ifxAssignOptions() and ifxGetOptionDups() want to have "username".

Though my testing proves that this actually doesn't trigger any errors, since ifxAssignOptions() is responsible to handle the settings and push them to the remote side.

I'm going to provide a fix to make ifx_valid_options[] consistent with "username", in the meanwhile please check your setup with the "username" option.

[JCTTTTTTT]

Hi,

With "username"

SQLCODE=-23101

[JCTTTTTTT]

i edited

client_locale 'en_US.819', db_locale 'en_US.819'

but now :

DEBUG: informix FDW exception count: 1 ERREUR: informix FDW error: "Identifier is too long. " DÉTAIL : SQLSTATE IX000 (SQLCODE=56)

[psoo]

Please note that SE 7.25 is pretty old if i scan the versioning scheme correctly (IBM release notes say this version was released january 2004). I've tested the code below to IDS 10, only. So we currently are on unknown grounds :)

My suspicion is that SE7 has a much more limited maximum length for connection identifiers, which is in tested versions 128 characters. If that's true, we need a workaround to make the auto-generated connection identifier for ifxCreateConnectionXact() version specific. If you are still interested, i can provide a workaround for that you can test.

[JCTTTTTTT]

I'll be more than happy if you propose a workaround :)

[psoo]

One further question: you are compiling the Informix FDW yourself?

[JCTTTTTTT]

Absolutly, that's my last compil :

tar -xzf REL0_4_0.tar.gz cd informix_fdw-REL0_4_0 nano ifx_fdw.c rowid = (ItemPointer) palloc0fast(SizeOfIptrData); rowid = (ItemPointer) palloc0fast(sizeof(ItemPointerData)); sudo find / -name pg_config PG_CONFIG=/usr/lib/postgresql/10/bin/pg_config make PG_CONFIG=/usr/lib/postgresql/10/bin/pg_config make install

[psoo]

Please find commit f141f6d, which tries to address the identifier length issue i believe we ran into.

You need to clone from the informix_fdw repository directly and build from there, since i haven't done a new release yet.

[JCTTTTTTT]

wow ! great job ! it's work !

but now, i've another bug :

for exemple, if i creat this :

CREATE FOREIGN TABLE cpostal (
                codp_cps             char(5),
                vill_cps             char(25),
                cpay_cps             char(3),
                pays_cps             char(25),
                sect_cps             smallint,
                lsec_cps             char(25),
                cant_cps             smallint,
                lcan_cps             char(25)
                   )
SERVER informix_srv
OPTIONS ( query 'SELECT * FROM cpostal',
          database 'mybdd',
          informixdir '/usr/informix');

select * from public.cpostal works wonderfully.

but

IMPORT FOREIGN SCHEMA mybdd FROM SERVER informix_srv INTO public;

Return : ERROR: ERREUR: informix FDW error: "Column (extended_id) not found in any table in the query (or SLV is undefined). " DETAIL: SQLSTATE IX000 (SQLCODE=39)

[psoo]

The problem is this query in ifxGetTableDetailsSQL():

    char *get_column_info = "SELECT tabname, colno, colname, coltype, collength, extended_id"
        "  FROM systables a, syscolumns b"
        " WHERE a.tabid = b.tabid AND a.tabid = %d"
        " ORDER BY colno;";

This is used to retrieve all necessary properties of columns of a table (referenced by tabid), to rebuild the table schema.

Your old Informix instance doesn't support extended datatypes (they were introduced later, don't know when exactly). The extended_id column there references the data type properties from the system catalog table sysxtdtypes.

I'll try to provide a fix for that. The solution i have in mind is to special case the guts in ifxGetTableDetailsSQL() if we are connected to an old SE instance. There are other places where the extended_id attribute is used, especially the logic in ifxTypeidToPg() depends on it. So there have to be some careful handling, too.

[psoo]

Hi,

I've pushed a commit (3afb89d8bff9a38c988d7da89e3d1133fe3aeb3b) which changes the way the Informix foreign data wrapper handles connection warnings. Since i don't have an old Informix SE instance here, i'd like to ask you if you can test the latest commit in the master branch against your setup. This doesn't fix the IMPORT FOREIGN SCHEMA issue yet, but i'm primarily interested in what warnings you get during connection establishing. E.g. in your case you should see the following:

WARNUNG:  opened informix connection with warnings
DETAIL:  informix SQLSTATE 01I04: "Database selected "
HINWEIS:  connected to a Informix SE instance

[JCTTTTTTT]

Compil with last commit (3afb89d)

DEBUG:  informix connection dsn "xxx@xxx"
NOTICE:  connected to a Informix SE instance
DEBUG:  prepare query "SELECT * FROM cpostal"
ATTENTION:  informix_fdw: using NO SCROLL cursor without transactions

[JCTTTTTTT]

No News ! Oup's it's summer time :)

[psoo]

Yeah, i didn't had the time to work on it. But stay tuned, it is still on my radar.

[psoo]

Informix 7.25 SE is too old and requires some additional adjustment to the code i am not willing to do. So close this as "won't fix".