Results 1 to 9 of 9
  1. #1
    sudukrish is offline Member
    Join Date
    Apr 2009
    Posts
    12
    Rep Power
    0

    Default getting Null resultset for a prepareCall

    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.

  2. #2
    xcallmejudasx's Avatar
    xcallmejudasx is offline Senior Member
    Join Date
    Oct 2008
    Location
    Houston, TX & Flint, MI
    Posts
    609
    Rep Power
    6

    Default

    try committing before you do the select.
    Liberty has never come from the government.
    Liberty has always come from the subjects of government.
    The history of liberty is the history of resistance.
    The history of liberty is a history of the limitation of governmental power, not the increase of it.

  3. #3
    markw8500's Avatar
    markw8500 is offline Senior Member
    Join Date
    Jul 2008
    Location
    Pennsylvania, USA
    Posts
    136
    Rep Power
    0

    Default

    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...
    Who Cares... As Long As It Works...

  4. #4
    sudukrish is offline Member
    Join Date
    Apr 2009
    Posts
    12
    Rep Power
    0

    Default

    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

  5. #5
    RamyaSivakanth's Avatar
    RamyaSivakanth is offline Senior Member
    Join Date
    Apr 2009
    Location
    Chennai
    Posts
    841
    Rep Power
    6

    Default

    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
    Ramya:cool:

  6. #6
    sudukrish is offline Member
    Join Date
    Apr 2009
    Posts
    12
    Rep Power
    0

    Default

    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) {
    }

  7. #7
    RamyaSivakanth's Avatar
    RamyaSivakanth is offline Senior Member
    Join Date
    Apr 2009
    Location
    Chennai
    Posts
    841
    Rep Power
    6

    Default

    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
    Ramya:cool:

  8. #8
    sudukrish is offline Member
    Join Date
    Apr 2009
    Posts
    12
    Rep Power
    0

    Default

    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)

  9. #9
    RamyaSivakanth's Avatar
    RamyaSivakanth is offline Senior Member
    Join Date
    Apr 2009
    Location
    Chennai
    Posts
    841
    Rep Power
    6

    Default

    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()
    Ramya:cool:

Similar Threads

  1. Help with Resultset
    By xxAlemanxx in forum JDBC
    Replies: 6
    Last Post: 06-24-2008, 11:09 AM
  2. ResultSet to XML
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-14-2008, 09:50 AM
  3. ResultSet to HTML
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-12-2008, 09:32 AM
  4. ResultSet to JTable
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-11-2008, 09:01 AM
  5. ResultSet example
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 01-20-2008, 08:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •