| Author |
Message
|
| Michaelscof |
 Posted: Wed Jun 22, 2011 2:49 pm Post subject: Regarding Databaseinput node |
 |
|
Novice
Joined: 12 Nov 2009
Posts: 10
|
Hi All,
My Message flow need to poll for database changes. I created message flow with databaseinput node.
Problem is,If I do any database operations(delete/update/insert) from data perspective, message flow is starting. But flow is not responding when I do the same database operations using SQL Developer.When I import and run the databaseinput node sample, flow is responding to the db operations performed from control center. Am I missing any configuration setting for this ? Please advice.
One more thing is, My message flow need to respond for select operation performed on database. Suggest me the better solution for this ..Thanks in advance.. |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Wed Jun 22, 2011 2:55 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You need to tell who ever wants you to respond to a select operation that they need to change this requirement.
It will cause the database to slow to crawl. It is one of the worst ideas ever.
I think you will otherwise find that the databaseinput node only reacts to committed changes and not to changes that are still in transactions. |
|
| Back to top |
|
|
| Michaelscof |
| Posted: Wed Jun 22, 2011 4:39 pm Post subject: |
|
|
Novice
Joined: 12 Nov 2009
Posts: 10
|
Thank you. I will inform them.
Why message flow is not responding, when I execute the queries(delete/insert/update) from sql plus or sql developer ? 
Message flow responding to the same queries executing from data perceptive in MB toolkit.. Please advice.. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Jun 22, 2011 6:02 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Maybe because SQLPlus is not issuing commit, and data explorer is? |
|
| Back to top |
|
|
| Michaelscof |
| Posted: Thu Jun 23, 2011 6:12 am Post subject: |
|
|
Novice
Joined: 12 Nov 2009
Posts: 10
|
Yes.. Absolutely right.. I tested from sql developer by issuing the commit after insert/update, flow started immediately.. It really helps. Thx 
Once I get the database row from dbinput node, I need to construct xml message and send to the queue. Then, downstream flow will pick that msg and do webservice call. Once I got the response from webservice, I need to update the application table which was configured in dbinput node.
If I do the update on application table, message flow will starts again(due to the committed transaction on that table again).
My requirement is once I update the table after web service call, my message flow did not respond.. Please advice.. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Jun 23, 2011 6:17 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Michaelscof wrote: |
| My requirement is once I update the table after web service call, my message flow did not respond.. Please advice.. |
You can't do that.
You can code your flow to ignore the update when it receives it.
You should really look at using something other than the initial database update to start your flow, you should look at having whatever creates the database update either send a message or call a webservice or write a file or etc. etc. etc...
Or you should have the initial update done to a staging table, and your flow then does the real update to the real table. |
|
| Back to top |
|
|
| Michaelscof |
| Posted: Thu Jun 23, 2011 8:09 am Post subject: |
|
|
Novice
Joined: 12 Nov 2009
Posts: 10
|
External application do only insert operations, then dbinput node responds to that and process the information.
My flow shouldn't be respond when we update the table. Give me the path how to code that part ? Thanks in advance.. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Jun 23, 2011 9:05 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Add a column to the table that is not updated by the inserts from the applications.
Update this column in your flow to indicate that you have touched this row.
Code your flow to ignore any messages that have this column set. |
|
| Back to top |
|
|
| Michaelscof |
| Posted: Thu Jun 23, 2011 11:20 am Post subject: |
|
|
Novice
Joined: 12 Nov 2009
Posts: 10
|
They don't want to update the application table by adding new column, because same table is using another application.
I tried like this in my personal system.
I delete the update portion in the trigger which I created on application table. Now when I run my flow, it is responding to delete/insert operations. flow is not responding to updates on application table.
Suggest me that I am in the right direction or not ..
I need to test the same thing on office workstation. Thx. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Jun 23, 2011 11:42 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
If they don't want to change the final table, then you need to put in a staging table.
But the only reason it would make any difference to the application that is already using the table if you added a column, would be if the application was using things like "Select *" without being specific about what columns existed in the tables. It shouldn't change anything to add another table, not really. |
|
| Back to top |
|
|
|
|
