Oracle in result set mode not returning all rows

General discussion forum about all databases other than SqlBase.
jmccall

Oracle in result set mode not returning all rows

Post by jmccall » 11 Mar 2011, 23:40

I am supporting a legacy app. written in CTD2000 and connected to an Oracle database using the SQLORA32 router.

The customer has just noticed that one particular search they perform should return over 280,000 rows yet returns less than 10% of them.

Details
The SELECT is performed with result sets mode turned on. Oracle doesn't support back end result sets so CTD should be implementing front end result sets (FERS).

The application does the SqlPrepareAndExecute() then calls SqlGetResultSetCount( )to get the number of rows selected.
  • It returns around 20,800 instead of 283,464 (never consistently the same number, but always around 20,800)
  • If let it loop, fetching the rows until no more data, 20,800 is how many it retrieves
  • I don't see any FRS* files in the Windows temp. directory (should be there with FERS, right?)
  • If I run the same SQL in Oracle's SQLPlus I get the full set of results.
It's as if the SQL router can't open the the FRS* files so just gets as many rows into memory as possible and hopes I'll be happy with that (to be honest I would, but our customer isn't!).

I have tried turning on logging in SQL.INI. I get nothing unless I add the /FD switch (display fetch data) ... in which case I get everything, which grinds the whole thing to a halt.

Any ideas?
Thanks in advance,
James

~~~~~~~~~~

I doubt that this is even a CTD2000 specific issue, but for completeness:
Client
~~~~~
Windows XP SP?
CTD 2.0 PTF-3
sqlwntm.dll: Version 7.5.1-PTF1 M Build 12531
sqlora32.dll: Version 2.0-PTF3 (PR3), Build 12756

Oracle
ociw32.dll: Version 10.2.0.1.0

Server
~~~~~~
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

SQL.INI
~~~~~~~
[win32client]
clientname=Win32User

[win32client.dll]
comdll=sqlora32

[win32client.ora32]
log=c:\cmb\oratrace.log

[oragtwy]
remotedbname=xxxxxx,@TNS:yyyyyy
substitute=",
substitute=SYSSQL.,

Jeff Luther

Re: Oracle in result set mode not returning all rows

Post by Jeff Luther » 12 Mar 2011, 01:58

2 things come to mind:
* What's that SqlGetResultSetCount() query return if run from SQLTalk? Mod. the query to "select count(*) FROM... WHERE..."

* And by extension, is result set mode ON/TRUE for the app?

*"CTD2000 and connected to an Oracle database" -- what version of Oracle DB and client?

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests