• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to throw an Exception from SQL code

 
Campbell Ritchie
Sheriff
Posts: 48371
56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need a table called "asset" which contains assets, would you believe. Now it is possible to buy an asset, so I wrote a stored procedure, and I can easily write a stored procedure for disposing of an asset (eg sold, damaged, stolen). MySQL 5.0.51 or thereabouts.

But I don't want to call the dispose_of_asset procedure twice on the same asset. Is there any way to write SQL code which will raise an SQLException on JDBC if the dispose_of_asset procedure is called on something which has already been disposed of?

I have found this by Googling, which said to select a non-existent column. Is there a more elegant way to do it.
 
Paul Clapham
Sheriff
Pie
Posts: 20719
30
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do you have to have the stored procedure throw an exception, or could you have it return one value if it did its work correctly and a different value if it failed?
 
Campbell Ritchie
Sheriff
Posts: 48371
56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It isn't actually returning a value at present, so I could return TRUE or FALSE and use ROLLBACK; if the asset has already been disposed of.
 
Campbell Ritchie
Sheriff
Posts: 48371
56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you. I shall try changing it to a function with a boolean return type. Then whoever implements the JDBC bit (myself) will know to watch for that return.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic