"The statement did not return a result set" while calling procedure from java

Question

Currently I am calling an procedure from SQL server.

Here is my method :

@Override
    public String savePersonalData(PersonalDataDto personalDataDto) throws SQLException {
        String output = "";
        Connection conn = null;
        Statement sta = null;
        String errorClassAndMethod = getErrorContainingClassAndMethod();
        try {
            Gson gson = new GsonBuilder().create();
            String json = gson.toJson(personalDataDto);
            org.json.JSONObject jsonInput = new org.json.JSONObject(json);

            conn = createConnection(jsonInput);

            sta = conn.createStatement();
            String SPsql = "EXEC SP_UpdateAdvisoryBoxPersonalData ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?";

            PreparedStatement ps = conn.prepareStatement(SPsql);
            // ps.setEscapeProcessing(true);

            System.out.println(personalDataDto);

            ps.setString(1, personalDataDto.getClientId());
            ps.setString(2, personalDataDto.getDivisionId());
            ps.setString(3, personalDataDto.getClientRoleId());
            ps.setString(4, personalDataDto.getUserID());
            ps.setString(5, personalDataDto.getUserLoginDetailId());

            ps.setString(6, personalDataDto.getPersonalData().getAnv_Om_PlaatsGeboorte());
            ps.setString(7, personalDataDto.getPersonalData().getNaw_Om_Postcode());
            ps.setString(8, personalDataDto.getPersonalData().getNaw_Om_Voorletters());
            ps.setString(9, personalDataDto.getPersonalData().getNaw_Om_Naam());
            ps.setDate(10, personalDataDto.getPersonalData().getAnv_Dt_Geboorte());
            ps.setString(11, personalDataDto.getPersonalData().getAnv_Om_Email());
            ps.setString(12, personalDataDto.getPersonalData().getAnv_Om_TelefoonPrive());
            ps.setString(13, personalDataDto.getPersonalData().getAnv_Om_TelefoonWerk());
            ps.setString(14, personalDataDto.getPersonalData().getNaw_Om_Voornamen());
            ps.setString(15, personalDataDto.getPersonalData().getNaw_om_iban());
            ps.setString(16, personalDataDto.getPersonalData().getAnv_Om_LegitimatieAfgifte());
            ps.setInt(17, personalDataDto.getPersonalData().getNaw_Id());
            ps.setString(18, personalDataDto.getPersonalData().getNaw_Nr_Huis());
//          ps.setInt(19, personalDataDto.getPersonalData().getNaw_Id());
            ps.setString(19, personalDataDto.getPersonalData().getNaw_Om_Straat());

            ps.setString(20, personalDataDto.getPersonalData().getAnv_Om_TelefoonMobiel());
            ps.setString(21, personalDataDto.getPersonalData().getAnv_Cd_Nationaliteit());



            ResultSet rs = ps.executeQuery();

            /*boolean gotResults = ps.execute();
            ResultSet rs = null;
            if(!gotResults){
               System.out.println("No results returned");
            } else {
               rs = ps.getResultSet();
               System.out.println(rs.getMetaData());
            }*/

        } catch (Exception e) {
            e.printStackTrace();
            String errorMessageAndClassWithMethod = getErrorContainingClassAndMethod();
            throw new SpringAppRuntimeException(errorMessageAndClassWithMethod + e.toString());
        } finally {
            if (sta != null) {
                conn.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return output;
    }

I am using two different DTOs for this. PersonalDataDto is like :

public class PersonalDataDto {

    private String nawId;
    private String serverName;
    private String userName;
    private String password;
    private String databaseName;

    private String clientId;
    private String divisionId;
    private String clientRoleId;
    private String userID;
    private String userLoginDetailId;

    private PersonalDataInfoDto personalData;

// getters and setters for all

}

Next one is PersonalDataInfoDto and it is like :

public class PersonalDataInfoDto {

    private String anv_Om_TelefoonMobiel;

    private int naw_Id;

    private String anv_Om_TelefoonPrive;

    private String anv_Om_LegitimatieAfgifte;

    private int advisoryBoxContactRefID;

    private String naw_Om_Straat;

    private String naw_Om_Postcode;

    private String anv_Om_Email;

    private String naw_Nr_Huis;

    private String naw_Om_Voorletters;

    private String anv_Om_TelefoonWerk;

    private String naw_Om_Voornamen;

    private Date anv_Dt_Geboorte;

    private String naw_om_iban;

    private String anv_Om_PlaatsGeboorte;

    private String naw_Om_Naam;

    private String anv_Cd_Nationaliteit;

// getters and setters for all

}

Error is :

com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.

What changes I should make in code / input to get the result set from procedure? Should I need to add something in procedure while calling it through java as I did in the method?

Answer 1

Comment out PRINT statement and when you execute the query in a database client, a table is returned.

In your stored procedure, there are insert and update statements. If the stored procedure performs any inserts or updates prior to the final select, JDBC (the SQL Server driver) is getting confused with the row counts (and the exception the statement did not return a result set will be thrown).

So you just have to add SET NOCOUNT ON at the start of the stored procedure.