SQL Statement in Java not pulling back same information as in MSSQL

Question

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.

I guess you have to mention the db name in place of dbo in order for it to work in mysql — Chakradhar Vyza
– Chakradhar Vyza, Commented Feb 23, 2014 at 1: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
– Clockwork-Muse, Commented Feb 23, 2014 at 3:21

Alvin Bunk · Accepted Answer · 2014-02-23 01:42:40Z

1

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

if(total.next()){
   totalsql = total.getFloat(1);
   ...

answered Feb 23, 2014 at 1:42

Alvin Bunk

7,8123 gold badges31 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Clockwork-Muse Over a year ago

Could you at least also advise him against using floating point with what appears to be monetary values?

Alvin Bunk Over a year ago

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...

Clockwork-Muse Over a year ago

See some of the answers (and comments and links) to this question. Also look at some of the discussion here. 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.

Collectives™ on Stack Overflow

SQL Statement in Java not pulling back same information as in MSSQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related