3 Replies Latest reply on Aug 10, 2011 4:36 PM by ChrisJenkins-Oracle

    TimesTen 7.0.5.0.0 ODBC Driver]Function sequence Error

    joesaby
      Hi,

      We have been getting a Function Sequence error within Application code which is intermittent. Issue has been observed when at scale test which results in frequent reads into TimesTen

      Stack Trace:
      java.sql.SQLException: [TimesTen][TimesTen 7.0.5.0.0 ODBC Driver]Function sequence error
      com.timesten.jdbc.JdbcOdbc.createSQLException(JdbcOdbc.java:3184)
      com.timesten.jdbc.JdbcOdbc.standardError(JdbcOdbc.java:3318)
      com.timesten.jdbc.JdbcOdbc.SQLFetch(JdbcOdbc.java:844)
      com.timesten.jdbc.JdbcOdbcResultSet.next(JdbcOdbcResultSet.java:372)

      The application is a multithreaded service.

      All over the application code, Connection is retrieved from a singleton class
      ttConn = Utils.INSTANCE.getTTConn();
      ----------------------
      ........
      import com.timesten.jdbc.TimesTenDataSource;
      ........
      private final TimesTenDataSource ds = new TimesTenDataSource();
      ........
           public Connection getTTConn() {
              try {
                  if ((ttConn == null) || (ttConn.isClosed()))  {
                  
                      ttConn = ds.getConnection();
                  }
      
              } catch (SQLException ex) {
                      SoapServiceLogger.severe("Failed to get TT Connection");
                      SoapServiceLogger.severe(ex);
              }
      
              return ttConn;
          }
      ........
      Whats the likely cause of the issue & is there any best practices suggested to avoid the occurrence of the issue?

      Thanks,
      Jose

      Edited by: 878519 on Aug 10, 2011 4:09 AM
        • 1. Re: TimesTen 7.0.5.0.0 ODBC Driver]Function sequence Error
          ChrisJenkins-Oracle
          It's not possible to give a definitve answer based on this information but here are a few pointers...

          ODBC (and JDBC) are 'stateful' APIs; API handles (API objects) are always in a specififc state based on what operations have been performed on them up to that point. For any given handle state, only certain operations are allowed. A 'function sequence error' occurs when the operation requested via the APi is not valid for the current state of the APi handle it is being requested against.

          From the details provided, an application thread was trying to iterate over ResultSet and at some point the state of the underlying handle was not valid for an ODBC 'SQLFetch' operation.

          One cause of this that I have seen is in multi-threaded applications using some kind of connection pool where sometimes a connection gets returned to the pool in an incorrect state (e.g. maybe it has an open transaction or an open cursor) but there could be other causes as well.

          While it is conceivable that this could be a TimesTen bug (but pretty unlikely given the large number of Java/JDBC applications in production for a long time with TimesTen 7.0.5) it is far more likely some logic error in the application whereby it is not managing connections/transactions/ResultSets corrctly in a multi-threaded environment. You might want to look at those aspects of your code and also try and add some additional diagnostics so you can get a better picture of what leads up to this error.

          Chris
          • 2. Re: TimesTen 7.0.5.0.0 ODBC Driver]Function sequence Error
            joesaby
            Thanks for your reply Chris! You are right issue always happens when iterating through a resultset.

            There is no connection pool maintained within the application code, rather a connection is created & closed (not always closed explicitly) when required.

            Couple of questions
            1) With the above logic, would there be a chance of same connector objects being used by different threads?
            2) Could not closing a connector explicitly (Connector.close()) be any likely cause under load.

            Also could you point me to a guide with diagnostics tips such as to get more debug from the driver?

            Thanks, Jose

            Edited by: 878519 on Aug 10, 2011 4:25 AM
            • 3. Re: TimesTen 7.0.5.0.0 ODBC Driver]Function sequence Error
              ChrisJenkins-Oracle
              It's hard to comment in detail as i don't know what the code in your various classes/methods (Utils.INSTANCE.getTTConn(), ds.getConnection()).

              I would certainly recommend always explicitly closing a connection as soon as it is no longer required. Waiting for garbage collection to handle these things is generally a bad idea.

              Also, a design pattern that involves getting (opening?) a connection, using it and then closing it is likely to result in very poor performance unless the lifespan and usage of the connection is long lived and these operations do not happen very oftenm. If they happen for every query or every transaction then there is little benefit in using TimesTen as the overhead of opening and closing these connections will be incredibly large.

              I'm afraid there is not really any debug information available from the driver.

              Chris