13 Replies Latest reply: Apr 19, 2013 1:16 AM by svn123 RSS

    first,next,last,previous operation?

    svn123
      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
          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
            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
              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
                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
                  Read that tutorial. You don't seem to have a clue what you're doing.
                  • 6. Re: first,next,last,previous operation?
                    gimbal2
                    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
                      i didnt understand your post. can you re-phrase?
                      • 8. Re: first,next,last,previous operation?
                        svn123
                        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
                          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
                                 
                            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
                              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
                                >
                                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
                                  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.