09.03.0400, wrong real values - Mailing list pgsql-odbc

Hi,

I am using the database 9.4.0 with the odbc driver 09.03.0400  on linux machine with unixODBC.

There are different results if  I insert a float value=-0.0051 in the database with ODBC funtions SQLBindParameter(), SQLPrepare() and SQLExecute().

With the odbc version 09.03.0300 the saved value is 0.0051 and

with the odbc version 09.03.0400 the saved wrong value is -0.00510000018 (see below).

There is no difference between 32 and 64 bit.

Server Version on linux RHEL6:

psql (PostgreSQL) 9.4.0 (and 9.3.4 for the comparison)

~/.odbc.ini

[JWIE]

Driver=PostgreSQL

Port=5432

Database=jwie

FileUsage=1

UseServerSidePrepare=0

I use this table:

jwie=> \d TESTTABLE4;

                  Table "jwie.testtable4"

     Column      |            Type             | Modifiers

-----------------+-----------------------------+-----------

testtype        | character varying(64)       |

 testbooleanchar | character(1)                |

 testinteger     | bigint                      |

 testnumber      | numeric                     |

 testchar        | character(128)              |

 testvarchar     | character varying(128)      |

 testraw         | bytea                       |

 testlongraw     | bytea                       |

 testblob        | bytea                       |

 testlong        | text                        |

 testdate        | timestamp without time zone |

 testfloat       | double precision            |

C Code fragment:

:

   retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, ID_LEN, 0, szTypeID, 0, &typeID);

   retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_FLOAT, sizeof(float), 0, &f2, 0, &charID);

   retcode = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_FLOAT, sizeof(float), 0, &f3, 0, &numberID);

   retcode = SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_FLOAT, sizeof(float), 0, &f4, 0, &varcharID);

   retcode = SQLBindParameter(hstmt, 5, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_FLOAT, sizeof(float), 0, &f5, 0, &floatID);

   retcode = SQLPrepare(hstmt, (SQLCHAR*)"INSERT INTO TESTTABLE4 "

                                                    "(TESTTYPE, "

                                                    "TESTCHAR, "

                                                    "TESTNUMBER, "

                                                    "TESTVARCHAR, testfloat) "

                                                    "VALUES (?, ?, ?, ?, ?)", SQL_NTS);

   printf("retcode=%d\n", retcode);

   strcpy((char*)szTypeID, "FLOAT1");

   f2=-0.0051;

   f3=-0.0051;

   f4=-0.0051;

   f5=-0.0051;

   retcode = SQLExecute(hstmt);

:

Log files

09.03.0300 OK

========

conn = 0x198d2e0, PGAPI_Connect(DSN='JWIE', UID='jwie', PWD='xxxxx')

Driver Version='09.03.0300,201405140001'

Global Options: fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=255, max_longvarchar_size=8190

                disable_optimizer=0, ksqo=1, unique_index=1, use_declarefetch=0

                text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64

                extra_systable_prefixes='dd_;', conn_settings='(null)' conn_encoding=''

    [ PostgreSQL version string = '9.3.4' ]

    [ PostgreSQL version number = '9.3' ]

conn=0x198d2e0, query='select oid, typbasetype from pg_type where typname = 'lo''

    [ fetched 0 rows ]

    [ Large Object oid = -999 ]

    [ Client encoding = 'LATIN1' (code = 8) ]

conn=0x198d2e0, query='INSERT INTO TESTTABLE4 (TESTTYPE, TESTCHAR, TESTNUMBER, TESTVARCHAR, testfloat) VALUES ('FLOAT1', '-0.0051'::float8, '-0.0051'::float8, '-0.0051'::float8, '-0.0051'::float8)'

conn=0x198d2e0, query='INSERT INTO TESTTABLE4 (TESTTYPE, TESTCHAR, TESTNUMBER, TESTVARCHAR, testfloat) VALUES ('FLOAT2', '4.5678e+12'::float8, '4.5678e+12'::float8, '4.5678e+12'::float8, '4.5678e+12'::float8)'

09.03.0400 not OK

========

conn = 0x247f2e0, PGAPI_Connect(DSN='JWIE', UID='jwie', PWD='xxxxx')

Driver Version='09.03.0400,Jan  6 2015'

Global Options: fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=255, max_longvarchar_size=8190

                disable_optimizer=0, ksqo=1, unique_index=1, use_declarefetch=0

                text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64

                extra_systable_prefixes='dd_;', conn_settings='(null)' conn_encoding=''

    [ PostgreSQL version string = '9.4.0' ]

    [ PostgreSQL version number = '9.4' ]

conn=0x247f2e0, query='select oid, typbasetype from pg_type where typname = 'lo''

    [ fetched 0 rows ]

    [ Large Object oid = -999 ]

    [ Client encoding = 'LATIN1' (code = 8) ]

conn=0x247f2e0, query='INSERT INTO TESTTABLE4 (TESTTYPE, TESTCHAR, TESTNUMBER, TESTVARCHAR, testfloat) VALUES ('FLOAT1', '-0.00510000018'::float8, '-0.00510000018'::float8, '-0.00510000018'::float8, '-0.00510000018'::float8)'

conn=0x247f2e0, query='INSERT INTO TESTTABLE4 (TESTTYPE, TESTCHAR, TESTNUMBER, TESTVARCHAR, testfloat) VALUES ('FLOAT2', '4.56779996e+12'::float8, '4.56779996e+12'::float8, '4.56779996e+12'::float8, '4.56779996e+12'::float8)'

Can you please check this?

Thank you.

Best Regards

Jens Wiechmann