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

Avoid Select statment after updating dynamic record.

 
sohaib yasir
Ranch Hand
Posts: 51
Eclipse IDE Java MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a requirement where i need to use updated value from one table to JAVA method. I am updating my table using following Query in JAVA.



which returns me updated number of rows I need updated current_visit_no to pass it some other function. Currently i am doing select current_visit_no from patients to get updated value.
I am looking to avoid select statement after updation. Any suggestion ?
 
Paul Clapham
Sheriff
Posts: 21567
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
sohaib yasir wrote:I am looking to avoid select statement after updation.


It would help if you could explain the problem which led to that decision. Because there's no obvious reason why you would want to avoid using a select statement.
 
sohaib yasir
Ranch Hand
Posts: 51
Eclipse IDE Java MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:
sohaib yasir wrote:I am looking to avoid select statement after updation.


obvious reason why you would want to avoid using a select statement.


I want to save a DB hit through my JAVA Code. In a single call i want to get Updated value if it is not updating then obviously executeUpdate() method would return me zero. My real concern to save DB hit to get updated value. I know it not the behavior of language, i am looking for but i posted it that there may be some solution of which i am not aware.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Firstly, take steps to make sure that the additional DB hit is actually causing you problems. Your requirement needs some effort, and it is completely possible that the effort will not result in a perceivable performance improvement of your application.

Assuming you've verified this additional call is what slows your application down unacceptably, you don't have many options. As far as I know, SQL in general (and therefore also JDBC) doesn't support getting updated values back. There are some vendor specific SQL extensions to allow this (for example, Oracle's RETURNING INTO clause), but this would obviously lead to database specific code. Another option is to create a stored procedure (or function) that will do the update and return the updated value in one of its out parameters (or as a result). Even if you had to select the value again in the stored procedure to get it, you'd save the network trip, which is usually the most crucial.
 
sohaib yasir
Ranch Hand
Posts: 51
Eclipse IDE Java MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin Vajsar wrote: create a stored procedure (or function) that will do the update and return the updated value in one of its out parameters (or as a result). Even if you had to select the value again in the stored procedure to get it, you'd save the network trip, which is usually the most crucial.


Thanks Martin Vajsar. I think this is the best possible solution that i can do. Thanks for this great idea.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic