File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Updating a table column when fireing select statement. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Updating a table column when fireing select statement." Watch "Updating a table column when fireing select statement." New topic
Author

Updating a table column when fireing select statement.

Jigar Naik
Ranch Hand

Joined: Dec 12, 2006
Posts: 757
Hi ,

I have got the following table structure.

SQL> DESC SMS_INBOUND;
Name Null? Type
----------------------------------------- -------- ----------------------------
MSG_ID NOT NULL NUMBER(20)
DNIS NUMBER(10)
SHORT_MESSAGE VARCHAR2(200)
MOBILE_NUMBER NUMBER(10)
RECEIVE_TIME TIMESTAMP(6)
READ_FLAG NUMBER(2)



Now i want to read 10 messages at particular interval, Lets say i am reading 10 messages using select statement and when to fire the select statement i am deciding through java scheduler.

but while i am reading a messages from the table using select statement i want to update the READ_FLAG value at the same time instance.

If it possible ?
PLUS some other application will be continuously performing insert in SMS_INBOUND table.

so use of the flag is to keep track on how many messages i have read using select statement for further processing.

How do i achieve that ?

Please suggest me some idea for the same.

PS : I have posted the same topic on Oracle forum also, may be there is a solution at oracle database level.


Jigar Naik


Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Forgive me, but sounds like a slightly suspect design. I am assuming your sms_inboud table is some sort of queue? I.e. some process inserts data into it, and you have a TimerTask that reads chunks of data, does something, then flags them as read? If you have a choice, the easiest way to do this would be to delete the read data and insert it into a sms_processed table. Or to delete them and have a trigger drop data into a history table.

If you can't do that, you could read an update in a transaction. This will have the effect of behaving somewhat like a single statatement.

If you have another process that can update your data you may want to consider pessimistic locking. This is not normally a good choice, because it creates a bottleneck in the database. You can use select...for update to lock the rows you are processing while you aere processing them. If you need to know for certain nothign else is changing the data you have selected while you are processing it, this would be a way.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Jigar Naik
Ranch Hand

Joined: Dec 12, 2006
Posts: 757
yeah it's exactly like the JMS, but i can not add more table to the database as it's a third party database.

but now my question is what if i fire a simple select statement like

Select pkey,col1,col2... from table_name where read_flag = 0;

and i got suppose 10 records

and based on the pkey i fire update statement on the table_name ?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

That will work. What may give you problems is if more than one process updates this table. If it doesn't your solution is fine. If is does, you may have to cater for handling the selected data changing before you've updated it.
Jigar Naik
Ranch Hand

Joined: Dec 12, 2006
Posts: 757
okkk... i got you, i need something which locks that particular record, so that no other process can update it,till the time i update that record.

Thanks a lot...
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Updating a table column when fireing select statement.