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 Capturing result of SUM query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Capturing result of SUM query" Watch "Capturing result of SUM query" New topic
Author

Capturing result of SUM query

Garrett Rowe
Ranch Hand

Joined: Jan 17, 2006
Posts: 1296
I'm stuck trying to capture the output of the SUM
SQL command. I have a table called Coins with columns
Type, and Value. The exercise asks us to (among other things) use the 'SUM'
SQL command to get the sum of the Values column. Here
is the section of code that is giving me trouble.

The SimpleDataSource class just provides a convienent method for establishing a connection to the database. I
know there is data in the database because the rest of
my queries return predictable results. If needed, I
could attach my source code. I would be greatful for
any tips you can provide.

Thanks,
Garrett


Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peter
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29219
    
135

Garrett,
That code looks fine to me. What does the query return if you just run:
"SELECT Values FROM Coins" ?

Obviously, this won't solve your problem, but it may narrow it down. Also, what database are you using?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Garrett Rowe
Ranch Hand

Joined: Jan 17, 2006
Posts: 1296
I figured it out.

The problem wasn't that

while (result.next())

was returning false, it was that I was trying to provide a column name when I was examining my ResultSet:


This is a snippet from the working code


Thanks for the help. While going back and trying the query you suggested, the light switch just flipped on.

btw I'm using HSQLDB
[ February 18, 2006: Message edited by: Garrett Rowe ]
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333

If you'd prefer to get column by name, which is always easier than keeping your indexes straight as the code evolves, most databases/drivers will support this:

Garrett Rowe
Ranch Hand

Joined: Jan 17, 2006
Posts: 1296
Thanks for the tip stu. That's one that wasn't in my text. That's exactly why I love this forum.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29219
    
135

Many databases require an "as" when definining a column name alias:
"String query = "SELECT SUM(Value) as SUM_VALUE FROM Coins";"
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Capturing result of SUM query
 
Similar Threads
does gc still have a chance to do its job if i kill the java process
Could not get a good connection to the database ?
Beginner at Ant and Servlets
? Could not get a good connection to the database ?
Problems with .sql(script files) and .properties files. [Using Netbeans]