Hi

I'm trying to build the where clause "STRING_FIELD = :string_field":

predicateExpression.Add(PredicateFactory.CompareValue(SomFieldIndex.StringField, ComparisonOperator.Equal, stringValue));

It works fine for a non-empty stringValue, but when initializing stringValue to be String.Empty the where clause generated is " STRING_FIELD = '' ", therefore no record is retrieved - that's because empty strings are saved in Oracle DB as nulls.

I would think that LLBLGen will notice this scenario and generate "STRING_FIELD IS NULL", but it doesn't.

Is there an elegant solution for this problem? I don't want to set a condition (if stringValue == ""...else...) everytime I build a query with string fields.

As different databases disagree on treating empty strings as nulls, some consider them null, and some not.

That's why we provide FieldCompareNullPredicate. Pleaser refer to the LLBLGen Pro manual "Using the generated code -> Adapter/SelfServicing -> Filtering and sorting -> The predicate system"

So you're actually saying that every place I build a predicate-expression consists of a string-field (a lot of places...) I should write the following code:

if(stringValue == null || stringValue == String.Empty) predicateExpression.Add(PredicateFactory.CompareNull(...)); else predicateExpression.Add(PredicateFactory.CompareValue(...));

Am I right?

Yes, how else can it distinguish between comparing to "" vs. comparing to NULL ? (if I'm not mistaken, it's a setting whether you get "" as NULL in oracle or not, and "" is available as a valid value in later versions, but please correct me if I'm wrong).

(if I'm not mistaken, it's a setting whether you get "" as NULL in oracle or not, and "" is available as a valid value in later versions, but please correct me if I'm wrong).

I'm not sure of that, I need to check it.

Yes, how else can it distinguish between comparing to "" vs. comparing to NULL ?

Can you agree that getting a string-value from a client for say, filtering, is a pretty common scenario? Imagine an Oracle-based application that performs queries by client-request, and needs to build appropriate SQL statements. It would be nice if LLBLGen could build "STRING_FIELD IS NULL" even if I use CompareValue(FieldIndex, ComparisonOperator.Equal, String.Empty) and not CompareNull(FieldIndex) since "STRING_FIELD = ''" always returns false.

I understand your situation, but I won't change the code. The reason is that a predicate is a database independent object. If you're using adapter and both sqlserver and oracle, you'll have a problem when you want to re-use your code on sqlserver, as it will fail. While vice versa it won't, because you then would have used CompareNULL.

There's another reason: predicates create their own sql. This thus means that if a comparevalue predicate is used, it then has to know about oracle and to generate IS NULL instead of = param. That's unacceptable as that would break the separation of concern.

Why not create a simple utility method which produces the predicate for you, then you avoid the if statements and it comes down to the same amount of statements as otherwise is required.

Why not create a simple utility method which produces the predicate for you, then you avoid the if statements and it comes down to the same amount of statements as otherwise is required.

Did that, eventually I realized that this is the only way.

I understand your situation, but I won't change the code. The reason is that a predicate is a database independent object. If you're using adapter and both sqlserver and oracle, you'll have a problem when you want to re-use your code on sqlserver, as it will fail. While vice versa it won't, because you then would have used CompareNULL.

There's another reason: predicates create their own sql. This thus means that if a comparevalue predicate is used, it then has to know about oracle and to generate IS NULL instead of = param. That's unacceptable as that would break the separation of concern.

I understand the importance of separation, but having the CompareNull generating "FIELD IS NULL" doesn't mean that it knows Oracle? I'm not familiar with other DBs, but imagine a fictitious DB in which null statement goes like this: "FIELD EQUALS NULL" - the CompareNull predicate will then fail since it generates "IS", no?

IS NULL is standard SQL. Everything that's not standard SQL among the supported DB's is implemented in the DBSpecificCreator class which is implemented by every DQE. For example, creating a subquery, creating parameters, create an alias etc. these things are branched out to the creator class of which every predicate gets an instance of before it generates SQL.

IS NULL however is standard SQL. Every db uses this syntaxis, hence it's generated by the predicate. If we add a DB to our list of supported DBs and it doesn't support IS NULL (which I doubt, because it's part of hte iso standard, even though not all elements of the iso standard for SQL are implemented by all db's) then we'll branch it out to the creator class, but for now that's unnecessary and I also don't see it necessary.

For example operator conversions are branched out to the creator classes.

Understood, Thank you very much for the DB review