Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Simple DB Error check in SQL-Server Stored Proc

 
Monty Guppy
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I am successfully able to call a simple (INSERT) SP in sql-server. I am trying to understand what would be the best way to check to see if the INSERT in my SP failed or succeeded. Here is my code:
SP:
--------------------------------
CREATE Procedure dbo.AddReport
(
@rpt_idVarChar(5),
@rpt_nameVarChar(35),
@FileDataImage
)
As
/*******************************************************************************
Funtion: AddReport
Author: Jason Skaggs
Created Date: 4/18/2001
Purpose:
*******************************************************************************/
INSERT INTO tbl1(rpt_id, rpt_name)
VALUES(@rpt_id,@rpt_name)
if @@error <> 0
Begin
RAISERROR ('Could not add report to tbl1', 16, 1)
Return -1
End
return 0
--------------------------------
My java code looks like this:
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
CallableStatement cStmt=conn.prepareCall("{call AddReport(?,?,?)}");
cStmt.setObject(1,strRptRecID);
cStmt.setObject(2,strRptName);
cStmt.setObject(3,strRptData.getBytes());
cStmt.execute();
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Any help would be appreciated.
 
Monty Guppy
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Found the asnwer.
The Java should look like this (notice the diffence in the call to stored procedure as well as registration of the return_value):
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
CallableStatement cStmt=conn.prepareCall("{?=call AddReport(?,?,?)}");
/** Registering a parameter that would output the status of the insert i.e. returns -1 in case of error, 0 if no error ***/
cStmt.registerOutParameter(1, Types.INTEGER);
//the input parameters
cStmt.setObject(2,strRptRecID);
cStmt.setObject(3,strRptName);
cStmt.setObject(4,strRptData.getBytes());
cStmt.execute();
int iReturnValue=cStmt.getInt(1);
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic