Four Stars ul2
Four Stars

From tOracleRow to tPostgresqlRow

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,

2 REPLIES
Community Manager

Re: From tOracleRow to tPostgresqlRow

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

Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Four Stars ul2
Four Stars

Re: From tOracleRow to tPostgresqlRow

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

 

trow.png

 

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

 

tsqloutput.png

 

 

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.