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

We encourage new members to introduce themselves here. Get to know one another and share your interests.
Post Reply
ArunBerlin
Posts: 33
Joined: Tue Oct 27, 2020 1:19 pm
Location: India

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

Post by 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 
User avatar
robert
Posts: 4352
Joined: Fri Aug 21, 2015 10:57 am
Location: Netherlands

Re: Error on SQL Query execution in Oracle Database

Post by robert »

Arun,

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


Robert
XSharp Development Team
The Netherlands
robert@xsharp.eu
User avatar
ArneOrtlinghaus
Posts: 398
Joined: Tue Nov 10, 2015 7:48 am
Location: Italy

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

Post by 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
Posts: 33
Joined: Tue Oct 27, 2020 1:19 pm
Location: India

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

Post by ArunBerlin »

Hi Robert,

.NET framework version: v4.6
Dialect : Visual objects
Type : Windows Application
XSharp version : 2.12.2.0
ArunBerlin
Posts: 33
Joined: Tue Oct 27, 2020 1:19 pm
Location: India

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

Post by ArunBerlin »

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