Retrieve record from DB?

[Hari Dhanakoti]

Here in my code i have the operation of getting the Maximum primary key and i have to make use for numbering the persons registering.

QUERY

select TOP 1 person_id from persons order by person_id desc

Here in this query it results as

person_id
5

Then how can i read the value of the person_id in the java program. As i had tried to make use of the getInt(),getString() methods with the result set where it specifies the output as

java.sql.SQLException: No current row in the ResultSet.

I am not sure how to solve this problem.

[Rob Spoor]

You have two options:
1) check if rs.next() (where rs is a ResultSet) returns true; if so, read the result, otherwise there are no rows and use 1

2) use "select MAX(person_id) AS MaxID from persons". That will always return a value, possibly NULL. If you use getInt, that NULL will be converted to 0.

[Adeel Ansari]

I think, you didn't call rs.next() prior getting the result from the set.

[Hari Dhanakoti]

Hi,

I got a temporary solution.

Where if i make use of the PreparedStatement then the problem arises that no result set found, but in case if i make use of the ordinary statement it has no problem with retrieving information from the database.

I had used only the rs.next() in while loop no other way the result set values are retrieved.

Can anyone say me what could be the reason for the difference in using these two statements in this situation.

[Adeel Ansari]

Originally posted by Wing Rider:
Where if i make use of the PreparedStatement then the problem arises that no result set found, but in case if i make use of the ordinary statement it has no problem with retrieving information from the database.

Where is the code?

[Hari Dhanakoti]

Working Code

String sql = "select Max(person_id) as personID from persons";
        stmt = conn.createStatement();
        rs = stmt.executeQuery("select Max(person_id) as personID from persons");
while(rs.next()){
          String val = rs.getString(1);
          System.out.println("val is "+val);
        }

Error Code

pstmt = conn.prepareStatement("select TOP 1 person_id from persons order by person_id desc");
rs = pstmt.executeQuery();
while(rs.next()){
          String val = rs.getString(1);
          System.out.println("val is "+val);
        }

[Adeel Ansari]

These are 2 different queries pal. Use the same query with PreparedStatement you will get the result fine.