Solved

Oracle/VB.NET: Monitor Database Realtime

Posted on 2003-12-05
10
1,240 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
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…

757 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

20 Experts available now in Live!

Get 1:1 Help Now