ODBC: Several values returned by SQLColumns are incorrect

[Jieping Zhang]

Below are the failures in SQLColumn API testing:

1. In the resultset returned by SQLColumn API, value of column ColNullable is 2 rather than 1, column REMARK is empty.

Test create table =>create table GTN2BSG5FQ (KXE2QSC7HC char(10) CHARACTER SET ISO88591) no partition
=======================================================================
SQLColumns: compare results of columns fetched for following column
The Column Name is KXE2QSC7HC and column type is char
***ERROR: ColNullable expect: 1 and actual: 2 are not matched
***ERROR: Remark expect: CHARACTER CHARACTER SET ISO88591 and actual: are not matched
Number of rows fetched: 1

2. Somehow if the table has more than 3 columns, the 3rd column seems got lost as nothing regarding the 3rd column is returned in the resultset. For test case below, 3rd column E5IPGXAHNB has no info in the resultset.

19:18:38 Test create table =>create table GTN2BSG5FQ (KXE2QSC7HC char(10) CHARACTER SET ISO88591,RMSYLIFAR4 varchar(10) CHARACTER SET ISO88591,E5IPGXAHNB long varchar CHARACTER SET ISO88591,ZQW9LNYDG3 decimal(10,5)) no partition
=======================================================================
19:18:40 SQLColumns: Test #3
SQLColumns: SQLColumns function call executed correctly.

SQLColumns: compare results of columns fetched for following column
The Column Name is KXE2QSC7HC and column type is char
***ERROR: ColNullable expect: 1 and actual: 2 are not matched
***ERROR: Remark expect: CHARACTER CHARACTER SET ISO88591 and actual: are not matched

SQLColumns: compare results of columns fetched for following column
The Column Name is RMSYLIFAR4 and column type is varchar
***ERROR: ColNullable expect: 1 and actual: 2 are not matched
***ERROR: Remark expect: VARCHAR CHARACTER SET ISO88591 and actual: are not matched

SQLColumns: compare results of columns fetched for following column
The Column Name is E5IPGXAHNB and column type is long varchar
***ERROR: ColName expect: E5IPGXAHNB and actual: ZQW9LNYDG3 are not matched
***ERROR: ColDataType expect: 12 and actual: 3 are not matched
***ERROR: ColTypeName expect: VARCHAR and actual: DECIMAL are not matched
***ERROR: ColPrec expect: 2000 and actual: 10 are not matched
***ERROR: ColLen expect: 2000 and actual: 12 are not matched
***ERROR: ColScale expect: 0 and actual: 5 are not matched
***ERROR: ColRadix expect: 0 and actual: 10 are not matched
***ERROR: ColNullable expect: 1 and actual: 2 are not matched
***ERROR: Remark expect: VARCHAR CHARACTER SET ISO88591 and actual: are not matched
Number of rows fetched: 3

[RuoYu Zuo (ruo-yu-zuo)]

I have run some debug in this part of SMD calls and looked in to the buffer right after sql returns to mxosrvr, no translation acted yet, the values are just as same as the bug described, we may need SQL team to look into this first to make sure the values are returned correctly from their side.

[xiu-zhu.zhang (xiu-zhu-zhang)]

It will insert into values to table 'trafodion."_MD_".columns' when execute 'create table...'. I found the inserted values is incorrect in it.

There is my operational procedure:
1). create table GTN2BSG5FQ (KXE2QSC7HC char(10) CHARACTER SET ISO88591,RMSYLIFAR4 varchar(10) CHARACTER SET ISO88591,E5IPGXAHNB long varchar CHARACTER SET ISO88591,ZQW9LNYDG3 decimal(10,5),UJZ_VWO6PF decimal(5,2) unsigned,OUDJTKTVBM numeric(10,5),FQVKTFH0RD numeric(5,2) unsigned,D93AO8UXJJ smallint,XY_L4PKEZB smallint unsigned,MAWIIS6VO7 integer,WHQLMPNEZC integer unsigned,URCY1GTN2B bigint,SG5FQVKTFH real,RDD93AO8UX float,JJXY_L4PKE double precision,ZBMAWIIS6V date,O7WHQLMPNE time,ZCURCY1GTN timestamp) no partition;

