OUTPUT clause in not enabled when using Read Database Operator

Question

Hi,

I am trying to execute MERGE statement using Read Database operator. Not able to use OUTPUT clause in insert statement  throwing syntax error. MERGE statement without OUTPUT clause is working fine in Execute SQL operator. But it is throwing error with output clause and also it doesn't have output port. , Please check the below (Merge.PNG) for the same.

Facing below error:

Thanks in advance.

BalazsBaranyRM · Answer

Hi!

Which database type are you using? The error message is coming from the database, so RapidMiner can't do a lot about it.

I just tested with Postgres, an insert statement wrapped in a common table expression worked and returned the new data in Read Database. So it is not completely impossible. You probably need to find a variant of the statement your database accepts in the "SELECT" context. If your database can wrap write statements in a common table expression, or you can create a function or procedure in the database that makes the changes, there is a chance to change the SQL.

This was my example query, likely PostgreSQL specific:

with insertselect as (
  insert into inserttest (data)
  values ('Test val from writable CTE')
  returning *
)
select *
from insertselect;

Regards,
Balázs

gLearn · Answer

Okay, here we need Read database, since Execute SQL is not having output port to get results.
Basically output clause is only having issue there inside Read base.

BalazsBaranyRM · Answer

Hi @gLearn,

you should use Execute SQL for statements that change the database. Read Database ist just for reading.

Regards,
Balázs