Page 1 of 1

Correct way to execute SQL

Posted: Mon Jul 04, 2011 12:59 pm
by teebag
I have the following SQL

Code: Select all

set temporary option temp_extract_name1 = '/shared/jtesting/ei_phone.txt'
set temporary option temp_extract_column_delimiter = '|'

select *
from ei_phone
where pk_phone between 71894755 and 72542062

set temporary option temp_extract_name1 = ''

which works if I add it as "SQL query" of DB_Input_Table with an Edge to next component

but does not work if I use it as "SQL query" of DB_Execute which I thought would be the correct component to use for this kind of SQL.

Re: Correct way to execute SQL

Posted: Thu Jul 07, 2011 10:17 am
by jurban

DBExecute can work in 2 modes:

1) execute stored procedure - you have to indicate this by a parameter and then the results of the stored procedure are written to the output port of the component
2) execute a SQL query - this is the default, and the SQL query is executed by java.sql.Statement.executeUpdate(). In this case the return values of the SQL statement are not handled - which is your case.

So in summary - if you want to use DBExecute to execute SQL which returns some data, it has to use a stored procedure. I hope this gives you some insight into the area...

Best regards,