Oracle database change notification not working

Oracle database change notification not working

Postby Feneck91 » Wed 26 Feb 2014 15:42

I'm trying to get all database notification to be able to reflect database changed into my application without having to put refresh button everywhere !

I have read and try this tutorial

I have execute on database :
Code: Select all
GRANT CHANGE NOTIFICATION TO GDD;
GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO GDD;

And create code :
Code: Select all
        OracleCommand selectCommand = null;
        public void SurveyAllTables()
        {
            // Create an OracleDependency object and set it to track the resul set returned by selectCommand.
            OracleConnection oracleConnection = DatabaseManagerContext.Connection as OracleConnection;
            oracleConnection.Open();

            selectCommand = new OracleCommand("Select * from \"GDD\".\"DMUtilisateur\"", oracleConnection);

            OracleDependency dependency = new OracleDependency(selectCommand);
            // Setting object-based change notification registration
            dependency.QueryBasedNotification = false;

            // When the IsNotifiedOnce property is true, only the first change 
            // of the traced result set will generate a notification.
            // Otherwise, notifications will be sent on each change 
            // during the selectCommand.Notification.Timeout period.
            selectCommand.Notification.IsNotifiedOnce = false;

            // Specifies whether notifications will contain information on rows changed.
            //selectCommand.Notification.RowLevelDetails = true;

            selectCommand.ExecuteNonQuery();
            dependency.OnChange += dependency_OnChange;
        }

        private void dependency_OnChange(object sender, OracleNotificationEventArgs eventArgs)
        {
            DataTable dt = eventArgs.Details;

            Console.WriteLine("The following database objects were changed:");
            foreach (string resource in eventArgs.ResourceNames)
                Console.WriteLine(resource);

            Console.WriteLine("\n Details:");
            Console.Write(new string('*', 80));
            for (int rows = 0; rows < dt.Rows.Count; rows++)
            {
                Console.WriteLine("Resource name: " + dt.Rows[rows].ItemArray[0]);
                string type = Enum.GetName(typeof(OracleNotificationInfo), dt.Rows[rows].ItemArray[1]);
                Console.WriteLine("Change type: " + type);
                Console.Write(new string('*', 80));
            }
        }


But no idea, when I add or remove item into GDD.DMUtilisateur table, I never receive notification. I'm working with oracle database installed in my own computer (local) 11g version and dotConnect 8.1 pro versions.

Any idea ?
Feneck91
 
Posts: 37
Joined: Mon 12 Aug 2013 13:52

Re: Oracle database change notification not working

Postby Pinturiccio » Thu 27 Feb 2014 15:33

Feneck91 wrote:I have read and try this tutorial

This is the tutorial for ODP.NET. For using OracleDependency feature read the corresponding Devart tutorial

In order for OracleDependency to work, add the following code to the end of the SurveyAllTables() function
Code: Select all
OracleCommand insertCommand=new OracleCommand("your insert query to DMUtilisateur table", oracleConnection);
insertCommand.ExecuteNonQuery();
Thread.Sleep(10000);


Please also note that the OracleDependency feature does not work in Direct mode.
Pinturiccio
Devart Team
 
Posts: 1891
Joined: Wed 02 Nov 2011 09:44

Re: Oracle database change notification not working

Postby Feneck91 » Thu 27 Feb 2014 16:03

I must put Direct to true for several month, devart don't know why it don't work when I set it to false !

Code: Select all
                // Init Connection string
                OracleConnectionStringBuilder oraCSB = new OracleConnectionStringBuilder();
                oraCSB.Direct = false; // http://forums.devart.com/viewtopic.php?f=1&t=29044&sid=de989fc560357196643526db2ba70821 <= Please also note that the OracleDependency feature does not work in Direct mode.
                oraCSB.Server = DataManager.Properties.Settings.Default.DatabaseHost;
                oraCSB.Port = int.Parse(DataManager.Properties.Settings.Default.DatabasePort);
                oraCSB.Sid = "orcl";
                oraCSB.UserId = "username";
                oraCSB.Password = "password";
                oraCSB.Unicode = true;
                oraCSB.Pooling = false;
                oraCSB.PersistSecurityInfo = true; // Important else the password is lost after use it : http://forums.devart.com/viewtopic.php?f=30&t=27911
                DatabaseManagerContext.Connection = new OracleConnection(oraCSB.ConnectionString);


Exception :
[System.Data.Entity.Core.ProviderIncompatibleException] = {"An error occurred while getting provider information from the database. This can be caused by Entity Framework using an incorrect connection string. Check the inner exceptions for details and ensure that the connection string is correct."}
HResult = -2146233087
InnerException = {"The provider did not return a ProviderManifestToken string."}

Any idea ?
Feneck91
 
Posts: 37
Joined: Mon 12 Aug 2013 13:52

Re: Oracle database change notification not working

Postby MariiaI » Fri 28 Feb 2014 12:41

Feneck91 wrote:Exception :
[System.Data.Entity.Core.ProviderIncompatibleException] = {"An error occurred while getting provider information from the database. This can be caused by Entity Framework using an incorrect connection string. Check the inner exceptions for details and ensure that the connection string is correct."}
HResult = -2146233087
InnerException = {"The provider did not return a ProviderManifestToken string."}
Any idea ?

Please specify the full call stack of this error with all inner exceptions.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Oracle database change notification not working

Postby Feneck91 » Fri 03 Apr 2015 09:59

The project was stopped for one year but I'm back.

I have read the How To Configure Oracle Instant Client.

Lot of evolutions for this problem but it don't work yet...
The first problem was : I haven't understood that I needed an Oracle Instant Client !

In my sample, I have installed the instant client into C:\instantclient_12_1 directory.

I list here all the problems I have fixed :
  1. Download Oracle Instant Client : I'm on Windows 7 64 bits system but my C# project is compiled into x86 mode (32 bits) (see Generation / target plateform = x86 into project settings) : you must download the same target as the software that use it (for me 32 bits) !
  2. If you are working on 64 bits system and your software is in 32 bits, you must install registry keys into HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ and not into HKEY_LOCAL_MACHINE\SOFTWARE\ else it cannot found registry entry.
  3. Like explain into tutorial : add TNS_ADMIN = C:\instantclient_12_1 into environment variable.
  4. Your registry sample must be corrected ! All backslash has been lost !
    Here the registry file to import (with Wow6432Node but you can remote it depending of your target plateform).
    Code: Select all
    Windows Registry Editor Version  5.00

    [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE]
    "ORACLE_HOME"="C:\\instantclient_12_1"
    @=""
    "ORACLE_HOME_NAME"="OraHome"
    "ORACLE_GROUP_NAME"="Oracle  -  OraHome"
    "NLS_LANG"=""

    [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\ALL_HOMES]
    "HOME_COUNTER"="1"
    "DEFAULT_HOME"="OraHome"
    "LAST_HOME"="0"
    @=""

    [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\ALL_HOMES\ID0]
    "NAME"="OraHome"
    "PATH"="C:\\instantclient_12_1"
    "NLS_LANG"=""

    [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\HOME0]
    "ORACLE_HOME"="C:\\instantclient_12_1"
    "ORACLE_SID"="ORCL"
    "ID"="0"
    "ORACLE_GROUP_NAME"="Oracle  -  OraHome"
    "ORACLE_HOME_NAME"="OraHome"
    "NLS_LANG"=""
    "ORACLE_HOME_KEY"="Software\\ORACLE\\HOME0"
  5. I use a class derived from DbConfiguration. I must create a IManifestTokenResolver class into my derived DbConfiguration class constructor :
    Code: Select all
        public class DatabaseManagerFactoryConfiguration : DbConfiguration
        {
            public DatabaseManagerFactoryConfiguration()
            {
                SetManifestTokenResolver(new CManifestTokenResolverOracle());
            }
        }


        internal sealed class CManifestTokenResolverOracle : IManifestTokenResolver
        {
            /// <summary>
            /// The default token resolver.
            /// </summary>
            private static readonly IManifestTokenResolver DefaultManifestTokenResolver = new DefaultManifestTokenResolver();

            public string ResolveManifestToken(DbConnection _dbConnection)
            {
                string strReturn;

                if (here test if your are trying to connect in direct mode or not)
                {   // Here : NOT DIRECT MODE
                    strReturn = "Oracle";//"Oracle, 11.2.0.1"; <-- Both seems working
                }
                else
                {
                    strReturn = DefaultManifestTokenResolver.ResolveManifestToken(_dbConnection);
                }

                return strReturn;
            }
        }


I still have an error message :
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA


Stack Trace :
à Devart.Data.Oracle.aq.a(b8 A_0, o A_1)
à Devart.Data.Oracle.OracleInternalConnection..ctor(b8 connectionOptions, OracleInternalConnection proxyConnection)
à Devart.Data.Oracle.ao.a(n A_0, Object A_1, DbConnectionBase A_2)
à Devart.Common.DbConnectionFactory.a(DbConnectionPool A_0, n A_1, DbConnectionBase A_2)
à Devart.Common.DbConnectionPoolGroup.a(DbConnectionPool A_0, DbConnectionBase A_1)
à Devart.Common.DbConnectionPool.a(DbConnectionBase A_0)
à Devart.Common.DbConnectionPool.GetObject(DbConnectionBase owningConnection)
à Devart.Common.DbConnectionFactory.b(DbConnectionBase A_0)
à Devart.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection)
à Devart.Common.DbConnectionBase.Open()
à Devart.Data.Oracle.OracleConnection.Open()
à Devart.Common.Entity.ae.a(DbConnection A_0)
à Devart.Data.Oracle.Entity.ay.a(OracleConnection A_0)
à Devart.Data.Oracle.Entity.OracleEntityProviderServices.GetDbProviderManifestToken(DbConnection connection)
à Devart.Data.Oracle.Entity.OracleEntityProviderServices.a(DbConnection A_0)
à Devart.Data.Oracle.Entity.OracleEntityProviderServices.DbCreateDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection)
à System.Data.Entity.Core.Common.DbProviderServices.CreateDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection)
à System.Data.Entity.Core.Objects.ObjectContext.CreateDatabase()
à System.Data.Entity.Migrations.Utilities.DatabaseCreator.Create(DbConnection connection)
à System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
à System.Data.Entity.Migrations.Infrastructure.MigratorBase.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
à System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)
à System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update()
à DataManagerDAL.DataManagerFactory.ADatabaseManagerFactory.InitDatabaseModel() dans d:\Dev\Topobase2\DataManager\trunk\03-source\DataManager\DataManagerDAL\DataManagerFactory\ADatabaseManagerFactory.cs:ligne 200
à DataManagerDAL.DataManagerFactory.DatabaseManagerFactoryOracle.InitDatabaseModel() dans d:\Dev\Topobase2\DataManager\trunk\03-source\DataManager\DataManagerDAL\DataManagerFactory\DataManagerFactoryOracle.cs:ligne 199
à DataManagerDAL.DataManagerFactory.ADatabaseManagerFactory.SetConnectionInformations(Nullable`1 _connectionInformations) dans d:\Dev\Topobase2\DataManager\trunk\03-source\DataManager\DataManagerDAL\DataManagerFactory\ADatabaseManagerFactory.cs:ligne 268
à DataManager.DataManagerApp.OnStartup(StartupEventArgs _eStartupEvent) dans d:\Dev\Topobase2\DataManager\trunk\03-source\DataManager\DataManager\DataManagerApp.xaml.cs:ligne 151


Questions :
  • Really need to add client folder to PATH ? It seem's to work without it
  • What is missing ? Need to copy some ora files into C:\instantclient_12_1 folder ?I have tried but nothing is working !

If I found a solution, I'll reply here !

EDIT : I have found a way set the OracleConnectionStringBuilder server like :
Code: Select all
oraCSB.Server = String.Format("(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})))",
                               _connectionInformations.Value.Server,
                               _connectionInformations.Value.Port,
                               _connectionInformations.Value.Sid
                             );

Will try to understand.
Modifying Path is not needed !

Don't forget to set access to your user :
Code: Select all
GRANT change notification TO the_user;
Feneck91
 
Posts: 37
Joined: Mon 12 Aug 2013 13:52

Re: Oracle database change notification not working

Postby Pinturiccio » Mon 06 Apr 2015 13:41

If we understood you correctly, the issue is solved. Could you please confirm it?
Pinturiccio
Devart Team
 
Posts: 1891
Joined: Wed 02 Nov 2011 09:44

Re: Oracle database change notification not working

Postby Feneck91 » Tue 07 Apr 2015 05:05

Yes but it is strange to must do all of this to make it work.
Your tutorial is a little bit simple ! I don't think I have the simplest way to make it work...

Another problem :
If OracleConnectionStringBuilder.Unicode is set to true on connection string, the OracleNotificationEventArgs is unreadable, only the first char is read on table name, rowid...

Patch : Get the connection, make a clone and set Unicode to False.
It's work but...

I get the rowid field ! I record only the ID (primary key) of each data into my software !
How can I get the ID in place of rowid ?
I use code first.
For the moment, this notification cannot be used to update the data displayed to the user because I cannot use the rowid !
Feneck91
 
Posts: 37
Joined: Mon 12 Aug 2013 13:52

Re: Oracle database change notification not working

Postby Pinturiccio » Wed 08 Apr 2015 11:00

Feneck91 wrote:How can I get the ID in place of rowid ?

If you got rowid, you can get the corresponding ID as well. Suppose, you have stored the rowid in a variable:
Code: Select all
String rowid


With it you can get ID with the following query:
Code: Select all
"select ID from TABLE_NAME where rowid='"+rowid+"'";
Pinturiccio
Devart Team
 
Posts: 1891
Joined: Wed 02 Nov 2011 09:44

Re: Oracle database change notification not working

Postby Feneck91 » Thu 09 Apr 2015 06:26

The problem is not this one !
The problem is : when I receive the OracleNotificationEventArgs it contains ONLY the ROWID.
To update GUI, I have the primary key only.

How to make the link between them ? OK, if update / insert I can make request !
But..... when row is deleted, how can I know Which ID is because making a request will not return deleted rows !

I have a way but it force to read ALL the rows to find wich rows are deleted !
I could find by making a command.ExecuteReader() BEFORE the notification and keep the reader while no notification fire !

I will make a post to explain how to use the Oracle Database change and may be some peaple will have another ideas...
Feneck91
 
Posts: 37
Joined: Mon 12 Aug 2013 13:52

Re: Oracle database change notification not working

Postby Pinturiccio » Fri 10 Apr 2015 14:24

Feneck91 wrote:But..... when row is deleted, how can I know Which ID is because making a request will not return deleted rows !

You can use the flashback feature. For example, use the following query:
Code: Select all
"select ID from TABLE_NAME AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE) where rowid = '" + rowid + "'";


For more information about flashback feature, please refer to http://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_fl.htm
Pinturiccio
Devart Team
 
Posts: 1891
Joined: Wed 02 Nov 2011 09:44

Re: Oracle database change notification not working

Postby Feneck91 » Mon 13 Apr 2015 13:20

It's not work all the time !
Create and destroying fast => the element is not found !
I have done this request :
Code: Select all
Select ID from TABLE VERSIONS BETWEEN TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE) AND SYSTIMESTAMP where rowid = '" + rowid + "'";


It's work better !
Feneck91
 
Posts: 37
Joined: Mon 12 Aug 2013 13:52


Return to dotConnect for Oracle