CreateParameterName is called twice causing parameter names @p2, @p4, @p6, etc.

Posts   
 
    
SanderF
User
Posts: 125
Joined: 11-Dec-2006
# Posted on: 24-Jan-2024 11:48:41   

Hi, we are using LLBLGen Pro 5.10 and are writing a FreePredicate class with parameters support.

[Serializable]
    public class FreePredicate : SD.LLBLGen.Pro.ORMSupportClasses.Predicate
    {
        private readonly string _filter;
        private readonly object[] _args;

        public FreePredicate(string filter) => _filter = filter;

        public FreePredicate(string filter, params object[] args)
        {
            _filter = filter;
            _args = args;
        }

        public override string ToQueryText()
        {
            if (DatabaseSpecificCreator == null)
                DatabaseSpecificCreator = new SqlServerSpecificCreator();

            Parameters.Clear();

            var query = _filter;

            var i = 0;
            if (_args != null)
            {
                foreach (var arg in _args)
                {
                    var value = arg.ToString();

                    // Left %
                    if (query.Contains("%{" + i.ToString() + "}"))
                    {
                        query = query.Replace("%{" + i.ToString() + "}", "{" + i.ToString() + "}");
                        value = "%" + value;
                    }

                    // Right %
                    if (query.Contains("{" + i.ToString() + "}%"))
                    {
                        query = query.Replace("{" + i.ToString() + "}%", "{" + i.ToString() + "}");
                        value = value + "%";
                    }

                    // Remove single quotes decoration
                    if (query.Contains("'{" + i.ToString() + "}'"))
                    {
                        query = query.Replace("'{" + i.ToString() + "}'", "{" + i.ToString() + "}");
                    }
                    Parameters.Add(DatabaseSpecificCreator.CreateParameter(System.Data.ParameterDirection.Input, value));

                    i++;
                }
            }

            return string.Format(query, Parameters.Select(p => p.ParameterName).ToArray());
        }

        public override string ToQueryText(bool inHavingClause) => ToQueryText();
    }

If we lookup the parameter name created by the function "DatabaseSpecificCreator.CreateParameter(System.Data.ParameterDirection.Input, value)", then the names are @p2, @p4, @p6, etc.. instead of just @p1, @p2, @p3, etc.

If I am correct, then this is caused by the following code in DbSpecificCreatorBase.cs:


    //
    // Summary:
    //     Creates a name usable for a Parameter, based on "p" and a unique marker, prefixed
    //     with the parameter prefix for this DQE available in ParameterPrefix.
    //
    // Returns:
    //     Usable parameter name.
    protected string CreateParameterName()
    {
        _uniqueMarker++;
        return ParameterPrefix + "p" + _uniqueMarker;
    }

    //
    // Summary:
    //     Creates a new parameter instance. It sets the value but doesn't set the type
    //     so the parameter type depends on the value.
    //
    // Parameters:
    //   valueToSet:
    //     The value to set.
    //
    //   parameterName:
    //     Name of the parameter. By default the empty string, which means it will create
    //     a new name
    //
    // Returns:
    //     ready to use parameter object
    protected DbParameter CreateParameterInstance(object valueToSet, string parameterName = "")
    {
        object obj = valueToSet;
        if (obj != null && obj.GetType().IsEnum)
        {
            obj = Convert.ChangeType(obj, Enum.GetUnderlyingType(obj.GetType()));
        }

        object realValueToUse;
        string parameterType = DetermineDbTypeNameForValue(obj, out realValueToUse);
        DbParameter dbParameter = CreateParameterInstance(parameterType);
        dbParameter.ParameterName = (string.IsNullOrEmpty(parameterName) ? CreateParameterName() : parameterName);
        dbParameter.Value = realValueToUse ?? DBNull.Value;
        return dbParameter;
    }

The line "DbParameter dbParameter = CreateParameterInstance(parameterType);" calls the following method:

    //
    // Summary:
    //     Creates a new parameter instance.
    //
    // Parameters:
    //   parameterType:
    //     Type of the parameter.
    //
    // Returns:
    //     ready to use parameter object
    protected DbParameter CreateParameterInstance(string parameterType)
    {
        DbParameter dbParameter = FactoryToUse.CreateParameter();
        dbParameter.ParameterName = CreateParameterName();
        SetParameterType(dbParameter, parameterType);
        return dbParameter;
    }

Note that the line "dbParameter.ParameterName = (string.IsNullOrEmpty(parameterName) ? CreateParameterName() : parameterName);" calls CreateParameterName() when parameterName is empty (as is in our case). Thus CreateParameterName() is called twice, one time in method "CreateParameterInstance(object valueToSet, string parameterName = "")" and the other in method "protected DbParameter CreateParameterInstance(string parameterType)". The CreateParameterName() method increments the _uniqueMarker each time the method is called.

Is this a bug or a feature ;-) ?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39712
Joined: 17-Aug-2003
# Posted on: 25-Jan-2024 09:19:53   

It indeed looks like it is called twice. We'll look into it

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39712
Joined: 17-Aug-2003
# Posted on: 25-Jan-2024 09:48:17   

Changing this caused a lot of tests to fail because the sql string produced was different (as the parameters changed). This is a breaking change as users who test their code with the same technique (compare the sql string produced with an expected one) will have breaking tests and that can be a lot of work to correct it.

We won't change the outcome of the parameter names as it'll potentially produce a lot of work for users. We'll make a change internally so the parameter names stay the same but it won't create a redundant name. As this can't be done with an architectural change, we'll postpone this.

Frans Bouma | Lead developer LLBLGen Pro
SanderF
User
Posts: 125
Joined: 11-Dec-2006
# Posted on: 25-Jan-2024 11:47:51   

We won't change the outcome of the parameter names as it'll potentially produce a lot of work for users. We'll make a change internally so the parameter names stay the same but it won't create a redundant name. As this can't be done with an architectural change, we'll postpone this.

I'm not sure what you are saying here. You won't change it, be you've changed something internally?

Am I safe to say that the incorrect parameter naming is harmless and I can include it in the production codebase?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39712
Joined: 17-Aug-2003
# Posted on: 25-Jan-2024 17:22:11   

SanderF wrote:

We won't change the outcome of the parameter names as it'll potentially produce a lot of work for users. We'll make a change internally so the parameter names stay the same but it won't create a redundant name. As this can't be done with an architectural change, we'll postpone this.

I'm not sure what you are saying here. You won't change it, be you've changed something internally?

Sorry for the confusion flushed We won't change the behavior, but will make internal changes so that the duplicate call to the name creation is removed in a future version, in such a way that the end result will still have the gap in numbering (p2, p4, p6 instead of p1, p2, p3 in the case this occurs). To do that we have to make an architectural change which requires us to postpone it.

Am I safe to say that the incorrect parameter naming is harmless and I can include it in the production codebase?

Yes it's harmless. We use a unique counter that always increases to make sure parameters are unique. If there's a gap between parameter names, that's ok. We append the number to make sure we don't generate duplicate names.

Frans Bouma | Lead developer LLBLGen Pro