Re: Problems with nextval and currval calling JAVA and Oracle 9i

Alexander Vogl wrote:
> Hello,
>
> need some Help.
> I'm will insert some data into some different tables at an Oracle 9i
> Database.
> Now I have create following sequence:
>
> CREATE SEQUENCE fid_seq start with 1
>
>
> Now I have to access via JAVA to the sequence.
> Here the code of the 2 methodes I'm using:
> <snip>
>
> public static int getNextval_fid()
> {
> String query = "select fid_seq.nextval from dual ";
> int val = 0;
> try
> {
> Connection cn = MakeConnection.get_connection();
> PreparedStatement stmt = cn.prepareStatement(query);
> ResultSet result = stmt.executeQuery();
> while (result.next())
> {
> val = result.getInt(1);
> System.out.println(val);
> }
> result.close();
> stmt.close();
> cn.close();
> System.out.println("fid_nextval:"+val);
> }
> catch (SQLException e)
> {
> LogMsg.dbError(query, e, "SequenceBean::getNextval_fid");
> }
> return val;
> }
>
> public static int getNextval_fid()
> {
> String query = "select fid_seq.nextval from dual ";
> int val = 0;
> try
> {
> Connection cn = MakeConnection.get_connection();
> PreparedStatement stmt = cn.prepareStatement(query);
> ResultSet result = stmt.executeQuery();
> while (result.next())
> {
> val = result.getInt(1);
> System.out.println(val);
> }
> result.close();
> stmt.close();
> cn.close();
> System.out.println("fid_nextval:"+val);
> }
> catch (SQLException e)
> {
> LogMsg.dbError(query, e, "SequenceBean::getNextval_fid");
> }
> return val;
> }
>
> </snip>
>

Not that I'm a Java expert in any way, but both your methods look exactly the same to me. However, what you wanted to know (I'm guessing here) is why the following code wont work:

     public static int getCurval_fid()
      {
          String query = "select fid_seq.curval from dual ";
          int val = 0;
          try
          {
              Connection cn = MakeConnection.get_connection();
              PreparedStatement stmt = cn.prepareStatement(query);
              ResultSet result = stmt.executeQuery();
              while (result.next())
              {
                  val = result.getInt(1);
                  System.out.println(val);
              }
              result.close();
              stmt.close();
              cn.close();
               System.out.println("fid_curval:"+val);
          }
          catch (SQLException e)
          {
              LogMsg.dbError(query, e, "SequenceBean::getCurval_fid");
          }
          return val;
      }

Curval is only defined in your session *AFTER* a call to nextval. Since you seem to open a new connection with

 > Connection cn = MakeConnection.get_connection();

you have a new session, thus curval is not defined yet.

HTH Holger Received on Thu Jan 22 2004 - 08:04:20 CST