Hello

I have an entityCollection ( generated from a Oracle View) The View (so the entity collection ) has only one DATE field.

The code ise so simple. I want to get count of some filtered data

KpkKontTrhIhrHizmetVwCollection ihracatViewCollection = new KpkKontTrhIhrHizmetVwCollection();
                IPredicateExpression filter = new PredicateExpression();
                filter.Add(KpkKontTrhIhrHizmetVwFields.KpkKontTarihceHizmetId == kpkKontTarihceHizmetIdList);

                int count = (ihracatViewCollection.GetScalar(Rota.DataAccess.KpkKontTrhIhrHizmetVwFieldIndex.KpkKontTarihceHizmetId, null, AggregateFunction.Count, filter)).ToInt();

when the GetScalar is executed, the query is generated but also an exception is thrown.

Generated Sql query: Query: SELECT * FROM (SELECT COUNT("YNA"."KPK_KONT_TRH_IHR_HIZMET_VW"."KPK_KONT_TARIHCE_HIZMET_ID") AS "KpkKontTarihceHizmetId" FROM "YNA"."KPK_KONT_TRH_IHR_HIZMET_VW" WHERE ( ( "YNA"."KPK_KONT_TRH_IHR_HIZMET_VW"."KPK_KONT_TARIHCE_HIZMET_ID" IN ( 1)))) WHERE rownum <= 1 Parameter: 1 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 23053920. Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ: no paging. Exception thrown: 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' in SD.LLBLGen.Pro.ORMSupportClasses.dll

if you copy the generated SQL and paste TOAD (oracle client applicatiın) and run, it just works fine

TOAD

KpkKontTarihceHizmetId

                 0

1 row selected.

it is not related to getScalar, if you run GetMulti() on the same entitycollection, same exception occurs.

somehow this exception was previously ignored in the llblgen classes version 2.0, as if this new version was start to not ignore.

details about ora exception http://www.dba-oracle.com/t_ora_01843_not_a_valid_month.htm

public struct KpkKontTrhIhrHizmetVwTransferObject : IComparable<KpkKontTrhIhrHizmetVwTransferObject> { public long IhrKonsimentoId { get; set; } public int IhrYukuIdLength { get; } public string SortField { get; set; } public DateTime IhrArdiyesizGirisTarihi { get; set; }

LGP FILE

<EntityDefinition Name="KpkKontTrhIhrHizmetVw" ObjectID="4bda2cbe-f8b5-444a-b700-a7eb0e15cd47">
        <Fields>
          <Field Name="IhrArdiyesizGirisTarihi" Type="datetime" IsOptional="true" FieldIndex="21">
            <OutputSettingValues>
              <SettingValues>
                <SettingValue Name="64:GenerateAsNullableType" Value="false" Type="3" />
              </SettingValues>
            </OutputSettingValues>
          </Field>

<EntityMapping EntityName=":KpkKontTrhIhrHizmetVw" TargetName="ynadev.arkas:YNA:KPK_KONT_TRH_IHR_HIZMET_VW">
          <FieldMappings>
            <FieldMapping FieldName="IhrArdiyesizGirisTarihi" TargetFieldName="IHR_ARDIYESIZ_GIRIS_TARIHI" />

<View Name="KPK_KONT_TRH_IHR_HIZMET_VW">
                    <Field Name="IHR_ARDIYESIZ_GIRIS_TARIHI" Ordinal="22" IsOptional="true" DbType="7" />
                  </Fields>

i'll appreciate if you could help

Partially good news, exception is caused by "OracleManagedDriver".

so, my final question , is there any option in the llblgen runtime configuration to ignore this spesific oracle exception?

   public int  ORA01843Test(TransactionHelper transactionHelper)
        {
            try
            {
                ITransaction transaction = transactionHelper.GetTransaction();
                const string sql = "SELECT * FROM (SELECT COUNT(\"YNA\".\"KPK_KONT_TRH_IHR_HIZMET_VW\".\"KPK_KONT_TARIHCE_HIZMET_ID\") AS \"KpkKontTarihceHizmetId\" FROM \"YNA\".\"KPK_KONT_TRH_IHR_HIZMET_VW\" WHERE ( ( \"YNA\".\"KPK_KONT_TRH_IHR_HIZMET_VW\".\"KPK_KONT_TARIHCE_HIZMET_ID\" IN (23053920)))) WHERE rownum <= 1 ";

                OracleConnection cnn = (OracleConnection)transaction.PhysicalTransaction.Connection;
                OracleCommand cmd = cnn.CreateCommand();
                cmd.Transaction = (OracleTransaction)transaction.PhysicalTransaction;
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                cmd.BindByName = true;
                int count = cmd.ExecuteScalar().ToInt();
                return count;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

Hello the problem is caused by the oracle odp settings. I need to run the method below AFTER OPEN AN ORACLE CONNECTION.

private void SetSessionGlobalization(Oracle.ManagedDataAccess.Client.OracleConnection oracleConnection)
        {
            OracleGlobalization info = oracleConnection.GetSessionInfo();
            info.DateFormat = "DD/MM/RRRR";
            info.DateLanguage = "TURKISH";
            info.TimeStampFormat = "DD/MM/RRRR HH24:MI:SSXFF";
            info.TimeStampTZFormat = "DD/MM/RRRR HH24:MI:SSXFF TZR";
            //info.Calendar: "GREGORIAN" default ile aynı
            //Comparison: "BINARY" default ile aynı
            info.Currency = "TL";
            info.DateFormat = "DD/MM/RRRR";
            info.DateLanguage = "TURKISH";
            info.DualCurrency = "YTL";
            info.ISOCurrency = "TURKEY";
            info.Language = "TURKISH";
            //LengthSemantics: "BYTE" default ile aynı
            //NCharConversionException: "BYTE" default ile aynı
            info.NumericCharacters = ",.";
            info.Sort = "TURKISH";
            info.Territory = "TURKEY";
            info.TimeStampFormat = "DD/MM/RRRR HH24:MI:SSXFF";
            info.TimeStampTZFormat = "DD/MM/RRRR HH24:MI:SSXFF TZR";
            oracleConnection.SetSessionInfo(info);
        }

**Because i need to run custom method after opening the connection, i need oracleConnection.Open() row in the template. But could not find it. I've creation of the connection but opening.

Can you advise me how can i set these parameters right after the an oracle connection is open.**

/// <summary>
        /// Creates a new OracleConnection
        /// </summary>
        /// <param name="connectionString">Conectionstring To use</param>
        /// <returns>A ready to use, closed, sqlconnection object</returns>
        public static OracleConnection CreateConnection(string connectionString)
        {
            return new OracleConnection(connectionString);
        }

/// <summary>
        /// Creates a new closed OracleConnection object based on the connection string read from the *.config file of the appdomain.
        /// The connection string is stored in a key with the name defined in the constant connectionKeyString, mentioned above.
        /// </summary>
        /// <returns>A ready to use, closed, sqlconnection object</returns>
        public static OracleConnection CreateConnection()
        {
            if(ActualConnectionString==string.Empty)
            {
<# Rota_ConnectionStringDecryptionTemplate #>
            }

            return CreateConnection(ActualConnectionString);
        }

/// <summary>
        /// Determines which connection to use: the connection held by the passed in transaction (if any) or a new one (if no Transaction was passed in)
        /// </summary>
        /// <param name="containingTransaction">A transaction the caller participates in. If null, the caller is not participating in a transaction</param>
        /// <returns>A ready to use connection object</returns>
        public static IDbConnection DetermineConnectionToUse(ITransaction containingTransaction)
        {
            if(containingTransaction!=null)
            {
                return containingTransaction.ConnectionToUse;
            }
            else
            {
                return CreateConnection();
            }
        }

i've solved the problem by modifing "dbUtils.template". I implemented a method that subscribes to the connection opening event.

public static OracleConnection CreateConnection(string connectionString)
{
  var oracleConnection = new OracleConnection(connectionString);
  oracleConnection.ConnectionOpen += ConOpenCallback;
  return oracleConnection;
}

public static void ConOpenCallback(OracleConnectionOpenEventArgs eventArgs)
{
 var oracleConnection = eventArgs.Connection;
 SetSessionGlobalization(oracleConnection);
}

Indeed, the db Utils is the way to set those settings. Good you figured it out. Thanks for posting the solution as well.

DbUtils was a template we used before v5, but it's deprecated (and removed from the templates we ship). I have no idea where dbutils.template comes from in your v5.5 install, but it's not shipped with our installer.

It's not sufficient to do it this way, I'm afraid.

The way to do this is simple: create a partial class of CommonDaoBase and override CreateConnection. First call the base method, then alter the DbConnection object you got back from the base method.

No need for template changes.

The way to do this is simple: create a partial class of CommonDaoBase and override CreateConnection. First call the base method, then alter the DbConnection object you got back from the base method.

You're right. As i migrate the old 2.0 project DbUtils comes with the old design. I refactor and i implement the create connection override as you describe. I can delegate Connection Event and trigger the code as the connection opens.

Everyting go well until this point. After we go the production, we have notice that

the odp command oracleConnection.SetSessionInfo(info); prevents the disposing of the current connection (that is opened by llblgen code) and quickly max. pool size limit is reached and this causes the exception below

I'm absolutly aware of that is is mainly about Oracle Managed ODP driver. Somehow this "SetSessionInfo" command and the way we open and close the oracle connection in the DaoBase class makes this happen.

I've written this message to inform you of the situation. I'd appreciate if you have any comment that can benefit us for the solution. If you have not any comment, it's ok.

thanks Serkan

[OracleException (0x80004005): Pooled connection request timed out] OracleInternal.ConnectionPool.PoolManager3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch) +11389 OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch) +956 OracleInternal.ConnectionPool.OracleConnectionDispenser3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword, OracleConnection connRefForCriteria) +1634 Oracle.ManagedDataAccess.Client.OracleConnection.Open() +3500 SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteSingleRowRetrievalQuery(IRetrievalQuery queryToExecute, ITransaction containingTransaction, IEntityFields fieldsToFill, IFieldPersistenceInfo[] fieldPersistenceInfos) +181 SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformFetchEntityAction(IEntity entityToFetch, ITransaction containingTransaction, IPredicateExpression selectFilter, IPrefetchPath prefetchPathToUse, Context contextToUse, ExcludeIncludeFieldsList excludedIncludedFields) +391 SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.FetchExisting(IEntity entityToFetch, ITransaction containingTransaction, IPrefetchPath prefetchPathToUse, Context contextToUse, ExcludeIncludeFieldsList excludedIncludedFields) +50 Rota.DataAccess.EntityClasses.DilEntityBase.Fetch(Int64 id, IPrefetchPath prefetchPathToUse, Context contextToUse, ExcludeIncludeFieldsList excludedIncludedFields) +148 Rota.DataAccess.EntityClasses.DilEntityBase.InitClassFetch(Int64 id, IValidator validator, IPrefetchPath prefetchPathToUse) +81 Rota.DataAccess.EntityClasses.DilEntityBase..ctor(Int64 id) +35 Yna.AuthenticationHelpers.AuthenticationHelper.PrepareSession(KullaniciEntity kullaniciEntity, String password)

The docs on SetSessionInfo() suggest dispose etc. should work fine: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/odpnt/ConnectionSetSessionInfo.html#GUID-8F565C3E-8C54-4A5D-A890-88EE15C6D7F1

The example shows that at least. The connection is disposed after usage has been completed in selfservicing. You keep the connection object around with custom code? If you use the OrmProfiler (free for llblgen pro users, see My Account -> Downloads -> Ormprofiler) you can see if connections are disposed or not. They're disposed in your case?

Dispose on a connection should move it back to the pool. It might be that the info you set on the connection makes it 'unique' but I doubt that's the case here...

(edit) additionally, it might be the connection pool might not be big enough, default is 100, but if you don't have more than 100 simultaneous connections, then that's not the cause.

Another reason might be you start actions in a loop which all use their own connection, while they should be using a shared one in a transaction (so you have to add the collection or entity first to a transaction object). That's a downside of selfservicing, but again, without the SetSessionInfo() it should occur as well, so it's odd.

I do assume you call the SetSessionInfo() on all connections?

First of all, sorry for comparatively late answer. It's a hard day

We called the SetSessionInfo() on all connections and that was a mistake. We did rollback.

at the end of the day

Adding " <globalization culture="TR-TR" in the web config

and calling

Thread.Current.Culture="TR-TR" where we pass LLGBLGEN code and use direct Oracle connection solve the problem.

Thanks for helping Fellas!

Regards, Serkan

Glad you found a solution!