2 Replies Latest reply on Aug 19, 2016 12:51 PM by eric

    Server ESS "connection lost contact"


      I have a FileMaker solution with an External SQL Source (ESS) on the server (Windows), meaning the DSN setup is also on the server.

      It seems a connection to the Oracle database does not occur until the client refers to one of the ESS's shadow tables, because that very first reference in a session takes more time than subsequent connections to all the rest of the shadow tables.

      It is strange to me that this delayed ESS connection would occur once per client session, since it is the server that has all the connection information, firewall ports open, etc.

      Why isn't this ESS connection already open on the server for all subsequent client sessions after the first client refers to a shadow table?


      If a user with a FileMaker Pro client is connected to the FileMaker file for a lengthy amount of time, all the shadow table fields display the following error:

      ODBC Error: [Oracle][ODBC][Ora]ORA-03135: connection lost contact

      Process ID: 9013

      Session ID: 270 Serial number: 19220

      Refresh Window [ Flush cached eternal data ] displays the same error.

      For an individual client to reestablish the ESS connection, we've been closing the solution all the way, because even a hidden file window persists having a lost connection.

      When the user reopens the file after it has been closed completely, ESS reconnects.

      Is there a script step or another action that will force ESS to reconnect without having to close and reopen the FileMaker file?

        • 1. Re: Server ESS "connection lost contact"

          I found the answer to my main question is on page 25 of this document:

          Best Practices: External SQL Data Sources


          Managing ODBC Connections

          In the normal course of things, connections to specified ODBC sources are made automatically when a user needs to work with ESS-based data. It’s possible, though, that sometimes a connection to an ODBC source won’t be made as expected, or that a developer will want to deliberately disconnect from an ODBC source.


          In FileMaker Pro 9 or later, the File Open and File Close script steps have been extended to allow them to work with ODBC data sources. File Open, when called on an ODBC data source, will attempt to reconnect to the data source if there is not a currently open connection. File Close will attempt to close the connection to the data source. After either of these steps, the data source will behave as it normally would: if a connection has been opened, all ESS functionality will be available, and if the connection has been closed, the next request for data from that source will trigger a new connection attempt.


          Or these days known as Open File and Close File.

          Closing the file works, because the ESS "file" will reopen on the next attempt to refer to a shadow table.


          It is a good idea to avoid closing an ESS connection when the current layout is based on that data source. It is best to find a way to navigate to a different layout first, or the user may see some odd behavior, though the operation should succeed.
          1 of 1 people found this helpful
          • 2. Re: Server ESS "connection lost contact"

            After reading that Best Practices: External SQL Data Sources, I think I see the answer to my other question:

            Why isn't this ESS connection already open on the server for all subsequent client sessions after the first client refers to a shadow table?

            It looks like ESS is assuming that each client has to provide credentials to connect to the ESS. That's a safe assumption, but I wish I could override it so that authenticating to my FileMaker solution would be all that is required to use a more persistent pre-authenticated ESS connection on the server.