Null semantics :)

CloverDX Platform
1

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

2

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

3

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.

4

Hi Franck !

That would be great - reproducing problem is half of the solution :slight_smile:

In case you won’t find the remedy, let us know what is the exact setup under which it does not work as specifed.

David.

5

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

6

Hello,

I’ve posted the patch last thrusday, the patch id is 001971.

Is it the good way to submit a patch ?

Damien

7

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

8

Hello !

It is definitely a good way… - thank you ! Unfortunately Berlios site was down so we couldn’t get to the patch until today.

David.