tDBRow bug - unable to run update statements for Postgres tables in tDBRow (Postgres) components in sequence

[rohsuh]

Hi,

I am using Talend Data Management Platform Studio version 7.2 (see attachment for patch details)

I am working on a job where I am running UPDATE statements in tDBRow (Postgres) components updating certain columns different staging tables after processing them in the following manner. 

The values being populated into the columns come from context variables. These context variables are being populated at runtime.

tJava -> tDBInput (Postgres) ->tJavaRow (populate context variables) 

-> OnSubJobOk ->

tJava

-> OnSubJobOk ->

tDBRow (Postgres) Update # 1

-> OnSubJobOk->

tJava -> tDBInput (Postgres) ->tJavaRow (populate context variables) 

-> OnSubJobOk ->

tDBRow (Postgres) Update # 2 

-> OnSubJobOk ->

tJava -> tDBInput (Postgres) ->tJavaRow (populate context variables) 

-> OnSubJobOk ->

tDBRow (Postgres) Update # 3

-> OnSubJobOk ->

tJava -> tDBInput (Postgres) ->tJavaRow (populate context variables) 

-> OnSubJobOk ->

tDBRow (Postgres) Update # 4

Job Snapshot shared in attachment.

The first update statement, runs successfully and updates the relevant columns (green

arrow) if records are available to satisfy the criteria in the where clause. However, any subsequent update statement either within the same tDBRow (Postgres) or in subsequent tDBRow (s) execute but do not update the relevant columns in different tables (red arrows) even if there are records that satisfy the criteria in the where clause.

My colleague and I tried to override the underlying postgresql-42.2.5.jar with the latest version available in Maven website - postgresql-42.2.23.jar within the Talend Studio but were unsuccessful as the job errored .

Can you please help in troubleshooting and resolving this issue?

Thanks very much

Kind regards

Rohini

[Anonymous]

Hi

I don't think the issue is related to the driver jar file Do you use an existing DB connection or create the connection on each tDBRow component? If former, don't forget to use a tDBCommit to commit the change.

Regards

Shong

[XJ_1630]

could you post the actual error message ? thx

[rohsuh]

hi @Shicong Hong​ , we use an existing tPostgresqlConnection throughout the job and we use a tPostgresqlCommit at the end to commit changes. Its confusing as to why the first tDBRow works and the rest dont.

@Xuan Junior​ there are no errors but a behaviour which is unexpected.

[XJ_1630 · Accepted solution]

if you are updating & retrieving details to update your variables (based on your screenshot) : check that the value of your variable is as you expect

@roshu is correct

if you use one connection to update records

**but** a different connection for reading records WHILE changes are not committed;

your variable may not read uncommitted changes

enjoy the views over the lovely little airport in Oxford-shire ..

[rohsuh]

@Xuan Junior​ ,In the whole job, I am using 2 DB Connections to connect to 2 separate schemas. I am retrieving value using one DB connection (tPostgresConnection_1) but passing that value to a context variable. Then I am using the value in the context variable to update column in a table using the second DB Connection (tPostgresConnection_1) . Will this approach not work? How do I get around this issue, if that's what's causing the update not to work?

Kind regards

Rohini

[XJ_1630]

@Rohini Subash​ - in this Forum is difficult to assert if the approach is correct or not; we do not have sufficient details of the job.

the above responses provide good pointers to seek a solution.

and, you are getting there!

keep us posted

BTW, i meant that @Shicong Hong​ was correct (see above, it was late!)

😅

[rohsuh]

Thanks ​ @Shicong Hong​ and @Xuan Junior​ for your pointers. I have managed to isolate the problem as @Xuan Junior​ mentioned down to two separate DB Connections being used interchangeably across multiple tDBInputs and tDBRows leading to this issue. I have found that using one single connection to select (tDBInput) and update (tDBRow) the data across source tables has helped minimising any values not being passed from one table (db schema #1) to another (db schema #2) and resolved the problem I was facing. In order to troubleshoot it, I had to deactivate one of the connections and resolve the problem. This has ensured that the values are being selected/updated correctly at the source tables. However, I have used the second DB Connection solely for updates (tDBOutput) for target system tables. I was also restricted by the fact that I could not commit anywhere during the entire ETL process (target system restrictions) and had to use a tDBCommit only at the end.

[rohsuh]

@Shicong Hong​ @Xuan Junior​ I am using tJDBCxxxx components to satisfy the above requirements I have and this seems to work without any of the above.