CODEWITHSUNDEEP
javadatabaseoracle-databaseexceptionh2
Victor Nedioglo
Victor Nedioglo

Reputation: 33

H2: "data conversion error" in WHERE clause

I am using the H2 database (v1.3.170) for JUnit testing in my project. Our production environment uses Oracle DB so we have to do lot conversions of the DDLs exported from Oracle to make them working with H2. One of the issues we are facing right now is following:

select * from table_country c where c.code > 0 AND 'USA' = c.name(+)

This query works fine in Oracle, but fails in H2 with below stack trace:

org.h2.jdbc.JdbcSQLException: Data conversion error converting "USA"; SQL statement:
select * from table_country c where c.code>0 AND 'USA' = c.name(+) [22018-170] 
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) 
    at org.h2.message.DbException.get(DbException.java:158) 
    at org.h2.value.Value.convertTo(Value.java:852) 
    at org.h2.value.Value.getBoolean(Value.java:373) 
    at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:188) 
    at org.h2.command.dml.Select.prepare(Select.java:802) 
    at org.h2.command.Parser.prepareCommand(Parser.java:218) 
    at org.h2.engine.Session.prepareLocal(Session.java:414) 
    at org.h2.server.TcpServerThread.process(TcpServerThread.java:253) 
    at org.h2.server.TcpServerThread.run(TcpServerThread.java:149) 
    at java.lang.Thread.run(Unknown Source) 
Caused by: java.lang.NumberFormatException 
    at java.math.BigDecimal.<init>(Unknown Source) 
    at java.math.BigDecimal.<init>(Unknown Source) 
    at org.h2.value.Value.convertTo(Value.java:801) 
    ... 8 more

I tried removing the (+) - it worked:

select * from table_country c where c.code > 0 AND 'USA' = c.name

But as I said, I don't want to change this in production (want the Oracle => H2 conversion to be transparent).

Please suggest what could be the issue and a better way to solve this?

Upvotes: 2

Views: 4210

Answers (1)

user330315
user330315

Reputation:

(+) syntax for outer joins is deprecated in Oracle. Stop using it.

In your case an "outer join" doesn't really makes sense anyway because you are not joining.

You probably want something like this (which works reliably across all DBMS)

select * 
from table_country c 
where c.code > 0 
  and (c.name = 'USA' or c.name is null);

A final word: using a different DBMS for testing and production makes your tests worthless. There are too many subtle differences between the DBMS that you simply can't cover when using different DBMS.

Upvotes: 1

Related Questions