Grokbase Groups Camel dev August 2007
FAQ
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.

Search Discussions

  • James Strachan at Aug 29, 2007 at 5:01 pm

    On 8/29/07, Nicky Sandhu 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.
  • Nicky Sandhu at Aug 29, 2007 at 5:52 pm

    James.Strachan wrote:
    On 8/29/07, Nicky Sandhu 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.
  • James Strachan at Aug 29, 2007 at 6:07 pm

    On 8/29/07, Nicky Sandhu wrote:
    James.Strachan wrote:
    On 8/29/07, Nicky Sandhu 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.
  • Nodet Guillaume at Aug 29, 2007 at 6:08 pm
    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 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/
  • Nicky Sandhu at Aug 29, 2007 at 6:38 pm

    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.
  • James Strachan at Aug 30, 2007 at 6:31 am

    On 8/29/07, Nicky Sandhu 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 Strachan at Aug 30, 2007 at 6:01 am

    On 8/29/07, Nodet Guillaume 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
  • Brian McCallister at Aug 29, 2007 at 6:08 pm
    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.
  • Nicky Sandhu at Aug 29, 2007 at 10:07 pm
    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdev @
categoriescamel
postedAug 29, '07 at 3:15p
activeAug 30, '07 at 6:31a
posts10
users5
websitecamel.apache.org

People

Translate

site design / logo © 2022 Grokbase