Java, Stored procedure, result set question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Java, Stored procedure, result set question

Author

Topic

burferd
Starting Member

2 Posts

Posted - 2009-07-27 : 23:13:11

I have a java application that is using JDBC to intereact with a SQL Server 2005/2008 database.

I am having problems getting result set and OUT parameters from the stored procedure written by my database administrator.

My code is listed below.

Here is my problem. I need to fetch back a result set as return value and I also need to fetch back values as OUT parameters from the stored procedure.

The problem that I see with the code below is that I actually get back a result set, but when I attempt to access the OUT values (commented out in the code snipit), the result set closes and I get an exception that says the resulset is closed.

If I comment out the 3 lines that read the OUT parameters, I can process the result set with no problem.
I have another version where I do not return a result set.
In this case, I can read the OUT parameters with no problem.

So, basically, I can do either - read the OUT parameters, or read the return value as a result set.
Why can't I do both as I can with Oracle and MySQL?

I have the following questions:
1) Is it possible to have a return value that is a result set and have OUT parameters in the same stored procedure?
2) if so, what is wrong with my java code? I am using very similar code for MySQL and Oracle with no problems.
3) Why am I able to fetch either the return value as a result set or read the OUT parameters, but not both?

Here is the java code - I can post the stored procedure if necessary:

---------------------------------------------------------------------
String query = "{ ? = call Get_Company_List( ?,?,?,? ) }";
try
{
CallableStatement cs = con.prepareCall( query );
cs.registerOutParameter( 1, java.sql.Types.OTHER ); // Returned result set
cs.setInt( 2, 0 );
cs.registerOutParameter( 3, java.sql.Types.INTEGER );
cs.registerOutParameter( 4, java.sql.Types.INTEGER );
cs.registerOutParameter( 5, java.sql.Types.VARCHAR );
ResultSet rs = (ResultSet)cs.executeQuery();
// int coCount = cs.getInt(3);
// int retCode = cs.getInt(4);
// String retMsg = cs.getString(5);

// Pricess ResultSet
i = 0;
while( rs.next() )
{
<snip...>
i++;
}
rs.close();
cs.close();
}
// Catch exception
<snip...>

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-28 : 00:23:42

let's see the stored procedure too

burferd
Starting Member

2 Posts

Posted - 2009-07-28 : 09:20:51

Here is one that gives the same results.
-----------------------------------------------

USE [FMTax]
GO
/****** Object: StoredProcedure [dbo].[Get_UserStatus_List] Script Date: 07/28/2009 08:16:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*****************************************************************************
#
# Name: Get_UserStatus_List.sql
#
#*****************************************************************************/

ALTER PROCEDURE [dbo].[Get_UserStatus_List] (
@p_Id INTEGER,
@P_count INTEGER OUT,
@P_rtn_code INTEGER OUT,
@P_rtn_msg VARCHAR(200) OUT
)
AS

-- Return Codes
DECLARE @l_Rtn_Success INTEGER = 0;
DECLARE @l_Rtn_Success_Msg VARCHAR(20) = 'Success';

DECLARE @l_Rtn_RecNotFound INTEGER = 50001;
DECLARE @l_Rtn_TooManyRecs INTEGER = 50002;
DECLARE @l_Rtn_RaiseSignal INTEGER = 50098;
DECLARE @l_Rtn_GeneralFailure INTEGER = 50099;

-- Context Specific Error Text
DECLARE @l_ContextData VARCHAR(100) = '';

-- Error Severities
DECLARE @l_Severity_Mild INTEGER = 10;
DECLARE @l_Severity_High INTEGER = 16;
DECLARE @l_Severity_Fatal INTEGER = 22;

-- Error State
DECLARE @l_State INTEGER = 10;

-- Debug
DECLARE @l_SystemParmCategory_Debug VARCHAR(50) = 'Debug';
DECLARE @l_SystemParmParm_Level VARCHAR(50) = 'Level';
DECLARE @l_DebugLevel INTEGER = 0;
DECLARE @l_DebugLevel_1 INTEGER = 1;
DECLARE @l_DebugLevel_2 INTEGER = 2;
DECLARE @l_DebugLevel_3 INTEGER = 3;

-- Misc
DECLARE @l_CurrDate DATETIME = GETDATE();

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Initializations
SELECT @l_DebugLevel = Num_Value
FROM System_Parms
WHERE Category=@l_SystemParmCategory_Debug
AND Parm = @l_SystemParmParm_Level;

-- Validate Inputs
IF @l_DebugLevel >= @l_DebugLevel_1
BEGIN
SELECT 'Get_UserStatus_List():' AS ProcName,@p_Id AS UserStatusId;
END;

SET @l_ContextData = 'UserStatusId:(' + CONVERT(VARCHAR(5),@p_Id) + ')';
RAISERROR(@l_Rtn_GeneralFailure, @l_Severity_Mild,@l_State);

BEGIN TRY -- Validate Inputs

IF (@p_Id IS NULL OR @p_Id = 0)
BEGIN
SELECT @P_count = COUNT(*) FROM User_Status;

SELECT User_Status_Id AS UserStatus_Id,
User_Status_Name AS User_Status_Name,
Description AS Description
FROM User_Status;
END
ELSE
BEGIN
SET @P_count = 1;

SELECT User_Status_Id AS UserStatus_Id,
User_Status_Name AS User_Status_Name,
Description AS Description
FROM User_Status
WHERE User_Status_Id = @p_Id;
END;

SET @p_Rtn_Code = @l_Rtn_Success;
SET @p_Rtn_Msg = @l_Rtn_Success_Msg;

END TRY
BEGIN CATCH
EXEC error_output @l_Severity_High, @l_ContextData, @p_Rtn_Code OUT, @p_Rtn_Msg OUT;
END CATCH;

-- Validate Returned values
IF @l_DebugLevel >= @l_DebugLevel_1 BEGIN
SELECT @p_Rtn_Code AS RtnCode,@p_Rtn_Msg AS RtnMsg;
END;

END;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources