ClientId problem

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
aldare
Posts: 17
Joined: Mon 14 Dec 2009 08:55

ClientId problem

Post by aldare » Mon 28 Jun 2010 14:35

Hi,

I've tried to use the new ClientId property of OracleConnection and found out it is closing the connection. The state is Open before I try to set it up and Closed when it's been set.

Is it a bug or am I missing the point of it?

Thank you very much
Ales Lipensky

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Tue 29 Jun 2010 10:42

The ClientId property can be used to specify the user of the connection. It may be useful, e.g. to distinguish connections of different application instances or different tasks inside a single application instance. The connection should be closed to re-set the ClientId parameter.

aldare
Posts: 17
Joined: Mon 14 Dec 2009 08:55

Post by aldare » Tue 29 Jun 2010 13:00

When I run this code with non-devart OracleConnection object:

using(OracleConnection con = new OracleConnection("..."))
{
con.Open();

Console.WriteLine("Logon time: {0}", new OracleCommand(selectLogon, con).ExecuteScalar());

con.ClientId = System.Security.Principal.WindowsIdentity.GetCurrent().Name;

System.Threading.Thread.Sleep(5000);

Console.WriteLine("Get Client ID: [{0}]", new OracleCommand("SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') FROM DUAL", con).ExecuteScalar());

Console.WriteLine("Logon time: {0}", new OracleCommand(selectLogon, con).ExecuteScalar());

con.Close();
}


I get this output :

Logon time: 29.06.2010 13:54:07
Get Client ID: [KATASTR\zmeskalk]
Logon time: 29.06.2010 13:54:07

Wit that connection it doesn't close it and even doesn't reopen.
Your OracleConnection object is closing it. Why then?

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Wed 30 Jun 2010 12:11

Thank you for your suggestion, we will investigate the possibility of changing the OracleConnection behaviour on setting the ClientId property. We will inform you about the results.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 09 Jul 2010 17:54

The ClientId property is one of the properties used to distribute connections between different connection pools. Thus it is important that ClientId is not changed after the connection is placed to the pool. Could you please specify the scenario in which you need the connection to remain opened when ClientId is changed?

aldare
Posts: 17
Joined: Mon 14 Dec 2009 08:55

Post by aldare » Mon 12 Jul 2010 15:43

I changed the logic so whenever I open the ObjectContext, I set the ClientId of OracleConnection before I send any query to the database.
You can see it here :

OracleConnection connection = entities.GetConnection() as OracleConnection;
if (connection != null)
connection.ClientId = "userABC";

The problem is that when I try to ensure the ClientId has been set ...

var a = entities.ExecuteStoreQuery("SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') FROM DUAL", new object[] { });

I get a null string. Why? I am expecting to receive "userABC"

Thank you

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Tue 13 Jul 2010 08:50

The problem is the fact that entities.Connection is not an OracleConnection instance. It is an EntityConnection instance.
I have used the following code and have got the correct ClientId value:

Code: Select all

      using (Entities entities = new Entities()) {
        OracleConnection connection = (entities.Connection as EntityConnection).StoreConnection as OracleConnection;
        if (connection != null)
          connection.ClientId = "userABC";
        var a = entities.ExecuteStoreQuery("SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') FROM DUAL", new object[] { });
      }

aldare
Posts: 17
Joined: Mon 14 Dec 2009 08:55

Post by aldare » Tue 13 Jul 2010 10:23

It is a bit more complicated than this. I am using EFProviderWrappers (http://code.msdn.microsoft.com/EFProviderWrappers) and my ObjectContext contains method GetConnection() with that implementation :

public DbConnection GetConnection()
{
return ((DbConnectionWrapper)(this.UnwrapConnection())).WrappedConnection;
}

It actually is returning OracleConnection. I can set the breakpoint and it goes to the if(...) section and sets the ClientId correctly.

Anyway, I've tried it without the EFProviderWrappers and the result is the same - null value.

I can send you a printscreen with exactly your code and values in watch showing correctly set ClientId of OracleConnection and null value int the result of calling SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') FROM DUAL

I am a bit confused why it should work at your systems and not mine.

Thank you for any suggestions

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 14 Jul 2010 10:30

Could you please make a simple test project for this error reproducing?
Maybe the error is associated with the EFProvider Wrapper.
One more thing: maybe the query results simply were not materialized, try calling First() after the query.

aldare
Posts: 17
Joined: Mon 14 Dec 2009 08:55

Post by aldare » Wed 14 Jul 2010 14:49

Of course, I can. Where can I post it?

btw: The error is there even without the wrappers. And it has been materialized.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Thu 15 Jul 2010 10:23

As far as I understood, the problem is reproducible using the code I have posted.
Are you using the latest 5.70.146 build of dotConnect for Oracle?
What are your Oracle server and client versions?

aldare
Posts: 17
Joined: Mon 14 Dec 2009 08:55

Post by aldare » Thu 15 Jul 2010 11:30

Yes, it is reproducible. So probably it doesn't make sense to send it to you.
I am using 5.70.146.
Oracle server is 11.2g
I am not using any client, just direct access. Might that be the reason?

Thank you

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Thu 15 Jul 2010 12:19

Thank you for the additional information.
I have reproduced the problem with the Direct mode.
I will post here about the results of our investigation.

aldare
Posts: 17
Joined: Mon 14 Dec 2009 08:55

Post by aldare » Thu 15 Jul 2010 12:59

It is not critical for us, I've used the client and it works.
Thank you

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 03 Mar 2011 14:08

We have fixed the bug with the OracleConnection.ClientId property in the Direct mode. I will post here when the new build of dotConnect for Oracle is available for download.

Post Reply