Correct way to execute SQL

teebag

• July 04, 2011 00:00

• Answered

Follow

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.

• 

  jurban

  • July 07, 2011 09:17

  0

  Comment actions Permalink

  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

Please sign in to leave a comment.