• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 869
  • Last Modified:

combine insert and update sql statement in JSP

I would like to insert data in a table a table called Reservations and Update the Books table to change the reserved row from "N" to "Y". It is a website that let the user reserve a book...if the book is reserved, the username is inserted in the reservations table and the status of the books will change to (REserved=='Y')...

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("Jdbc:Odbc:dBase","","");
stmt=con.createStatement( );

stmt.executeUpdate("INSERT INTO Reservations(ISBN,ReservedDate)"
+ "VALUES('"+isbn_n+"','"+date_d+"')" AND "UPDATE Books SET Reserved='Y' WHERE BookTitle='"+Book_Title+"'");


out.println("You have reserved the book successfully");


}

Open in new window


it is seems like the way i combine the two is wrong...could you pls help with to discover the mistake...

Thanks folks
0
ozzyfanta
Asked:
ozzyfanta
3 Solutions
 
cmalakarCommented:
You must execute the statements separately and use transactions.

Look into this tutorial.

http://download.oracle.com/javase/tutorial/jdbc/basics/transactions.html
0
 
dqmqCommented:
You cannot do an update and an insert in the same SQL statement.

My recommendation is to visualize "Reserve a Book" as a complete business process.  Encapsulate the entire process in a stored procedure.  And yes, use transactions.

One more thought.  From database purist standpoint, you've got some redundancy in your design. In other words, a single fact (book is reserved) represented two different ways (a row in reservation table, a Y/N flag in book table).   Best:  eliminate the Y/N flag; if needed as a column, derive it in a view. Better: maintain the Y/N flag using triggers rather than in application code to drastically lesson the probability of getting out-of-sync.  Good:  maintain both the reservation table, flag and transaction in a backend procedure; i.e. a business process layer (as described in paragraph 1)

Not so good:  trying to stay in sync from JSP code.


0
 
AkAlanCommented:
You can do an Update/Insert using the same Stored Procedure in SQL. You just pass in the PK value for an Update or not for an Insert using the following example.

USE [YourDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[Update_InsertToYourTable]

	@TrainingClassID int,
	@ClassDate datetime,
	@Instructor varchar(50),
	@ClassLocation varchar(50),
	@ClassDescription varchar(50),
	@ClassDuration varchar(50),
	@DatabaseUpdatedBy varchar(50),
	@DatabaseUpdatedOn datetime
AS
SET NOCOUNT ON
IF @TrainingClassID = 0 BEGIN
	INSERT INTO HR_TrainingClasses (
		[ClassDate],
		[Instructor],
		[ClassLocation],
		[ClassDescription],
		[ClassDuration],
		[DatabaseUpdatedBy],
		[DatabaseUpdatedOn]
	)
	VALUES (
		@ClassDate,
		@Instructor,
		@ClassLocation,
		@ClassDescription,
		@ClassDuration,
		@DatabaseUpdatedBy,
		@DatabaseUpdatedOn
	)
	SELECT SCOPE_IDENTITY() As InsertedID
END
ELSE BEGIN
	UPDATE HR_TrainingClasses SET 
		[ClassDate] = @ClassDate,
		[Instructor] = @Instructor,
		[ClassLocation] = @ClassLocation,
		[ClassDescription] = @ClassDescription,
		[ClassDuration] = @ClassDuration,
		[DatabaseUpdatedBy] = @DatabaseUpdatedBy,
		[DatabaseUpdatedOn] = @DatabaseUpdatedOn
	WHERE [TrainingClassID] = @TrainingClassID

END

SET NOCOUNT OFF

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now