SqlFetchNext With Oracle

[satyavangala]

We have noticed a problem with 6.1 Sp3 build 34099 while using SqlFetchNext and Oracle driver. Here's the problem.

A number variable has been initialized to 0. A Sql query was executed using SqlPrepareAndExecute. Output is supposed to go into the variable. SqlFetchNext call was made. There were no rows found and the variable should have been left at 0. Instead, the variable is set to an unknown value which isn't visible when used with Expressions tool. This is strange and never happened before. This concerns us a lot. There is a lot of code in the system which makes these types of calls and assumes that the compiler wouldn't change the value of the variable. We could fix this by checking the output of SqlFetchNext and reinitializing the value of the variable to zero if the return from SqlFetchNext is not FETCH_Ok. However, it is impossible for us to change all the code this way. We hope this is something that got introduced in this version of the compiler by accident.

Appreciate your quick input.

Thanks,
Satya Vangala

Set nDataCount = 0
If NOT SqlPrepareAndExecute(gvhSqlImmReplace,'SELECT COUNT(OBJECTTYPE) FROM DRUI
INTO :dataCount WHERE OBJECTTYPE = 0 AND SEC_ID = :SEC_ID ')
Return FALSE
Call SqlFetchNext(gvhSqlImmReplace,gvnFetchRet)
Compiler sets the dataCount variable to nothing if the Sql call found no rows. Previous versions used to leave the value as 0. This is very important because many other functions use this value and they are failing because the value isn't a number anymore.

Changin the code to this would work. We have to set dataCount variable value to 0 explicilty. However, this concerns us a lot.


If NOT SqlPrepareAndExecute(gvhSqlImmReplace,'SELECT COUNT(OBJECTTYPE) FROM DRUI
INTO :dataCount WHERE OBJECTTYPE = 0 AND SEC_ID = :SEC_ID ')
Return FALSE
If SqlFetchNext(gvhSqlImmReplace,gvnFetchRet) != FETCH_Ok
Set dataCount = 0

[Christof]

Correct me, if I am wrong, but a "SELECT COUNT..." always returns exactly on row, so if the first SqlFetchNext doesn't return FETCH_Ok, there must be a completely different problem!

Could you put a "When SQLError"-handler before the call to SqlPrepareAndExecute? I guess this call is failing. As far as I remember, the return code of SqlPrepareAndExecute will only be set to FALSE, if there is a "When SQLError" block, which returns FALSE.

[satyavangala]

Thanks for the update. The problem I'm dealing with isn't related to a specific call. In general when there are no rows in the table matching the query condition, whether it is SqlExists or SqlPrepareAndExecute, the into variable is set to no value. I hope the attached document shows what I'm tryint to say.

Appreciate your help.
Regards,
Satya Vangala

[markkuvento]

Can't reproduce.

Tried with initialisations

num=0
and
num=NUMBER_Null
and
num=5

"select 1 into :num from dual where 1=2"

into variable remained unaltered.

with
"select count(*) into :num from dual where 1=2"

into variable is zero and fetch return code = FETCH_Ok as supposed.

Same results with using a real table insted of the pseudo table dual.

TD6.1-SP3r, Build 34099, Native router
Oracle 11gr2

BR,
Markku Vento