[TUSCANY-2731] Cannot insert null values in nullable columns of Oracle

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: Java-DAS-beta1
• Fix Version/s: Java-DAS-Next
• Component/s: Java DAS RDB
• Environment: JDK 1.5, Oracle 10g, Windows XP
• Patch Info: Patch Available
• Assignee: Unassigned
• Reporter: Kumar Iyer
• Created: 09/Dec/08 06:12
• Updated: 09/Dec/08 06:12

Description

Unable to insert null values into Oracle nullable columns.

When I tried to insert null parameter values in an insert command and execute the same, I am presented with the following exception trace:

Exception in thread "main" java.lang.RuntimeException: java.sql.SQLException: Invalid column type
at org.apache.tuscany.das.rdb.impl.InsertCommandImpl.execute(InsertCommandImpl.java:52)
at com.mycompany.myproject.dao.TestClient.addEmployeeWithConfig(TestClient.java:171)
at com.mycompany.myproject.dao.TestClient.main(TestClient.java:200)
Caused by: java.sql.SQLException: Invalid column type
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.driver.OracleStatement.get_internal_type(OracleStatement.java:6164)
at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:1316)
at org.apache.tuscany.das.rdb.impl.Statement.executeUpdate(Statement.java:163)
at org.apache.tuscany.das.rdb.impl.Statement.executeUpdate(Statement.java:132)
at org.apache.tuscany.das.rdb.impl.InsertCommandImpl.execute(InsertCommandImpl.java:49)
... 2 more

The code is as follows:

private static void addEmployeeWithConfig() {
DAS das = null;
Connection con = null;
try

{ con = getConnection(); das = DAS.FACTORY.createDAS(getConfig("TestMapping.xml"), con); Command cmd = das.getCommand("insert employee"); cmd.setParameter(1, 8); cmd.setParameter(2, null); cmd.execute(); //CAUSED THE EXCEPTION con.commit(); }

catch (SQLException e)

{ e.printStackTrace(); }

finally {
if (null != con) {
try

{ con.close(); }

catch (SQLException e)

{ e.printStackTrace(); }

}
}
}

The Oracle table creation command is:

CREATE TABLE EMPLOYEE
(
ID decimal(10) PRIMARY KEY NOT NULL,
NAME varchar2(100),
);

Here, NAME is a nullable column

The relevant config file information is:

<Table tableName="EMPLOYEE">
<Column columnName="ID" primaryKey="true" converterClassName="com.mycompany.myproject.sdo.LongConverter"/>
</Table>

<Command name="insert employee" SQL="insert into employee(ID, NAME) values (?, ?)" kind="insert">
<Parameter direction="IN" index="1" columnType="commonj.sdo.Long"/>
<Parameter direction="IN" index="2" columnType="commonj.sdo.String"/>
</Command>

Note that I have added the "LongConverter" to convert the BigDecimal to a Long type. (If I don't perform this step, das.applyChanges(root) will throw an Exception when I update an employee data object)

Suggestion to fix:
---------------------

The following condition in Line 158 of "org.apache.tuscany.das.rdb.impl.Statement.executeUpdate()" is passing:

if (param.getType() == null)

This is inspite of setting the parameter descriptors in the insert command.

When you construct a ParameterExtendedImpl object, the "type" field is remaining uninitialized:

public ParameterExtendedImpl(org.apache.tuscany.das.rdb.config.impl.ParameterImpl parameterImpl)

{ this.columnType = parameterImpl.getColumnType(); this.direction = parameterImpl.getDirection(); this.index = parameterImpl.getIndex(); this.name = parameterImpl.getName(); }

So, doesn't it have to be this way:

public ParameterExtendedImpl(org.apache.tuscany.das.rdb.config.impl.ParameterImpl parameterImpl)

{ this.columnType = parameterImpl.getColumnType(); this.direction = parameterImpl.getDirection(); this.index = parameterImpl.getIndex(); this.name = parameterImpl.getName(); setColumnType(columnType); //SHOULDN'T THIS LINE BE ADDED? }

When I applied this patch, it seems to be working well. But I am not sure if this is indeed the fix.