Tuesday, November 10, 2009

How to call and get output from stored procedure in java

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[genTicketGen] ( @head varchar(10),@price varchar(20),@cno varchar(10),@issby varchar(50),@tno varchar(50) output)

AS
BEGIN
DECLARE
@dd varchar(50),
@dd1 varchar(50),
@dd2 varchar(50),
@dt varchar(50),
@seq int,
@no int;


set @seq = 0;
set @dd = (SELECT CONVERT(VARCHAR(50), GETDATE(), 10) AS [MM-DD-YYYY]);
set @dt = (SELECT CONVERT(VARCHAR(50), GETDATE(), 103) AS [DD/MM/YYYY]);
set @dd1 = @dd + ' 00:00:00 AM';
set @dd2 = @dd + ' 11:59:59 PM';


select @no = count (*) from tblGen_Ticket_Issue where issue_datetime > @dd1 and issue_datetime < @dd2

select @seq = max(sequence) from tblGen_Ticket_Issue where issue_datetime > @dd1 and issue_datetime < @dd2


if( @no = 0)
begin
set @seq =1;

end
else
begin
set @seq = @seq+1;

end

set @tno = convert(varchar(20),@seq)+' '+@head+' '+@dt;

begin
insert into tblGen_Ticket_Issue values (@tno,@seq,@head,@price,getdate(),@cno,@issby);
end

return 1
END


..........................................................................
How to call and get output from stored procedure in java
..........................................................................

Connection con = new DbConnection().getConnection();
CallableStatement cst = con.prepareCall("{call genTicketGen(?,?,?,?,?)}");

cst.setString(1,no);
cst.setString(2, price.getText());
cst.setString(3,cno);
cst.setString(4,MainFrame.Uname);

cst.registerOutParameter(5, java.sql.Types.VARCHAR);

cst.execute();

String valtno = cst.getString(5);
System.out.println(valtno);

No comments:

Post a Comment