Stored Procedure Not Sending Back Resultset

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 2000 Forums

Transact-SQL (2000)

Stored Procedure Not Sending Back Resultset

Author

Topic

rkhosla
Starting Member

3 Posts

Posted - 2002-08-22 : 16:03:07

Hi folks,
Im having trouble retrieving a Resultset in my Java Class from a Transact SQL stored procedure. I do not get a Resultset back in my java class. The sp uses a temporary table. If I use the same logic but use an sp that does not use a temp table, I get a Resultset back!!! Its quite strange.
Heres my code(a simplified version of it for easy reading, which has the same problem):
Stored Proc Test1 - WITH TEMP TABLES..THIS ONE DOES NOT RETURN A RS!!
CREATE PROCEDURE sandbox.p_test1
AS
BEGIN
CREATE TABLE #TEMPITEMS
(
ID numeric(16,0) identity ,
FIELD1 varchar(20)
)
insert into #TEMPITEMS(FIELD1)
(
select noticetype_v.DESCRIPTION from noticetype_v
)

select t.field1 from #TEMPITEMS t

drop table #TEMPITEMS
END
go
Stored Proc p_test - WITHOUT TEMP TABLES..THIS ONE RETURNS A RS!!!!
CREATE PROCEDURE sandbox.p_test
AS
BEGIN
select noticetype_v.DESCRIPTION from noticetype_v
end
go
Java Class:
-------
cs = conn.prepareCall("{call "+DBSCHEMA+".sandbox.p_test1()}");
gotresults = cs.execute()
if (gotresults)
System.out.println("Successful!!!");
rs = (ResultSet) cs.getResultSet(); //p_test1 DOES NOT RETURN AN RS
// p_test DOES NOT RETURN AN RS
sLine="4";
if (rs != null)
{
while (rs.next())
{
System.out.println(rs.getString(1));
sLine="5";
}
}
else
{
System.out.println("Resultset is null");
}

If I were to replace p_test1 with p_test in my java class, I get a RS back

Any thoughts on what I might be doing wrong here, or what I might need to change to get this work...
Thanks in advance.
Cheers,
Rahul

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-08-22 : 16:08:22

Here's a wild gues...

In ADO, if you don't do SET NOCOUNT ON you sometimes can not get data out of the temp table. it may be the same case here

At the top of your Stored Proc (before you do any selects etc) put:
SET NOCOUNT ON

At the bottom, when you have dropped all of your temp tables etc:
SET NOCOUNT OFF

Try that, and let us know if that doesn't work.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources