Camel JDBC Component

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

Camel JDBC Component

Nicky Sandhu
In my rookie attempt to create the JDBC component (https://issues.apache.org/activemq/browse/CAMEL-128) I encountered the following questions
1. If the exchange out is send out the ResultSet (open cursor) then is there support for a callback on the exchange to allow a lifecycle of end on the exchange to allow one to close out the ResultSet. Instead I have created a limit on the read size and copied the data into a list of hash maps. Not very efficient and I don't like it much. Any suggestions ?

2. I would like the component to poll the database. Is such a polling consumer better accomplished by doing from("timer:...").setBody(sqlStmt).to("jdbc:...").pipeline("direct:a") ?

3. Lastly the conversion mechanism from result set to other types. Any suggestions on whether using the type converter is appropriate here ?
Reply | Threaded
Open this post in threaded view
|

Re: Camel JDBC Component

jstrachan
On 8/29/07, Nicky Sandhu <[hidden email]> wrote:
>
> In my rookie attempt to create the JDBC component
> (https://issues.apache.org/activemq/browse/CAMEL-128)

Great stuff BTW! Have applied your patch - keep up the great work! :)\


> I encountered the
> following questions
> 1. If the exchange out is send out the ResultSet (open cursor) then is there
> support for a callback on the exchange to allow a lifecycle of end on the
> exchange to allow one to close out the ResultSet. Instead I have created a
> limit on the read size and copied the data into a list of hash maps. Not
> very efficient and I don't like it much. Any suggestions ?

We're just in the process of adding an onComplete / onFailure handlers
to the exchange, so you can close things like result sets and the
like.

https://issues.apache.org/activemq/browse/CAMEL-123

Hopefully as soon as those are working we can use 'em


> 2. I would like the component to poll the database. Is such a polling
> consumer better accomplished by doing
> from("timer:...").setBody(sqlStmt).to("jdbc:...").pipeline("direct:a") ?

Yeah - that should work now (we changed the timer component to be more
reusable).

Another idea - in general - is to make the endpoint derive from
DefaultPollingEndpoint which has the 'poll by default' for a Consumer;
then you just implement a PollingConsumer for the endpoint. Though in
this case we probably wanna configure the SQL in the DSL, so using the
timer as above is maybe simplest.



> 3. Lastly the conversion mechanism from result set to other types. Any
> suggestions on whether using the type converter is appropriate here ?

Yeah - am sure we could think of some useful conversions; maybe to
Lists of Maps or something; or to XML? Maybe the cached JDBC result
set stuff might be useful? Or there's SDO? I've not looked at JDBC 4
yet but IIRC there's some SQL <-> POJO mapping stuff in there too I
think.

--
James
-------
http://macstrac.blogspot.com/
Reply | Threaded
Open this post in threaded view
|

Re: Camel JDBC Component

Brian McCallister
In reply to this post by Nicky Sandhu
Very cool -- do you recall if JDBC connections and result sets are  
allowed to be used across different threads?

-Brian

On Aug 29, 2007, at 8:14 AM, Nicky Sandhu wrote:

>
> In my rookie attempt to create the JDBC component
> (https://issues.apache.org/activemq/browse/CAMEL-128) I encountered  
> the
> following questions
> 1. If the exchange out is send out the ResultSet (open cursor) then  
> is there
> support for a callback on the exchange to allow a lifecycle of end  
> on the
> exchange to allow one to close out the ResultSet. Instead I have  
> created a
> limit on the read size and copied the data into a list of hash  
> maps. Not
> very efficient and I don't like it much. Any suggestions ?
>
> 2. I would like the component to poll the database. Is such a polling
> consumer better accomplished by doing
> from("timer:...").setBody(sqlStmt).to("jdbc:...").pipeline
> ("direct:a") ?
>
> 3. Lastly the conversion mechanism from result set to other types. Any
> suggestions on whether using the type converter is appropriate here ?
> --
> View this message in context: http://www.nabble.com/Camel-JDBC- 
> Component-tf4348358s22882.html#a12389082
> Sent from the Camel - Development mailing list archive at Nabble.com.
>

Reply | Threaded
Open this post in threaded view
|

Re: Camel JDBC Component

Nicky Sandhu
In reply to this post by jstrachan
James.Strachan wrote
On 8/29/07, Nicky Sandhu <nickysandhu@hotmail.com> wrote:
>
> In my rookie attempt to create the JDBC component
> (https://issues.apache.org/activemq/browse/CAMEL-128)

Great stuff BTW! Have applied your patch - keep up the great work! :)\
Cool. That was quick! It is still evolving so there maybe upcoming patches.


James.Strachan wrote
> I encountered the
> following questions
> 1. If the exchange out is send out the ResultSet (open cursor) then is there
> support for a callback on the exchange to allow a lifecycle of end on the
> exchange to allow one to close out the ResultSet. Instead I have created a
> limit on the read size and copied the data into a list of hash maps. Not
> very efficient and I don't like it much. Any suggestions ?

We're just in the process of adding an onComplete / onFailure handlers
to the exchange, so you can close things like result sets and the
like.

https://issues.apache.org/activemq/browse/CAMEL-123

Hopefully as soon as those are working we can use 'em
There you go again...keeping one step ahead of me :)

James.Strachan wrote
> 3. Lastly the conversion mechanism from result set to other types. Any
> suggestions on whether using the type converter is appropriate here ?

Yeah - am sure we could think of some useful conversions; maybe to
Lists of Maps or something; or to XML? Maybe the cached JDBC result
set stuff might be useful? Or there's SDO? I've not looked at JDBC 4
yet but IIRC there's some SQL <-> POJO mapping stuff in there too I
think.
List of maps is what it is doing right now. Really once you have onComplete available on exchanges I can allow the default of putting the ResultSet (read open cursor) on the exchange and refactor this conversion to a @Converter method. There is of course components like iBatis that work directly off a ResultSet and I can see implementing a converter component like
to("jdbc:...").convertTo(resultSetToObjectMapper) where the mapper converts the resultset to custom object using the mappers configuration. A little different from the @Converter strategy where there is only one map possible between two types and possibly a bit more dynamic (@Converters loaded at startup time vs mappers are configured before use)

Reply | Threaded
Open this post in threaded view
|

Re: Camel JDBC Component

jstrachan
On 8/29/07, Nicky Sandhu <[hidden email]> wrote:

> James.Strachan wrote:
> >
> > On 8/29/07, Nicky Sandhu <[hidden email]> wrote:
> >>
> >> In my rookie attempt to create the JDBC component
> >> (https://issues.apache.org/activemq/browse/CAMEL-128)
> >
> > Great stuff BTW! Have applied your patch - keep up the great work! :)\
> >
> Cool. That was quick! It is still evolving so there maybe upcoming patches.

Great! Patches are always welcome! :)


