topic Re: Error pstmt_tDBOutput_1 cannot be resolved in Talend Studio

Error pstmt_tDBOutput_1 cannot be resolved

Anonymous — Tue, 04 Sep 2018 13:58:16 GMT

Hi,

I'm trying to migrate data from SQL to PostgreSQL using Talend Open Studio 7.0.1

I created the empty tables at the destination.

Instead creating static components I created a metadata table with "list of tables" to be migrated from source to destination. The table also contains source table name and destination table name.

I used 'tFlowToIterate' to fetch source and destination table names to global variables and next used these global variable names in the 'tDBInput' and 'tDBOutput' but throws the below generic error and could not understand the issue.

However, it works when I try to hardcode the table names without using global variable but fails when using global variables.

Am I missing any step here?

Error:

Error in the component's propertiesstmt_tDBOutput_1 cannot be resolved
pstmt_tDBOutput_1 cannot be resolved
pstmt_tDBOutput_1 cannot be resolved
pstmt_tDBOutput_1 cannot be resolved to a variable
pstmt_tDBOutput_1 cannot be resolved
pstmt_tDBOutput_1 cannot be resolved to a variable
pstmt_tDBOutput_1 cannot be resolved

Re: Error pstmt_tDBOutput_1 cannot be resolved

fdenis — Tue, 04 Sep 2018 14:16:17 GMT

can you show job and bd components configuration.

Re: Error pstmt_tDBOutput_1 cannot be resolved

Anonymous — Tue, 04 Sep 2018 14:29:38 GMT

I have attached a word document which has screenshots of all components.

pstm_49.docx

Re: Error pstmt_tDBOutput_1 cannot be resolved

fdenis — Tue, 04 Sep 2018 14:52:56 GMT

did you set any columns on row2?
I think you have set one column type dynamics.

Re: Error pstmt_tDBOutput_1 cannot be resolved

Anonymous — Tue, 04 Sep 2018 15:06:13 GMT

Nope, I did not define any column in row2.

I guess I found the issue but not a solution or workaround.

When I modify the 'tFlowToIterate' to get only one execution it succeeds but fails when it iterates more times.

This is because when three records are coming from iterator where each record gets a different set of columns to the tDBInput, however, row2 seems to have a static list of columns and is not refreshing with new columns and throws error.

Since the destination DB is already having all the tables defined and is not dependent on source DB schema, Is it possible to refresh the schema of row2 in each iteration?

Thanks,

Deepa

Re: Error pstmt_tDBOutput_1 cannot be resolved

fdenis — Tue, 04 Sep 2018 16:54:50 GMT

start by setting row2.column1 as Dynamics
it must solve your problem.

if it's not suffisant creat a new job with contexts values fieldsList TableName
use tRunJob to set contexe (globalMap) and run this job
this is the best way you can alow paralel run with it.
good luck

Re: Error pstmt_tDBOutput_1 cannot be resolved

Anonymous — Tue, 04 Sep 2018 17:07:21 GMT

Any lead which can help me set row2.column1 as dynamic.

I checked all options in row2 configuration but did not find to set the values dynamically.

Thanks

Deepa

Re: Error pstmt_tDBOutput_1 cannot be resolved

fdenis — Wed, 05 Sep 2018 08:04:15 GMT

dynamic is a type.
are you on the open version?
if yes you did not have this type.
but you can generate from source db one String who is an insert statements and run this insert query on the destination db.

Re: Error pstmt_tDBOutput_1 cannot be resolved

Anonymous — Wed, 05 Sep 2018 08:48:27 GMT

@fdenis

Thank you for the suggestion.

Yes, I'm using open studio and do not see this option. I recently started working with TOS 7.0.1.

Could you share any reference or more information on how to start with your idea?

Re: Error pstmt_tDBOutput_1 cannot be resolved

fdenis — Wed, 05 Sep 2018 09:12:06 GMT

Select CONCAT ('Insert into Talbe1 (field1,field2) values (', field1, ', ', field2, ')' from Table1
the row1,sqlString have to be executed on destination db

Re: Error pstmt_tDBOutput_1 cannot be resolved

Anonymous — Wed, 05 Sep 2018 10:07:45 GMT

I guess I should not be using 'tDBOutput' component. Could you please specify what components are required?

Re: Error pstmt_tDBOutput_1 cannot be resolved

fdenis — Wed, 05 Sep 2018 10:15:00 GMT

tPostgresSqlRow or tDbRow

Re: Error pstmt_tDBOutput_1 cannot be resolved

Anonymous — Tue, 18 Sep 2018 09:02:51 GMT

@fdenis

I am not sure how this would work if source and destination tables are on different databases.
Could you unfold more pointers for your suggestion?

Re: Error pstmt_tDBOutput_1 cannot be resolved

fdenis — Tue, 18 Sep 2018 09:49:44 GMT

the idea is to dynamically create a list of insert statement. SQL is a universal language.
Then ones you save SQL insert statement and you manually execute the queries file or you directly execute insert statement into destination db.

Re: Error pstmt_tDBOutput_1 cannot be resolved

Anonymous — Tue, 18 Sep 2018 12:52:02 GMT

I think I understood your hint but facing issues due to lack to knowledge on Talend.
I have attached the screenshots in a document for your reference.

My Steps:
I created connections for both source and destination.
Now on source, I use the query shared by you.

Now how to load this query to string and execute on the destination.

pstm_189.docx

Re: Error pstmt_tDBOutput_1 cannot be resolved

fdenis — Tue, 18 Sep 2018 13:09:31 GMT

ok,
First what is your SQL level?
did you lean Talend using tutos, cours or colleague?

Re: Error pstmt_tDBOutput_1 cannot be resolved

Anonymous — Tue, 18 Sep 2018 13:19:33 GMT

My SQL level is good(7 out of 10) but I'm very new to Talend, not more than 15 days of working experience.

I'm learning Talend through multiple ways like YouTube videos, other community posts and blogs.

Thank you.

Re: Error pstmt_tDBOutput_1 cannot be resolved

fdenis — Tue, 18 Sep 2018 13:30:48 GMT

ok so in talend orange links are dataset.
your first query have to retrieve a dataset containing only one Varchar data.
your first component (connection can be inside or outside) ) have to be tmssqlinput inside query write a sql query who extract a Varchar containing an sql insert statement (not mine, it's just a sample) .
add one column (columnName) to the output link and connect it to tLogRow.
run it.
when it's working connect it to tDBRow in query field insert row1.columnName to execute each row.

Re: Error pstmt_tDBOutput_1 cannot be resolved

Anonymous — Tue, 18 Sep 2018 15:08:08 GMT

Thanks for your detailed explanation.

However, I already learnt how these components work and also transformed data in a sample run.

As per the previous attachment, I'm struggling to create varchar using tDBRow, pass it to destination and execute the Varchar there.

This is the area which I need your help.

Thank you.

Re: Error pstmt_tDBOutput_1 cannot be resolved

fdenis — Tue, 18 Sep 2018 16:16:43 GMT

so in Query field:
"Select concat(('Insert into Table1 (field1,field2) values (',sid,',''',studentname,''')') from students"