File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Strange query results using Java, but OK w/SQLSVR Mgmt Studio Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Strange query results using Java, but OK w/SQLSVR Mgmt Studio" Watch "Strange query results using Java, but OK w/SQLSVR Mgmt Studio" New topic
Author

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

James Brooks
Gunslinger
Ranch Hand

Joined: Aug 17, 2006
Posts: 165
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

Hello. My name is Inigo Montoya. You killed my father. Prepare to die.
John Kimball
Ranch Hand

Joined: Apr 13, 2009
Posts: 96
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

Joined: Apr 14, 2004
Posts: 10336


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?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
James Brooks
Gunslinger
Ranch Hand

Joined: Aug 17, 2006
Posts: 165
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

Joined: Apr 14, 2004
Posts: 10336

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

Joined: Aug 17, 2006
Posts: 165
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

Joined: Apr 14, 2004
Posts: 10336

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

Joined: Aug 17, 2006
Posts: 165
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

Joined: Apr 14, 2004
Posts: 10336

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

Joined: Aug 17, 2006
Posts: 165
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

Joined: Apr 14, 2004
Posts: 10336

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

Joined: Aug 17, 2006
Posts: 165
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

Joined: Aug 17, 2006
Posts: 165
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

Joined: Aug 17, 2006
Posts: 165
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Strange query results using Java, but OK w/SQLSVR Mgmt Studio