Question about tdbRow component

[Serphentelm]

Hi,

is possible in the tdbRow component calling a routine's method followed by un update?

for example, in my query window I want to execute the following:

Utility.addColumn("my_table", "column_name", "column_type");

"update my_table set column_name = ... where ... ";

Utility.addColumn("my_table2", "column_name", "column_type");

"update my_table2 set column_name = ... where ... "

The method was defined in the User defined routines and just check if a column exists and it will drop it and the it recreate the column empty.

Is there a way to achieve this?

[anselmopeixoto]

Hi @matteo marchesi​ 

I suggest you use a tJava component to call the routine and them connect this component to tDBRow using OnComponentOk trigger.

[Serphentelm]

hi anselmopeixoto; thanks to the reply.

Does the tJava component be linked to an input? Because I've tried as you suggested as following:

but the component isnt'd doing anything.

In the tJava component i call 3 times the same method as above, that will return 3 sql statements; e.g:

Alter table tmp add my_col int;

Alter table tmp add my_col2 int;

Alter table tmp add my_col3 varchar(20);

[anselmopeixoto · Accepted solution]

Hi @matteo marchesi​ 

I'm sorry for I didn't understand this requirement initially.

Does your routine return a SQL command as a String?

If that's the case, executing the routine inside tDBRow should work, but I would split one command per tDBRow (it is possible to run multiple commands on a single tDBRow, but it becomes harder to catch/analyze errors origin and sometimes you need to add an additional parameter to the connection to allow multiple commands).

So, I would suggest you put it like this:

tDBRow_1: Utility.addColumn("my_table", "column_name", "column_type");

OnComponentOk

tDBRow_2:

"update my_table set column_name = ... where ... "

and so on...

[Serphentelm]

Yeah, sorry for the poor information I gave.

Since the method just return an sql query as a string, I figured out that I can simply concat them with the + operator.

I got your suggestion about having a more readeable situazione with single tRow component, but since lot of my commitments are just adding few columns, I'll evalueate every situation if it's more convenient using more component or just having one with multiple instruction. Thank anyway