Ability to execute Store Procedure

[brunodomenici]

Hi,

Is it possible to implement JDBC source and sink to execute an Stored Procedure to get and write data on database?

Thank you

[ericpearl]

this or any kind of additional flexibility would be good

[liukrimhrim]

Hi,

Is it possible to implement JDBC source and sink to execute an Stored Procedure to get and write data on database?

Thank you

Hi, there! We are considering implementing this feature. To help us here, I wonder if you would please elaborate your needs a bit. To be specific, under what kind of scenarios you would want to use stored procedure on the source side or on the sink side. A few example would also be very helpful. Thank you!

[liukrimhrim]

this or any kind of additional flexibility would be good

As I mentioned in the other reply, we are considering implementing this feature. If you would like to elaborate your needs a bit, it will help us a lot. Thank you!

[brunodomenici]

I believed it could be useful both, source and sink. My case specifically was on source. I needed to call a Store Procedure with the timestamp of connector's last execution (like in timestamp pool mode). If we are able to set which parameter IN is the timestamp field and which OUT parameter connect should send data to the topic, something like that:

CREATE OR REPLACE PROCEDURE PROCEDURE1 
(
  LAST_TIMESTAMP IN DATE 
, RESULT OUT CURSOR 
) AS 
BEGIN
  NULL;
END PROCEDURE1;

[sangvk]

I want to read input data from my kafka topic and then execute a stored procedure in the sink connector, is that possible?

[pshussain]

I am also looking for the stored procedure execution in sink connector

[Avijit-07]

I want to read input data from my kafka topic and then execute a stored procedure in the sink connector, is that possible?

Exact same scenario I'm looking for as well. Cheers.

[shaunmaris]

The ability to execute stored procedures through a sink connector will be a massive win. This will add a lot of flexibility

[Avijit-07]

Hi,

Is there any update on this feature? Seems like it'll be a great feature to remove dependency from scheduler based approach to pull data from a SP .

[polakr]

Hello, any plans with the SP sink connector? Is SP world considered only as not cool enough to be covered anymore or there is some block that prevents mapping data to SP calls? May custom sink connector be a solution then? Anyone tried to build custom sink calling SPs (Oracle or MSSQL or any)?

[NathanNam]

@Avijit-07 @sangvk @shaunmaris could you share some examples of how you want to use a predefined procedure with records from Kafka in JDBC Sink?

[dmarupov]

Has this feature ever been implemented? Meaning the ability to call a stored procedure in the DB from the connector? If not, would it be possible to change the code to achieve this goal? Thank you.

[NathanNam]

@dmarupov It has not been prioritized. Since the code is open-sourced, you can customize this connector.

[const]

For those who want it for sink. For Postgres it is possible to specify table.types = "TABLE, VIEW" in the sink configuration, then create a view, and than create a trigger on the view of type "INSTEAD OF INSERT" that calls stored procedure. BTW please document the property table.types in the documentation. Approach for other databases might differ.

We have used it work around problems with the default upsert strategy.

I had no need of this for the source, but if you could create a view from the stored procedure call, this should theoretically work as well.

[Schm1tz1]

Hi, it is not designed to be used that way but possible to use stored procedure output also with a JDBC Source. For bulk mode it will simply execute the query and process the result, for incremental mode it is a bit tricky as he will append a WHERE clause to the query. I did some examples for these two cases here: https://gist.github.com/Schm1tz1/589a794b7b2a4daa1919088c8899cdad