2

That's the query i'm running in database:

    SELECT CD_MAT, SUM(QT_MAT_UTIL) AS QT_MAT_UTIL FROM T476 
    WHERE (CD_UN_EXEC_SERV= 'X10' OR CD_UN_EXEC_SERV IN (SELECT 
    A.CD_UN_ORGANIZ FROM T016 A WHERE A.CD_UN_ORGANIZ_HIE ='X10'))
    AND MM_RA_EXEC = 6 AND AA_RA_EXEC = 2016
    AND CD_MAT = 15210001010
    GROUP BY CD_MAT
    ORDER BY 1 ASC, 2 DESC;

And that's the result I get when I run it in IBM Acess:

IBM Acess result

And that result is correct.


But when I run that same query in java using ResultSet, that's the result I get:

result java's ResultSet is giving me


I have no idea why this is happening. Does anyone knows what am i doing wrong?


That's my java code(i'm absolutely sure that the variables are right):

  public ArrayList<Map<String, Object>> myProblematicMethod(String cod, Integer first, Integer last) throws Excecao {

ArrayList<Map<String, Object>> lista;
lista = new ArrayList<>();

try {

  StringBuffer sql = new StringBuffer();
  sql.append("SELECT CD_MAT , SUM(QT_MAT_UTIL) AS QT_MAT_UTIL FROM T476 ");
  sql.append(" WHERE (CD_UN_EXEC_SERV = ? OR CD_UN_EXEC_SERV ");
  sql.append(" IN (SELECT A.CD_UN_ORGANIZ FROM T016 A ");
  sql.append(" WHERE A.CD_UN_ORGANIZ_HIE = ?)) ");
  sql.append(" AND MM_RA_EXEC= ? AND AA_RA_EXEC= ? ");
  sql.append(" AND CD_MAT = 15210001010 ");
  sql.append(" GROUP BY CD_MAT ORDER BY 1 ASC, 2 DESC ");

  PreparedStatement ps = this.banco.criarPS(sql.toString());

  ps.setString(1, cod);
  ps.setString(2, cod);
  ps.setInt(3, first);
  ps.setInt(4, last);

  ResultSet resultSet = this.banco.consultarSQL(ps);

  while (resultSet.next()) {
    LinkedHashMap<String, Object> materiais = new LinkedHashMap<>();

    // campos presentes em todas as consultas realizadas
    materiais.put("cod", resultSet.getString("CD_MAT"));
    materiais.put("qnt", resultSet.getBigDecimal("QT_MAT_UTIL"));

    lista.add(materiais);
  }

      } catch (SQLException e) {
        e.printStackTrace();
        throw new Excecao(e.getErrorCode(), e.getSQLState() + " - " + e.getMessage());
      }

  return lista;
  }

Java does not return any exception to me and there's no other column with that value it is returning. I'm using java 1.8.0_65.

5
  • 1
    Did you try hardcoded values? Commented Feb 21, 2019 at 12:48
  • hi @mao, I've checked the database and the credencials, but I dont know if I understood your question, the outcome when I run the same query in IBM acess is the one in the first image(4.0000), and the outcome when I run it in ResultSet is the one in the second image(10). (the variables in java code have the same value I put in the Acess's image, i've debuged) Commented Feb 21, 2019 at 12:49
  • 1
    So maybe the criarPs thing (that you don’t show) is messed up. Avoiding PreparedStatement is not a good solution. Commented Feb 21, 2019 at 14:40
  • I'm with @NathanHughes台湾不在中国 on this one: this.banco.criarPS is doing something, and we don't know what. I'm already a little suspicious of anything that ships a query string to something else for query preparing. Side note: As presented, there's no reason to use a StringBuffer - just create it as a single String. Also, there's ways to use named parameters (requires more setup, though), but possibly not your actual problem. Commented Feb 22, 2019 at 18:18
  • @NathanHughes台湾不在中国 Im looking on criarPS, i'll post here if i find anything, thank you! Commented Feb 22, 2019 at 18:21

2 Answers 2

1

You are not executing the same query in Java as the one you want. In the PreparedStatement in Java you are missing the ' AND CD_MAT = 15210001010 ' part.

Sign up to request clarification or add additional context in comments.

1 Comment

I posted wrong in here, I'll correct the question, but it still returning an wrong result (QT_MAT_UTIL value)
0

I've resolved this by not using preparestatement to set the variables in the query, now I put them into the query using simple StringBuilder append and it resolved.

(I use PrepareStatement in other classes and it works fine)

4 Comments

NO, ABSOLUTELY NOT. Using a StringBuilder just enabled SQL Injection (you're not validating cod are you?). If PreparedStatement is giving you different results, you maybe have deeper issues that need to be looked into (index statistics being out of date does this, rarely, for example).
@Clockwork-Muse will I still be enabeling sql injections even if I start validating the variables before bulding the sql?
You might be safe, assuming it's something trivial (Only three alphabetic ASCII characters, or something). Anything complicated, and you're in trouble - if it's possible to include quotes in the string itself, for example. Integer-based variables are probably safe (hijacking them requires creating and setting as default a custom culture, which is non-trivial - although, are your app and db server set to the same culture?). Note that prepared statements allow reusing the query plan (and might return cached objects), which might give a performance gain.
But, seriously, just use a prepared statement. There's no good reason, here, to concatenate the variables in. You will get safer, less error-prone, more maintainable code by doing so, and it will (probably) run faster, too. If you're getting an error, you need to figure out why, and fix it - after all, it might eventually spread!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.