Oracle not in resultset mode

[Johann]

Hi to All,

I have troubles at a customer site with front end result sets on a Oracle 11g2 and a TD 2.1. Every handle connects with SqlSetResultSet(hSql, TRUE) and from time to time I get an error message 00163 SQL RNA Result sets not active.
Reason: Attempting to get the number of rows in a result set with the sqlnrr() function, or attempting to set a position within a result with the sqlprs() function, and the application program is not in result set mode.

But the application is in result set mode!

Has somebody faced this problem?

Any hint is very appreciated

Regards
Hans

[Jeff Luther]

No, I haven't heard of this. You have some 'holes' in your description so let's see if I can fill them in, so I better understand what you are reporting:
* SqlConnect( hSql )
* SqlSetResultSet(hSql, TRUE)
* now you SqlPrepareAndExecute() some query
* now you call SqlGetResultSetCount ( hSql, nCount ) and this is where the error is occurring??

Hmmm, are you catching the T/F return from SqlSetResultSet[/b](hSql, TRUE) ?
RS mode is a front-end process for non-SQLBase DBs -- the router caches rows in a temp file on the client machine as they are fetched so in case there's a SqlFetchRow() or SqlPreviousRow() that row can be obtained. SqlFetchNext() does not need RS mode.

One test:
* do your prepExe call and have a query you're guaranteed will return 2 or more rows
* do not call SqlGetResultSetCount
* instead, call SqlFetchRow() and pass a 1 -- I think first row is row 0, that 1 = 2nd row.

If RS mode is on SqlFetchRow should succeed. Otherwise, you'd get that SQL error again.

So, why the error? I don't know. Client machine doesn't support create/write of a temp file? If not, I would think SqlSetResultSet would return FALSE.

[Johann]

Hi Jeff,

today in the mornig I started a log with

[win32client.ora32]
log=oracle.log

and there in the file comes up always repeated an error 280 while setting a LockTimeout
(00280 DBO ITO Invalid timeout value,
Reason: Trying to set an invalid database lock timeout value.
Remedy: Set the lock wait timeout value using a valid number. The number represents seconds to wait for a database lock to be acquired. After the specified time has elapsed, the transaction is rolled back. The default setting is 300 seconds. Valid timeout values are:o 1800 Seconds to wait for a lock (1 second to 30 minutes) Wait forever for a lock held by another transaction Never wait for a lock and immediately return atimeout error (nowait locking)).

The program code looks always following when connecting a sql handle (a function call):

!!CB!! 129
If (nDATABASE_Brand = DBV_BRAND_ORACLE7 or nDATABASE_Brand = DBV_BRAND_ORACLE)
Call SqlSetIsolationLevel (hSqlHilf, 'CS')
Call SqlSetResultSet(hSqlHilf, TRUE)
Call SqlPrepareAndExecute(hSqlHilf, "alter session set nls_numeric_characters='.,'")
Call SqlPrepareAndExecute(hSqlHilf,"alter session set nls_sort='GERMAN'")
Call SqlPrepareAndExecute(hSqlHilf,"alter session set nls_date_format = 'DD.MM.YYYY HH24:MI:SS'")
Else If nDATABASE_Brand = 31 ! ODBC, MSSQLServer, ACCESS etc.
Call SqlSetIsolationLevel (hSqlHilf, sIsolationLevel)
Call SqlSetResultSet(hSqlHilf, TRUE)
Call SqlSetParameter( hSqlHilf, DBP_PRESERVE, TRUE, '' )

********* with the following setting we get the error 280 ********
Call SqlSetLockTimeout( hSqlHilf, nTimeOut )
*************************************************************************
Call SqlSetParameter( hSqlHilf, DBP_AUTOCOMMIT, FALSE, '' )


The log entries are looking following:
12/13/12 10:40:25 2> [execute]
12/13/12 10:40:25 2> [compile] alter session set nls_sort='GERMAN'
12/13/12 10:40:25 2> [execute]
12/13/12 10:40:25 2> [compile] alter session set nls_date_format = 'DD.MM.Y
12/13/12 10:40:25 2> YYY HH24:MI:SS'
12/13/12 10:40:25 2> [execute]
12/13/12 10:40:25 2> [set database parameter] param = 3003 value = 1
12/13/12 10:40:25 2> [set timeout] 20
12/13/12 10:40:25 2> [ERROR] 280 ERROR.SQL NOT FOUND LOOKING UP ERROR 00280
12/13/12 10:40:25 2> [set database parameter] param = 3006 value = 0
12/13/12 10:40:25 2> [compile] select wert_logisch, typ from WWPLUS.einstel
12/13/12 10:40:25 2> lungen where bez = 'RECH
12/13/12 10:40:25 2> TE_ANLEGEN'
12/13/12 10:40:25 2> [execute]
12/13/12 10:40:25 2> [fetch]
12/13/12 10:40:25 2> [end of fetch]
12/13/12 10:40:25 2> [commit]
12/13/12 10:40:25 2> [disconnect]
12/13/12 10:40:25 2> [connect] dbname = CENTRO username = BENUTZER
12/13/12 10:40:25 2> [oracon] login string BENUTZER@tns:centro
12/13/12 10:40:25 2> [set database parameter] param = 5106 value = 0
12/13/12 10:40:25 2> [get database parameter] param = 3001
12/13/12 10:40:25 2> [compile] alter session set nls_numeric_characters='.,
12/13/12 10:40:25 2> '
12/13/12 10:40:25 2> [execute]
12/13/12 10:40:25 2> [compile] alter session set nls_sort='GERMAN'
12/13/12 10:40:25 2> [execute]
12/13/12 10:40:25 2> [compile] alter session set nls_date_format = 'DD.MM.Y
12/13/12 10:40:25 2> YYY HH24:MI:SS'
12/13/12 10:40:25 2> [execute]
12/13/12 10:40:25 2> [set database parameter] param = 3003 value = 1
12/13/12 10:40:25 2> [set timeout] 30
12/13/12 10:40:25 2> [ERROR] 280 ERROR.SQL NOT FOUND LOOKING UP ERROR 00280
12/13/12 10:40:25 2> [set database parameter] param = 3006 value = 0
12/13/12 10:40:25 2> [compile] Select Bez, Darf_Ansehen, Darf_Anlegen, Darf
12/13/12 10:40:25 2> _Aendern, Darf_Loeschen, Darf_Ausfuehren from WWPLUS.
12/13/12 10:40:25 2> BenutzerRechte where BNINR = :1 Union Select
12/13/12 10:40:25 2> Bez,Darf_Ansehen,Darf_Anlegen,Darf_Aendern,Darf_Loesc
12/13/12 10:40:25 2> hen,Darf_Ausfuehren from WWPLUS.GruppenRechte G where
12/13/12 10:40:25 2> GINR = :2 and not exists ( Select Bez from WWP
12/13/12 10:40:25 2> LUS.BenutzerRechte B where B.Bez = G.Bez and BNINR =
12/13/12 10:40:25 2> :3 )
12/13/12 10:40:25 2>
12/13/12 10:40:25 2> order by 1
12/13/12 10:40:25 2> [execute]
12/13/12 10:40:25 2> [1] 3000383
12/13/12 10:40:25 2> [2] 3000000
12/13/12 10:40:25 2> [3] 3000383
12/13/12 10:40:25 2> [fetch]
12/13/12 10:40:25 2> [fetch]
12/13/12 10:40:25 2> [end of fetch]
12/13/12 10:40:25 2> [disconnect]
12/13/12 10:40:25 2> [connect] dbname = CENTRO username = BENUTZER
12/13/12 10:40:25 2> [oracon] login string BENUTZER@tns:centro
12/13/12 10:40:25 2> [set database parameter] param = 5106 value = 0
12/13/12 10:40:25 2> [get database parameter] param = 3001
12/13/12 10:40:25 2> [compile] alter session set nls_numeric_characters='.,
12/13/12 10:40:25 2> '
12/13/12 10:40:25 2> [execute]
12/13/12 10:40:25 2> [compile] alter session set nls_sort='GERMAN'
12/13/12 10:40:25 2> [execute]
12/13/12 10:40:25 2> [compile] alter session set nls_date_format = 'DD.MM.Y
12/13/12 10:40:25 2> YYY HH24:MI:SS'
12/13/12 10:40:25 2> [execute]
12/13/12 10:40:25 2> [set database parameter] param = 3003 value = 1
12/13/12 10:40:25 2> [set timeout] 20
12/13/12 10:40:25 2> [ERROR] 280 ERROR.SQL NOT FOUND LOOKING UP ERROR 00280
12/13/12 10:40:25 2> [set database parameter] param = 3006 value = 0

Can this error have an influence on errors 166 and 163 which are reported from the users?

Any hint is very appreciated
Regards
Hans

[Jeff Luther]

there in the file comes up always repeated an error 280 while setting a LockTimeout
(00280 DBO ITO Invalid timeout value,

This forum thread referred to an "00163 SQL RNA Result sets not active." earlier. Has this changed?

Couple comments:
* "Call SqlXXX" isn't a good coding style, especially for debugging. I always suggest "If NOT Sqlxxx" and then have error handling, a msg box, Return FALSE, etc. in there. Call throws away any FALSE return and causes the code to continue. In a 'perfect' world this might be ok, but the 'real' world and when debugging you are missing out on some feedback as to what the problem might be.

* Call SqlSetLockTimeout( hSqlHilf, nTimeOut ) -- is this returning TRUE and is nTimeOut = 20 or 30? (I wonder if Oracle doesn't allow a timeout change?)

Can this error have an influence on errors 166 and 163 which are reported from the users?

Did you happen to see what those errors are? TD has a DBERROR.EXE utility for looking at error details:

166: 00166 SQL CRS Cannot create a front-end result set

163: 00163 SQL RNA Result sets not active
Reason: This error can occur because the client workstation is out of memory, or because a temporary file name could not be created, or because the temporary front-end result set file could not be opened.

Based on 166 I would say that is the heart of the problem and accounts for the 163 error. (My guess is that SqlSetResultSet() is returning FALSE.) OS security or non-admin. issue? I don't know what timeout might have to do with this, though.

I suggest a small test case, keep it simple and see if you can still duplicate this problem. There's a lot in the LOG file that likely does not need to be there. We cannot debug a log anyway, though it can provide you some detail info. when used with a test case.

[Johann]

Hi Jeff,

here is a test case. My question is, which setting has the oracle if the function Set bOk = SqlSetLockTimeout( hSqlHilf, nTimeOut ) fails?
In case it is 0 (because the setting did not function), what happens to the program, is a creation of a frs* file possible or delivers oracle immediatly the selected data while the app is creating a frs file and cannot receive the data -> 166/163?

Regards
Hans

[Jeff Luther]

OK, here's a reply for SqlSetLockTimeout() and the test case you provided:
* I tested with Oracle 11g and TD v6.1 and get the same FALSE return.

* I talked with our router developer about this and he found out by looking at original Gupta router source that these are the only 2 valid timout values you can pass when backend is Oracle:
-1 = wait forever
0 = never wait

I just verified this but a mod. to the test to first call this:
Set bOk = SqlGetParameter( hSqlHilf, DBP_LOCKWAITTIMEOUT, nTimeOut, sDummyParameter )

to find out current/default timeout param. for our Oracle DB and upon return nTimeOut = 0. So "Never Wait" is the default
In Expressions windows I tried SqlSetLockTimeout() by passing either -1 or 0 and in both cases this function does return TRUE.

I also tried this call:
Set bOk = SqlSetParameter( hSqlHilf, DBP_LOCKWAITTIMEOUT, 60, sDummyParameter )
and it's also failing. So either that limit is in TD router source for Oracle, generally, or that Set param. FALSE return was something that came from Oracle.

CONCLUSION: as of now timeout possibilities are forever (-1) or never (0).

The developer is looking into this issue to see if Oracle can support a specific timout setting other than -1 or 0. If it can, then I'll add an enhancement request for this. Enhancement would be for a future TD version, though, so with user's current CTD v2.1 s/he must use 0 or -1.

[Jeff Luther]

P.S. I did hear back from our router developer after he did some investigating; here's what he wrote:
"It looks like the reason this was never implemented was that you can’t directly set a timeout on an OCI execute. Based on what I found, I would say that if anything this is a doc bug and we shouldn’t go after changing the implementation."

DOC BUG: We talked about this too and he's referring to the fact that for Oracle SqlSetLockTimeout() Help should include what I provided you earlier: TD-18775

For Oracle, there are the only 2 valid timout values you can pass in the nTimeout paramter:
-1 = wait forever
0 = never wait

[Johann]

Hi Jeff,

in the meanwhile I have a log here with 102MB which I analysed. The only errors loged are the timeout errors. The users reported me following sql error numbers (I asked them to write them down): 163, 23113, 3701 and 32545.

So my question is, how reliable is the log from oracle router? Why are the errors reported from the users, not reported? Is there an influence to the app from a complete different direction? Which dlls are reporting sql errors beside the orarouter?

Has anybody an idea how to track down the error messages?

Any hint is very appreciated

Regards
Hans

[Johann]

The next step in this awful odyssee will be, to change the timeout to -1 (wait for ever) and see what happens. If the things get better, I would assume the nwtwork at customer site needs too long for data transfer to fit oracles needs with timeout 0 param.

Regards
Hans

[Johann]

Hello to All,

first of all, I wish you a merry christmas an a happy New Year.

To keep you updated, the things got better with "wait for ever = -1" parameter.

Regards
Hans