wood burning stoves*
The moose likes JDBC and the fly likes Problem with jdbc and sql server Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem with jdbc and sql server" Watch "Problem with jdbc and sql server" New topic
Author

Problem with jdbc and sql server

Paulo Henrique Peres
Greenhorn

Joined: Apr 16, 2012
Posts: 3
I'll try to explain what is mysteriously happening.

A bug was found in the test environment that simply don't reproduce in the development environment. A query in the test environment database returns nothing but when I restored the database backup of the test environment on my machine it did return a result.

For running the app connecting on the test environment database I could reproduce the bug, so we thought the bug could be related to something in the database server (since it did work before being formatted a short time from now) or Hibernate.

I extract the query generated by Hibernate and used it with JBDC PreparedStatement, but the bug still remains.

The query used is below:

Thus not return anything:
Note: If we run it straight into manager sql server returns the value 1

PreparedStatement psValorAtributo =
conn.prepareStatement("select count(*) as col_0_0_ from TABELA_AUDITOR auditorto0_ where auditorto0_.id_auditor=? " +
"and(auditorto0_.id_auditor in(select auditorto1_.id_auditor from TABELA_AUDITOR auditorto1_, TABELA_QUALIFICACAO qualificac2_ " +
"where auditorto1_.id_auditor=qualificac2_.id_auditor and ((((qualificac2_.id_sistema_gestao=? ))))))and(1 like 1 escape '\\' )");

psValorAtributo.setLong(1, new Long(171));
psValorAtributo.setLong(2, new Long(83));

ResultSet rs = psValorAtributo.executeQuery();
rs.next();
System.out.println("############################## PREPARED: " + rs.getInt(1));


Thus returns the value that actually is correct: 1
PreparedStatement psValorAtributo =
conn.prepareStatement("select count(*) as col_0_0_ from TABELA_AUDITOR auditorto0_ where auditorto0_.id_auditor=171 " +
"and(auditorto0_.id_auditor in(select auditorto1_.id_auditor from TABELA_AUDITOR auditorto1_, TABELA_QUALIFICACAO qualificac2_ " +
"where auditorto1_.id_auditor=qualificac2_.id_auditor and ((((qualificac2_.id_sistema_gestao=? ))))))and(1 like 1 escape '\\' )");

//psValorAtributo.setLong(1, new Long(171)); This parameter is put RETREAT DIRECTLY IN QUERY
psValorAtributo.setLong(1, new Long(83));

ResultSet rs = psValorAtributo.executeQuery();
rs.next();
System.out.println("############################## PREPARED: " + rs.getInt(1));

Thus returns the value of real fact

The problem is only with SQL Server and in very specific situations. Someone already encountered this, or something?
Paulo Henrique Peres
Greenhorn

Joined: Apr 16, 2012
Posts: 3
I'll try to explain what is mysteriously happening.

A bug was found in the test environment that simply don't reproduce in the development environment. A query in the test environment database returns nothing but when I restored the database backup of the test environment on my machine it did return a result.

For running the app connecting on the test environment database I could reproduce the bug, so we thought the bug could be related to something in the database server (since it did work before being formatted a short time from now) or Hibernate.

I extract the query generated by Hibernate and used it with JBDC PreparedStatement, but the bug still remains.

The query used is below:

Thus not return anything:
Note: If we run it straight into manager sql server returns the value 1



Thus returns the value that actually is correct: 1



Thus returns the value of real fact

The problem is only with SQL Server and in very specific situations. Someone already encountered this, or something?
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

Welcome to the ranch Paulo, sorry i have never seen anything like this, you can make the command fail just by putting the number 171 inside the statement and not as a parameter? that is I very weird.
Paulo Henrique Peres
Greenhorn

Joined: Apr 16, 2012
Posts: 3
Tank you
The command failed to bring the 171 number as parameter, if I put in the statement works normally
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Problem with jdbc and sql server
 
Similar Threads
slow query using JDBC in servlet
login problem
Get or Insert
prepare statement or just statement
Problem with Prepared Statements