When we set:
db2set DB2_COMPATIBILITY_VECTOR=ORA
below code will not return any row.
---
strcpy(aiu, "AIU");
EXEC SQL CONNECT TO DB1 USER hidehy using o0imach1;
EMB_SQL_CHECK("CONNECT");
EXEC SQL DECLARE cur0 CURSOR FOR
SELECT C1,C2,C3
FROM T1 WHERE C2=:aiu;
---
The above code should return rows as same as when
DB2_COMPATIBILITY_VECTOR is not set.
Problem Reproducible Steps:
1. $ run_ora.sh
--- run_ora.sh -----
./prep_ora.sh
./prep_app.sh
./app
-------------------
--- prep_ora.sh -----
#
db2stop
db2set DB2_COMPATIBILITY_VECTOR=ORA
db2start
db2 drop db db1
db2 create db db1 using codeset UTF-8 territory JP
db2 connect to db1
db2 drop table t1
db2 "create table t1(c1 int, c2 char(20), c3 varchar(20), c4
char(20))"
db2 "insert into t1 values (1, 'AIU','AIU','AIU')"
db2 "insert into t1 values (2, 'AIU ','AIU ','AIU ')"
db2 "insert into t1 values (3, 'AIU ','AIU ','AIU ')"
db2 "insert into t1 values (4, 'AIU ','AIU ','AIU ')"
db2 "select * from t1"
-------------------
--- prep_app.sh -----
#!/bin/ksh -x
PATH=/usr/vac/bin:$PATH
db2 connect to db1
db2 prep app.sqc bindfile
db2 bind app.bnd
db2 terminate
cc -o app app.c -I$HOME/sqllib/include -L$HOME/sqllib/lib -ldb2
-------------------
--- app.sqc -----
... snip ...
main () {
int i = 0;
strcpy(aiu, "AIU");
EXEC SQL CONNECT TO DB1 USER db2inst1 using db2inst1;
EMB_SQL_CHECK("CONNECT");
EXEC SQL DECLARE cur0 CURSOR FOR
SELECT C1,C2,C3
FROM T1 WHERE C2=:aiu;
EXEC SQL OPEN cur0;
EMB_SQL_CHECK("cursor -- open");
EXEC SQL FETCH cur0 INTO :c1,:c2,:c3;
i = 0;
while (sqlca.sqlcode != 100) {
i++;
printf("C1=[%d], C2=[%s], C3=[%s]\n", c1, c2, c3);
EXEC SQL FETCH cur0 INTO :c1,:c2,:c3;
EMB_SQL_CHECK("cursor -- open");
}
printf("%d record(s) selected.\n", i);
EXEC SQL CLOSE cur0;
EMB_SQL_CHECK("cursor -- close");
EXEC SQL CONNECT RESET;
EMB_SQL_CHECK("CONNECT RESET");
}
-------------------
From CLP, it can return rows as expected by running as:
db2 "select c1,c2,c3 from t1 where c2='AIU'" |
- It works fine if unset DB2_COMPATIBILITY_VECTOR.
- It works fine if set "DB2_COMPATIBILITY_VECTOR=10FDF" and
recreate database.
- If we use cast() as below
FROM T1 WHERE C2=cast(:aiu as char(20));
in the Problem Reproducible Step, it works around.
- If we add trailing spaces, it works around.
example:
---
char str[21];
strcpy(str, "hello ");
--- |
