Set java variable depending on sql return value

Question

I have the following problem:

I need to set a boolean true is an entry in a database exists.

        ResultSet rc1 = null;
        int Counterval;
        String url = "jdbc:db2://localhost:50000/dbname"; 
        Connection conn = DriverManager.getConnection(url,"",""); 
        Statement st = conn.createStatement();
        String sqlmd="select count(*) as Counter from tablename where mdsum = '"+mdSum+"' and filename = '"+Filename+"'"; 

        rc1=st.executeQuery(sqlmd);
        Counterval=rc1.getInt("Counter");
        System.out.println("VAL: "+Counterval);
        conn.close();

I get the following error message:

 [jcc][t4][1090][10899][4.19.26] Illigal operation to read at the current cursor position. ERRORCODE=-4476, SQLSTATE=02501

How can I do this? I use DB2 if that is of importance

Thanks for your help in advance.

TheVagabond

Why are you trying executeUpdate when you are just selecting a set of results ? Why are you not trying executeUpdate ? — Surajit Biswas, Oct 12 '16 at 10:33

Mario J.G. · Accepted Answer · 2016-10-12T11:16:06.483

I don't know if you've tried the code, but if you did, what kind of exception are you getting?

You should try st.executeQuery instead of st.executeUpdate and then just compare if the returning number of count() function is bigger than 0

EDIT: Seeing now your exception in your edited question:

A ResultSet cursor is initially positioned before the first row; the first call to the method next makes the first row the current row; the second call makes the second row the current row, and so on.

if (rc1.next()){
   booleanVariable = true;
}

This will set the boolean variable in true if the variable resultSet (rc1) is returning something, it means that in the database there is data according to the query you made. If it returns false then it is empty.

I hope it can help you. Greets.

score 0 · Answer 2 · edited May 23 '17 at 11:48

You should callResultSet.next() method first to move the cursor to the first row.

boolean check;
String sqlmd="select count(*) as Counter from tablename where mdsum = '"+mdSum+"' and filename = '"+Filename+"'"; 

rc1=st.executeQuery(sqlmd);
rc1.next();
Counterval=rc1.getInt("Counter");
if(Counterval > 0){
 // do something
}else{
 // do something
}
System.out.println("VAL: "+Counterval);

According to docs of next method -

Moves the cursor forward one row from its current position. A ResultSet cursor is initially positioned before the first row; the first call to the method next makes the first row the current row; the second call makes the second row the current row, and so on.

For better understanding read this answer.

Set java variable depending on sql return value

2 Answers2