Re: tracking changes on a table through ODBC application

db_guy_2002_at_yahoo.com (db dude) wrote

> > ODBC.. real time?
> >
> Or close to real time, but resending queries is not an option. I would
> prefer to lower the isolation level and keep an open cursor.

Why? I can not think off-hand on any reason why you want to that...

Problem. How do you "detect" a change on a table and/or row.

Answer. Trigger.

Problem. How do I propogate changes in a table to another database and/or table?

Answer. Replication/snapshots/materialised views.

I fail to see where a cursor and ODBC fit into it... specifically because I see the above two problems in absense of you stating what problem you are attempting to solve..

> > > One idea is to run a select query on the table (select * from table)
> > > once, keep the resultset open throughout the lifetime of the
> > > application, and then do SQLFetch() to get each new row that has been
> > > inserted into the table.
> >
> > Er.. no. That's is not only illogical, but a dumb idea. Okay, Spock
> > would not have said dumb. But then I'm not half Vulcan.
> >
>
> How is Spock or Valcan related to to my original question? Thanks for
> your propoganda.

Not an issue of propoganda but one of logic.

How will the cursor know that a new row has been added to table, or an existing row changed?

There are no dirty reads in Oracle. Which means you need to refresh the cursor every second (or faster) to keep up to date with the changes in the table.

Where's the logic in that?

> > > My question is weather it is possible to keep the resultset open and
> > > see changes from other users in that result set. Can this be done
> > > using a cursor?
> >
> > No. Read up on how Oracle provides read consistency.
>
> Yes, but it also provides option to change the isolation levels.

You can also use a 12 gauge to shoot yourself in the foot. The question though is why on earth do you want to do that?  

> Yes, I am very well aware of databases like Oracle, Informix, and
> others. Try not to assume to much.

Well, you had me wondering there.. still do actually.

> > Why do you want to monitor the table that way?
>
> Thats not of your concern. There are already enough people worrying
> about that.

If we understand the requirement, we are in a much better position to provide technical advice that will be relevant and useful.

> > What business
> > requirement are you trying to satisfy?
>
> Again none of your concern. There are already enough people worrying
> about that.

Again. If we understand the requirement, we are in a much better position to provide technical advice that will be relevant and useful.

> > Have you looked at auditing?
> >
> Yes.

And?

> > Have you looked at something like datestamping rows via update/insert
> > trigger?
> >
>
> If something as simple as this would have worked, I wouln't be posting
> on this stupid forum where people are more intersted in bullshitting
> rather than suggeting constructive solutions.

How on earth can we provide constructive solutions if we do not know what the problem is that you are attempting to solve?

> Again. please re-read my question. I am not looking for alternative
> solutions or speculating over what is possible or not. I am looking
> for a solution to the excat problem that I posted. If you are not
> capable or providing one, please don't spread garbage on the internet
> by replying.

You have not stated a problem. You have stated what solution you have came up with and now wants to know how to do it.

Fact. You can not. The solution that you have is not a solution, nor can it work on Oracle. Period.

Again - if you state WHY you want to do something like this, we can offer you advice on Oracle features/products/whatever that can make it happen.

Have you looked at datetime stamping the rows? Have you looked at triggers using DBMS_PIPE notifications? Have you looked at AQ? If these do not work for you, then why not? What makes your problem so unique?

Oracle is a feature rich environment.. your problem can very likely be addressed effectively and efficiently.. if you are prepared to tell us just what the heck it is.

--
Billy