This discussion is archived
13 Replies Latest reply: Apr 18, 2013 11:16 PM by svn123 RSS

first,next,last,previous operation?

svn123 Explorer
Currently Being Moderated
hi experts,

am using java code one of the java supported ide. i need to get the first,next,previous,last data from the db..

i had a query. it have run time params. when i using the rs.first i ran into problem.

code follows..
package project1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;


public class SMain {
    public static void main(String[] args) throws Exception {
    try {
    String url = "jdbc:oracle:thin:@xxx.xxx.x.xxx:1521:xxxx";
    String driver = "oracle.jdbc.driver.OracleDriver";
    String user = "xxx";
    String password = "xxx";
    Class.forName(driver);
    Connection connection = DriverManager.getConnection(url, user, password);
   
      PreparedStatement st = connection.prepareStatement("select  name , name1, name2 , name3 from table_name where nx = :1 ");
      st.setString(1, "x");
      ResultSet rs1 = st.executeQuery();
      rs1.first();
      String no = "1x : " + rs.getString(1) +  "2x: " + rs.getString(2) + "3x: " + rs.getString(3) + "4x: " +  rs.getString(4);
      System.out.println(quoteno);
      rs1.close();
      st.close();
    connection.close();
    } catch (Exception e) {
    System.err.println(e);
    }
    }
}
by using this code: am getting this error
java.sql.SQLException: Invalid operation for forward only resultset : first
By this code am not getting any error... value of the first row is getting
code follows.
package project1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;


public class SMain {
    public static void main(String[] args) throws Exception {
    try {
    String url = "jdbc:oracle:thin:@xxx.xxx.x.xxx:1521:xxxx";
    String driver = "oracle.jdbc.driver.OracleDriver";
    String user = "xxx";
    String password = "xxx";
    Class.forName(driver);
    Connection connection = DriverManager.getConnection(url, user, password);
    Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_READ_ONLY);
    String sqlQuery = "select name,name1,name2,name3 from table_name;
    ResultSet rs = stmt.executeQuery(sqlQuery);
    rs.first();
    String no = "1x : " + rs.getString(1) +  "2x: " + rs.getString(2) + "3x: " + rs.getString(3) + "4x: " +  rs.getString(4);
    System.out.println(no);
    }
    rs.close();
    stmt.close();
    connection.close();
    } catch (Exception e) {
    System.err.println(e);
    }
    }
}

{code}

different between two codes are prepared statement & statement with ResultSet.TYPE_SCROLL_INSENSITIVE.

i found out the doc regarding statement vs prepared statment here http://docs.oracle.com/javase/tutorial/jdbc/basics/processingsqlstatements.html

my need is so simple . i need to use run time parameters and also i should get the value of the first row using rs.first.

anyhowi understood what doc says.. am unable to complete my need. can anyone guide me...                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 1. Re: first,next,last,previous operation?
    Tolls Journeyer
    Currently Being Moderated
    If you just want the first row then just call rs.next(), which will give you the first row.

    If you need (as suggested in the first part of your post) to be able to move around the result set and use a PreparedStatement then use the prepareStatement method that lets you specify the type of result set to return.

    Though I would question why you feel the need to jump around the result set.
  • 2. Re: first,next,last,previous operation?
    svn123 Explorer
    Currently Being Moderated
    hi,

    here i again summarize if you not getting me correctly

    first part of the code says
    1.preparedstatement with params
    2.using rs.first().
    here am not getting output. stuck up with error.
    so i skipped to second part of the code.

    second part of the code says.
    1. statement with no params
    2.using rs.first().
    getting my output... but i need to pass param this is important..
    "my need is to get first row value and also using with params.."
    i hope you understood correctly?...

    coming to your post
    If you just want the first row then just call rs.next(), which will give you the first row.
    instead of calling rs.first you are saying call rs.next am right?
    If you need (as suggested in the first part of your post) to be able to move around the result set and use a >PreparedStatement then use the prepareStatement method that lets you specify the type of result set to return.
    here you are saying dont use statement instead of that.. use prepared statement for passing params. am right?
    Though I would question why you feel the need to jump around the result set.
    here i cant get you. anyhow i understood somewaht you are insisting me to go with prepared statment for using run time params and also use rs.next operation to get the first row am right?

    please confirm..

    sorry lately updated. please check this post again.
  • 3. Re: first,next,last,previous operation?
    Tolls Journeyer
    Currently Being Moderated
    Do you need to read the result set in any order other than the order they appear in?
    If not then just read the result set using next().

    If you do need to be able to move around the result set other than from row 1 to row n, then you need to use the prepareStatement method on Connection that lets you set the result set types, as you did for the createStatement method.
    Look in the API for Connection.

    I'm not insisting anything as I do not know what your requirements (as in your real requirements, not your current interpretation of them) are.

    I would suggest reading the JDBC tutorial, since not knowing how a ResultSet works is a bit of a problem when working with JDBC:
    http://docs.oracle.com/javase/tutorial/jdbc/
  • 4. Re: first,next,last,previous operation?
    svn123 Explorer
    Currently Being Moderated
    just i need to get the row in an order. 1.2.3.4.5.6.7...... and so on.. not in random
    how do i re-write the code. can you help me. little bit.
  • 5. Re: first,next,last,previous operation?
    PhHein Guru Moderator
    Currently Being Moderated
    Read that tutorial. You don't seem to have a clue what you're doing.
  • 6. Re: first,next,last,previous operation?
    gimbal2 Guru
    Currently Being Moderated
    svn123 wrote:
    just i need to get the row in an order. 1.2.3.4.5.6.7...... and so on.. not in random
    how do i re-write the code. can you help me. little bit.
    He did!
    I would suggest reading the JDBC tutorial, since not knowing how a ResultSet works is a bit of a problem when working with JDBC:
    http://docs.oracle.com/javase/tutorial/jdbc/
  • 7. Re: first,next,last,previous operation?
    svn123 Explorer
    Currently Being Moderated
    i didnt understand your post. can you re-phrase?
  • 8. Re: first,next,last,previous operation?
    svn123 Explorer
    Currently Being Moderated
    for cursors.
    http://docs.oracle.com/javase/tutorial/jdbc/basics/retrieving.html

    for prepared statement.
    http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

    i understood somewhat. but..

    can anyone confirm me this point

    by using this rs.next i can able to get those things right order 1.2.3.4.5... ?
  • 9. Re: first,next,last,previous operation?
    Tolls Journeyer
    Currently Being Moderated
    You should go through the whole thing, but this page covers the basics:
    http://docs.oracle.com/javase/tutorial/jdbc/basics/processingsqlstatements.html

    and if you don't understand that then there's not much a forum can do to help.
  • 10. Re: first,next,last,previous operation?
    svn123 Explorer
    Currently Being Moderated
         
    PreparedStatement st = connection.prepareStatement("select name,name1,name2,name4 from table_name where nx = :1");
          st.setString(1, "x");
          ResultSet rs1 = st.executeQuery();
          rs1.next();
          String quoteno = "x : " + rs1.getString(1) +  "x1: " + rs1.getString(2) + "x2: " + rs1.getString(3) + "x3: " +  rs1.getString(4);
          System.out.println(no);
          rs1.close();
          st.close();
    as per doc says. cursor move to first record. here now able to do get my output.
    You should go through the whole thing, but this page covers the basics:
    http://docs.oracle.com/javase/tutorial/jdbc/basics/processingsqlstatements.html
    and if you don't understand that then there's not much a forum can do to help.
    yes i understood.
  • 11. Re: first,next,last,previous operation?
    svn123 Explorer
    Currently Being Moderated
    PreparedStatement st = connection.prepareStatement("select name,name1,name2,name4 from table_name where nx = :1");
          st.setString(1, "x");
          ResultSet rs1 = st.executeQuery();
          while(rs1.next()) {
          String quoteno = "x : " + rs1.getString(1) +  "x1: " + rs1.getString(2) + "x2: " + rs1.getString(3) + "x3: " +  rs1.getString(4);
          System.out.println(no);
          rs1.close();
          st.close();
    }
    all the records in an order 1.2... to n.. thanks toll. can you confirm me am going in a right way.
  • 12. Re: first,next,last,previous operation?
    rp0428 Guru
    Currently Being Moderated
    >
    here i again summarize if you not getting me correctly

    first part of the code says

    1.preparedstatement with params
    2.using rs.first().

    here am not getting output. stuck up with error.
    >
    Yes - and if you read the API for the 'first' method it tells you why you are getting that exception.

    You can NOT use the 'first' method if the result set type is TYPE_FORWARD_ONLY.

    http://docs.oracle.com/javase/6/docs/api/java/sql/ResultSet.html#first()
    >
    first
    boolean first()
    throws SQLExceptionMoves the cursor to the first row in this ResultSet object.

    Returns:
    true if the cursor is on a valid row; false if there are no rows in the result set
    Throws:
    SQLException - if a database access error occurs; this method is called on a closed result set or the result set type is TYPE_FORWARD_ONLY
    SQLFeatureNotSupportedException - if the JDBC driver does not support this method
    Since:
    1.2
    >
    And although you say this
    >
    so i skipped to second part of the code.

    second part of the code says.

    1. statement with no params
    2.using rs.first().

    getting my output... but i need to pass param this is important..
    >
    Yes -that works. But NOT because you used a 'statement' instead of a 'preparedStatement'.

    It works because you used this
        Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_READ_ONLY);
    That result set is TYPE_SCROLL_INSENSITIVE so, as the API quote above says the 'first' method will NOT raise an exception.

    If you review the Field Summary at the top of the link I provided you will see this
    static int TYPE_SCROLL_INSENSITIVE 
              The constant indicating the type for a ResultSet object that is scrollable but generally not sensitive to changes to the data that underlies the ResultSet. 
    Using that constant produces a 'scrollable' result set. You can use 'first', 'last' like you saw.

    Has NOTHING to do with statement versus preparedStatement.
  • 13. Re: first,next,last,previous operation?
    svn123 Explorer
    Currently Being Moderated
    hi rp0428,

    great person 1000 thanks to you... perfect answers in a cut short superb:) hat's off :)
    pretty clear documentation. it really give my answer clearly.

    Tolls , PhHein ,gimbal2 thanks folks.
    you people also helped me.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points