Hi, I am testing LLBLGen and I ran in a strange issue. Whenever I run this query I get an error (reported below):

var validUser = (from x in metadata.CTAC_USERS where (x.USERID.Trim().ToUpper() == aUserId.Trim().ToUpper()) && (x.PASSWORD.ToUpper() == PasswordManager.EncodePassword(aPassword)) select x).First();

Error:

An exception was caught during the execution of a retrieval query: ORA-00933: SQL command not properly ended . Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

I tried to remove First but the problem persisted. As soon as I removed the calls to Trim and ToUpper, the query ran just fine.

Any advice or this is a bug?

Thanks a lot, Alessandro Federici

Mmm. I first thing this isn't supported for Oracle, but I didn't found any limitation here. Could you please update to the latest LLBLGenPro Runtime Library version and try again? Also, would be helpful post the Generated SQL.

Hi, here's what I found out. Given:

var validUsers = (from x in metadata.CTAC_USERS where (x.USERID.Trim().ToUpper() == aUserId.Trim().ToUpper()) && (x.PASSWORD == PasswordManager.EncodePassword(aPassword)) select x);

                var validUser = validUsers.First();

Generated Sql query:

    Query: SELECT * FROM (SELECT DISTINCT "LPLA_1"."RECNUM", "LPLA_1"."USERID", "LPLA_1"."FIRSTNAME", "LPLA_1"."MIDDLENAME", "LPLA_1"."LASTNAME", "LPLA_1"."PASSWORD", "LPLA_1"."LASTON", "LPLA_1"."ENABLED", "LPLA_1"."DEPARTMENT_REC" AS "GROUP_REC", "LPLA_1"."USER_GROUP_REC" FROM "COBRA"."CTAC_USERS" "LPLA_1" WHERE ( ( ( ( ( ( UPPER(TRIM("LPLA_1"."USERID"))) = :LPFA_11) AND ( "LPLA_1"."PASSWORD" = :PASSWORD2))))))) WHERE rownum <= 1
    Parameter: :LPFA_11 : AnsiString. Length: 9. Precision: 0. Scale: 0. Direction: Input. Value: "AFEDERICI".
    Parameter: :PASSWORD2 : AnsiString. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "2E8164CB1177A22D635AD7F29886FDA4".

Apparently the problem is an extra ")" character right before the WHERE rownum<=1 If I remove that character, the query runs just fine. That said, I am not sure why it needs a DISTINCT clause since I didn't specify any

It's an error in the mapping of string.Trim() to the db function TRIM() in the oracle DQE: accidently an extra ) was placed inside the function template.

I'll create a new build for you. Distinct is emitted because you set a limiter (1) through the First() call. The engine which produces the query then gets a signal to avoid duplicates. As it doesn't know the set of fields to fetch (it gets a list of fields to emit into the query) form a unique entity, it can't check whether duplicates could occur, hence the distinct.

This might be a bit restrictive perhaps: '1' as limiter doesn't really matter when duplicates are occuring inside the query or not. We could opt for a more optimal path there, where if the limiter is > 1 -> distinct is required, but if it's 1 or 0, it doesn't matter (distinct filtering is used to avoid materializing duplicate entity objects when fetching a set of entities).

Excellent! Thanks for the prompt reply. While the fact that my very first LLBLGen query failed did not make me very happy, I am excited to see that I got quick support.

Let me know when you have a new build I can download.

kingalef wrote:

Excellent! Thanks for the prompt reply. While the fact that my very first LLBLGen query failed did not make me very happy, I am excited to see that I got quick support.

I can imagine it must have been a real bummer. I'm sorry you had to deal with that crash. We carefully examined every mapping and as there are so many, it's a bit hard to test them all in all situations. Anyway, it's fixed now. I've attached all 3 oracle DQE dlls as I didn't know which one you're using. Please copy the dlls into the runtimelibraries\DotNET20\ folder in the demo installation and rebuild your project. vs.net should then pick up the new dlls. (which have fileversion date 2.6.9.0203)

In the worst case, you could have overriden the mapping yourself, please see this page

Let me know when you have a new build I can download.

I've attached the zip to this post (click the paperclip )

Stuff happens, no biggie I'll try the new DLLs and let you know if there are any problems.

Once again, thanks a lot for great support.

Are you guys aware that this is still broken in 2.6 (TRIM still has an extraneous parenthesis)?

Also, any reason that the standard function mapping is disabled in the Oracle provider (that is, the call to CreateFunctionMappingStore() that is at the end of static DynamicQueryEngine constructor is missing)?

Thanks, Carl

cnelson1 wrote:

Are you guys aware that this is still broken in 2.6 (TRIM still has an extraneous parenthesis)?

Also, any reason that the standard function mapping is disabled in the Oracle provider (that is, the call to CreateFunctionMappingStore() that is at the end of static DynamicQueryEngine constructor is missing)?

Thanks, Carl

Please post the Runtime library build nr. ? and also please specify an example, as it might be related to other functions being used at the same time.

Also the code is right there in the Oracle DQE:

/// <summary>
/// Static CTor for initializing TraceSwitch
/// </summary>
static DynamicQueryEngine()
{
    DynamicQueryEngine.Switch = new TraceSwitch("OracleDQE", "Tracer for Oracle Dynamic Query Engine");

    // load and initialize schema overwrites
    _schemaOverwrites = new Dictionary<string,string>();
    NameValueCollection schemaOverwriteDefinitions = (NameValueCollection)ConfigurationManager.GetSection( "oracleSchemaNameOverwrites" );
    if(schemaOverwriteDefinitions!=null)
    {
        for(int i=0;i<schemaOverwriteDefinitions.Count;i++)
        {
            string key = schemaOverwriteDefinitions.GetKey(i);
            string value = schemaOverwriteDefinitions.Get(i);
            if(_schemaOverwrites.ContainsKey(key))
            {
                continue;
            }
            _schemaOverwrites.Add(key, value);
        }
    }

    // load the following settings if specified: trigger sequence setting, ansiJoins setting
    _triggerSequences = false;      // default
#if ANSI
    _ansiJoins = true;              // default for 10G or when ANSI is specified during compilation.
#else
    _ansiJoins = false;             // default for normal builds, which have to target 8i as well.
#endif

    object valueFromConfig = ConfigurationManager.AppSettings["OracleTriggerSequences"];
    if(valueFromConfig != null)
    {
        _triggerSequences = Convert.ToBoolean(valueFromConfig);
    }
    valueFromConfig = ConfigurationManager.AppSettings["OracleAnsiJoins"];
    if(valueFromConfig != null)
    {
        _ansiJoins = Convert.ToBoolean(valueFromConfig);
    }

    // create the functionmapping store used for Linq usage. 
    CreateFunctionMappingStore();
}

so I'm not sure which sourcecode you're looking at?

Oops...sorry, I have 2.6, but not the LATEST 2.6

Never mind...my apologies.