ResultSet NullPointerException

Question

I have a stored procedure, I want to call it from JDBC, I got null pointer exception in the line"

while (restuls.next()) {

My code is:

Connection con = Database.getConnection();
            CallableStatement callableStatement = null;
            try {
                String storedProcedure = "{call getAllCustomerAddresses(?,?,?,?,?,?,?)}";
                callableStatement = con.prepareCall(storedProcedure);
                callableStatement.setInt(1, this.getID());
                callableStatement.registerOutParameter(2,
                        java.sql.Types.INTEGER);
                callableStatement.registerOutParameter(3,
                        java.sql.Types.VARCHAR);
                callableStatement.registerOutParameter(4,
                        java.sql.Types.INTEGER);
                callableStatement.registerOutParameter(5,
                        java.sql.Types.INTEGER);
                callableStatement.registerOutParameter(6,
                        java.sql.Types.INTEGER);
                callableStatement.registerOutParameter(7,
                        java.sql.Types.VARCHAR);
                callableStatement.execute();
                System.out.println(callableStatement.getInt(2));
                System.out.println(callableStatement.getString(3));
                System.out.println(callableStatement.getInt(4));
                System.out.println(callableStatement.getInt(5));
                System.out.println(callableStatement.getInt(6));
                System.out.println(callableStatement.getString(7));
                ResultSet restuls = callableStatement.getResultSet();
                while (restuls.next()) {
                    int addressID = restuls.getInt(2);
                    String label = restuls.getString(3);
                    int regionID = restuls.getInt(4);
                    int areaID = restuls.getInt(5);
                    int cityID = restuls.getInt(6);
                    String description = restuls.getString(7);
                    this.addresses.add(new CustomerAddressImpl(this, label,
                            description, RegionImpl.getInstance(regionID),
                            AreaImpl.getInstance(areaID), CityImpl
                                    .getInstance(cityID), addressID));
                }

look at the code, the System.out.println is working , and It is printing the right values from database, so why the results set is null please??

another thing, I must use result set because the stored procedure returns many rows.

I am really confusing why I can print the right values but the result set is null

Thanks in advance

Edit

If you want to give you the stored procedure tell me please

Stored Procedure

ALTER PROCEDURE [dbo].getAllCustomerAddresses(
@customerID INT,
@addressID INT OUTPUT,
@label VARCHAR(200) OUTPUT,
@regionID INT OUTPUT,
@areaID INT OUTPUT,
@cityID INT OUTPUT,
@description TEXT OUTPUT
)
AS
    SET NOCOUNT Off;
SELECT     @addressID = [ID],
@label = [label],
@regionID = [regionID],
@areaID = [areaID],
@cityID = [cityID],
@description = [description]
FROM  Customer_Address
WHERE customerID = @customerID

Answer 1

Your stored procedure doesn't actually produce a ResultSet because you are using output parameters (not 100% sure, I don't have a SQL Server handy to test).

You may just need to call CallableStatement.getObject(int) or CallableStatement.getObject(String) (or a type specific getter) to get the values instead. If you want to process as a ResultSet, then you should not use the output parameters in your stored procedures, but write the stored procedure as a select without assigning to output parameter. That will create a result set from the stored procedure

Another possibility might by that your stored procedure is first returning one or more update counts before returning the result set. The boolean return value of execute() indicates whether the first result is an update count or a ResultSet. You will need to repeatedly call getMoreResults() and getUpdateCount() to be sure you have processed every result.

Your posted stored procedure contains SET NOCOUNT OFF which signals to SQL Server (or Sybase) that you want update (and I believe select) counts returned as well, you might want to try using SET NOCOUNT ON.

You can also try to process the results of execute() like this to find out if there are indeed multiple update counts etc before the result set:

boolean result = pstmt.execute();
while(true)
    if (result) {
        ResultSet rs = pstmt.getResultSet();
        // Do something with resultset ...
    } else {
        int updateCount = pstmt.getUpdateCount();
        if (updateCount == -1) {
            // no more results
            break;
        }
        // Do something with update count ...
    }
    result = pstmt.getMoreResults();
}

See also Java SQL: Statement.hasResultSet()?

Answer 2

execute() method of PreparedStatement returns true if result set presents and false otherwise. You do not check the return value of execute(). I think that if you do that you see that it is false.

The reason should be in your stored procedure that IMHO does not return value. So, try to analyze it to understand the problem.

Here are recommendations I can give you:

1. Use executeQuery() that directly returns ResaultSet instead of execute(). I think this is more convenient.
2. Avoid using stored procedures that couple your platform independent java code with specific type of database. Try to write all logic in java and use portable SQL statements only.
3. The last time I saw pure JDBC code was about 10 years ago. There are a lot of tools that help you to avoid writing SQL inside java code. Take a look on JPA, Hibernate, iBatis etc.