Oracle/VB.NET: Monitor Database Realtime

I have a VB.NET WinForms application that I would like to have monitor a specific table within an Oracle database in realtime (or as near realtime as possible).

Basically, if a new record gets insert,updated, or deleted from this table I want my application to display the changes immediately.

This table will be modified by other sources and not the application itself.

What would be the best way to implement something like this?

LVL 15
liebrandAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MattWareCommented:
Consider putting triggers on your database to detect instantly db changes (update, delete, etc.).  That trigger can use the COM Automation Option on Oracle.  That will allow your trigger to communicate directly with some service (a COM+ app would do the job nicely).  Your application should be a client to this service and the COM+ component will update your windows app realtime.  Hope that helps.

--M

for info on COM Automation:
http://home.fms.indiana.edu/users/cshelton/oracle/server.815/a69012/Output/ch1.htm

for info on COM+ applications w/VB.NET
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q315707&ID=kb;en-us;Q315707&SD=MSDN
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
liebrandAuthor Commented:
Is there no other way of doing it besides using COM+? I would like to purely use VB.NET and not involve VB6 at all.
0
MattWareCommented:
There are certainly many ways to solve the problem, however you can completely implement the above (with the exception of the minor piece of writing the trigger) with VB.NET.  The second link I included showed how to use VB.NET to create a COM+ transactional application.  The best way for me to get a notification of a database change is a trigger.  The best way for me to have an Oracle database communicate with a non-database entity (e.g. application) is via COM Automation.  You could certainly put a thread in your windows app that constantly checks the server, but this is far less efficient, and would not scale if many users began using the client.  It may seem like an overkill to have 3 components (trigger, COM app, and Windows app), but each one serves a discreet function and the architecture would scale well as your database or client was to grow.

--M
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

liebrandAuthor Commented:
Sorry for my ignorance (this is new to me)...

I would like to use the methods outlined in the second link (using VB.NET to create a COM+ transactional application) -- however, the example shows it doing an update, rather than looking for a change.

If my application is just sitting idle, how would these methods know to check for the change in the database?

Perhaps a very small and simple example (that fits what I am trying to do) would help me understand ( i can increase the points if necessary ).
0
liebrandAuthor Commented:
I guess we probably shouldnt worry about this anyways -- i used the sample from the second link and I get this error message:

System.InvalidOperationException: Distributed Transactions require Oracle
9i client software.

Server stack trace:
   at OracleCOMTest.ClsES.CheckForChange() in I:\USERS\PLiebran\My Documents\Vis
ual Studio Projects\OracleCOMTest\Module1.vb:line 22
   at System.Runtime.Remoting.Messaging.Message.Dispatch(Object target, Boolean
fExecuteInContext)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMes
sage msg, Int32 methodPtr, Boolean fExecuteInContext)

Exception rethrown at [0]:
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage req
Msg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgDa
ta, Int32 type)
   at OracleCOMTest.ClsES.CheckForChange() in I:\USERS\PLiebran\My Documents\Vis
ual Studio Projects\OracleCOMTest\Module1.vb:line 7
   at OracleCOMTest.Module1.Main() in I:\USERS\PLiebran\My Documents\Visual Stud
io Projects\OracleCOMTest\Module1.vb:line 33

I am running Oracle 8...

0
MattWareCommented:
Well, I can't help you much with Oracle versions, but please refer to the following link.  This will at least help you see what VB.NET can do for you in creating Distributed Transaction servers which I believe is exactly the solution you are searching for:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/disttranvbnet.asp
0
MattWareCommented:
But again - you really should look at how you can communicate with an external application with an Oracle trigger.  This is your best shot at efficient real-time updating.

--M
0
liebrandAuthor Commented:
The trigger is definately a viable option ( i am using triggers now ). The problem is I cannot use COM because my oracle database is sitting on a UNIX server (which does not support COM).

Ideally, I would also like to avoid the treading within the application because that is unnecessary traffic (and like you said, its inefficient).

I am not sure how else I can get oracle triggers to talk to an external application ... that is my problem.
0
liebrandAuthor Commented:
Does the Oracle COM Automation talk to the oracle listener on the client which in turn talks to the COM services on windows?

My DBA thinks that oracle needs to be running on windows instead of unix in order to use Oracle COM Automation.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.