09.03.0400, wrong real values

From: Jens Wiechmann <jens(dot)wiechmann(at)redknee(dot)com>
To: "pgsql-odbc(at)postgresql(dot)org" <pgsql-odbc(at)postgresql(dot)org>
Subject: 09.03.0400, wrong real values
Date: 2015-01-20 12:41:16
Message-ID: BLUPR0501MB882EF60DD016C456B80501A834B0@BLUPR0501MB882.namprd05.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

[cid:image001(dot)jpg(at)01D034B4(dot)BED59390]

Jens Wiechmann
> Research and Development

Tel: + 49 30 9210 24539
Mobile: + 49 152 56747131
E-mail: jens(dot)wiechmann(at)redknee(dot)com<mailto:jens(dot)wiechmann(at)redknee(dot)com>
Website: www.redknee.com<http://www.redknee.com>
Twitter: @RedkneeRKN
TSX: RKN
[cid:image002(dot)jpg(at)01D034B4(dot)BED59390]<http://twitter.com/redkneeRKN>[cid:image003(dot)jpg(at)01D034B4(dot)BED59390]<http://www.linkedin.com/company/redknee>[cid:image004(dot)jpg(at)01D034B4(dot)BED59390]<http://www.youtube.com/user/RedkneeRKN>[cid:image005(dot)jpg(at)01D034B4(dot)BED59390]<http://www.facebook.com/pages/Redknee/153720457985750>

Email Disclaimer<http://www.redknee.com/confidentiality/>

Redknee (Germany) GmbH
Geschäftsführung / Managing Directors: David Charron, Sabine Domes / Sitz der Gesellschaft: Berlin
Registered office: Berlin / Registergericht: Berlin / Commercial register: Berlin, HRB 152437 B

Responses

Browse pgsql-odbc by date

  From Date Subject
Next Message Heikki Linnakangas 2015-01-20 20:41:17 Re: 09.03.0400, wrong real values
Previous Message Heikki Linnakangas 2015-01-18 17:44:41 Re: [sysadmins] Moving psqlodbc from pgfoundry to github