Win a copy of Murach's Python Programming this week in the Jython/Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

How to delete and insert data into two different tables in jsp  RSS feed

 
Mithra Thej
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all, I am creating a portal using JSP and MySQL with eclipse IDE. I have a scenario where data is stored on draft table and is retrieved then stored on another table. I need the entry in the draft table to be deleted after the insertion. When I tried to run the code it redirects to success page but makes no impact on database(neither inserts not gets deleted). I have pasted the code below




Could anyone please help me in executing both queries?

Thanks in advance.
 
Ulf Dittmer
Rancher
Posts: 42970
73
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to JavaRanch.

JSPs should never access DBs directly. That kind of logic belongs in backing beans or servlets, so that JSPs are used only for display, not for processing.
 
Mithra Thej
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you for your valuable reply. But unknowingly i have done everything in jsp. I'll definitely change the code to servlets but as of now could you please tell me how to execute two sql statements concurrently?
 
Ulf Dittmer
Rancher
Posts: 42970
73
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"Concurrently"? Are you sure that's what you need to do? If so, it likely won't happen. But I doubt that's what you meant. Maybe you meant both should be part of the same transaction? So that either both are executed, or none?

it isn't working

Why don't you start by telling us what that means? What happens if you execute this code, and how is that different from what you were expecting?
 
Ulf Dittmer
Rancher
Posts: 42970
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A couple more points: line 23 can't possibly work as you've posted it - the JSP shouldn't even compile.

Lines 43/44: You're throwing away the benefit of PreparedStatement by interpolating the SQL with the ID. Use "?" and set parameters as you're doing with the INSERT.

And lastly: are there any exception messages printed to the log file? If the first statement throws an exception, then the second one shouldn't even be attempted, yes? That's the kind of flow control that's easy to do in Java, but not in a JSP. So start by converting all this into Java code running in a servlet.
 
Mithra Thej
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm sorry for using the word "concurrently". Yes, I need it to be a part of same transaction. When I execute the code it simply redirects to success.jsp but doesn't reflect any changes in the database. but when i tried to run without the "Delete" query part it gets inserted into the database.
 
Mithra Thej
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That was not the code that i had. I had edited it earlier anyway I have posted the edited code.
 
Ulf Dittmer
Rancher
Posts: 42970
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please do not edit posts like that. Now all the previous posts no longer make sense. You even deleted your question!

But that code anyway does not answer any of the questions I asked. I only see that the 2nd DB access still uses a non-prepared PreparedStatement.
 
Ulf Dittmer
Rancher
Posts: 42970
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Start reading about transactions here: http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html. But you really need to have a solid code base first, so start by moving the Java code into a servlet.
 
Mithra Thej
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am sorry ULF, I'll get back to you after converting it into servlet
 
Mithra Thej
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi ULF,

Could you please tell me what is the risk or disadvantage in using jsp alone without servlet?
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!