Problems with EMP5193: Oracle data corruption

[PeterFr]

Hi,

problem with TD52 Oracle NATIVE router:
in some situations bad data is stored in oracle database with EMP5193.
Tested on Windows XP/SP3 systems with Oracle Router 10.2.0.10 on a german system with
NLS_LANG=GERMAN_GERMANY.WE8MSWIN1252 (from registry)

The following picture shows a field before and after insert (with fetch from database):


If I switch on TD trace in sql.ini I got the following:

1/27/10 18:29:35 0> SQLRouter LOG Utility - version 2.1.0
1/27/10 18:29:35 0> SQLRouter/Oracle - version 4.0.0
1/27/10 18:29:35 0> remotedbname=xxxx,@xxxx
1/27/10 18:29:35 0> longbuffer=2000000
1/27/10 18:29:35 0> fetchrow=20
1/27/10 18:29:35 0> substitute=SYSSQL.,
1/27/10 18:29:35 1> [connect] dbname = XXXXXXX username = xxxxx
1/27/10 18:29:35 1> [oracon] login string xxxxx@XXX
1/27/10 18:29:35 1> [get database parameter] param = 1004
1/27/10 18:29:35 1> [set database parameter] param = 5106 value = 0
1/27/10 18:29:35 1> [get database parameter] param = 5517
1/27/10 18:29:36 1> [ERROR] 196 Invalid GET parameter
1/27/10 18:29:37 1> [compile] INSERT INTO DTABUCH (EUR_B, SATZNUMMER, INSTI
1/27/10 18:29:37 1> TUT, DTA_AUS_NR, BU_TYP, NAME_AUF, KTO_AUF, BLZ_AUF, BL
1/27/10 18:29:37 1> Z_EMPF, KTO_EMPF, BUTEXT, NAME_EMPF1, V_ZWECK1, V_ZWECK
1/27/10 18:29:37 1> 2, V_ZWECK3, V_ZWECK4, V_ZWECK5, TERMIN, WHG, IAZG, ZG
1/27/10 18:29:37 1> _SA_NR ) VALUES (:1 , :2 , :3 , :4
1/27/10 18:29:37 1> , :5 , :6 , :7 , :8 , :9 , '424544'
1/27/10 18:29:37 1> , '04000', :10 , :11 , 'VOM 30.11.09
1/27/10 18:29:37 1> ', 'BEL.INT-NR 2261 ', :12 , :13
1/27/10 18:29:37 1> , :14 , :15 , 'I', '00001610' )
1/27/10 18:29:37 1> [get database parameter] param = 1004
1/27/10 18:29:37 1> [get database parameter] param = 1004
1/27/10 18:29:37 1> [get database parameter] param = 1004
1/27/10 18:29:37 1> [get database parameter] param = 1004
1/27/10 18:29:37 1> [get database parameter] param = 1004
1/27/10 18:29:37 1> [get database parameter] param = 1004
1/27/10 18:29:37 1> [get database parameter] param = 1004
1/27/10 18:29:37 1> [get database parameter] param = 1004
1/27/10 18:29:37 1> [get database parameter] param = 1004
1/27/10 18:29:37 1> [get database parameter] param = 1004
1/27/10 18:29:37 1> [get database parameter] param = 1004
1/27/10 18:29:37 1> [get database parameter] param = 1004
1/27/10 18:29:37 1> [execute]
1/27/10 18:29:37 1> [1] 550
1/27/10 18:29:37 1> [2] 12706293
1/27/10 18:29:37 1> [3] 001
1/27/10 18:29:37 1> [4] 20100127062937
1/27/10 18:29:37 1> [5] LK
1/27/10 18:29:37 1> [6] Peter Franz
1/27/10 18:29:37 1> [7] 34200301
1/27/10 18:29:37 1> [8] 70150000
1/27/10 18:29:37 1> [9] 74362663
1/27/10 18:29:37 1> [10] MSC & Software GmbH
1/27/10 18:29:37 1> [11] RE.NR. FA MUE II UST 12/09
1/27/10 18:29:37 1> [12]
1/27/10 18:29:37 1> [13]
1/27/10 18:29:37 1> [14] 2010-01-27-18.29.37.280000
1/27/10 18:29:37 1> [15] EUR
1/27/10 18:29:37 1> [bind] position = 1, buffer size = 3, datatype = 1
1/27/10 18:29:37 1> [bind] position = 2, buffer size = 8, datatype = 1
1/27/10 18:29:37 1> [bind] position = 3, buffer size = 3, datatype = 1
1/27/10 18:29:37 1> [bind] position = 4, buffer size = 14, datatype = 1
1/27/10 18:29:37 1> [bind] position = 5, buffer size = 2, datatype = 1
1/27/10 18:29:37 1> [bind] position = 6, buffer size = 11, datatype = 1
1/27/10 18:29:37 1> [bind] position = 7, buffer size = 8, datatype = 1
1/27/10 18:29:37 1> [bind] position = 8, buffer size = 8, datatype = 1
1/27/10 18:29:37 1> [bind] position = 9, buffer size = 8, datatype = 1
1/27/10 18:29:37 1> [bind] position = 10, buffer size = 19, datatype = 1
1/27/10 18:29:37 1> [bind] position = 11, buffer size = 27, datatype = 96
1/27/10 18:29:37 1> [bind] position = 12, buffer size = 27, datatype = 96
1/27/10 18:29:37 1> [bind] position = 13, buffer size = 27, datatype = 96
1/27/10 18:29:37 1> [bind] position = 14, buffer size = 7, datatype = 12
1/27/10 18:29:37 1> [bind] position = 15, buffer size = 3, datatype = 1
1/27/10 18:29:37 1> [commit]
1/27/10 18:29:37 1> [compile] SELECT V_ZWECK1 FROM DTABUCH
1/27/10 18:29:37 1> WHERE SATZNUMMER = :1
1/27/10 18:29:37 1> [describe] name = V_ZWECK1 ,data size = 35, datatype =
1/27/10 18:29:37 1> 1
1/27/10 18:29:37 1> [get database parameter] param = 1004
1/27/10 18:29:37 1> [execute]
1/27/10 18:29:37 1> [1] 12706293
1/27/10 18:29:37 1> [bind] position = 1, buffer size = 8, datatype = 1
1/27/10 18:29:37 1> [fetch]
1/27/10 18:29:37 1> fetch data [1:1]
1/27/10 18:29:37 1> BF00BF00 BF00BF00 BF00BF00 BF00BF00 |................|
1/27/10 18:29:37 1> BF00BF00 BF00BF00 BF00 |.......... |
1/27/10 18:29:37 1> [end of fetch]
1/27/10 18:36:31 1> [disconnect]

Attached a small repro.

Thx.

[PeterFr]

... please could anybody try the repro with EMP5193 and Oracle?
Don't want this show stopper in TD52SP1...
Thank's a lot!

[randy]

I can confirm this. It seems like it doesn't like trailing blanks in bind variables.


Set sZText1 = 'Re.Nr. FA MUE II UST 12/09' -> OK
Set sZText1 = 'Re.Nr. FA MUE II UST 12/09 ' -> NOT OK
Set sInsDTA = "INSERT INTO DTABUCH (SATZNUMMER, V_ZWECK1 ) VALUES ( :sSaNr, :sZText1 )"

But
Set sInsDTA = "INSERT INTO DTABUCH (SATZNUMMER, V_ZWECK1 ) VALUES ( :sSaNr, '" || sZText1 || "')" -> ALWAYS OK

[Jeff Luther]

Peter and Randy: FYI, I was able to verify that base 5.2 + EMP5193 did indeed fetch back bad data:

ora11g_TD52-RTM+EMP5193-ShowsERROR.png

and that with yesterday' internal 5.2 (that will be SP1, due out next month) the fetch back returns good data:

[BeoWin]

Hi Jeff,
is there an EMP for this bug? We had to stop migrating
from TD51 to TD52 because of this bug. I know that this
Question was asked a hundred times before: When will
SP1 be released?

Chris

[Jeff Luther]

is there an EMP for this bug?

No, because according to my testing JAN 29th with an internal 5.2 SP1 it's fixed, so should be fixed when SP1 is released. Which is supposed to happen any day now.

[BeoWin]

Thanks a lot for your reply Jeff.
Let me explain our problem to be absolutely sure, that this is the same case as PeterFr
described here and it is solved with SP1:

We have a table TC containing e.g.
TC.LFLNAM
Réunion
Réunion
Réunion

Problem occurs when columns have special characters like é.
This statement returns 0 rows: ( sLFLNAM contains 'Réunion' )
'SELECT COUNT(*) INTO :nCount FROM TC WHERE LFLNAM = :sLFLNAM'

And this statenment works fine and returns 3 rows:
'SELECT COUNT(*) INTO :nCount FROM TC WHERE LFLNAM = \'' || sLFLNAM || '\'

This problem only occurs with Oracle ( in this case Oracle 10g ), with
SQLBase and SQLServer there is no problem at all. Is this the same
case as described in this thread?

[Jeff Luther]

I'd suggest waiting for 5.2 SP1 and retest then, almost out now, last I heard. His issue is slightly different and had to do with a LONG value.

[BeoWin]

Hopefully it works. We already bought TD5.2 and spent a lot of time
in testing and migrating, but if this doesn't work, then we have to
go back to TD5.1.
Does nobody have this problem too or can anyone confirm that?