0

I'm developing a DB layer, using JDBC (ojdbc6.jar) which allows to perform basic DB operations by getting and returning a Map containing Name/Value pairs. For example:

map.put("StrField", "str");
map.put("IntField", 10);
map.put("BoolField", true);
DB.insert(tableName, map);
...
map = DB.query("SELECT * from MY_TABLE.."); // assuming this returns only one row
Boolean flag = map.get("BoolField");
// etc...

The insert operation is easy to implement because conversion from Java primitive types to Oracle types is easy and intuitive.

For the opposite direction (Oracle->Java) the correction is slightly more tricky. I managed to convert all types except "Boolean". Since there is no BOOLEAN data type in Oracle DB, I'm looking for a way to define a field so that my retrieval layer will clearly identify such a field as Boolean.

The best way I found so far is to use something like this:

BOOL_FIELD CHAR(1) DEFAULT 0 NOT NULL CHECK (BOOL_FIELD in (0, 1))

Plus, I make sure that I don't use CHAR(1) anywhere in my DB, except when "Boolean" is needed. This is not a big limitation because CHAR(1) is not very useful for anything else anyway.

And in my Java conversion code, I check if a particular returned field is java.sql.Types.CHAR (via ResultSetMetaData.getColumnType() call), and I also check that the size of the field is 1, in which case I can safely call ResultSet.getBoolean() and get field value as Java Boolean data type.

So far, it works really well, but now I have a problem with external databases, i.e. databases, in which I can't control the table/column definition. For such databases, I'm OK not to have Boolean values at all, but I wonder how I can distinguish my "Boolean" from just a regular CHAR(1) that someone else is using in the external database.

I could ignore this entirely, and in case of CHAR(1) get Boolean values true/false, but this wasn't my intention. For example, if a particular CHAR(1) column has a value of 'X' (impossible for my database, but valid case in external database), then my conversion code will return true instead of 'X'.

I'm looking for some "metadata" or trick which I can add to my "BOOLEAN" DB field, so my JDBC layer could get this metadata and if it gets it - assume it's MY Boolean and not someone else's CHAR(1) which looks similar.

Any ideas on such a trick (even if not 100% safe, but reasonably safe)?

Thanks.

Edit Just as an example, using RAW(1) in Oracle would do the trick perfectly, because there is close-to-zero chance anyone, anywhere will be using RAW(1) for anything else. The problem with RAW(1) is that it's really hard to use in SQL queries, i.e. it's hard to do WHERE with such "boolean" definition.

Wanna Know All
  • 681
  • 2
  • 8
  • 18
  • 1
    If you used ResultSet directly, instead of using a Map which doesn't add much value, the result set would do the appropriate, well-defined conversion for you. – JB Nizet Nov 28 '13 at 18:21
  • possible duplicate of http://stackoverflow.com/questions/3726758/is-there-a-boolean-type-in-oracle-databases – nidhin Nov 28 '13 at 18:24
  • Why are you setting a CHAR field to a numeric value? Shouldn't it be '0' or '1', or create a number(1) field. – OldProgrammer Nov 29 '13 at 21:39
  • @JBNizet, I AM using `ResultSet` inside my data layer, as you can see. The problem is that my code should be able to decide automatically when to use `rs.getBoolean()` and when to use `rs.getString()`. Imagine I then serialize the retrieved data to JSON - I want Booleans to appear as `true/false` and not as `"1"/"0"` in my JSON. The question is how to do this using `ResultSet`, if I don't know if a particular field was meant to be bool, I just know its Oracle DB definition. – Wanna Know All Dec 01 '13 at 08:20
  • @nidhin, I don't ask if there's a Boolean in Oracle. I know there isn't one. That's why I'm looking for an alternative way to convert whatever IS available in Oracle to Java Boolean. – Wanna Know All Dec 01 '13 at 08:21
  • @OldProgrammer, in the above SQL definition that I'm using, the values are actually stored as '1' and '0' (i.e. DB stores them as ASCII 48 and 49 internally). Ironically, this is a problem for me, because I would prefer DB to store them as ASCII `00` and `01`, in which case I could "detect" that it's "my" boolean. The problem is, that if I do this (e.g. by using `CHR(0)`), `ResultSet.setBoolean(true)` fails with exception! – Wanna Know All Dec 01 '13 at 08:25
  • My point is that the caller should know and decide what to retrieve, and in which format, from the result set. You're trying to guess it from the metadata and decide for the caller. If the caller directly used the result set, he could call getBoolean() or getString() on the same column, and both would work and return what he wants to return. The choice of type to use shouldn't come from the type of the columnin the database, but from what the caller wants to do with this column. – JB Nizet Dec 01 '13 at 08:26
  • @JBNizet, IMHO, the basic principle and one of main reasons to have types - is for the users of the data to be able to get the data in the right and usable format. My original caller is actually a browser client, getting JSON, the client doesn't care about Oracle DB limitations, the client wants to have `true/false` and I cannot blame him because it's a very reasonable wish. What you are suggesting is to make mid-layer to be aware of the client logic. In other words, if the client knows it wants Boolean field, the mid-layer should replicate all that knowledge too. And that's not scalable. Tnx. – Wanna Know All Dec 01 '13 at 08:37
  • @SemenSemenych did you see answer from ammoQ – nidhin Dec 02 '13 at 12:21
  • @nidhin, of course. I know that there's no Boolean in Oracle, that's why I call it "boolean". I'm looking for any workaround, in Oracle, so I can safely guess in my java code that a field should be treated as Java Boolean, when converting from Oracle ResultSet's data to Java. – Wanna Know All Dec 03 '13 at 11:13

1 Answers1

0

OK, so here's how it can be done, kind of nicely:

  • Define "boolean" field in Oracle as RAW(1):

    BOOL_FIELD RAW(1) DEFAULT '00' CHECK (BOOL_FIELD in ('00', '01'))

    There is close-to-zero chance that anyone will be using RAW(1) for anything else

  • Use getString/setString in Java to convert true/false to/from '00'/'01'

The main problem with the above approach is that '00'/'01' must be always used in SQL statements. In other words, if accidentally someone uses WHERE BOOL_FIELD = '1', it will always return empty result, due to strange Oracle handling of char to binary conversion.

The above problem, if scaled to a really big long-living system is not fool-proof, so it's not safe enough. I decided to take a more straight forward approach - I have a connection-set flag, where I specify if it's "my" database connection (in which case I can treat CHAR(1) as Java Boolean) or "foreign" database (in which case Java Boolean is not supported).

Wanna Know All
  • 681
  • 2
  • 8
  • 18