Change Code?

One Star

Change Code?

Hi,
I created a tdbinput
then a tPerlRow
then a tdbOutput
the 3 objects are linked via main link input->perlrow->output
in the tperlrow i calculate the values for the output
il all works fine
But somethimes i would like to say to tdboutput not to write anything
to perform this i wrote the tperlrow so that it gives to the tdboutput the () value ( @tPerlRow_1=() )
i would like to let dbinput not to insert anything if the row in input is ()...
is it possible for me to modify the automatic code generated adding a if (@tDBOutput_1) before the push?
Or there is some other way?
thank u

Accepted Solutions
One Star

Re: Change Code?

I was able to solve my own problem by trial and error. I eventually tried enough combinations and came up with an oracle error of ora-01003 which let me to use a tJava with several tOrarows and that did the trick for me

All Replies
One Star

Re: Change Code?

Hi Gb!
In fact you've a component to do just this: the tMap.
Connect your tdbInput to a tMap and the tMap to your tdbOutput.
Then double-click on the tMap and drag'n drop columns from left-table to the right-table.
Then add a constraint on your right table (your ouput) clicking on the little-green-plus-with-question-mark icon (sic) on the top of the table.
In this field, add your code constraint (eg: $row1 eq '' or wathever else).
If you want to get rejected lines, just follow this :
Add a second output clicking on the main-green-plus-icon on the top of the right column.
Add some columns on this new output and click on the reject-checkbox on the top of this table.
Close the mapper using "Ok" and connect the new tMap output (NewOutput1 in my example) on (for example) a tFileOutputXML.

Regards,
Fabrice
ps: if you really need to do specific calculation you can add a tPerlRow beetween the tDBInput and the tMap
One Star

Re: Change Code?

Great i'll try it...
onother question...
in my schema (not yet using the map)
is it possible in the code i wrote in the tPerlRow to know if i am in the last row of the tdbinput?
Employee

Re: Change Code?

is it possible in the code i wrote in the tPerlRow to know if i am in the last row of the tdbinput?

Yes it is :-) There is a global variable associated to the tDBInput : NB_LINE. In your tPerlRow "Code" property, press Ctrl+Space and choose the variable in the auto-completion list.
PS: by the way, can you create a new topic for each question you have, it will be easier for other users to find questions & answers :-) thank you
One Star

Re: Change Code?

I am sorry but the ($_globals{tPerlRow_1}{NB_LINE} gives empty value.
Employee

Re: Change Code?

take $_globals{tDBInput_1}{NB_LINE} instead :-)
One Star

Re: Change Code?

It returns -1
Employee

Re: Change Code?

You're right, it was working with MySQL and I had assumed it was working this way for any database. Reading more carefully DBI module documentation, I see:
For SELECT statements, execute simply "starts" the query within the database engine. Use one of the fetch methods to retrieve the data after calling execute. The execute method does not return the number of rows that will be returned by the query (because most databases can't tell in advance), it simply returns a true value.

My conclusion: it works with MySQL, it doesn't work with Oracle (it returns "0E0") and I don't know yet for other DBMS.
So, we have a problem. The only reliable way to solve this problem is to increment the line counter at each iteration. The problem with this solution is that the total number of lines is available only at the end of the loop, and you can't use it in the tPerlRow linked to the tDBInput.
Anyway, I've created the bug 196 in the bugtracker concerning this issue.
Employee

Re: Change Code?

It returns -1

Which DBMS?
One Star

Re: Change Code?

SQLSERVER.
Now, how to know when i am at the end?
It would be ok for me now because i need to perform an operation only at the end and so i don't realy need total number of lines.
Is there something like .EOF?
Employee

Re: Change Code?

You can't know in the tPerlRow if it's the last iteration. But you can do it another way: instead of trying to know if it's the last iteration to perform a specific action, use a tPerl with a "trigger, run after" link, as illustrated by the screenshot.
One Star

Re: Change Code?

Hi,
I would like to be able to run the following oracle commands within Talend. Is there a way?
drop table contr_catalog_newcols;
create table contr_catalog_newcols as select * from contr_catalog
where M_PRICE <> '-10.00%';
--add columns
alter table contr_catalog_newcols add
(mb_client_product_id varchar(100), mb_online_customer_price char(14));
commit;
--for product
--find m_clientproductid
update contr_catalog_newcols set mb_client_product_id=trim(substr(m_product_id, 1, instr(m_product_id, ':',1,1)-1));
commit;
update contr_catalog_newcols set mb_client_product_id=trim(m_product_id) where mb_client_product_id is null;
commit;
and then I can do my regular mapping
thanks,
bob
One Star

Re: Change Code?

I was able to solve my own problem by trial and error. I eventually tried enough combinations and came up with an oracle error of ora-01003 which let me to use a tJava with several tOrarows and that did the trick for me