> James.Strachan wrote:
> >
> >> I encountered the
> >> following questions
> >> 1. If the exchange out is send out the ResultSet (open cursor) then is
> >> there
> >> support for a callback on the exchange to allow a lifecycle of end on the
> >> exchange to allow one to close out the ResultSet. Instead I have created
> >> a
> >> limit on the read size and copied the data into a list of hash maps. Not
> >> very efficient and I don't like it much. Any suggestions ?
> >
> > We're just in the process of adding an onComplete / onFailure handlers
> > to the exchange, so you can close things like result sets and the
> > like.
> >
> > https://issues.apache.org/activemq/browse/CAMEL-123
> >
> > Hopefully as soon as those are working we can use 'em
> >
> There you go again...keeping one step ahead of me :)

:)


> James.Strachan wrote:
> >
> >> 3. Lastly the conversion mechanism from result set to other types. Any
> >> suggestions on whether using the type converter is appropriate here ?
> >
> > Yeah - am sure we could think of some useful conversions; maybe to
> > Lists of Maps or something; or to XML? Maybe the cached JDBC result
> > set stuff might be useful? Or there's SDO? I've not looked at JDBC 4
> > yet but IIRC there's some SQL <-> POJO mapping stuff in there too I
> > think.
> >
> List of maps is what it is doing right now. Really once you have onComplete
> available on exchanges I can allow the default of putting the ResultSet
> (read open cursor) on the exchange and refactor this conversion to a
> @Converter method.

Great.

Am sure for folks who wanna use it, we could use SDO as another POJO
holder of the result set etc. e.g. am sure camel-sdo could add
converters from ResultSet <-> SDO DataObject stuff etc

I guess the sweet spot of SDO is being able to execute any old SQL &
turn it into a DataObject then perform expressions on it as a kinda
POJO thingy & turn it to/from XML. So either you use a custom mapping
to a POJO (JPA or custom Converter or iBatis) - or let SDO slurp it
up. Or there's always the List of Maps etc


> There is of course components like iBatis that work
> directly off a ResultSet and I can see implementing a converter component
> like
> to("jdbc:...").convertTo(resultSetToObjectMapper) where the mapper converts
> the resultset to custom object using the mappers configuration.

Yeah. I could imagine us having an iBatis component too; either as a
converter from result sets from the jdbc component, or for doing named
queries via the URL (where the actual SQL is in some XML config file
or something).

e.g.

from("ibatis:findRecentlyUpdatedOrdersQuery?productName=beer").to("activemq:topic:someTopic")



> A little
> different from the @Converter strategy where there is only one map possible
> between two types and possibly a bit more dynamic (@Converters loaded at
> startup time vs mappers are configured before use)

Yeah; sounds good to me.

--
James
-------
http://macstrac.blogspot.com/
Reply | Threaded
Open this post in threaded view
|

Re: Camel JDBC Component

Guillaume Nodet
Administrator
In reply to this post by Nicky Sandhu
Btw, I was wondering if a consumer could be implemented for JDBC too:
an example would be a polling consumer that polls for rows in a given  
table
and send a message for each new row (we need a strategy to determine if
a row is new: it could be deleting processed rows or flagging them  
somehow)...

On Aug 29, 2007, at 7:51 PM, Nicky Sandhu wrote:

>
>
> James.Strachan wrote:
>>
>> On 8/29/07, Nicky Sandhu <[hidden email]> wrote:
>>>
>>> In my rookie attempt to create the JDBC component
>>> (https://issues.apache.org/activemq/browse/CAMEL-128)
>>
>> Great stuff BTW! Have applied your patch - keep up the great  
>> work! :)\
>>
> Cool. That was quick! It is still evolving so there maybe upcoming  
> patches.
>
>
>
> James.Strachan wrote:
>>
>>> I encountered the
>>> following questions
>>> 1. If the exchange out is send out the ResultSet (open cursor)  
>>> then is
>>> there
>>> support for a callback on the exchange to allow a lifecycle of  
>>> end on the
>>> exchange to allow one to close out the ResultSet. Instead I have  
>>> created
>>> a
>>> limit on the read size and copied the data into a list of hash  
>>> maps. Not
>>> very efficient and I don't like it much. Any suggestions ?
>>
>> We're just in the process of adding an onComplete / onFailure  
>> handlers
>> to the exchange, so you can close things like result sets and the
>> like.
>>
>> https://issues.apache.org/activemq/browse/CAMEL-123
>>
>> Hopefully as soon as those are working we can use 'em
>>
> There you go again...keeping one step ahead of me :)
>
>
> James.Strachan wrote:
>>
>>> 3. Lastly the conversion mechanism from result set to other  
>>> types. Any
>>> suggestions on whether using the type converter is appropriate  
>>> here ?
>>
>> Yeah - am sure we could think of some useful conversions; maybe to
>> Lists of Maps or something; or to XML? Maybe the cached JDBC result
>> set stuff might be useful? Or there's SDO? I've not looked at JDBC 4
>> yet but IIRC there's some SQL <-> POJO mapping stuff in there too I
>> think.
>>
> List of maps is what it is doing right now. Really once you have  
> onComplete
> available on exchanges I can allow the default of putting the  
> ResultSet
> (read open cursor) on the exchange and refactor this conversion to a
> @Converter method. There is of course components like iBatis that work
> directly off a ResultSet and I can see implementing a converter  
> component
> like
> to("jdbc:...").convertTo(resultSetToObjectMapper) where the mapper  
> converts
> the resultset to custom object using the mappers configuration. A  
> little
> different from the @Converter strategy where there is only one map  
> possible
> between two types and possibly a bit more dynamic (@Converters  
> loaded at
> startup time vs mappers are configured before use)
>
>
> --
> View this message in context: http://www.nabble.com/Camel-JDBC- 
> Component-tf4348358s22882.html#a12392303
> Sent from the Camel - Development mailing list archive at Nabble.com.
>

--
Cheers,
Guillaume Nodet
------------------------
Blog: http://gnodet.blogspot.com/

Reply | Threaded
Open this post in threaded view
|

Re: Camel JDBC Component

Nicky Sandhu
gnodet wrote
Btw, I was wondering if a consumer could be implemented for JDBC too:
an example would be a polling consumer that polls for rows in a given  
table
and send a message for each new row (we need a strategy to determine if
a row is new: it could be deleting processed rows or flagging them  
somehow)...
That was the use case I had before I started down this path. I believe I can now do something like
 from("timer:poller?period=10000").setBody("select * from table where somecondition is true").to("jdbc:myDatasource?readSize=1000").splitter(rowSplitter).process(myprocess);

Note..I still have to write a row splitter component but that should be easy (substitute your own splitting process for now)

Now whats missing still above is deleting the processed rows ... that goes back to being able to support onComplete or onError ... so once James and Hiram stabilize their commits we should be able to wrap a transaction around this and in the onComplete part of that do something like
setBody("delete from table where somecondition is true").to("jdbc:myDatasource") or onError do something else
Reply | Threaded
Open this post in threaded view
|

Re: Camel JDBC Component

Nicky Sandhu
In reply to this post by Brian McCallister
I am not sure if it is thread safe...however the jdbc component is thread safe. the connections and result sets are closed by the producer so there should be no leaks there. connection/statement  reuse should be handled by DataSource impls in the driver
Brian McCallister wrote
Very cool -- do you recall if JDBC connections and result sets are  
allowed to be used across different threads?

-Brian
Reply | Threaded
Open this post in threaded view
|

Re: Camel JDBC Component

jstrachan
In reply to this post by Guillaume Nodet
On 8/29/07, Nodet Guillaume <[hidden email]> wrote:
> Btw, I was wondering if a consumer could be implemented for JDBC too:
> an example would be a polling consumer that polls for rows in a given
> table
> and send a message for each new row (we need a strategy to determine if
> a row is new: it could be deleting processed rows or flagging them
> somehow)...

Agreed! FWIW the JPA component does that...
http://activemq.apache.org/camel/jpa.html

--
James
-------
http://macstrac.blogspot.com/
Reply | Threaded
Open this post in threaded view
|

Re: Camel JDBC Component

jstrachan
In reply to this post by Nicky Sandhu
On 8/29/07, Nicky Sandhu <[hidden email]> wrote:

> gnodet wrote:
> >
> > Btw, I was wondering if a consumer could be implemented for JDBC too:
> > an example would be a polling consumer that polls for rows in a given
> > table
> > and send a message for each new row (we need a strategy to determine if
> > a row is new: it could be deleting processed rows or flagging them
> > somehow)...
> >
>
> That was the use case I had before I started down this path. I believe I can
> now do something like
>  from("timer:poller?period=10000").setBody("select * from table where
> somecondition is
> true").to("jdbc:myDatasource?readSize=1000").splitter(rowSplitter).process(myprocess);

As I kinda alluded to in a previous mail in this thread; if folks
wanted we could try simplify this DSL statement a bit by using some
way to lookup named queries via the URI. e.g. if we did support
ibatis, or some ibatis like mechanism to make it easy to refer to
named queries to simplify the polling DSL...

from("ibatis:findRecentlyUpdatedOrdersQuery?productName=beer").to("activemq:topic:someTopic")

this would combine the from(timer).setBody(sql).to(jdbc) to a single
from() statement - at the cost of having a level of indirection for
the SQL; looking it up by name in the Registry or an iBatis mapping
file etc


> Note..I still have to write a row splitter component but that should be easy
> (substitute your own splitting process for now)

I wonder if splitting by row is gonna be such a common use case, we
might wanna enable that as an option by default in the jdbc component?
I guess the issue is what to convert the row to; so I guess we need to
use the DSL to define how to split it and once split, how to convert
the rows into something.

Is there an easy expression to turn a resultset into individual row
objects? (Then we could use ay of the existing expression languages
like EL).


> Now whats missing still above is deleting the processed rows ... that goes
> back to being able to support onComplete or onError ... so once James and
> Hiram stabilize their commits we should be able to wrap a transaction around
> this and in the onComplete part of that do something like
> setBody("delete from table where somecondition is
> true").to("jdbc:myDatasource") or onError do something else

BTW the JPA component does this - its just you've gotta write an
entity bean for your table first which means it does take a bit longer
to get stuff done. The entity bean class name is used to define the
query to poll; then the entity bean is deleted (or updated) when its
been processed.
--
James
-------
http://macstrac.blogspot.com/