• 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

How to update status automaticallly

 
Ranch Hand
Posts: 51
Eclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi All,
I need guidance from all the experts here to find out the best implementation to perform following scenario :
Let's say I have a patient table where every new patient is inserted as a new row. My task is that i want to updated Status column as checkout patient after 12 hours his recorded is entered in MySQL database.

Table attribute are as follows :
Name | Age | Gender | Address | DOB | Time (When row entered) | Status|

I am using Status as
N : New Patient
C : Checkout Patient

I need to change status field from N to C every 12 hours form the difference time when its status was N.
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
That sounds easily doable via a SQL statement triggered in a variety of ways, depending on the circumstances. What ideas have you had? Or, to put it another way, what specific issue has you stumped at the moment? Creating the SQL statement to do so? Triggering it? Something else?
 
sohaib yasir
Ranch Hand
Posts: 51
Eclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Ulf Dittmer wrote:That sounds easily doable via a SQL statement triggered in a variety of ways, depending on the circumstances. What ideas have you had? Or, to put it another way, what specific issue has you stumped at the moment? Creating the SQL statement to do so? Triggering it? Something else?



If it could be done through Triggers, that's good. I want to triggering it but don't know how ? According to my limited knowledge of triggers can shoot on INSERT,UPDATE,DELETE etc. I have time constraint on my attribute how does a trigger know about the time completion of every row ? . Correct me if i am wrong.
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I meant "trigger it" in the general sense - maybe a cron job that runs every N minutes. SQL triggers fire if something gets inserted, updated or deleted from a table - so there's no guarantee about when they run. If you want predictability (maybe the value should get updated no later than 5 minutes after the 12 hour mark), then cron jobs can provide that, whereas SQL triggers can't.
 
sohaib yasir
Ranch Hand
Posts: 51
Eclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
While doing R&D, i came across that database events can also do same job after N Minutes. So what should be preference ?
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Another option might be not to store the status in the table at all, but just recalculate it - based on current time - when needed. This might even be done in a select statement, or perhaps a view could be created that would hide the logic.
 
sohaib yasir
Ranch Hand
Posts: 51
Eclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Martin Vajsar wrote:or perhaps a view could be created that would hide the logic.



Martin Vajsar,
Your answer is very ambiguous. Will you please elaborate this ?
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Assuming that the Status column depends on the Time column only:

The syntax might be a bit off - I don't use MySQL very often.

Whether this is feasible depends mainly on whether there's another logic applied to the Status column you haven't mentioned.

Edit: fixed the logic in the query.
 
sohaib yasir
Ranch Hand
Posts: 51
Eclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Martin Vajsar for the explanation. My Question is who is going to trigger this Select view ? and what you think about this View performance after an year when table containing thousand patients entries per day? The purpose of adding status column in database is to add Where clause in query. So that patient that are with the status N fetched quickly.

 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The PatientView view would be used by your application instead of the PatientTable.

Good point about querying the table. That query would indeed be inefficient, you'd have to use another one:and you'd index the time column.

There isn't one best solution. The solution I describe has the advantage that there aren't derived data stored in the table, you don't need a job to update those data and the data cannot become stale because of a failed job. There are disadvantages too - you need to calculate the Status column every time you need it and cannot easily query it. It's up to you to weight these pros and cons and choose the solution. I just wanted to mention it, as my personal preference is to avoid storing derived data in the database if it can be easily avoided.
 
sohaib yasir
Ranch Hand
Posts: 51
Eclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Martin Vajsar for this help. It fulfills my requirements.

 
Hoo hoo hoo! Looks like we got a live one! Here, wave this tiny ad at it:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic