How to insert into a postgres table using a tDBConnection that references a shared connection
Hello!
Our team is experimenting with sharing connections from one of our initialization joblets to all of the components within a calling job.
I've attached a numbered screenshot to explain.
This is where the shared connection is initialized, using the "use or register a shared DB connection" tic box.
This is another connection. All the fields are blank, but the same tic box is also checked, and the shared DB connection value matches step 1.
This runs a simple query using the connection in step 2. This step is SUCCESSFUL.
This also uses the connection from step 2. It attempts to insert, but it returns a "relation "some_schema.some_table_name" does not exist.
It's strange that the query works, but the insert doesn't work. I know with 100% certainty that the table being inserted into DOES IN FACT exist. There is no doubt of this. When I switch the step 4 insert to use the connection from the SetupLoad joblet, it succeeds. So it appears that there is some difference between the two, even though the step 2 connection just references the step 1 connection.
I have tried specifically declaring the database schema, and also omitting it. No difference.
Is it possible to do an insert using a tDBConnection component that uses a shared DB connection?
NOTE: in the future, we want to use this technique within other joblets that need to connect to databases, so that's why I'm not just selecting the step 1 tDBConnection component in all of my database components. We want the joblets to be able to initialize a connection using the "shared DB connection" functionality. But we need them to do inserts/updates.
When you specifically declared the database schema, did you do this on the source connection shared by your joblet or was it done on the "client" tDBconnection component inside your job?
I believe it should work if the source tDBconnection points to the same schema your destination table is in.
Try to retrieve the schema from the metadata connection and use that as an output(The schema would be the same as the table and you can check if talend is reading it).
Check your config of the component, set it to "create table if not exist" and set the action to "INSERT"
If your not sure, you can always use the lookup tool from the component config.(right side of the table name box)
