[ psqlodbc-Bugs-1000710 ] CHAR field of resultset contains garbled data

Bugs item #1000710, was opened at 2006-08-14 22:00
You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1000710&group_id=1000125

Category: None
Group: None
>Status: Closed
Resolution: None
Priority: 3
Submitted By: Farid Zidan (faridz)
Assigned to: Hiroshi Inoue (hinoue)
Summary: CHAR field of resultset contains garbled data

Initial Comment:
PostgreSQL ODBC Driver 8.02.00.02

resultset for CHAR(8) column contains garbled data.

Example, column cmp_type_cd of the following select ( see attached picture )

Select
"cmp_type_cd", "description", "ctrl_ins_dtm", "ctrl_upd_dtm", "ctrl_usr_id"
from
"in_sync_cmp_type"

Table DDL is:
/*==============================================================*/
/* Table: in_sync_cmp_type */
/*==============================================================*/
create table in_sync_cmp_type (
cmp_type_cd CHAR(8) not null,
description VARCHAR(30) not null,
ctrl_ins_dtm TIMESTAMP not null default CURRENT_TIMESTAMP,
ctrl_upd_dtm TIMESTAMP not null,
ctrl_usr_id VARCHAR(128) not null,
constraint PK_IN_SYNC_CMP_TYPE primary key (cmp_type_cd)
);

Sample Data:

INSERT INTO in_sync_cmp_type VALUES (
'DATA',
'Data',
'2005-11-18 00:00:00',
'2005-11-18 00:00:00',
'sys');

INSERT INTO in_sync_cmp_type VALUES (
'COL',
'Column',
'2005-12-21 00:00:00',
'2005-12-21 00:00:00',
'sys');
INSERT INTO in_sync_cmp_type VALUES (
'COLM',
'Column',
'2005-12-21 00:00:00',
'2005-12-21 00:00:00',
'sys');

INSERT INTO in_sync_cmp_type VALUES (
'IDX',
'Index',
'2005-12-21 00:00:00',
'2005-12-21 00:00:00',
'sys');
INSERT INTO in_sync_cmp_type VALUES (
'IDXM',
'Index',
'2005-12-21 00:00:00',
'2005-12-21 00:00:00',
'sys');

INSERT INTO in_sync_cmp_type VALUES (
'PK',
'PK',
'2005-12-15 00:00:00',
'2005-12-15 00:00:00',
'sys');
INSERT INTO in_sync_cmp_type VALUES (
'PKM',
'PK',
'2005-12-15 00:00:00',
'2005-12-15 00:00:00',
'sys');

INSERT INTO in_sync_cmp_type VALUES (
'FK',
'FK',
'2005-11-18 00:00:00',
'2005-11-18 00:00:00',
'sys');
INSERT INTO in_sync_cmp_type VALUES (
'FKM',
'FK',
'2005-11-18 00:00:00',
'2005-11-18 00:00:00',
'sys');

INSERT INTO in_sync_cmp_type VALUES (
'PRIV',
'Privilege',
'2005-12-21 00:00:00',
'2005-12-21 00:00:00',
'sys');
INSERT INTO in_sync_cmp_type VALUES (
'PRIVM',
'Privilege',
'2005-12-21 00:00:00',
'2005-12-21 00:00:00',
'sys');

INSERT INTO in_sync_cmp_type VALUES (
'TABLE',
'Tables',
'2005-12-21 00:00:00',
'2005-12-21 00:00:00',
'sys');

INSERT INTO in_sync_cmp_type VALUES (
'FOLDER',
'Folder',
'2006-01-26 00:00:00',
'2006-01-26 00:00:00',
'sys');

INSERT INTO in_sync_cmp_type VALUES (
'TRG',
'Trigger',
'2006-02-28 00:00:00',
'2006-02-28 00:00:00',
'sys');
INSERT INTO in_sync_cmp_type VALUES (
'TRGM',
'Trigger',
'2006-02-28 00:00:00',
'2006-02-28 00:00:00',
'sys');

INSERT INTO in_sync_cmp_type VALUES (
'PROCLS',
'Procedures',
'2006-04-13 00:00:00',
'2006-04-13 00:00:00',
'sys');

INSERT INTO in_sync_cmp_type VALUES (
'TEXT',
'Text',
'2006-05-08 00:00:00',
'2006-05-08 00:00:00',
'sys');

INSERT INTO in_sync_cmp_type VALUES (
'PCOL',
'Procedure/Function Column',
'2006-06-06 00:00:00',
'2006-06-06 00:00:00',
'sys');
INSERT INTO in_sync_cmp_type VALUES (
'PCOLM',
'Procedure/Function Column',
'2006-06-06 00:00:00',
'2006-06-06 00:00:00',
'sys');

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

Comment By: Farid Zidan (faridz)
Date: 2006-08-24 15:42

Message:
Thanks for the explanation. I will additionally test the
updated driver dll with PARAMETER bind offset pointer for
updating data and will log any new issues if necessary.
CompareData buffers are indeed unaligned.

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

Comment By: Hiroshi Inoue (hinoue)
Date: 2006-08-24 15:25

Message:
FYI the 0x4 fix may be no longer needed.

The cause of the problem was the driver
handled length/indicater buffers as aligned
but CompareData's buffers are not aligned
unfortunately.

Thanks.

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

Comment By: Farid Zidan (faridz)
Date: 2006-08-24 13:17

Message:
It works fine now. Also fixes SQLForeignKeys update/delete
rules garbled data. Thanks alot!

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

Comment By: Hiroshi Inoue (hinoue)
Date: 2006-08-24 13:03

Message:
Oops sorry.
I've just uploaded the dll (again?).

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

Comment By: Farid Zidan (faridz)
Date: 2006-08-24 12:22

Message:
I am missing the dll? ( not attached to this issue )

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

Comment By: Hiroshi Inoue (hinoue)
Date: 2006-08-24 05:42

Message:
Thanks a lot.
Please try the attached dll.

regards,
Hiroshi Inoue

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

Comment By: Farid Zidan (faridz)
Date: 2006-08-24 04:17

Message:
Picture of row buffer memory after second fetch with row
bind parameter offset. Third line string data is incorrectly
terminated: 20 20 06 00 instead of 20 20 20 00

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

Comment By: Farid Zidan (faridz)
Date: 2006-08-24 04:16

Message:
Attached is sample MS VC project to demonstrate this issue.
Also attached is picture of buffer memory showing the
overrun data.

If you run the test with menu option to use row bind offset
parameter you see that on the second fetch the driver is
incorrectly overrunning the first resultset column data area
by writing terminating the string by 20 20 06 00 ( third
line the attached memory picture ). So the driver looks
like incorrectly calculating target addresses or doing a
byte swap, or some other thing incorrectly.

If you run the test using the second Test menu option and
without using row bind parameter, everything looks ok.

Not sure if the driver is also incorrectly doing parameter
bind offset pointer math, but it may be worth checking since
it is not doing the row bind parameter offset math correctly.

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

Comment By: Hiroshi Inoue (hinoue)
Date: 2006-08-24 00:53

Message:
> Tried the 0x4 fix. I am still getting an
> occasional one bad character before the
> null terminator in the string buffer.

Unfortunately I can't see the behavior here.

> Please let me know if would be helpful to
> put together a small MS VC project to
> demonstrate this issue.

Yes it would be helpful for me.

regards,
Hiroshi Inoue

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

Comment By: Farid Zidan (faridz)
Date: 2006-08-23 11:25

Message:
Tried the 0x4 fix. I am still getting an occasional one bad
character before the null terminator in the string buffer.
See attached picture.

Please let me know if would be helpful to put together a
small MS VC project to demonstrate this issue. Thanks.

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

Comment By: Hiroshi Inoue (hinoue)
Date: 2006-08-23 04:26

Message:
Please try to set the Extra opts DSN option as 0x4
using ODBC administrator. You can see the option
at Advanced options(Datasource) Page2.

regards,
Hiroshi Inoue

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

Comment By: Farid Zidan (faridz)
Date: 2006-08-19 23:44

Message:
You can download the application from http://www.zidsoft.com/
To duplicate this issue, create a PostgreSQL data source and
create the application repository in this data source ( keep
the application repository set to the <embedded repository>)

Do a DBMS comparison between <embedded Repository> and the
postgreSQL data source.

Compare table in_sync_cmp_type between the <embedded
repository> and the postgreSQL repository by right clicking
on the tree and choosing Add Tables/Views comparison from
the popup menu.

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

Comment By: Hiroshi Inoue (hinoue)
Date: 2006-08-19 08:30

Message:
What kind of software are you using ?

regards,
Hiroshi Inoue

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

You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1000710&group_id=1000125