Oracle CQN in devArt does not provide RowID information

Oracle CQN in devArt does not provide RowID information

Postby lingster » Wed 08 Mar 2017 17:16

Hi,

I am trying to use the Oracle CQN/OracleDependency feature within DevArt/Oracle.
I create the following query:
Code: Select all
select id, last_update_datetime from my_table t where t.id = 12345
and register it for update notifications, then I update a row in the table above to generate a notification from Oracle.

The first problem I have is when I try to set the
Code: Select all
Notification.OracleQueryBasedNotificationMode == OracleQueryBasedNotificationMode.Guaranteed;

I get the following exception:
Code: Select all
ERROR: Devart.Data.Oracle.OracleException (0x80004005): ORA-29977: Unsupported column type for query registration in guaranteed mode
   at Devart.Data.Oracle.be.d(Int32 A_0)
   at Devart.Data.Oracle.de.a(Int32 A_0, s A_1)
   at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Data.Oracle.OracleCommand.ExecuteReader()
   at ...**myfunction**...


If I set the mode to
Code: Select all
OracleQueryBasedNotificationMode.BestEffort
then the callback is executed, but no rowid information is returned.
Here's the code I use to setup the callback:
Code: Select all
                    connection.Open();
                    OracleCommand cmd = new OracleCommand(sqlSelect, connection);
                    dep = new OracleDependency(cmd);
                   
                    cmd.Notification.IsNotifiedOnce = true; // only notify once for testing purposes, false will continuously listen for updates
                    cmd.Notification.Timeout = 0; // never timeout
                    cmd.Notification.IsPersistent = false; // persist the message in-memory queue, faster on the db than if set to true
                    cmd.Notification.QueryBasedNotificationMode = QMode;
                    cmd.Notification.RowLevelDetails = true;                       

                    dep.OnChange += new OnChangeEventHandler(callbackAction);
                    var dr = cmd.ExecuteReader();


I run the same setup using the Oracle Managed drivers and this works and executes my callback function and provides the rowids which have changed.

I'm using devart 9.2.x drivers with oracle 12.1 server and 12.1 OCI 64bit drivers.

Has anyone successfully been able to use OracleDependency notifications and let me know if I'm missing anything?
lingster
 
Posts: 2
Joined: Tue 22 Mar 2016 17:08

Re: Oracle CQN in devArt does not provide RowID information

Postby Pinturiccio » Mon 13 Mar 2017 14:52

We have reproduced the issue with the OracleQueryBasedNotificationMode.Guaranteed mode. We will investigate it and post here about the results as soon as possible.

lingster wrote:I run the same setup using the Oracle Managed drivers and this works and executes my callback function and provides the rowids which have changed.

Please create and send us a small complete test project, using ODP.NET Managed driver, and corresponding DDL/DML scripts, which reproduce the issue.
Pinturiccio
Devart Team
 
Posts: 2024
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle