xsharp.eu

Posted: **Fri Sep 15, 2023 6:50 am**

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

Posted: **Fri Sep 15, 2023 6:54 am**

Arun,

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

Robert

Posted: **Fri Sep 15, 2023 7:13 am**

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

Posted: **Fri Sep 15, 2023 8:01 am**

Hi Robert,

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

Posted: **Thu Sep 21, 2023 3:08 pm**

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

xsharp.eu

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

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

Re: Error on SQL Query execution in Oracle Database

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

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

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