2). select column_name,fs_data_type,sql_data_type from trafodion."_MD_".columns where object_uid=(select object_uid from trafodion."_MD_".objects where object_name='GTN2BSG5FQ');
                Result after execute this select:
                       COLUMN_NAME FS_DATA_TYPE SQL_DATA_TYPE
                       D93AO8UXJJ 130 SIGNED SMALLINT
                       E5IPGXAHNB 70 LONG VARCHAR
                       FQVKTFH0RD 133 UNSIGNED INTEGER
                      JJXY_L4PKE 143 DOUBLE
                      KXE2QSC7HC 0 CHARACTER
                      MAWIIS6VO7 132 SIGNED INTEGER
                      O7WHQLMPNE 192 DATETIME
                      OUDJTKTVBM 134 SIGNED LARGEINT
                      RDD93AO8UX 143 DOUBLE
                      RMSYLIFAR4 64 VARCHAR
                      SG5FQVKTFH 142 REAL
                      SYSKEY 134 SIG...

[xiu-zhu.zhang (xiu-zhu-zhang)]

The SQLColumns information based on table TRAFODION."_MD_".objects and TRAFODION."_MD_".columns.

There is the SQL Statement currently:
select ... from (...) dt,TRAFODION."_MD_".objects ob,TRAFODION."_MD_".columns co where co.fs_data_type=dt.fs_data_type...

From this statement I know it distinguish different data type by field 'fs_data_type' in table 'TRAFODION."_MD_".columns' when execute 'create table ...' I found it will express another data type but actually it doesn't this data type.

It doesn't distinguish accurately data type.

It need be distinguished accurately firstly then the correct data can be queried out.
We can add another column in table TRAFODION."_MD_".columns or update field 'fs_data_type' to different value for every different data type to distinguish it when execute 'create table ...'.
SQL Team maybe need to do this part work firstly.

[xiu-zhu.zhang (xiu-zhu-zhang)]

The SQLColumns data based on TRAFODION."_MD_".columns table but several data type being missing in ..."_MD_".columns table.

There is SQL Statement verifying the being missing data type:
1).
create table GTN2BSG5FQ (KXE2QSC7HC char(10) CHARACTER SET ISO88591,RMSYLIFAR4 varchar(10) CHARACTER SET ISO88591,E5IPGXAHNB long varchar CHARACTER SET ISO88591,ZQW9LNYDG3 decimal(10,5),UJZ_VWO6PF decimal(5,2) unsigned,OUDJTKTVBM numeric(10,5),FQVKTFH0RD numeric(5,2) unsigned,D93AO8UXJJ smallint,XY_L4PKEZB smallint unsigned,MAWIIS6VO7 integer,WHQLMPNEZC integer unsigned,URCY1GTN2B bigint,SG5FQVKTFH real,RDD93AO8UX float,JJXY_L4PKE double precision,ZBMAWIIS6V date,O7WHQLMPNE time,ZCURCY1GTN timestamp) no partition;

2).
select column_name,fs_data_type,sql_data_type from trafodion."_MD_".columns where object_uid=(select object_uid from trafodion."_MD_".objects where object_name='GTN2BSG5FQ');

We can see that data type has been missed for below fields from the result:
        FQVKTFH0RD
        OUDJTKTVBM
        RDD93AO8UX
        O7WHQLMPNE
        ZBMAWIIS6V
        ZCURCY1GTN

[xiu-zhu.zhang (xiu-zhu-zhang)]

The SQLColumns data based on field 'FS_DATA_TYPE' belonging to ..."_MD_".columns table.

Here is the Statement which is be used to query the SQLColumns info:
SELECT ...
FROM TRAFODION."_MD_".objects ob
                ,TRAFODION."_MD_".columns co
                ,TRAFODION."_MD_".columns co1
                ,(
                                VALUES ('DATE',91,10,'{d ''','''}',NULL,1,0,2,NULL,0,NULL,'DATE',NULL,NULL,'DATE',192,NULL,10,6,9,1,NULL,1,3,3,0),...
                ) dt(……"LOCAL_TYPE_NAME", "MINIMUM_SCALE", "MAXIMUM_SCALE", "SQL_TYPE_NAME",
                                "FS_DATA_TYPE", "NUM_PREC_RADIX……)
WHERE ob.OBJECT_UID = co.OBJECT_UID
                AND dt.FS_DATA_TYPE = co.FS_DATA_TYPE
                AND co.OBJECT_UID = co1.OBJECT_UID
                AND co1.COLUMN_NUMBER = 0...;

The 'FS_DATA_TYPE' has missed currently in ..."_MD_".Columns for several data type so can't get correct result.