Correct way to execute SQL

Support/help with CloverETL (4.9) and CloverDX (5.0 or newer) implementation problems

teebag
Posts: 2
Joined: Mon Jul 04, 2011 12:52 pm

Correct way to execute SQL

Postby teebag » Mon Jul 04, 2011 12:59 pm

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 = ''
go


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.

jurban
Posts: 163
Joined: Fri Jul 20, 2007 9:25 am

Re: Correct way to execute SQL

Postby jurban » Thu Jul 07, 2011 10:17 am

Hi,

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,
Jaro
Jaroslav Urban
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com