Correct way to execute SQL

CloverDX Platform
1

I have the following SQL

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.

2

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

1 Like