5 Replies Latest reply on Jun 14, 2006 5:53 PM by weston.price

    Informix error -710 / invalid entries in prepared statement

    lafr
    lafr Jun 9, 2006 3:50 PM

      We are using Informix 9.4 and 10.0 as database server.
      To get the index statistics up to date you'll have to execute an "update statistics" regulary.
      Most often this is done on the weekend when there is low traffic on the system.

      If we use the prepared statement cache, the next time a prepared statement is used again after the update statistics, we get an SQLException because of database error code -710:
      -710 Table <table-name> has been dropped, altered or renamed.

      The database connection used is marked as bad, because -710 is handled in InformixValidConnectionChecker as fatal.

      Is there a way to use the cache but avoid this situation?

      • 3190 Views
      • Tags:
        • 1. Re: Informix error -710 / invalid entries in prepared statem
          weston.price
          weston.price Jun 13, 2006 12:30 PM (in response to lafr)

          A modification was made in the INFORMIX exception sorter

          http://jira.jboss.org/jira/browse/JBAS-3120

          The -710 condition was marked as fatal to avoid reusing connections/statements that are invalid.

            • Actions
          • 2. Re: Informix error -710 / invalid entries in prepared statem
            lafr
            lafr Jun 13, 2006 6:04 PM (in response to lafr)

            Yes I know. This change was initiated by me.
            The prepared statements are unusable and should be thrown away and rebuild at this point.
            But the application should not get any notice about this.
            How to achieve this?

              • Actions
            • 3. Re: Informix error -710 / invalid entries in prepared statem
              weston.price
              weston.price Jun 13, 2006 6:27 PM (in response to lafr)

              I thought it was you :-)

              The exception sorter (the implementation that determines whether or not an exception was fatal or not) does not differentiate between connection or statement. Since the statements are per managed connection, errors on the connection or the statement will be reflected in the sorter and typically will require that the connection be destroyed.

              Another way to think of it is that there is no such thing as a prepared statement being fatal, and not have it effect the underlying managed connection. So, I am not sure what you mean by

              "But the application should not get any notice about this."

              Perhaps if you could explain a bit more. Sorry if I am not understanding your intent.

                • Actions
              • 4. Re: Informix error -710 / invalid entries in prepared statem
                lafr
                lafr Jun 14, 2006 5:43 PM (in response to lafr)

                If the reuse of a prepared statement fails because of this (tables or statistics for the tables used in the query have changed since prepare) the prepared statement should be thrown away first. JBoss should then try to create a new prepared statement then.
                If this also fails, there seems to be a fatal error and the connection should be marked unusable.
                The session bean uisng the entity bean with the unusable statement gets the Exception.
                For informix error -710 a new prepare of the statement should succeed, and the session been using the using the entity bean get's nothing aboud this new prepare and can contiue in execution.

                  • Actions
                • 5. Re: Informix error -710 / invalid entries in prepared statem
                  weston.price
                  weston.price Jun 14, 2006 5:53 PM (in response to lafr)

                  So, what you are saying is that in the case of Informix, the statement should be attempted twice? This can be accomplished via coding. There is nothing to prevent your code from catching the exception, evaluating the code, and attempting to execute the statement a second time.

                  JCA is agnostic in terms of DB exceptions, coding for this particular case would imply that we 'know' of a specific INFORMIX error and attempt to execute the query again.

                  Sorry, but it just doesn't seem to be a valid use case in this context.

                  I can rework the INFORMIX exception sorter to not fail in the case of this particular error, effectively putting it back the way it was. The connection would still be valid in this case allowing you to execute the statement again.


                    • Actions