• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Updating a table column when fireing select statement.

 
Ranch Hand
Posts: 763
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Jigar Naik
Ranch Hand
Posts: 763
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 763
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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...
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic