Change BooleanNumbericConverter to match -1 and 1

Posts   
 
    
ww
User
Posts: 83
Joined: 01-Oct-2004
# Posted on: 25-Jun-2020 23:04:33   

I'm using LLBLGen with an Oracle database that was originally written to using a different database framework. Boolean values are in NUMBER(1,0) columns, but the previous framework used -1 for True, while LLBLGen uses +1. The BooleanNumericConverter handles this fine when entities are loaded--it treats anything !=0 as True. But if I query the database it doesn't work, because it generates a query to test for "=1". Is there a way to make LLBLGen generate the query as "<>0" instead? Or should I just run a script to convert all the -1s to +1s?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 26-Jun-2020 11:53:20   

I'm afraid a type converter won't help in this particular case, as the boolean predicate is defined in a function mapping. A type converter converts values back/forth which are then stored in parameters, this particular predicate is defined in the Oracle DQE we ship. At least I think you run into that (CASE WHEN ... =1 THEN ... END).

Do you have a simple example query together with the generated SQL which shows the =1 so we can pinpoint you to where the =1 comes from ?

Frans Bouma | Lead developer LLBLGen Pro
ww
User
Posts: 83
Joined: 01-Oct-2004
# Posted on: 29-Jun-2020 18:14:57   

I'm already scripting a lot of schema changes for the application as part of this upgrade/conversion process, so I just added queries to convert the -1 values to +1 for all my boolean columns, and that's a reasonable solution for my situation.

But for example, I had code like this:


                    var bucket = new RelationPredicateBucket();
                    bucket.PredicateExpression.AddWithAnd(MyTableFields.MyColumn == true);

Then use DataAdapter.FetchEntityCollection to retrieve entities.

This generates SQL like this:


Generated Sql query: 
    Query: SELECT ... FROM ... WHERE ( "MYSCHEMA"."MYTABLE"."MYCOLUMN" = :p1) 
    Parameter: :p1 : Int16. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

What I would want to see there is the generated query using "<>0" instead of "=1", but I assume that gets complicated because the DQE doesn't know this is supposed to be a boolean value rather than an integer.

(If I change the code so the Predicate uses "MyColumn!=false" then it works correctly (the SQL generated is "<>0") but this didn't seem like a maintainable workaround.)

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 29-Jun-2020 23:19:13   

You got it right.

The simplest and most clean resolution is the one you did. Converting the values in the database.

The second best is to use the MyColumn!=false

I don't see any other straight and clean way to solve this.