Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

To check whether the data in a table has changed or not-In Oracle 10 g

 
Vivek Kr Agrawal
Ranch Hand
Posts: 51
Eclipse IDE Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,
I am writing a small program in Java to append(StringBuffer) all the values of PKEYSEQ(primary key) of a table. Then I am creating a message digest in Java from the StringBuffer. If the PKEYSEQ is changed, the newly created message digest will not match with the previous message digest and I will know that some thing has changed in the table.
But the problem is that if anyone changes a column other than the PKEYSEQ,I'll be unable to find that the table has changed or not. I think by now you guys have understood my problem. If I take all the data of the table to create a message digest, the process will become inefficient. So please suggest some better way to achieve this through Java. Can we have some modified date or something in Oracle and if yes then how to get that through Java?

With Regards,
Vivek Agrawal
 
James Boswell
Bartender
Posts: 1051
5
Chrome Eclipse IDE Hibernate
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I can only suggest a last modified date or a version number for each row. Your digest code will know about the last value of this column and check it for an update.

[Edit]You should not be attempting to modify the primary key of a row on an update.

http://databases.about.com/cs/administration/g/primarykey.htm
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Vivek Agrawaal wrote:Hi All,
I am writing a small program in Java to append(StringBuffer) all the values of PKEYSEQ(primary key) of a table. Then I am creating a message digest in Java from the StringBuffer. If the PKEYSEQ is changed, the newly created message digest will not match with the previous message digest and I will know that some thing has changed in the table...

Why are you doing this? Your application should not update the PK in the first place.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why do you need to find out whether a table was updated? If your goal is to read rows changed since last read, you need to know this can be quite problematic to implement so that you don't miss an update nor read an update twice. It also depends on the database you're using (since different DBs use different locking and concurrency models).
 
Vivek Kr Agrawal
Ranch Hand
Posts: 51
Eclipse IDE Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
To chris webster,
I am not going to update the PKEYSEQ(primary key). What I mean is I am just going to take all the values of the column PKEYSEQ in a stringBuffer and create a message Digest in java. I will store that message Digest somewhere. And now if someone else add/delete any row in the column, the message Digest will change.
What i want that only I should be able to use this table from the backend and if someone else tries to add/delete any row I will show one message that this table is modified by third party. I am not going to use this table. And for example if I am going to add one more row, I will first calculate the messageDigest and if it matches with the earlier value I will proceed further.



With regards
Vivek
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It sounds like this table should be read-only for most users and only the DB/APP admins (who should know what they are doing) should be able to modify it.

Your scheme could probably be made to work too, just include all table columns in the digest. You need to reread the table in full anyway to scan for changes. It then depends on how often you'll reread the table to find out it changes - if it is once an hour, for example, it might take up to an hour before the fact that the table was changed will be discovered by your software.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic