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

*"If you want to give you the stored procedure tell me please"* No thanks. For better help sooner, post an [SSCCE](http://sscce.org/). — Andrew Thompson, May 12 '13 at 09:12
@Satya yes ofc, and the evidence is the printing method is working perfectly — Marco Dinatsoli, May 12 '13 at 09:14
@home my stored procedure is just select, there is no update — Marco Dinatsoli, May 12 '13 at 09:15
check this http://stackoverflow.com/questions/14946959/callablestatement-getresultset-always-return-null-when-calling-an-oracle-funct — Satya, May 12 '13 at 09:16
@Satya I have already checked it. and if you checked the selected answer you will see that the author says that he doesn't know why the problem is keeping — Marco Dinatsoli, May 12 '13 at 09:19
It doesn't 'print the right values from the database'. It prints the parameter values that you set in the CallableStatement, which have nothing to do with the database. — user207421, May 12 '13 at 09:46

score 2 · Answer 1 · answered May 12 '13 at 09:22

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:

Use executeQuery() that directly returns ResaultSet instead of execute(). I think this is more convenient.
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.
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.

That is not entirely true, `execute()` returns `true` if the first/current result is a `ResultSet`, and `false` if it is an update count. See my answer here for a more detailed explanation: http://stackoverflow.com/questions/14213824/java-sql-statement-hasresultset/14413141#14413141 — Mark Rotteveel, May 12 '13 at 09:26
I will try your answer, and for your last recommendation, I really tried to avoid sql in the java logic, that is why i used stored procedure, please tell me how to make my java independant of my sql, thank you — Marco Dinatsoli, May 12 '13 at 09:36

score 1 · Accepted Answer · edited May 23 '17 at 12:29

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()?

I don't have update in my stored procedure, please check my edit answer, i put the stored procedure — Marco Dinatsoli, May 12 '13 at 09:37
@MarcoDinatsoli That doesn't really matter afaik, SQL Server will send an 'affected rows' count for every statement in a stored procedure according to the documentation of [`SET NOCOUNT`](http://msdn.microsoft.com/en-us/library/ms189837.aspx): _"SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure"_ — Mark Rotteveel, May 12 '13 at 09:39
@MarcoDinatsoli See also my latest addition; it might just be that your stored procedure doesn't produce a resultset. — Mark Rotteveel, May 12 '13 at 09:45
I tried you code, It gives me an empty result, not null but empty but I am seeing the table , it absolutly have results and as i told you i can print them, i made it "on" instead of "off" and it still the same, — Marco Dinatsoli, May 12 '13 at 09:50

ResultSet NullPointerException

Edit

Stored Procedure

2 Answers2

Linked