1
package com.abc.server;

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import com.abc.client.modules.beans.AdminBean;
import com.abc.client.modules.beans.ResponseBean;
import com.abc.client.modules.utils.DBPool;

public class AdminServerService {
    private Connection con = null;
    private DBPool dbPool = new DBPool();

    public ResponseBean getAllGroupDetails(int pageSize, int pageNumber) {
        ResponseBean dataSet = new ResponseBean();
        List<AdminBean> groupDetailsList = new ArrayList<AdminBean>();
        ResultSet rs = null;
        InputStream in;
        CallableStatement cstmt = null;
        String driverClass,url = null, username = null,password = null;

        try {
            in = new FileInputStream("C:\\Users\\vchag\\Desktop\\jdbc.properties");

            Properties prop     = new Properties();
            prop.load(in);

            driverClass = prop.getProperty("SQLJDBC.driver");
            url = prop.getProperty("SQLJDBC.url");
            username = prop.getProperty("SQLJDBC.username");
            password = prop.getProperty("SQLJDBC.password");

            in.close();

            Class.forName(driverClass);
            con = DriverManager.getConnection(url, username, password);         
            cstmt = con.prepareCall(stored procedure name,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            cstmt.setInt("LOGIN_USER_ID",10);

            boolean isResultsAvailable = cstmt.execute();
            int resultSet = 1;

            // Loop through the available result sets.
            while (isResultsAvailable) {
                rs = cstmt.getResultSet();

                if (rs != null) {
                    if (resultSet == 1) {
                        while (rs.next()) {

                            AdminBean result = new AdminBean();
                            result.setGroupId(rs.getInt(1));
                            result.setGroupName(rs.getString(2));

                            groupDetailsList.add(result);
                        }
                        dataSet.setGridData(groupDetailsList);
                    }
                }

                rs.close();
                isResultsAvailable = cstmt.getMoreResults();
                resultSet++;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbPool.closeAll(rs, cstmt, con);
        }
        return dataSet;
    }
}

I want to iterate over the multiple result sets returned from the stored procedure. When I call the procedure using application with cstmt.execute it always returns false even when the result set are available. I am using java 7 and smartgwt 2.6.0 sdk. When I run the same code in a standalone application, it works fine but it doesn't works in my application.

Chag Vaibhav P
  • 93
  • 1
  • 2
  • 12
  • You are misinterpreting the meaning of the boolean return value of `execute`. Which database are you using? If you are using SQL Server, then consider adding `SET NO_COUNT ON` at the start of your stored procedure. Otherwise, look at solutions like https://stackoverflow.com/questions/14690295/execute-sp-msforeachdb-in-a-java-application/14694174#14694174 which shows how to correctly handle multiple results. – Mark Rotteveel Jun 21 '18 at 08:41
  • I am using the sql server database. I have already added SET NO_COUNT_ON at the start of the procedure. if i remove the last two parameters from the below statment then it is working fine. cstmt = con.prepareCall(stored procedure name,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); Thanks for your reply. – Chag Vaibhav P Jun 21 '18 at 09:45
  • That does sound odd. You might want to explicitly add that the question itself, and also include the version of the SQL Server JDBC driver you're using. – Mark Rotteveel Jun 21 '18 at 10:09
  • I am using the sql server 2014 management studio, java 7 and smartgwt sdk 2.6.0 – Chag Vaibhav P Jun 21 '18 at 10:18
  • And what is the version of your JDBC driver? – Mark Rotteveel Jun 21 '18 at 10:58
  • JDBC driver version is 2.0.1803.100 – Chag Vaibhav P Jun 22 '18 at 10:49
  • That is a pretty old version. Consider upgrading, the latest one is 6.4.0, see https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-2017&viewFallbackFrom=sql-server-2014 – Mark Rotteveel Jun 22 '18 at 11:59

0 Answers0