Boolean Fields

[smit_al]

What is the best way to handle boolean fields? Usually we create a one character field and put Y or N in the field. Auto Code doesn't seem to have a way to identify those fields as boolean. Is there a parameter in vvOut that I can use to identify these fields as boolean so that execSQLtoJSON will treat them that way?

[robert.swanson]

Probably your best approach on the back-end would be to modify the SQL statement to use a case clause to convert your Y/N values into boolean true/false equivalents. The execSQLtoJSON procedure will recognize the 'true' and 'false' strings as booleans and thus not encapsulate them in quotes on the response.

For example, in the Valence VVUSERS file there is a VVACTIV flag set to '1' or '0' to indicate enabled or disabled users, respectively. If I wanted to return that as a boolean value to the front-end I could do something like this in my RPG program:

stmt='select vvloginid, '
            'case when vvactiv=''1'' then ''true'' '+
                                    'else ''false'' end as active '+    
      'from vvusers '+                                          
     'order by vvloginid';                                      
vvOut_execSQLtoJSON(vvout:stmt);

This would return a JSON response that looks something like this:

{"dataRoot":[{"VVLOGINID":"USER1","ACTIVE":false},{"VVLOGINID":"USER2","ACTIVE":true},{"VVLOGINID":"USER3","ACTIVE":true},{"VVLOGINID":"USER4","ACTIVE":false}],"totalCount":4}

That said, I like your idea of being able to flag a field in AutoCode as boolean, so it would make the SQL statement adjustments for you accordingly. I've added this to our feature request list for Valence 5.1.