Change Code?

[_AnonymousUser]

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

[Anonymous]

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

[_AnonymousUser]

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?

[Anonymous]

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

[_AnonymousUser]

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

[Anonymous]

take $_globals{tDBInput_1}{NB_LINE} instead 🙂

[_AnonymousUser]

It returns -1

[Anonymous]

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.

[Anonymous]

It returns -1

Which DBMS?

[_AnonymousUser]

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?