The moose likes JDBC and Relational Databases 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
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
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
 
Have you checked out Aspose?
 
subject: Problem with jdbc and sql server
 
It's not a secret anymore!