Four Stars

Prepared statement with null values in a tOracleRow

I was tOracleRow to execute a SQL update in a prepared statement and I found that the entire job will hang after a small number of rows if there's a null value for any of the columns. There's no output, even if you pass the rows through a tLogRow first. You do get output like this after you kill the job:

 

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull
[ERROR]: talend_proj.orbcadtimingdwconsumer_0_1.OrbCadTimingDwConsumer - tOracleRow_1 - null
[ERROR]: talend_proj.orbcadtimingdwconsumer_0_1.OrbCadTimingDwConsumer - tOracleRow_1 - null
[ERROR]: talend_proj.orbcadtimingdwconsumer_0_1.OrbCadTimingDwConsumer - tOracleRow_1 - null
(repeated a bunch more times)

 

I've resolved this by filtering out the null values and sending them off to a different tOracleRow with the null column omitted, but this is only reasonable if you have a single column that can be null.

 

Also, even if it is expected that tOracleRow should not bind null parameters to a prepared statement, it should fail more gracefully and explicitly. The current behavior cost me several hours of head-scratching.

2 REPLIES
Moderator

Re: Prepared statement with null values in a tOracleRow

Hello,

Have you already  tried to mark your input schema as "nullable" in your schema setting?

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

Re: Prepared statement with null values in a tOracleRow

Yep:

nullableForeignKey.PNG