getting Null resultset for a prepareCall

[sudukrish]

Hi i am trying to get resultset after running a procedure.When i do it from the database it is giving out the result but when i do it from JDBC it gives me null result set.I am using sql server. There are some inserts in the procedure before a select which returns a resultset . i am not sure if it is becoz of that.Kindly help me out.

[xcallmejudasx]

try committing before you do the select.

[markw8500]

It could be a couple of different things... If committing the transaction in your procedure does not work post your procedure, your table set up and your query so we can check it out...

[sudukrish]

Thanks for the help .This is the procedure being called ...

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[JB_Student_Item_Grades]
(@sid int,
@courseid int
)
as

--
--declare @sid int
--declare @courseid int
--set @sid =
--set @courseid =
Declare @reqitems table(lesson int, task int)

insert into @reqitems
exec [GRU_PacingTasks] @sid,@courseid


select
gci.ItemID
,gci.ItemDescription
,gsi.Percentage
,gsi.LetterGrade
,git.TypeDescription
,gsi.Points
,gci.PossiblePoints
,gci.PercentOfTotalGrade
,case when req.task is not null then '1' else '0' end as required
from dbo.GradeBook_Course_Items gci
--left join GradeBook_Student_Items gsi
left join (select sid,itemid,GenerateID,isdeleted,Percentage,LetterG rade,Points from dbo.GradeBook_Student_Items where @courseid = courseid) gsi --needed to limit to course
on gsi.GenerateID = gci.GenerateID and
gsi.sid = @sid and
gsi.ItemID = gci.ItemID
join dbo.GradeBook_CourseGenerateCourseLookup lu
on lu.generateid = gci.generateid and
lu.courseid = @courseid
join dbo.LU_Gradebook_ItemType git
on git.itemTypeID = gci.itemTypeID
left join @reqitems req on req.lesson = gci.lesson and req.task = gci.task
where (gsi.isDeleted = 0 or gsi.isDeleted is null)
--and gci.GenerateID = @generateid
--and (gsi.sid = @sid or gsi.sid is null)
and (gci.ExtraCreditCourseID = 0 or gci.ExtraCreditCourseID = @courseID)
--and gsi.courseid=@courseid -- Stopped proc from displaying all results
order by gci.DisplayOrder

[RamyaSivakanth]

Hi,
One small suggestion.
Instead of calling execute using CallableStatement,try to use executeQuery and get the resultSet.

Please send the portion of code which calls the stored procedure.

-Regards
Ramya

[sudukrish]

yes i am using callable statements and executeQuery

Connection conn = null;
CallableStatement cstmt = null;
SQLServerDataSource ds = new SQLServerDataSource();
ds.setUser("46436");
ds.setPassword("3466");
ds.setServerName("54.28.275.62");
ds.setPortNumber(1433);
ds.setDatabaseName("edison");

try {
conn = ds.getConnection();
cstmt = conn.prepareCall("{JB_Student_Item_Grades(?,?)}");
cstmt.setInt(1, 107856);
cstmt.setInt(2, 5050);
rs = cstmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(4));
}
} catch (Exception e) {
}

[RamyaSivakanth]

Hi Krish,
My doubt here is if no rows returned ResultSet will be empty but it will not be null.Print the exception inside the catch and let us see.
-Regards
ramya

[sudukrish]

Thats exactly my point but this is what i get as soon as I hit the executeQuery ....

com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
at com.microsoft.sqlserver.jdbc.SQLServerException.ma keFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStat ement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStat ement$PrepStmtExecCmd.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Un known Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.e xecuteCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.ex ecuteCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.ex ecuteStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStat ement.executeQuery(Unknown Source)
at test.CheckProcedure.main(CheckProcedure.java:41)

[RamyaSivakanth]

Hi,
One last try we can do.Instead of executeQuery use execute.And then using getResultSet() try to get the ResultSet object.
Print the boolean value returned by execute meth.


execute
public boolean execute()
throws SQLExceptionExecutes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement. Some prepared statements return multiple results; the execute method handles these complex statements as well as the simpler form of statements handled by the methods executeQuery and executeUpdate.
The execute method returns a boolean to indicate the form of the first result. You must call either the method getResultSet or getUpdateCount to retrieve the result; you must call getMoreResults to move to any subsequent result(s).


Returns:
true if the first result is a ResultSet object; false if the first result is an update count or there is no result
Throws:
SQLException - if a database access error occurs or an argument is supplied to this method
See Also:
Statement.execute(java.lang.String), Statement.getResultSet(), Statement.getUpdateCount(), Statement.getMoreResults()