Solved

Oracle/VB.NET: Monitor Database Realtime

Posted on 2003-12-05
10
1,245 Views
Last Modified: 2008-02-20
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?

0
Comment
Question by:liebrand
  • 5
  • 4
10 Comments
 
LVL 2

Accepted Solution

by:
MattWare earned 250 total points
ID: 9883789
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
 
LVL 15

Author Comment

by:liebrand
ID: 9883886
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
 
LVL 2

Expert Comment

by:MattWare
ID: 9883955
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
 
LVL 15

Author Comment

by:liebrand
ID: 9883994
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 15

Author Comment

by:liebrand
ID: 9884055
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
 
LVL 2

Expert Comment

by:MattWare
ID: 9884124
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
 
LVL 2

Expert Comment

by:MattWare
ID: 9884130
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
 
LVL 15

Author Comment

by:liebrand
ID: 9884147
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
 
LVL 15

Author Comment

by:liebrand
ID: 9884189
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
This is an explanation of a simple data model to help parse a JSON feed
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now