1 Reply Latest reply: Nov 5, 2010 9:39 PM by gdarling - oracle RSS

    mysterious DB problem

    user10404620
      Hi All

      Today I have a mysterious problem where I wish to ask you a quetion! Our Asp.Net Application is consuming several of WCF Service to get the Data for the Application. To ensure that the User is the correct user he has to send a cookie in the Soap Header of the Service. In this cookie is a Guid wish we use to run a select on the Database to reload the Information for this User from our Caching table. The Problem now is that after let me say 1 or 2 days the Application is no longer able to Select any data from the DB. Every time we say ExecuteReader we get zero Results from the DB.

      When this happen while I’m debugging I can stop at the ExecuteReader line copy the select to a DB tool and it returns results! The biggest Problem with this is that the error is not adjustable we just have to wait until it happens. My question is does anyone had this miysterious problem already or can some tell where to look for a Solution for this?

      As DBProvider we are using the System.Data.Oracle and Oracle.DataAccess.Client
      It happens on a DataBase 10.2.0.3 or on 11g
      .Net Frameworkversion is 3.5 SP1 with all updates


      Regards Michael



      The code that will be executed looks like this one:
      DbProviderFactory factory = DbProviderFactories.GetFactory(oCtlr.DBProvider);
      IDbConnection con = factory.CreateConnection();
      con.ConnectionString = oCtlr.DBConnection;
      IDbCommand com = con.CreateCommand();
      com.CommandTimeout = 0;
      com.CommandText = “ SELECT SCACHINGID , BCACHINGOBJECT, DTLASTACCESSEDTIME, SSLIDINGDURATION, NTYPE FROM CACHING WHERE ( (NTYPE = :Param0 ) AND (SCACHINGID = :Param1 ) )“;
      IDataParameter param = factory.CreateParameter();
      param.DbType = System.Data.DbType.Int64;
      param.Value = 1
      oCmd.Parameters.Add(param);
      IDataParameter param = factory.CreateParameter();
      param.DbType = System.Data.DbType.String;
      param.Value = 'f314c6d0-471b-46a0-8c67-baef2ded224a'
      oCmd.Parameters.Add(param);


      The Select looks like:
      SELECT SCACHINGID , BCACHINGOBJECT, DTLASTACCESSEDTIME, SSLIDINGDURATION, NTYPE FROM CACHING WHERE ( (NTYPE = :Param0 ) AND (SCACHINGID = :Param1 ) )
      Param0=1;
      Param1='f314c6d0-471b-46a0-8c67-baef2ded224a'
        • 1. Re: mysterious DB problem
          gdarling - oracle
          Hi,

          You'd be better off posting this in the ODP.NET forum (ODP.NET This forum is for the ASP.NET providers (role, membership, etc), but here's my thoughts..

          1) you said "we are using the System.Data.Oracle and Oracle.DataAccess.Client". You're using both? At the same time? Or are you saying you've tried one, then the other, and the behavior occurs with both? Which one do you see that behavior with?
          2) When you execute the same query from "a DB tool", are you passing bind variables, or did you hard code it?
          3) When you test from the DB tool, you've confirmed you're passing the same values by checking the content of the Parameters collection as opposed to passing a value you have stored in a variable; are sure you're passing what you think you're passing? You didn't accidentally forget to Clear parameters from a previous use of the command or anything?
          4) When it works, it returns 1 row then I assume? ie, you're not expecting 500 rows and get none?
          5) Once the behavior occurs, it keeps occurring until you restart the app, or...? All calls to ExecuteReader give that behavior? Or only calls for that specific query, trying to find that specific value? How do you recover?
          6) Once the behavior occurs in your production app, if you run a small console app that executes only that query looking for the specific value, does it succeed?


          I can't say as I've ever heard of anyone having that problem before, so I'm just throwing our random ideas here to try to help focus in on the behavior.

          Hope it helps, but realize it probably doesn't.
          Greg