Running a delete query within a route

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Running a delete query within a route

Matt Raible
I have a route that does a number of things:

.process(lookupToHeadersProcessor) // 1) converts input parameters to headers
.to("bean:myStoredProcedure") // 2) calls a stored procedure, that enters a record in a TEMP table and returns the ID
.recipientList(simple("sql:select ret_string from TEMP " +
                "where seq = ${body['v_num']}?dataSource=ds.customerName")) // 3) executes a query to get a string
// todo: DELETE from TEMP where seq = v_num;
.marshal().string("UTF-8") // 4) converts input string to string for CSV parsing
.unmarshal(csv) // 5) converts string to fields
.process(memberLookupProcessor) // 6) convert List<List<String>> from unmarshalling to Member object

How can I execute the DELETE query above w/o modifying the output between route steps? I believe if I input it in the location above, the string won't be returned from the previous step.

Thanks,

Matt
Reply | Threaded
Open this post in threaded view
|

Re: Running a delete query within a route

Claus Ibsen-2
Hi

Ah yeah in the EIP senses its more like a content enricher where you
do not enrich anything. You can do this today, but then you need to
provide a aggregation strategy that just returns the oldExchange.

Though we could also consider to make this easier out of the box, with
a sorta <noop> DSL or noop=true in a <to> etc.

As a content enricher that does not enrich is a bit "confusing".

You can also use the wiretap but then the delete happens in another
process and the original route just continues.

An alternative is to use a processor/bean and do the delete from
there. You can use a producer template if you want the camel sql
component to do it still.

That said we have in the past people asking for similar noop but when
they are executing some script such as groovy script etc. Which you
can do with the language component and set transform=false.

Any thoughts?


On Fri, Aug 22, 2014 at 12:06 AM, Matt Raible <[hidden email]> wrote:

> I have a route that does a number of things:
>
> .process(lookupToHeadersProcessor) // 1) converts input parameters to headers
> .to("bean:myStoredProcedure") // 2) calls a stored procedure, that enters a record in a TEMP table and returns the ID
> .recipientList(simple("sql:select ret_string from TEMP " +
>                 "where seq = ${body['v_num']}?dataSource=ds.customerName")) // 3) executes a query to get a string
> // todo: DELETE from TEMP where seq = v_num;
> .marshal().string("UTF-8") // 4) converts input string to string for CSV parsing
> .unmarshal(csv) // 5) converts string to fields
> .process(memberLookupProcessor) // 6) convert List<List<String>> from unmarshalling to Member object
>
> How can I execute the DELETE query above w/o modifying the output between route steps? I believe if I input it in the location above, the string won't be returned from the previous step.
>
> Thanks,
>
> Matt



--
Claus Ibsen
-----------------
Red Hat, Inc.
Email: [hidden email]
Twitter: davsclaus
Blog: http://davsclaus.com
Author of Camel in Action: http://www.manning.com/ibsen
hawtio: http://hawt.io/
fabric8: http://fabric8.io/
Reply | Threaded
Open this post in threaded view
|

Re: Running a delete query within a route

Matt Raible
On Fri, Aug 22, 2014 at 1:09 AM, Claus Ibsen <[hidden email]> wrote:

> Hi
>
> Ah yeah in the EIP senses its more like a content enricher where you
> do not enrich anything. You can do this today, but then you need to
> provide a aggregation strategy that just returns the oldExchange.
>
> Though we could also consider to make this easier out of the box, with
> a sorta <noop> DSL or noop=true in a <to> etc.
>
> As a content enricher that does not enrich is a bit "confusing".
>
> You can also use the wiretap but then the delete happens in another
> process and the original route just continues.
>
> An alternative is to use a processor/bean and do the delete from
> there. You can use a producer template if you want the camel sql
> component to do it still.
>
> That said we have in the past people asking for similar noop but when
> they are executing some script such as groovy script etc. Which you
> can do with the language component and set transform=false.
>
> Any thoughts?
>

I ended up using the processor route.

.to("bean:myStoredProcedure")
.to(logOutput)
.process(restatJdbcProcessor) // executes a query to get a string and
executes a delete
.unmarshal(csv)
.to(logOutput)
.process(memberLookupProcessor) // convert CSV output to Member object

I think a noop would be useful, but using a processor was an easy (and
probably cleaner) workaround in this case.

Thanks!

Matt


>
> On Fri, Aug 22, 2014 at 12:06 AM, Matt Raible <[hidden email]>
> wrote:
> > I have a route that does a number of things:
> >
> > .process(lookupToHeadersProcessor) // 1) converts input parameters to
> headers
> > .to("bean:myStoredProcedure") // 2) calls a stored procedure, that
> enters a record in a TEMP table and returns the ID
> > .recipientList(simple("sql:select ret_string from TEMP " +
> >                 "where seq =
> ${body['v_num']}?dataSource=ds.customerName")) // 3) executes a query to
> get a string
> > // todo: DELETE from TEMP where seq = v_num;
> > .marshal().string("UTF-8") // 4) converts input string to string for CSV
> parsing
> > .unmarshal(csv) // 5) converts string to fields
> > .process(memberLookupProcessor) // 6) convert List<List<String>> from
> unmarshalling to Member object
> >
> > How can I execute the DELETE query above w/o modifying the output
> between route steps? I believe if I input it in the location above, the
> string won't be returned from the previous step.
> >
> > Thanks,
> >
> > Matt
>
>
>
> --
> Claus Ibsen
> -----------------
> Red Hat, Inc.
> Email: [hidden email]
> Twitter: davsclaus
> Blog: http://davsclaus.com
> Author of Camel in Action: http://www.manning.com/ibsen
> hawtio: http://hawt.io/
> fabric8: http://fabric8.io/
>