Hi,
I have questions regarding handling of null values with DBOutputTable component.
I have the following flow :
DBI --> reformat --> DBO
In the destination table, I have a varchar field that is a fk to another table. Lets call it MyField.
In reformat, my transformation class does :
out.getField("MyField").setNull(true);
In DBO component, I map incoming clover field MyField with table field MyField.
And guess what : the database complains that FK constraint is violated by value ''.
Workaround it to not map MyField at all, but then of course I can't handle cases when MyField can have a value or be null.
What would be the path to go here ?
Thanks in advance,
Franck
I have questions regarding handling of null values with DBOutputTable component.
I have the following flow :
DBI --> reformat --> DBO
In the destination table, I have a varchar field that is a fk to another table. Lets call it MyField.
In reformat, my transformation class does :
out.getField("MyField").setNull(true);
In DBO component, I map incoming clover field MyField with table field MyField.
And guess what : the database complains that FK constraint is violated by value ''.
Workaround it to not map MyField at all, but then of course I can't handle cases when MyField can have a value or be null.
What would be the path to go here ?
Thanks in advance,
Franck
-
Thanks for the reply David.
But... I am positive I can reporduce the problem on a very simple test case, using Postgresql Jdbc driver.
So either the problem comes from pg driver, or this is a case where Clover don't work the way you tell me.
I am going to try to trace it down and come with a diagnosis or a patch...
Franck -
Thanks a lot for the report & fix !
We are going to change the source code asap. This change should get into upcoming release 2.1.2.
Can I suggest you using either Bug (https://developer.berlios.de/bugs/?group_id=618) or Patch (https://developer.berlios.de/patch/?group_id=618) subsystems of berlios ?
That would be even greater help.
Anyway, we really appreciate your effort.
David. -
Hi !
In case of DBOutputTable, if target (DB fields) is populated from Clover's field and that fields has NULL value (isNull() returns true) then JDBC's setNull() on that target field is called.
Clover's String field can't contain empty string as a value - that is considered to be NULL - i.e. assigning empty string to "String" fields results in NULL to be assigned.
David -
Hello,
I'm working with Franck on the problem. I think I found where the problem is. In the same case Franck describe, when cloveretl make the transformation from jetel to sql, it uses inner classes of CopySQLData such as CopyString, CopyInteger... The methods setJetel or setSQL are called to make the transfert from jetel to sql and from sql to jetel. the method setSQL should handle the isNull attribute (like other CopyXXX inner classes) of the jetel field but it does not. the code in version 2.1.1 of CopyString class is
static class CopyString extends CopySQLData {
/**
...
*/
CopyString(DataRecord record, int fieldSQL, int fieldJetel) {
super(record, fieldSQL, fieldJetel);
}
/**
...
*/
void setJetel(ResultSet resultSet) throws SQLException {
String fieldVal = resultSet.getString(fieldSQL);
if (resultSet.wasNull()) {
field.fromString(null);
} else {
field.fromString(fieldVal);
}
}
/**
... dhostin : should handle field.isNull ?
*/
void setSQL(PreparedStatement pStatement) throws SQLException {
pStatement.setString(fieldSQL, field.toString());
}
}
The method setSQL could be as following (if doesn't break anything)
void setSQL(PreparedStatement pStatement) throws SQLException {
if (!field.isNull()) {
pStatement.setString(fieldSQL, field.toString());
}else{
pStatement.setNull(fieldSQL, java.sql.Types.VARCHAR);
}
}
Damien
Please sign in to leave a comment.
Comments 7