From tOracleRow to tPostgresqlRow

[Anonymous]

Hello,

We try to move and old Oracle database from a Postgresql one and we used the tOracleRow. "birthdate" is marked as nullable value :

The code generated is :

if ((row1.birthdate == null)) {
    pstmt_tOracleRow_2.setNull(4, java.sql.Types.TIMESTAMP);
    } else {
        pstmt_tOracleRow_2.setTimestamp(
                4, new java.sql.Timestamp(row1.birthdate.getTime()));
    }

Everything is doing well but when we try to the TPostgresqlRow we've got only the secont part :

pstmt_tPostgresqlRow_1.setTimestamp(
    4, new java.sql.Timestamp(row1.birthdate.getTime()));

As birthdate can be null we've got a null pointer exception. It is strange the generator doesn't take the case null for postsgresql (test the same behavior occured for tMysqlRow).

Is there an option to check or is it a bug for the generated source ?

Regards,

[Anonymous]

Hello ul2
Can you please give us more details about the component? schema? query? so we can understand the problem.
Thanks!

Shong

[Anonymous]

Hi shong,

Here's a minumum subset with two values in generator (a date and an int, we've got much more values). In reality these values come from another database and date can be null as other values but here it is to explain. Here with tXxxSqlRow

For Postgresql :

query_tPostgresqlRow_1 = "insert into mytable values(?, ?)";
...
pstmt_tPostgresqlRow_1.setTimestamp(1, new java.sql.Timestamp(to_pg.date.getTime()));

For Mysql :

query_tMysqlRow_1 = "insert into mytable values(?, ?)";
...
pstmt_tMysqlRow_1.setTimestamp(1, new java.sql.Timestamp(to_my.date.getTime()));

For Oracle :

query_tOracleRow_1 = "insert into mytable values(?, ?)";
...
if ((to_ora.date == null)) {
    pstmt_tOracleRow_1.setNull(1, java.sql.Types.TIMESTAMP);
} else {
    pstmt_tOracleRow_1.setTimestamp(1, new java.sql.Timestamp(to_ora.date.getTime()));

Oracle is the only generating code with a test case with null value.

Second part with tXxxSqlOutput

If I use a tPostgresqlOutput the test case with null value is taken into account :

if (to_pg.date != null) {
    pstmt_tPostgresqlOutput_1.setTimestamp(1, neww java.sql.Timestamp(to_pg.date.getTime()));
} else {
	pstmt_tPostgresqlOutput_1.setNull(1, java.sql.Types.TIMESTAMP);
}

for mysql too :

if (to_my.date != null) {
    date_tMysqlOutput_1 = to_my.date.getTime();
    if (date_tMysqlOutput_1 < year1_tMysqlOutput_1
            || date_tMysqlOutput_1 >= year10000_tMysqlOutput_1) {
        pstmt_tMysqlOutput_1.setString(1,
                "0000-00-00 00:00:00");
    } else {
        pstmt_tMysqlOutput_1
                .setTimestamp(1, new java.sql.Timestamp(
                        date_tMysqlOutput_1));
    }
} else {
    pstmt_tMysqlOutput_1.setNull(1, java.sql.Types.DATE);
}

For Oracle too :

if (to_ora.date != null) {
    pstmt_tOracleOutput_1.setObject(1,
            new java.sql.Timestamp(to_ora.date.getTime()),
            java.sql.Types.DATE);
} else {
    pstmt_tOracleOutput_1.setNull(1, java.sql.Types.DATE);
}

I've got the problem for t[postgresql|mysql]row date type but I guess I could have the same behavior for other objects could be null.

Any help would be very appreciated.