You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@camel.apache.org by Nicky Sandhu <ni...@hotmail.com> on 2007/08/29 17:14:28 UTC
Camel JDBC Component
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.
Re: Camel JDBC Component
Posted by Nicky Sandhu <ka...@gmail.com>.
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
>
--
View this message in context: http://www.nabble.com/Camel-JDBC-Component-tf4348358s22882.html#a12396711
Sent from the Camel - Development mailing list archive at Nabble.com.
Re: Camel JDBC Component
Posted by Brian McCallister <br...@skife.org>.
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.
>
Re: Camel JDBC Component
Posted by James Strachan <ja...@gmail.com>.
On 8/29/07, Nicky Sandhu <ni...@hotmail.com> wrote:
> James.Strachan wrote:
> >
> > On 8/29/07, Nicky Sandhu <ni...@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.
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/
Re: Camel JDBC Component
Posted by James Strachan <ja...@gmail.com>.
On 8/29/07, Nicky Sandhu <ni...@hotmail.com> 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/
Re: Camel JDBC Component
Posted by Nicky Sandhu <ni...@hotmail.com>.
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
--
View this message in context: http://www.nabble.com/Camel-JDBC-Component-tf4348358s22882.html#a12393157
Sent from the Camel - Development mailing list archive at Nabble.com.
Re: Camel JDBC Component
Posted by James Strachan <ja...@gmail.com>.
On 8/29/07, Nodet Guillaume <gn...@gmail.com> 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/
Re: Camel JDBC Component
Posted by Nodet Guillaume <gn...@gmail.com>.
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 <ni...@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)
>
>
> --
> 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/
Re: Camel JDBC Component
Posted by Nicky Sandhu <ni...@hotmail.com>.
James.Strachan wrote:
>
> On 8/29/07, Nicky Sandhu <ni...@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)
--
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.
Re: Camel JDBC Component
Posted by ja...@gmail.com.
On 8/29/07, Nicky Sandhu <ni...@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! :)\
> 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/