Alter Session Problems

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
Oktane
Posts: 8
Joined: Wed 22 Sep 2010 14:16

Alter Session Problems

Post by Oktane » Mon 25 Jul 2011 17:28

Hi,

I am trying to set the schema name by using:
ALTER SESSION SET CURRENT_SCHEMA = ""
When my application is run (btw - I have modified the OnContextCreated on my EF DataContext class)

This gets executed before any of my commands are executed when my context is created, but I am getting no results returned. If I manually run the commands for SQL Developer -

ALTER SESSION SET CURRENT_SCHEMA = Schema
SELECT * FROM TABLE

and I get results from the DB with no issues.

When I run these operations in my code I get no results. I have monitored the activity using dbMonitor and I see the following

-Connection Open
-Alter Statement Prepared/Executed
-Close Connection
-Open Connection
-Connection is taken from Pool
-Select Statement Prepared/Executed
-Close Connection

All under the same OracleConnection object.

Any ideas?

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

Post by Shalex » Tue 26 Jul 2011 13:06

Please use one of the following OnContextCreated methods to use the same StoreConnection during context lifetime:

Code: Select all

partial void OnContextCreated()
{
            DbConnection conn = (Connection as EntityConnection).StoreConnection;
            conn.Open();
            ExecuteStoreCommand("ALTER SESSION SET CURRENT_SCHEMA = YourSchemaName");
}
OR

Code: Select all

partial void OnContextCreated()
{
            DbConnection conn = (Connection as EntityConnection).StoreConnection;
            conn.Open();
            conn.ChangeDatabase("YourSchemaName");
}
Notify us about the results.

HCRoman
Posts: 55
Joined: Wed 12 Aug 2009 05:47

better use StateChange to set schema and roles...??

Post by HCRoman » Wed 27 Jul 2011 11:22

Better is to track the StateChange event
This works for each connection open!

(
The code inside of the event handler can be one of the examples from Shalex too...
)

Code: Select all

partial void OnContextCreated() 
{ 
    Connection.StateChange += this.EntityConnection_StateChange;
} 

private void EntityConnection_StateChange(object sender, StateChangeEventArgs e)
{
   if (e.CurrentState == ConnectionState.Open)
   {
      DbConnection connection = ((EntityConnection)sender).StoreConnection;
      using (DbCommand command = connection.CreateCommand())
      {
         this.SetRoles(command);
         this.SetCurrentSchema(command);
      }
   }
}


Roman

Post Reply