aspose file tools*
The moose likes JDBC and the fly likes How to update status automaticallly Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to update status automaticallly" Watch "How to update status automaticallly" New topic
Author

How to update status automaticallly

sohaib yasir
Ranch Hand

Joined: Mar 10, 2014
Posts: 50

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.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42598
    
  65
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?


Ping & DNS - my free Android networking tools app
sohaib yasir
Ranch Hand

Joined: Mar 10, 2014
Posts: 50

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
Marshal

Joined: Mar 22, 2005
Posts: 42598
    
  65
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

Joined: Mar 10, 2014
Posts: 50

While doing R&D, i came across that database events can also do same job after N Minutes. So what should be preference ?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

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

Joined: Mar 10, 2014
Posts: 50

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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

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

Joined: Mar 10, 2014
Posts: 50

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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

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

Joined: Mar 10, 2014
Posts: 50

Thanks Martin Vajsar for this help. It fulfills my requirements.

 
Don't get me started about those stupid light bulbs.
 
subject: How to update status automaticallly