Numeric type conversion error

[serge-ads]

PostgreSQL ver.: 11.6, 12.1 (Cent OS 7.7.1910) Informix ver.: 12.10FC6 (Client SDK 4.10FC12/FC6, 3.70FC8) postgres field type: NUMERIC/FLOAT informix field type: FLOAT

When I try to insert into foreign table value with decimal part (for example 123.4) I get an error:

ERROR: could not convert attnum 16 to informix type "5", errcode -1213 SQL state: XX000

but if value is without dot (123) everything is OK...

Looks like there is an informix problem with the decimal separator conversion?

[psoo]

Thanks for the report, i'll look at it. Do you have a sample DDL Skript to reproduce the error (CREATE TABLE for the informix and foreign table would be fine).

[serge-ads]

DDL schemas

--> informix:

create table 'informix'.osm_pos ( id INT8 NOT NULL, user_lon FLOAT default 0.0000000000000000 not null, user_lat FLOAT not null ) extent size 16 next size 16 lock mode row;

--> postgres:

CREATE FOREIGN TABLE rtr.e_osm_pos ( id bigint NOT NULL, user_lon float NOT NULL default 0, user_lat float not null ) SERVER ifx_exchange OPTIONS (client_locale 'pl_PL.utf8', db_locale 'pl_PL.utf8', database 'exchange', table 'osm_pos');

insert into rtr.e_osm_pos (id, user_lon, user_lat) values (1, 23, 52); --OK insert into rtr.e_osm_pos (id, user_lon, user_lat) values (2, 23.5432, 52.1234); --ERROR: informix_fdw: conversion error "-1213"(attnum "1") insert into rtr.e_osm_pos (id, user_lon, user_lat) values (3, 23, 52.1234); --ERROR: informix_fdw: conversion error "-1213"(attnum "2")

select * from rtr.e_osm_pos; 1 23 52

[psoo]

Here's an update: looks like the problem is in an incorrect handling of locale vs. numeric conversion routines. PostgreSQL type output routine always formats a float value with a '.' as a decimal separator, whereas ESQL/C conversion routines (like rstod()) expect a locale-depending value.

While testing this i've discovered an incorrect handling of DBMONEY in ifx_connection.ec, which uses DB_MONEY instead, which is obviously a mistake. Also the connection cache in ifx_conncache.c doesn't recognize this setting while storing a connection. Currently working on it.

[serge-ads]

Good job! I have checked and now it works to me.