[Bug]: Table output - boolean conversion failure on Oracle database

[dave-csc]

Apache Hop version?

2.17

Java version?

17.0.2

Operating system

Linux

What happened?

When passing boolean fields to a Table output transform that writes to an Oracle database:

• it seems that Hop 2.17 doesn't handle the conversion correctly: you can get an error from Oracle like ORA-01722: invalid number
• with Hop 2.16 and below, the conversion is handled: true values are converted to integer 1, false to 0

(Note that Oracle doesn't support boolean columns, so a conversion is needed)

Workaround: use a Select values before Table output to convert the boolean fields into integers (the same conversion done automatically by Hop <2.16 is performed)

Issue Priority

Priority: 2

Issue Component

Component: Transforms

[hansva]

Odd, I don't think we have come anywhere near that code.

[nadment]

Have you tried disabling “Supports the Boolean data type” in the Advanced tab?

[dave-csc]

@nadment That option was already disabled 🤔 (I mean, it is on the metadata object for the DB... there's no "Advanced" tab in Table output)

[dave-csc]

Some additional info, I also tried to insert a (stream) boolean value into a (database) varchar field:

• Hop 2.17 inserts "Y" and "N" for true and false respectively (no errors here, but this explains the Oracle error when the column is of type number/integer instead)
• Hop 2.16 inserts "0" and "1"

Hope this helps :)

[hansva]

Can you include a clear reproduction path please? Is the field defined as a boolean, a string, or an integer?
Simple path, like a data grid and table output to a fictional Oracle database is fine. So we do not start debugging on assumptions.

[hansva]

And the definition of the destination table (what type are we inserting into?)

[dave-csc]

Summing up what I discovered:

• the field in Hop is always defined as a boolean
• in Hop 2.17 the Table output transform tries to insert its value as Y/N: this causes an Oracle error if the destination column is a numeric type (NUMBER, INTEGER, etc.)
• in Hop 2.16 the Table output tries to insert it as 0/1: this doesn't cause Oracle errors, because a number can be safely written in CHAR/VARCHAR columns, too

To test, just link a Data grid with a boolean field to a Table output, that writes in a table with a column (of different types, see above)