Hello,

We upgraded LLBL from 3.x to 5.8.1, and we got a problem: procedure names became case-sensitive - LLBL adds double quotes to the procedure schema and name before execution.

We call procedures in many places and it is difficult for us to fix them everywhere, so we found a settings in LLBL, which makes names case-insensitive: DynamicQueryEngine.CaseInsensitiveNames. When we set it to 'true' - the procedures start working, that problem is solved.

But we got another error in LLBL-generated query. Here is part of the query:

SELECT ALERTS_ODS.ALARM_RESULTS.ALARM_ID AS AlarmId,
       ALERTS_ODS.ALARM_RESULTS.ALARM_RESULT_ID AS AlarmResultId,
       ALERTS_ODS.ALARM_RESULTS.RESULT_DT AS Date,
       ....       
FROM ALERTS_ODS.ALARM_RESULTS
WHERE ((ALERTS_ODS.ALARM_RESULTS.ALARM_RESULT_ID = .....))

The problem here is Date- it is a keyword in Oracle and, if used as a name, must always be in double quotes.

This date came from LLBL design - we map table field RESULT_DT to the entity field Date:

        /// <summary>The Date property of the Entity AlarmResult<br/><br/></summary>
        /// <remarks>Mapped on  table field: "ALARM_RESULTS"."RESULT_DT".<br/>Table field type characteristics (type, precision, scale, length): Date, 0, 0, 0.<br/>Table field behavior characteristics (is nullable, is PK, is identity): true, false, false</remarks>
        public virtual Nullable<System.DateTime> Date
        {
            get { return (Nullable<System.DateTime>)GetValue((int)AlarmResultFieldIndex.Date, false); }
            set { SetValue((int)AlarmResultFieldIndex.Date, value); }
        }

We had to reset CaseInsensitiveNames setting back to false and implement custom temporary workaround, but it would be nice if you fix it in future. From my understanding, even if the setting is ON - field aliases in generated queries must always be in double quotes, in the case above:

SELECT ALERTS_ODS.ALARM_RESULTS.ALARM_ID AS "AlarmId",
       ALERTS_ODS.ALARM_RESULTS.ALARM_RESULT_ID AS "AlarmResultId",
       ALERTS_ODS.ALARM_RESULTS.RESULT_DT AS "Date"

LLBLGen version: 5.8.1

Exception: Exception Message: An exception was caught during the execution of a retrieval query: ORA-00923: FROM keyword not found where expected. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Source: SD.LLBLGen.Pro.ORMSupportClasses

Stack Trace:    
at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
   
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.ExecuteSingleRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFields2 fieldsToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo)
  
 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityUsingFilter(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfos, IRelationPredicateBucket filter)
   
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityUsingFilter(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse, IRelationPredicateBucket filter, ExcludeIncludeFieldsList excludedIncludedFields)
   
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntity(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse, ExcludeIncludeFieldsList excludedIncludedFields)
  
 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.<>c__DisplayClass9_0.<FetchEntity>b__0()
   
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.PerformPostEntitySaveActions(Boolean insertActions, ActionQueueElement`1 element, EntityBase2 entityToSave, Boolean saveSucceeded)
   
at SD.LLBLGen.Pro.ORMSupportClasses.ActionQueryController.PerformPostPersistenceQueryExecuted(PackedActionQuery packedQuery, Int32 amountSaved)
  
 at SD.LLBLGen.Pro.ORMSupportClasses.ActionQueryController.PerformPostActionQueryWork(Int32 resultActionQuery, PackedActionQuery packedQuery)
   
at SD.LLBLGen.Pro.ORMSupportClasses.ActionQueryController.ExecuteElements(List'1 elementsToRun)
  
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.PersistQueue(List'1 queueToPersist, Boolean insertActions)
   
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, IPredicateExpression updateRestriction, Boolean recurse)
   
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.<>c__DisplayClass19_0.<SaveEntity>b__0()
  
 at Powerex.Alerts.DataAccess.DBHelper.SaveEntity(IEntity2 ent, Boolean refresh, Boolean recurse) 

Good point. However it's not something we can change in a point release: changing the behavior might cause problems in current code where case insensitive aliases are assumed and thus can be considered a breaking change. The workaround you likely have deployed is renaming the Date field in the entity (it can be different from the mapped table field), to e.g. AlarmDate ? That would result in the alias become AlarmDate instead of Date.

The case differences in procedure names, is that caused by something? I.e.: if you sync the project with the relational model data it'll obtain the procedure names from the schema, generating the code from that will generate the right names into the code, so calling them shouldn't lead to problems. But if casing differs per deployed schema (which can happen with e.g. oracle because everything defaults to upper case anyway without quotes) then it's indeed problematic.

We're thinking of adding another setting, to avoid the breaking change.

(edit) We've decided to go for an extra setting which enforces case sensitivity on aliases (or better: names not originating from the schema). It's a stop-gap but it avoids breaking changes and can offer more finegrained control over what to do. This setting will be included in v5.9. In the mean time we advice you to rename the 'Date' field in the entity to 'AlarmDate'. This will make the exception go away. You need to update your code a bit where you use 'Date' and now use AlarmDate. Not sure if that's possible tho in your case

Actually, no, the workaround was this:

internal class CustomDynamicQueryEngine : DynamicQueryEngine
    {
        public override string GetNewStoredProcedureName(string currentName)
        {
            return base.GetNewStoredProcedureName(NormalizeName(currentName));
        }

        public override string GetNewPerCallStoredProcedureName(string currentName)
        {
            return base.GetNewPerCallStoredProcedureName(NormalizeName(currentName));
        }

        private static string NormalizeName(string currentName)
        {
            //we need this to support case insensitivity for calling stored procedures from facts
            //DynamicQueryEngine.CaseInsensitiveNames setting has an issue and doesn't work in Alerts
            return currentName?.Contains("\"") == false 
                ? currentName.ToUpperInvariant() 
                : currentName;
        }
    }

I'm not saying about stored procedures which are used in the LLBLGen designer (we don't do that), but about calls of custom SPs using LLBLGen framework. This was actually a breaking change in LLBLGen, Oracle by default is case-insensitive, as it was in LLBL v3, but from v4 or v5 you made it case-sensitive, which caused the problem.

Yes it was a bug in old versions not to wrap the names in "", so we had to introduce that breaking change back then. In any case, that workaround can work, renaming the field in the entity (so not in the table) would too (In case you didn't realize that is possible)

Yes, we realize that, but we have quite a few such fields and changing them will lead to changes in many places in the code, so... it would be great if you fix this case-sensitivity issue in future. Thanks!