boolean doesn't work in Oracle

[51mon]

When trying to insert a record with a boolean we get some bad conversion (in french below). The test is on a table with only field a boolean (a char in Oracle).

2013-10-22 19:35:44,097 [DEBUG] BaseMappedStatement insert data with statement 'INSERT INTO "TEST" ("some_boolean" ) VALUES (?)' and 1 args, threw exception: java.sql.SQLException: La conversion demandée n'est pas valide. Exception in thread "main" java.sql.SQLException: Unable to run insert stmt on object flowMap.model.TestInsert@7eb366: INSERT INTO "TEST" ("some_boolean" ) VALUES (?) at com.j256.ormlite.misc.SqlExceptionUtil.create(SqlExceptionUtil.java:22) at com.j256.ormlite.stmt.mapped.MappedCreate.insert(MappedCreate.java:135) at com.j256.ormlite.stmt.StatementExecutor.create(StatementExecutor.java:450) at com.j256.ormlite.dao.BaseDaoImpl.create(BaseDaoImpl.java:308) at flowMap.TestOracle.main(TestOracle.java:29) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120) Caused by: java.sql.SQLException: La conversion demandée n'est pas valide. at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8349) at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8286) at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8868) at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:240) at com.j256.ormlite.jdbc.JdbcDatabaseConnection.statementSetArgs(JdbcDatabaseConnection.java:325) at com.j256.ormlite.jdbc.JdbcDatabaseConnection.insert(JdbcDatabaseConnection.java:169) at com.j256.ormlite.stmt.mapped.MappedCreate.insert(MappedCreate.java:91) ... 8 more

[51mon]

I don't have time to see why this errors happens, this can be workarounded by using format = "integer".

[j256]

On Oct 22, 2013, at 1:38 PM, 51mon notifications@github.com wrote:

When trying to insert a record with a boolean we get some bad conversion (in french below). The test is on a table with only field a boolean (a char in Oracle).

Interesting. So by default, if I look at the OracleDatabaseType (http://goo.gl/hs5yS7), ORMLite uses a CHAR(1) as the defined type for boolean. Is that how your field is defined? Are you using a schema generated by ORMLite or trying to match an existing one?

Then it uses JDBC to/from to do the mapping. In your @DatabaseField() annotation the format field can be either "integer" in which case it will be defined as INTEGER in the schema and stored as 0 or non-0 (1). You can also use the format field to define the characters to use for true/false (format = "tf" or "yn").

If you turn on trace output for the com.j256.ormlite.stmt.mapped.MappedCreate class, you should be able to see the arguments that are causing the problem:

                logger.debug("insert data with statement '{}' and {} args, threw exception: {}", statement,
                             args.length, e);
                if (args.length > 0) {
                    // need to do the (Object) cast to force args to be a single object
                    logger.trace("insert arguments: {}", (Object) args);
                }

I don't have access to an Oracle database so ORMLite could certainly be doing something wrong here.
gray

2013-10-22 19:35:44,097 [DEBUG] BaseMappedStatement insert data with statement 'INSERT INTO "TEST" ("some_boolean" ) VALUES (?)' and 1 args, threw exception: java.sql.SQLException: La conversion demandée n'est pas valide.
Exception in thread "main" java.sql.SQLException: Unable to run insert stmt on object flowMap.model.TestInsert@7eb366: INSERT INTO "TEST" ("some_boolean" ) VALUES (?)

[j256]

On Oct 22, 2013, at 2:51 PM, 51mon notifications@github.com wrote:

I don't have time to see why this errors happens, this can be workarounded by using format = "integer".

Could be. This will change your schema to use 1/0 integer values instead. Certainly worth a try if you are under time pressure.
gray

[51mon]

Sorry but I don't know how to raise the log level. I tried to get the logger and set the log level, but it doesn't have a setLevel method:
Log log = LogFactory.getLog(com.j256.ormlite.stmt.mapped.MappedCreate.class);
log.setLevel( ...) // Wrong

Yes without specifing a format option you get a column with a type of CHAR(1).

I tried to set format ="YN" with no luck.

Finally I use the format = integer as it works and I need to move on.

[j256]

Sorry but I don't know how to raise the log level. I tried to get the logger but it hasn't a setLevel method
Log log = LogFactory.getLog(com.j256.ormlite.stmt.mapped.MappedCreate.class);

Here's the docs on ORMLite loggin:

http://ormlite.com/docs/logging

Yes without specifing a format option you get a CHAR(1).
I tried to set format ="YN" with no luck.

Anyone else using ORMLite with Oracle? Anyone else having problems (or success) with booleans?

Finally I use the format = integer as I need to move on.

And integer seems to work? Good.
gray

[51mon]

Using format = integer, I get this error when eagerly loading a foreign object:
java.lang.ClassCastException: java.lang.Character incompatible with java.lang.Integer at com.j256.ormlite.db.OracleDatabaseType$BooleanFieldConverter.sqlArgToJava(OracleDatabaseType.java:230) at com.j256.ormlite.field.BaseFieldConverter.resultToJava(BaseFieldConverter.java:28) at com.j256.ormlite.field.FieldType.resultToJava(FieldType.java:801) at com.j256.ormlite.stmt.mapped.BaseMappedQuery.mapRow(BaseMappedQuery.java:60) at com.j256.ormlite.stmt.SelectIterator.getCurrent(SelectIterator.java:270) at com.j256.ormlite.stmt.SelectIterator.nextThrow(SelectIterator.java:161) at com.j256.ormlite.stmt.StatementExecutor.query(StatementExecutor.java:202)

I think that's because resultToJava returns a Char and sqlArgToJava compare it with an integer (0).

Replacing 0 by '0' makes it work.

[51mon]

Closed by mistake

[j256]

Sorry to not keep this current. I still don't 100% understand why it isn't working. I've decided to create the DataType.BOOLEAN_CHAR (to support '1' '0' or 't' 'f') and BOOLEAN_INTEGER to better support int and char booleans. I also have improved the way that Oracle supports it. By default it will create a char and support '1' or '0' but if the format is "integer" it should fully support the int type. Sorry for the problems. This will be in 4.49. Let me know if I've screwed it up again.

[Deviluc]

We store booleans as CHAR(1 Byte) in the db, but using DataType.BOOLEAN_CHAR results in the following exception:
java.sql.SQLException: Invalid conversion requested at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8122) at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8034) at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8575) at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:238) at org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:529) at org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:529) at com.j256.ormlite.jdbc.JdbcDatabaseConnection.statementSetArgs(JdbcDatabaseConnection.java:359) at com.j256.ormlite.jdbc.JdbcDatabaseConnection.insert(JdbcDatabaseConnection.java:187) at com.j256.ormlite.stmt.mapped.MappedCreate.insert(MappedCreate.java:91) ... 26 more

The annotated field:
@DatabaseField(canBeNull = false, dataType = DataType.BOOLEAN_CHAR, format = "10", defaultValue = "0") private boolean locked;

Digging a bit in your code, I found the following line causing the Issue (JdbcDatabaseConnection.java:359):
stmt.setObject(i + 1, arg, typeVal);
Where typeVal is 12 which corresponds to java.sql.Types.VARCHAR, but the inserted value (arg) is a Character.
As of 2018, a docker container for an oracle 12c database is available here and is free to use for development purposes, maybe you have some time to check it out.