App hangs on ExecuteNonQuery on Stored procedure

App hangs on ExecuteNonQuery on Stored procedure

Postby awaitzbe » Wed 13 Jul 2011 08:25

Hi,
I am running a WPF client with Entity Framework that connects to a Oracle Server 11.1.0.7, using dotConnect for Oracle 6.30.185. The connection is in Direct Mode and ConnectionTimeout = 0.

Sometimes, when launching the program from deployed version (not in debugging mode) the app hangs / freezes when calling Stored procedures which calls command.ExecuteNonQuery(). The app never restore again. This started to happen only after I upgraded dotConnect for Oracle to 6.30.xx.

When attaching a debugger, the error says:
{"Network error:: An established connection was aborted by the software in your host machine"}
"Network error: 207"

or other Network problem.

The Call Stack says:
System.Data.Entity.dll!System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands
(System.Data.EntityClient.EntityCommand entityCommand, System.Data.CommandBehavior behavior) + 0x1e0 bytes
System.Data.Entity.dll!System.Data.EntityClient.EntityCommandDefinition.Execute
(System.Data.EntityClient.EntityCommand entityCommand = {System.Data.EntityClient.EntityCommand}, System.Data.CommandBehavior behavior) + 0x4c bytes
System.Data.Entity.dll!System.Data.EntityClient.EntityCommand.ExecuteReader(System.Data.CommandBehavior behavior = SequentialAccess) + 0x2d bytes
System.Data.Entity.dll!System.Data.EntityClient.EntityCommand.ExecuteScalar(System.Func resultSelector = {Method = {System.Reflection.RuntimeMethodInfo}}) + 0x2a bytes
System.Data.Entity.dll!System.Data.EntityClient.EntityCommand.ExecuteNonQuery() + 0x5c bytes
opsecdb1.exe!SecModel.SecEntities.Addblob(decimal? REQ_DETAIL_ID, byte[] REQ_RAW_DATA, ref decimal? CUR_ID = null) + 0x1a9 bytes

Can you help me on that? Should I downgrade the dotConnect version?

Thanks!
awaitzbe
 
Posts: 16
Joined: Thu 05 Nov 2009 14:41

Postby awaitzbe » Wed 13 Jul 2011 08:56

On the latest revision history, it says: "Freezing of the GC finalizer thread at unexpected connection closure while reading XMLType is fixed". Is there any connection between the 2 problems?
awaitzbe
 
Posts: 16
Joined: Thu 05 Nov 2009 14:41

Postby Shalex » Thu 14 Jul 2011 16:51

1. Try setting the "Default Command Timeout=0;" connection string parameter.
2. Can you reproduce the problem via plain ADO.NET (not via Entity Framework)?
3. Please send us the corresponding DDL/DML script to execute your stored procedure in our environment.
4. Specify your connection string (roughly, without credentials).
Shalex
Devart Team
 
Posts: 7836
Joined: Thu 14 Aug 2008 12:44

Postby awaitzbe » Sun 17 Jul 2011 09:18

This is my connection string.

Code: Select all
private string BuildConnectionString(string password)
{
    OracleConnectionStringBuilder oraCSB = new OracleConnectionStringBuilder();
    oraCSB.Direct = true;
    oraCSB.Port = 1529;
    oraCSB.Sid = "DEPTDB";
    oraCSB.UserId = Environment.UserName.ToLower();
    oraCSB.Password = password;
    oraCSB.Unicode = true;
    oraCSB.PersistSecurityInfo = true;
    oraCSB.Server = "localhost";
    oraCSB.ConnectionTimeout = 0;
    oraCSB.DefaultCommandTimeout = 0;

    // Initialize the EntityConnectionStringBuilder.
    EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
    entityBuilder.Provider = "Devart.Data.Oracle";
    entityBuilder.ProviderConnectionString = oraCSB.ConnectionString;

    // Set the Metadata location.
    entityBuilder.Metadata = @"res://*/DEPTModel.csdl|
                               res://*/DEPTModel.ssdl|
                               res://*/DEPTModel.msl";
    return entityBuilder.ToString();
}


Note the DefaultCommandTimeout = 0 did not solve the problem.
Also sent you the corresponding DDL/DML script with stored procedure to reproduce in your environment.
awaitzbe
 
Posts: 16
Joined: Thu 05 Nov 2009 14:41

Postby Shalex » Mon 18 Jul 2011 10:29

We have answered you by e-mail.
Shalex
Devart Team
 
Posts: 7836
Joined: Thu 14 Aug 2008 12:44

Postby awaitzbe » Tue 19 Jul 2011 09:26

OCI mode won't help because my customers won't install Oracle client in their machines.

But I think that since the server is auditing stored procedures, it might be causing the problem.
AUDIT_TRAIL=XML, EXTENDED

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS;
AUDIT NOT EXISTS;

When I made:
NOAUDIT EXECUTE PROCEDURE;
It seems to work.... I will keep looking whether it really solved the problem.
awaitzbe
 
Posts: 16
Joined: Thu 05 Nov 2009 14:41

Re: App hangs on ExecuteNonQuery on Stored procedure

Postby SUKHEN » Mon 23 Feb 2015 11:34

could you please tell me how you resolved your issue. I have the same problem while running ExecuteNonQuery application hang and control never come back to next line for ExecuteNonQuery.
SUKHEN
 
Posts: 1
Joined: Mon 23 Feb 2015 11:31

Re: App hangs on ExecuteNonQuery on Stored procedure

Postby Pinturiccio » Mon 02 Mar 2015 15:21

You are using the Direct mode, aren't you?

"Network error" is an internal error of our provider, which says "connection is not established" or "unexpected server response". It may be caused by the following reasons:
a) code tries to use a connection, which was previously closed or being currently used in another thread;
b) a server terminates the connection (server's problem);
c) a bug in the provider.

General recommendations:
1) avoid using the same OracleConnection in several threads at the same time because an OracleConnection instance is not guaranteed to be thread safe;
2) try using the "Validate Connection=true;" connection string parameter to validate connections that are being taken from the pool;
3) upgrade to the latest (8.4.359) version of dotConnect for Oracle. It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
4) try using the OCI mode (via Oracle Client).

We need to reproduce the problem in our environment to find out the true reason. If possible, try to localize the issue and send us a small test project with the corresponding DDL/DML script.
Pinturiccio
Devart Team
 
Posts: 2045
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle