0

For a long time I have used the char(1) Y/N method of representing Boolean values in Oracle and it has generally done the job fine. However I am having trouble getting my head around how to implement Booleans safely for my current project.

Previously data returned to PHP from Oracle would be used to populate classes with defined properties and types. This meant converting Y/N to an actual Boolean was easily handled at the property level by the constructor or some other method. What I am trying to do this time is a little different.

I am using the Qooxdoo framework and feeding it JSON which is then used to dynamically generate a model. The main point here is Qooxdoo contains no map of what properties are what type. What they are in JSON is what they end up in Qooxdoo.

Naturally I don't want to have to interpret Y/N values as boolean within Qooxdoo, so my first action was to pre-process the data set in PHP and convert Y and N to Boolean before encoding it. This is all fine in theory until someone enters their name (or any other string data) as Y or N, which happily goes into Oracle but then comes back out as a Boolean.

I can think of a couple of solutions such as using a longer more unique identifier, or enforcing no Y and N strings within Qooxdoo (my least fav idea), but I welcome any alternative idea on how to deal with this as I am sure I'm not the only one and this problem isn't just confined to Qooxdoo development.

Rob C
  • 1
  • 1
  • 2
    Why would you apply that preprocessing action to all columns indiscriminately? – Jeffrey Kemp Apr 03 '13 at 03:18
  • I guess the short answer is I don't. I just cant think of any other way. If the data set returned to PHP was able to discriminate between char(1) and varchar2 then you could reliably convert just the appropriate fields but that's the bit I haven't figured out. – Rob C Apr 03 '13 at 03:44
  • 2
    But even char(1) doesn't imply a Y/N field... your code needs to distinguish columns that are a Y/N field vs. others; since Oracle is no help, you may need to do something like use a column naming convention, e.g. `PROCESSED_YN` or something like that. – Jeffrey Kemp Apr 03 '13 at 03:47
  • That is a very reasonable idea. We do actually use the _YN naming convention so its not a big stretch at all. Cheers. – Rob C Apr 03 '13 at 03:59
  • another naming convection is IS_FOO, IS_BAR – PaulMurrayCbr May 03 '14 at 03:10

0 Answers0