getInt method isn't returning a value from a database table

Question

I have a oracle table defined like this:

create table image_table_test (id number primary key, image ordsys.ordimage);

when I try to select maximum ID value in that table and return it in java variable, using the code below, it returns a zero value, but the value in a table is set to 1. the rsx.getInt("ID"); method isn't returning an integer value from a table.

my table When I execute SELECT MAX(ID) as ID FROM image_table_test;:

ID
1

my java code:

Statement stmt;

try{
    stmt = conn.createStatement();

    String maxIdStr = "";
    int maxIdNum = 0;

    String maxIdSQL = "SELECT MAX(ID) as ID FROM image_table_test";                
    ResultSet rsx = stmt.executeQuery(maxIdSQL);

    while(rsx.next()){
        maxIdNum = rsx.getInt("ID"); // Here the value is passed as 0
    }

    maxIdNum = maxIdNum + 1;
    maxIdStr = Integer.toString(maxIdNum);

    System.out.println(maxIdStr);
    rsx.close();
    stmt.close();

I would appreciate your help if you know why is this happening?

Presumably the query fails for some reason and you are getting the default value that you set it to when you declare the variable. — Gordon Linoff, Jun 29 '14 at 00:51
I have declared a try and catch block, and it isn't throwing an exception. So i presume the query is not failing. The same query executes just fine on server directly. — , Jun 29 '14 at 00:59
Are you trying to fetch the last inserted id from the database? If yes then, transactionally, this is not the correct way to do so. Please [see](http://stackoverflow.com/q/1915166/1391249). — Tiny, Jun 29 '14 at 01:06
Please check to be sure that your Java code is connecting to the expected database. — Bob Jarvis - Слава Україні, Jun 29 '14 at 02:01

Federico Piazza · Accepted Answer · 2014-06-29T01:33:31.347

1

You code is ok, but I don't trust in your query result.

Try setting a negative value on maxIdNum to see if your are getting results from your query.

int maxIdNum = -1;

Another test you can do is replacing rsx.getInt("ID"); by rs.getString("ID"); to know if the result is a string it will fail in case of int so will help you to isolate the problem.

Another you can do is to set a query with a hardcoded result to know if there is another problem:

select 1 as ID from dual

edited Jun 29 '14 at 01:33

answered Jun 29 '14 at 01:27

Federico Piazza

30,085
15
87
123

If i set the variable to -1 it still returns 0. – Jun 29 '14 at 01:29
@eror404 I updated the answer. Add the query I added to check if that works. That will help you to know what the problem is – Federico Piazza Jun 29 '14 at 01:34
this returns one it must be the query but i can't see what is wrong with it. tnx – Jun 29 '14 at 01:36
For some reason capital letters does not work in java. If i execute it in lowercase: select max(ID) as ID from image_table_test, then it works. Opposed to SELECT MAX(ID) as ID FROM image_table_test; – Jun 29 '14 at 02:01
I wasted 3 hours on that. that is just plain stupid. – Jun 29 '14 at 02:03
@eror404 the good news is that you solved it. About the lowercase, maybe you have an old driver. Btw, it is always useful to have a hardcoded value to mock results. – Federico Piazza Jun 29 '14 at 02:05

score 0 · Answer 2 · answered Jun 29 '14 at 01:08

0

Since you are not fetching the ID column, I'd change it to :

maxIdNum = rsx.getInt(1);

instead of :

maxIdNum = rsx.getInt("ID");

I'd also change the query to :

String maxIdSQL = "SELECT MAX(ID) FROM image_table_test";

answered Jun 29 '14 at 01:08

Eran

387,369
54
702
768

I did try this and it still returns 0. – Jun 29 '14 at 01:13

getInt method isn't returning a value from a database table

2 Answers2