I have here some custom code that worked with the old version of LLBL and now is producing some bad SQL: UPPER(attrName.attrName) LIKE UPPER(@attr1) what is wrong. Good SQL would look like: UPPER(tblName.attrName) LIKE UPPER(@attr1)

following code is part of a public class IgnoreCaseLikePredicate : FieldLikePredicate

public override string ToQueryText(ref int uniqueMarker)
{
    base.Parameters.Clear();

    StringBuilder queryText = new StringBuilder(64);
    
    if(base.Negate)
    {
        queryText.Append("NOT ");
    }

    // create parameter
    uniqueMarker++;
    IDataParameter parameter = base.DatabaseSpecificCreator.CreateLikeParameter(String.Format("{0}{1}", base.FieldCore.Name, uniqueMarker), base.Pattern);
    base.Parameters.Add(parameter);

    queryText.AppendFormat("UPPER({0}) LIKE UPPER({1})", base.DatabaseSpecificCreator.CreateFieldName(base.PersistenceInfo,base.FieldCore.Name,base.FieldCore.Name), parameter.ParameterName);
    return queryText.ToString();
}

thanks for any help

p.s. database driver is firebird

I have temporary solution with CreateFieldNameSimple which is generating: UPPER(attrName) LIKE UPPER(@Attr1) but it could lead to new problems

The fieldname in the LikePredicate (which now also supports case insensitive compares) is created using:

base.DatabaseSpecificCreator.CreateFieldName(_field, _persistenceInfo, _field.Name, _objectAlias, ref uniqueMarker, inHavingClause)

so this would mean in your situation: queryText.AppendFormat("UPPER({0}) LIKE UPPER({1})", base.DatabaseSpecificCreator.CreateFieldName(base.FieldCore, base.PersistenceInfo, base.FieldCore.Name, base.FieldCore.ObjectAlias, ref uniqueMarker, false), parameter.ParameterName);

where uniqueMarker is the unique marker passed to ToQueryText()

Otis wrote:

The fieldname in the LikePredicate (which now also supports case insensitive compares) is created using:

base.DatabaseSpecificCreator.CreateFieldName(_field, _persistenceInfo, _field.Name, _objectAlias, ref uniqueMarker, inHavingClause)

I'll try it out.

There is one more problem that I run in to and I don't no it this a bug or my mistake. I have a typed list and method call someTL.Fill( 0, someSORT, false, somePREDICATE); is generating SQL code but with no DISTINCT ???

DISTINCT is not emitted in the query when there is a sort clause specified which is not in the select list, like your typed list has fields from entity A and B and you specify a relationcollection and sort on entity C. Is that the case?

Otis wrote:

DISTINCT is not emitted in the query when there is a sort clause specified which is not in the select list, like your typed list has fields from entity A and B and you specify a relationcollection and sort on entity C. Is that the case?

in the select list there are attributes from table A and B. the predicate and sort expression are using attributes from table C. B is parent of A and A is parent of C.

Is there a way to force DISTINCT?

Otis wrote:

The fieldname in the LikePredicate (which now also supports case insensitive compares) is created using:

base.DatabaseSpecificCreator.CreateFieldName(_field, _persistenceInfo, _field.Name, _objectAlias, ref uniqueMarker, inHavingClause)

so this would mean in your situation: queryText.AppendFormat("UPPER({0}) LIKE UPPER({1})", base.DatabaseSpecificCreator.CreateFieldName(base.FieldCore, base.PersistenceInfo, base.FieldCore.Name, base.FieldCore.ObjectAlias, ref uniqueMarker, false), parameter.ParameterName);

where uniqueMarker is the unique marker passed to ToQueryText()

I just tried it out and I just realized that this override method ToQueryText() is not called at all !!???

gof wrote:

Otis wrote:

DISTINCT is not emitted in the query when there is a sort clause specified which is not in the select list, like your typed list has fields from entity A and B and you specify a relationcollection and sort on entity C. Is that the case?

in the select list there are attributes from table A and B. the predicate and sort expression are using attributes from table C. B is parent of A and A is parent of C.

Is there a way to force DISTINCT?

No, as that will create an exception in firebird. (in every other database as well). If it doesn't in firebird, please let me know, but afaik, it does.

I just tried it out and I just realized that this override method ToQueryText() is not called at all !!???

ToQueryText(ref int uniqueMarker) is called.

Otis wrote:

gof wrote:

in the select list there are attributes from table A and B. the predicate and sort expression are using attributes from table C. B is parent of A and A is parent of C.

Is there a way to force DISTINCT?

No, as that will create an exception in firebird. (in every other database as well). If it doesn't in firebird, please let me know, but afaik, it does.

so why is not DISTINCT generated in my example?

Otis wrote:

ToQueryText(ref int uniqueMarker) is called.

ok didn't post the corect method public override string ToQueryText(ref int uniqueMarker) is not called. I set the breakpoint and nothing...

gof wrote:

Otis wrote:

gof wrote:

in the select list there are attributes from table A and B. the predicate and sort expression are using attributes from table C. B is parent of A and A is parent of C.

Is there a way to force DISTINCT?

No, as that will create an exception in firebird. (in every other database as well). If it doesn't in firebird, please let me know, but afaik, it does.

so why is not DISTINCT generated in my example?

Because you sort on a field that's not in the select list (i.e. the fields between SELECT and FROM, the real columns returned). I have to test if firebird throws an error as well, but every database I know throws an error when that happens IF distinct is specified.

so if you do: SELECT DISTINCT A., B. FROM A join B on.. join C on A... = C... WHERE somefilter ORDER BY C.foo

this will give an error normally. I've to test if firebird does this too, but if I'm not mistaken it does throw an exception in this situation.

Otis wrote:

ToQueryText(ref int uniqueMarker) is called.

ok didn't post the corect method public override string ToQueryText(ref int uniqueMarker) is not called. I set the breakpoint and nothing...

Hmm, I think I know what's wrong.

I had to add another ToQueryText() overload to make sure expressions were properly handled in HAVING clauses. So there is another overload in Predicate, which is: public virtual string ToQueryText(ref int uniqueMarker, bool inHavingClause)

You should give your routine that signature and add another one:

public override string ToQueryText(ref int uniqueMarker) { return ToQueryText(ref uniqueMarker, false); }

IPredicate.ToQueryText(ref int) is called from the Select query engine, IPredicate.ToQueryText(ref int, bool) is called from the IPredicateExpression.ToQueryText(ref int, bool).

Sorry to confuse you with that, I should have caught that earlier in the conversation.

Otis wrote:

Because you sort on a field that's not in the select list (i.e. the fields between SELECT and FROM, the real columns returned). I have to test if firebird throws an error as well, but every database I know throws an error when that happens IF distinct is specified.

so if you do: SELECT DISTINCT A., B. FROM A join B on.. join C on A... = C... WHERE somefilter ORDER BY C.foo

this will give an error normally. I've to test if firebird does this too, but if I'm not mistaken it does throw an exception in this situation.

OK. I get it. if I remove the ORDER BY and DISTINCT is back. using LLBL is great but it seems to have bad influence on my SQL knowledge

Otis wrote:

Hmm, I think I know what's wrong.

I had to add another ToQueryText() overload to make sure expressions were properly handled in HAVING clauses. So there is another overload in Predicate, which is: public virtual string ToQueryText(ref int uniqueMarker, bool inHavingClause)

You should give your routine that signature and add another one:

public override string ToQueryText(ref int uniqueMarker) { return ToQueryText(ref uniqueMarker, false); }

IPredicate.ToQueryText(ref int) is called from the Select query engine, IPredicate.ToQueryText(ref int, bool) is called from the IPredicateExpression.ToQueryText(ref int, bool).

Sorry to confuse you with that, I should have caught that earlier in the conversation.

That was it!. in the meanwhile I swithed to the new feature CaseSensitiveCollation = true and it works fine.

Thanks for helping me. The project is in final testing phase and the transition to the new LLBL was a bit tricky so any help is great

gof wrote:

Otis wrote:

Because you sort on a field that's not in the select list (i.e. the fields between SELECT and FROM, the real columns returned). I have to test if firebird throws an error as well, but every database I know throws an error when that happens IF distinct is specified.

so if you do: SELECT DISTINCT A., B. FROM A join B on.. join C on A... = C... WHERE somefilter ORDER BY C.foo

this will give an error normally. I've to test if firebird does this too, but if I'm not mistaken it does throw an exception in this situation.

OK. I get it. if I remove the ORDER BY and DISTINCT is back. using LLBL is great but it seems to have bad influence on my SQL knowledge

Bad? You just learned something!

Thanks for helping me. The project is in final testing phase and the transition to the new LLBL was a bit tricky so any help is great

Just post here or mail to support@llblgen.com if you need fast assistence with last minute issues so you can get your project out the door a.s.a.p.