0

When referring to the stored procedure client get two Resultset. With the first all is well, but the second consists of 20 rows, client developers claim that the procedure returns about 1000.

Connect connectObject = new Connect();
Connection connectionToPool = null;
CallableStatement procedure = null;
ResultSet table = null;
int rowCounter;
SOATO answer = new SOATO();
int counter = 1;

try {
    connectObject.init(POOL);
    connectionToPool = connectObject.getConnection();

    procedure = connectionToPool.prepareCall("{call procedure()}");
    procedure.execute();

    while (true) {
        rowCounter = procedure.getUpdateCount();

        if (rowCounter > 0) {             // This  is update counter
            procedure.getMoreResults();
            continue;
        }

        if (rowCounter == 0) {   // DDL command or 0 updates
            procedure.getMoreResults();
            continue;
        }

        table = procedure.getResultSet();     // If we reached here, we have a    
                                             //   set of data, or no more results
        if (table != null) {
            switch (counter) {
                case 1:                 // Area tables
                    answer.areaDataHandler(table);
                    counter++;
                    break;

                case 2:                // Region tables
                    answer.regionDataHandler(table);
                    counter++;
                    break;

                default:
                    break;
            }
            procedure.getMoreResults();
            continue;
        }
        break;                              // No more results
    }
    } catch (SQLException e) {
        e.toString();
    } finally {
        if (table != null) {
            try {
                table.close();
            } catch (SQLException e) {
                e.toString();
            }
        }
        if (procedure != null) {
            try {
                procedure.close();
            } catch (SQLException e) {
                e.toString();
            }
        }
        if (connectionToPool != null) {
            connectObject.releaseConnection(connectionToPool);
        }
    }
    return answer;
}

regionDataHandler() similar areaDataHandler()

public void areaDataHandler(ResultSet table) throws SQLException {

    while (table.next()) {
        Area temp = new Area();
        temp.setKodobl(table.getInt("kodobl"));
        temp.setNameobl(table.getString("nameobl"));
        area.add(temp);
    }
}
  • Database - MSSQL 2000
  • jdbc driver - jtds1.2.5

p.s. Please do not judge strictly junior and sorry for bad english

Nekto
  • 11
  • 2
  • did you run the sql manually to confirm what the client developers "claimed"? – jtahlborn Jun 10 '14 at 15:21
  • Why not use Eclipse or another IDE and debug what happens when you get to the call for the SPROC? You don't show the SPROCs here, so it is hard to help. I suggest debugging this, this might help you. – Alvin Bunk Jun 10 '14 at 15:22
  • 1
    There is no reason to not believe them because they are more interested in this system. I can't debug with IDE(or i don't know how i can do it) because I can't connect to the database with SPROCs from my workplace only from server. – Nekto Jun 11 '14 at 06:14
  • Show the code that calls this processing code, my suspicion would be that you are ignoring exceptions. – Mark Rotteveel Jun 11 '14 at 08:33
  • I call this code using the HTTP Analyzer from Oracle JDeveloper 12c. – Nekto Jun 11 '14 at 09:11
  • 2
    1. The fact that someone has no reason to lie, does not mean he can't make a mistake. If your client claims some proof or detail to the problem - verify it. You might as well find something interesting there as well. 2. As @Mark suggests - check your exceptions. With the code you have, something as silly as a null int in the result set and `setKodobl` taking a primitive argument would kill this procedure prematurely. Also - Are you seriously calling `e.toString()` in catch?!?! What is that even supposed to do? – Ordous Jun 11 '14 at 09:37
  • It could also be something silly like having set [max rows](http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#setMaxRows(int)) on the statement. – Mark Rotteveel Jun 11 '14 at 09:42
  • Sorry guys. I'm stupid. Error I write to the log but deleted it from the listing. Thanks to you, I remembered your requests logging and saw in it that error - java.sql.SQLException: Numeric overflow in conversion of value 2205000000 to type INTEGER. Thank you all, then I can handle myself) – Nekto Jun 11 '14 at 11:29
  • @Nekto For the sake of other SO users - why don't you make an answer to your own question, stating how you found the problem via extra logging and exception handling and thoughts on refactoring this code to make it more resiliant to these sort of errors (or at least easier to debug)? Unanswered questions don't help anyone. – Ordous Jun 11 '14 at 11:33

1 Answers1

0

Problem solved, it turned out went int overflow field. Problem found through via extra logging and exception handling. In my code is not checked exceptions. So guys do not be so stupid as I correctly handle exceptions in your projects

Nekto
  • 11
  • 2