none
Oracle Polling using function/storedproc in PolledDataAvailableStatement RRS feed

  • Question

  • Hi all,

    The documentation of the WCF Oracle Adapter for BizTalk has a note about the PolledDataAvailableStatement that says : "You must not specify stored procedures for this binding property. Also, this statement must not modify the underlying Oracle database" (http://msdn.microsoft.com/en-us/library/dd788467(v=bts.10).aspx).

    I just want to know why the PolledDataAvailableStatement must not modify the underlying database? In some scenarios, I use a pattern where I update records in the PolledDataAvailableStatement and use these records in the PollingStatement, and this pattern avoid missing records.

    Any inputs?


    David GROSPELIER ReachSOA - Architect and co-founder dgr@reachsoa.com http://www.reachsoa.com
    Monday, September 5, 2011 3:20 PM

Answers

  • the reason for the requirement of non modification is that pollDataAvailableStatement may be executed N times before the polling statement is executed, and this might lead to an inconsistent state of the database. Also, this is executed outside of the transaction. If your statement takes care of these, then it should be OK.
    -- Please mark as answered if this answers your question.
    Tuesday, September 6, 2011 4:29 AM

All replies

  • Hi, 

    Not sure if I get the requirement correctly. I have seen a select and an update in the PollingStatement property. But why would you require an update as part of the PolledDataAvailableStatement property. If you update records in PolledDataAvailableStatement and use these records in PollingStatement, how would this avoid missing records. However, not sure why the note is there in the documentation but it is per design, for the functionality in the PolledDataAvailableStatement we check whether  any data is available for PollingStatement to fire.


    Cheers,
    Bali
    MCTS: BizTalk Server 2010,BizTalk Server 2006 and WCF
    -----------------------------------------------------
    Mark As Answer or Vote As Helpful if this helps.
    Monday, September 5, 2011 6:21 PM
  • the reason for the requirement of non modification is that pollDataAvailableStatement may be executed N times before the polling statement is executed, and this might lead to an inconsistent state of the database. Also, this is executed outside of the transaction. If your statement takes care of these, then it should be OK.
    -- Please mark as answered if this answers your question.
    Tuesday, September 6, 2011 4:29 AM
  • Hi Bali,

    To be more precise, I tried 2 solutions for Oracle polling.

     

    In the first solution, I use a simple select statement in the PollingStatement to retrieve the records. In the PostPollingStatement I update these records, The main trouble of this approach is possible missing records (records that are created in the source table between the two statements).

     

    In the second approach, I use a Select + Update statement in the PoolingStatement (to mark the records as 'pending'). In the PostPollingStatement, I update the pending records. But in this solution, I have a blocking situation between the 2 transactions opened by the adapter, I have done many tests with my customer Oracle experts in order to detect the locks and we can't find a solution.

     

    So the better solution I found is to update the records in the PollingDataAvaikableStatement, to select these records in the PollingStatement and update again in the PostPollingStatement. This solution is live for a customer for a long time and it works very fine.

    I was just wondering why Microsoft says that we don't have to change the underlying database in the PolledDataAvailable statement.


    David GROSPELIER ReachSOA - Architect and co-founder dgr@reachsoa.com http://www.reachsoa.com
    Monday, September 12, 2011 6:45 AM
  • Hi David,

    I had tried the second approach once and had similar issues and had taken a different route and avoided update in the PolledDataAvailableStatement. 

    In your case, the working solution is perfect as even if the PolledDataAvailableStatement run N-times you would just be updating the flag so the DB is fine. 

    This is a good candidate for an undocumented feature if its handled in a proper way like you are doing.


    Cheers,
    Bali
    MCTS: BizTalk Server 2010,BizTalk Server 2006 and WCF
    Blog: http://dpsbali-biztalkweblog.blogspot.com
    -----------------------------------------------------
    Mark As Answer or Vote As Helpful if this helps.
    • Edited by DPS Bali Monday, September 12, 2011 7:15 AM
    Monday, September 12, 2011 7:15 AM
  • Hi David,

    how did you setup the PolledDataAvailableStatment property to do the update (and return a positive value if data was available)

    I'm trying to get a similar solution up and running with BizTalk 2009 and Adapter Pack 2.0.

    thanks
    /Henrik


    /Henrik

    Monday, February 13, 2012 4:23 PM
  • Hi Henrik

    Did you ever get an answer to this as I am experiencing the same issues!

    Thanks and Regards,

    Dave

    Tuesday, March 19, 2013 11:58 AM