Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Strange query results using Java, but OK w/SQLSVR Mgmt Studio

 
James Brooks
Gunslinger
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

Long story short:

When I perform the query:

use conversion
select met_code, met_last from r5meters

from within SQL Server Mgmt Studio, the results are correct, and what I expect:
Meter1 1.0000000000
Meter2 2.0000000000
Meter3 3.0000000000
Meter4 4.0000000000
Meter5 5.0000000000
Meter6 6.0000000000
Meter7 7.0000000000
DFS1 14.3327777778
DFS2 14.3327777778
DFS3 0.0000000000
DFS4 11.3544444444
DFS5 14.3327777778
DFS6 14.3327777778
DFS7 14.2019444444
DFS8 14.3327777778


However, when I do this programatically:


I get this strange result:

Meter1 1.0000000000
Meter2 2.0000000000
Meter3 3.0000000000
Meter4 4.0000000000
Meter5 5.0000000000
Meter6 6.0000000000
Meter7 7.0000000000
DFS1 14.3327777778
DFS2 14.3327777778
DFS3 0E-10
DFS4 0.0
DFS5 0.0
DFS6 0.0
DFS7 0.0
DFS8 0.0

Without all of the code (this is a big program, but I'll be happy to provide it) anyone know why I'm getting 'OE-10' in a double field, and why it's followed by all zeroes, when the DB clearly contains different, legit data? Thanks in advance!!

-Patrick
 
John Kimball
Ranch Hand
Posts: 96
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Looks suspiciously like a driver issue.

What happens if you return the two columns in two separate SQL statements (and result sets)?

 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Without all of the code (this is a big program, but I'll be happy to provide it) anyone know why I'm getting 'OE-10' in a double field, and why it's followed by all zeroes, when the DB clearly contains different, legit data? Thanks in advance!!

If its a double value, why are you using getString?
 
James Brooks
Gunslinger
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Sturrock wrote:

Without all of the code (this is a big program, but I'll be happy to provide it) anyone know why I'm getting 'OE-10' in a double field, and why it's followed by all zeroes, when the DB clearly contains different, legit data? Thanks in advance!!

If its a double value, why are you using getString?


I changed to getDouble() (silly mistake), but am still getting 0.0 where I should be getting legit values??? The OE-10 did go away with the method change:

1.0
2.0
3.0
4.0
5.0
6.0
7.0
14.3327777778
14.3327777778
0.0
0.0
0.0
0.0
0.0
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What exactly are you printing out? The code and output doesn't match, is it this line?

And if so, what is meterNames and what types of object does it store?
 
James Brooks
Gunslinger
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Sturrock wrote:What exactly are you printing out? The code and output doesn't match, is it this line?

And if so, what is meterNames and what types of object does it store?


I'm printing out names for industrial equipment, and the associated runtime, in hours. The meterNames.get() line was a cut and paste leftover. After taking it out, I still get:

Current result = Meter1 1.0
Current result = Meter2 2.0
Current result = Meter3 3.0
Current result = Meter4 4.0
Current result = Meter5 5.0
Current result = Meter6 6.0
Current result = Meter7 7.0
Current result = DFS1 14.3327777778
Current result = DFS2 14.3327777778
Current result = DFS3 0.0
Current result = DFS4 0.0 //is really non-zero
Current result = DFS5 0.0 //is really non-zero
Current result = DFS6 0.0 //is really non-zero
Current result = DFS7 0.0 //is really non-zero
Current result = DFS8 0.0 //is really non-zero

Here is my entire code for this class, if interested:


 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You sure this might not have something to do with it?


Also, just a thought: that code is very difficult to follow, not because the logic is hard, simply because you class is doing way too much. Its not very OO and you are probably going to run into more bugs because of this style of code writing. Have you considered breaking your class up into more specific types? Say once class for parsing the file, one class for talking to the database, one class for reporting errors etc. ? It might make it easier to spot where you are going wrong if you are scanning ten to twenty lines of code rather than 200.
 
James Brooks
Gunslinger
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Sturrock wrote:You sure this might not have soemthing to do with it?



Yeah, I commented that out, just in case, but still got the same result, so it doesn't appear to be the culprit.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ts still a little hard to see where the problem might be, there doesn't appear to be anything in you code that logs the output you are seeing, so its hard to say whether its a JDBC problem or a logging problem. It could be a driver issue but I would be surprised. It would mean that your driver writer has failed to handle a fairly common data type which is unlikely I think. What SQL data type are you using?

 
James Brooks
Gunslinger
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Sturrock wrote:ts still a little hard to see where the problem might be, there doesn't appear to be anything in you code that logs the output you are seeing, so its hard to say whether its a JDBC problem or a logging problem. It could be a driver issue but I would be surprised. It would mean that your driver writer has failed to handle a fairly common data type which is unlikely I think. What SQL data type are you using?


Thanks for all of the replies.

MET_CODE is a varchar(80), null acceptable
MET_LAST is a decimal(18, 10), null acceptable
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ah right - I've spotted where your logging is comming from.

OK, in Java double uses a maximum of 8 bytes to store its value. decimal in SQL Server can go to 17 bytes. My guess is some of your numbers are out of the range double can support. Try using getObject(), see what comes back.
 
James Brooks
Gunslinger
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Sturrock wrote:Ah right - I've spotted where your logging is comming from.

OK, in Java double uses a maximum of 8 bytes to store its value. decimal in SQL Server can go to 17 bytes. My guess is some of your numbers are out of the range double can support. Try using getObject(), see what comes back.


Haha, when I use result.getObject(MET_LAST), I get:

Current result = Meter1 1.0000000000
Current result = Meter2 2.0000000000
Current result = Meter3 3.0000000000
Current result = Meter4 4.0000000000
Current result = Meter5 5.0000000000
Current result = Meter6 6.0000000000
Current result = Meter7 7.0000000000
Current result = DFS1 14.3327777778
Current result = DFS2 14.3327777778
Current result = DFS3 0E-10 //???
Current result = DFS4 null // why is this null when I have a decimal value in SQL Server, I wonder?
Current result = DFS5 null //should be decimal value
Current result = DFS6 null //should be decimal
Current result = DFS7 null /should be decimal
 
James Brooks
Gunslinger
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
D'oh, sorry about the silliness. I forgot that I was overwriting these values in a different part of the program: Still wonder what OE-10 was all about/???

 
James Brooks
Gunslinger
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Update: It works as I showed, but I still get OE-10 for DFS3 value:

Current result = Meter1 1.0000000000
Current result = Meter2 2.0000000000
Current result = Meter3 3.0000000000
Current result = Meter4 4.0000000000
Current result = Meter5 5.0000000000
Current result = Meter6 6.0000000000
Current result = Meter7 7.0000000000
Current result = DFS1 85.9966666668
Current result = DFS2 85.9966666668
Current result = DFS3 0E-10 //???
Current result = DFS4 68.1266666664
Current result = DFS5 85.9966666668
Current result = DFS6 85.9966666668
Current result = DFS7 85.2116666664
Current result = DFS8 85.9966666668
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic