aspose file tools*
The moose likes JDBC and the fly likes Simple DB Error check in SQL-Server Stored Proc Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Simple DB Error check in SQL-Server Stored Proc" Watch "Simple DB Error check in SQL-Server Stored Proc" New topic
Author

Simple DB Error check in SQL-Server Stored Proc

Monty Guppy
Ranch Hand

Joined: Sep 15, 2001
Posts: 49
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

Joined: Sep 15, 2001
Posts: 49
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);
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Simple DB Error check in SQL-Server Stored Proc