ResultSet is from UPDATE. No Data

1
votes

The problem occurs when executeQuery function runs, the sql statement is work correctly and gives correct results when it is runned on the sql editor. When it is runned on jdbc it is not executed. The connection accepts multi queries. 

String query = "set @countOfLectureGrade = (SELECT Count(goc.Affect) FROM GradeOfCourse goc WHERE goc.LectureID = ?);" 
                + "SELECT u.SchoolID, u.Name, u.Surname, u.Role, u.Email, " 
                + "CASE WHEN @countOfLecture = 0 then 0 " 
                + "ELSE AVG(0.01 * goc.Affect * gos.Grade) " 
                + "END AS Average "  
                + "FROM GradeOfCourse goc, GradeOfStudent gos, User u, CourseOfStudent cos "
                + "WHERE " 
                + "(gos.CourseGradeID = goc.GradeID AND u.SchoolID = gos.StudentID AND goc.LectureID = ?) " 
                + "OR (u.SchoolID = cos.SchoolID AND cos.LectureID = ? AND @countOfLectureGrade = 0) " 
                + "GROUP BY u.SchoolID;";


try {
    connection = super.getConnection();
    PreparedStatement sqlStatement = connection.prepareStatement(query);
    sqlStatement.setInt(1, lectureID);
    sqlStatement.setInt(2, lectureID);
    sqlStatement.setInt(3, lectureID);
    ResultSet resultSet = sqlStatement.executeQuery();

java.sql.SQLException: ResultSet is from UPDATE. No Data.

3
javamysqlsqljdbc
why are you running 2 queries? - Minh Kieu
@MinhKieu Because, I could not to do it in single query, I tried but can not find a solution, then I solved with two query - umit.kas
why not run the first query stored java and pass it into the second query like you did for .setInt(4, countOfLectureGrade)? I suspect the JDBC driver had no ideas which resultset you wanted to return? - Minh Kieu

3 Answers

1
votes

This is not possible, you have to separate your queries, of for the best solution you can use procedures or function.

  1. Procedure should take lectureID
  2. Return your result, in your case it should multiples valus, you can read How to retrieve multiple rows from stored procedure in mysql? to know how to use procedure return multiple values
0
votes

I am not familiar with JDBC but a quick search suggests you should use execute rather than executeQuery.

execute: Returns true if the first object that the query returns is a ResultSet object. Use this method if the query could return one or more ResultSet objects. Retrieve the ResultSet objects returned from the query by repeatedly calling Statement.getResultSet.

https://docs.oracle.com/javase/tutorial/jdbc/basics/processingsqlstatements.html#executing_queries

0
votes

Have a look at the following documentation which explains to use execute() instead of executeQuery() and then fire a getResultSet() on the Result set that you get.

The whole approach is to change your query into a Stored procedure and invoke the same through a CallableStatement.

Documentation suggests:

Although CallableStatement supports calling any of the Statement execute methods (executeUpdate(), executeQuery() or execute()), the most flexible method to call is execute(), as you do not need to know ahead of time if the stored procedure returns result sets.

Hope this helps!