Error on SQL Query execution in Oracle Database in X# VO Project

[ArunBerlin]

Error message : [microsoft][odbc driver for oracle]cannot use keyset-driven cursor on join, with distinct clause, union, intersect or minus or on read only result set

ODBC Used : Microsoft ODBC for Oracle 32 bit - MSORCL32.DLL (07/12/2019)

I've verified that connection to the server was successful

Code: Select all

METHOD ExecuteSQLStatement(oSQLStatement, sText) 
	LOCAL lSuccess := TRUE AS LOGIC
	sText := IF(Empty(sText), '',sText)
	
	IF ! oSQLStatement:Execute()
		poError:ShowError(sText + oSQLStatement:SQLString, 2) 
		lSuccess := FALSE
	ENDIF
RETURN lSuccess

The below query is showing error

Code: Select all

SELECT * FROM Employee JOIN Salary ON  Employee.ID = Salary.ID

whereas these queries are working fine separately

Code: Select all

SELECT * FROM Employee, SELECT * FROM Salary 

[robert]

Arun,

A bit more info would be helpful
VO or X#?
Which version ?


Robert

[ArneOrtlinghaus]

I don't remember precisely all facts, but I remember similar errors many years ago in VO with ODBC to Oracle with complex statements together with "scrollable cursors".
The error message is a message from the ODBC driver, not from X#. In this case it is the Microsoft ODBC for Oracle.
As Oracle does not know scrollable cursors, the ODBC creates a main SQL with the ROWID or an internal identification and then for every movement a separate statement that retrieves the data from the ROWID. For Joins often this does not work correctly.

I suggest that you try to use the Oracle ODBC and not the Microsoft ODBC. Oracle ODBC clients newer than 11.2. up to Oracle 19 worked well for us for most queries. But also here had/have exceptions with complex statements. In these cases only a "Forward only" cursor can be used, so with the correct initial keyset parameters and retrieving all data only be "fetching" forward once.

Arne

[ArunBerlin]

Hi Robert,

.NET framework version: v4.6
Dialect : Visual objects
Type : Windows Application
XSharp version : 2.12.2.0

[ArunBerlin]

Thanks Arne. Tried configuring Oracle ODBC and not the Microsoft ODBC. It worked !