1

I am trying to get some information out of my database from Java.

String sql_get_Tot = "
    SELECT 
        dbo.table1.Quantity * dbo.table2.CostPerIndivdual AS QC 
    FROM 
        dbo.table1 
        INNER JOIN 
        dbo.table2 
            ON dbo._IISJoin.ItemID = dbo.table2.ItemID 
    WHERE
        dbo.table1.SupplierID = 2 AND 
        dbo.table1.ItemID = 1 AND 
        dbo.table1.InvoiceID = 2
";

state = con.createStatement();
    total = state.executeQuery(sql_get_Tot);
totalsql = total.getFloat(1);

This Returns nothing within the result set

Run Exactly the same query in MSSQL and I get 10.00

any ideas what is going wrong here, I have checked the basics such as connected to the right database and so on.

Thanks for any help you might be able to provide in advance.

Igor
  • 33,276
  • 14
  • 79
  • 112
user3342176
  • 11
  • 2
  • I guess you have to mention the db name in place of dbo in order for it to work in mysql – Chakradhar Vyza Feb 23 '14 at 01:25
  • @ChakradharVyza - He doesn't mention MySQL at all, so that's not likely the solution. For the OP - you're using floating point values for monetary values. This is **NOT** recommended, especially because you can't exactly represent values like `.1`. In Java you should be using `BigDecimal`. – Clockwork-Muse Feb 23 '14 at 03:21

1 Answers1

1

I believe that "total" is a ResultSet. So then you need to do a:

if(total.next()){
   totalsql = total.getFloat(1);
   ...
Alvin Bunk
  • 7,621
  • 3
  • 29
  • 45
  • Could you at least also advise him against using floating point with what appears to be monetary values? – Clockwork-Muse Feb 23 '14 at 03:22
  • Hi @Clockwork-Muse. I saw what you are saying, but I'm not sure what you mean by using `BigDecimal`? When I look at his code, I might use float too. Can you post an example, so we can follow what you mean? It sounds like and interesting answer... – Alvin Bunk Feb 23 '14 at 03:37
  • See some of the answers (and comments and links) to [this question](http://stackoverflow.com/questions/1661273/floating-point-arithmetic-not-producing-exact-results-in-java). Also look at some of [the discussion here](http://stackoverflow.com/questions/618535/what-is-the-difference-between-decimal-float-and-double-in-c/618596#618596). Accounting tends to have **very** stringent rules about what happens to fractional amounts; note that while floating point has well-defined behavior, it's not the same rules accounting goes by, and the values representable are usually non-intuitive. – Clockwork-Muse Feb 23 '14 at 09:07