4 Replies Latest reply on Aug 9, 2006 8:56 AM by Newsgroup_User

    Detect when queries physically written

      Using CF5 and have done so for many years.

      Some crystal reports we generate need some coldfusion queries to be run first to summarize some complicated data, this data is then inserted into some temp tables in access databse that crystal reports then reports off.

      Problem sometimes occurs when server is busy that coldfusion and odbc updates are not instant, so that crystal reports viewer misses out some records that are still in process of being written away.

      Putting a delay of a couple of seconds before transferring to crystal viewer fixes most occasions but I was wondering if there is a way to detect when queries have successfully physically been written to database.
        • 1. Re: Detect when queries physically written
          jdeline Level 1
          Does Crystal Reports have the ability to pause if a particular file is not present? I was thinking of creating a 1-byte trigger file that is deleted before the database update and recreated when the update has completed.
          • 2. Re: Detect when queries physically written
            Level 7
            My first thought would be a flag set in a persistent scope, session,
            application or server, depending on your exact requirements.

            At the beginning you would default something like session.queriesDone =
            false. Then just after the queries, set this to true.

            Then in your relocation to the crystal reports you would check to see if
            this value is true before continuing on to the report, else recheck in a
            couple of seconds.

            TheSpookster wrote:
            > Using CF5 and have done so for many years.
            >
            > Some crystal reports we generate need some coldfusion queries to be run first
            > to summarize some complicated data, this data is then inserted into some temp
            > tables in access databse that crystal reports then reports off.
            >
            > Problem sometimes occurs when server is busy that coldfusion and odbc updates
            > are not instant, so that crystal reports viewer misses out some records that
            > are still in process of being written away.
            >
            > Putting a delay of a couple of seconds before transferring to crystal viewer
            > fixes most occasions but I was wondering if there is a way to detect when
            > queries have successfully physically been written to database.
            >
            1 person found this helpful
            • 3. Re: Detect when queries physically written
              Thanks but both those answers won't help.

              Imagine in a singe cfm page, you do a simple cfquery and insert some data into a database table, then you cflocation to another cfm page that invokes a crystal report viewer (currently an activex plugin). Crystal report will then show contents of that newly updated table.

              The problerm is that coldfusion does not write the contents instantly to the database, it goes into a buffer area first and then gets written to the database. This can occur after cfm page has completely finished processing and is mainly due to load on server at the time.

              Even if you requery table after writing it, coldfusion is clever enough to tell you that it is written away, even if data is still in its to-be-written' buffer.

              So basically I'm after a way of determining if my cfquery updates and inserts have physically been written to the mdb file and are not still being buffered into it. Only then is it safe to transfer to report viewer.

              I have a feeling it's an impossible task, so I've increased the pause from 2 seconds to 5 seconds between the cfquerys and the transfer to report viewer. I'll have to wait for users to let me know if it has helped but they are concerned that report 'may' be missing data. Currently they can refresh report and the missing data will be shown, but that's not really a good solution.
              • 4. Re: Detect when queries physically written
                Level 7
                I think this should be possible, but it may be more difficult then it is
                worth doing.

                This would require the database to store and set some kind of flag. I
                would think that this could be fairly straight forward in a stored
                procedure for DBMS that support them. You may be able to do this in
                access with a module or something. But it sure would be a challenge..

                TheSpookster wrote:
                > Thanks but both those answers won't help.
                >
                > Imagine in a singe cfm page, you do a simple cfquery and insert some data into
                > a database table, then you cflocation to another cfm page that invokes a
                > crystal report viewer (currently an activex plugin). Crystal report will then
                > show contents of that newly updated table.
                >
                > The problerm is that coldfusion does not write the contents instantly to the
                > database, it goes into a buffer area first and then gets written to the
                > database. This can occur after cfm page has completely finished processing and
                > is mainly due to load on server at the time.
                >
                > Even if you requery table after writing it, coldfusion is clever enough to
                > tell you that it is written away, even if data is still in its to-be-written'
                > buffer.
                >
                > So basically I'm after a way of determining if my cfquery updates and inserts
                > have physically been written to the mdb file and are not still being buffered
                > into it. Only then is it safe to transfer to report viewer.
                >
                > I have a feeling it's an impossible task, so I've increased the pause from 2
                > seconds to 5 seconds between the cfquerys and the transfer to report viewer.
                > I'll have to wait for users to let me know if it has helped but they are
                > concerned that report 'may' be missing data. Currently they can refresh report
                > and the missing data will be shown, but that's not really a good solution.
                >
                1 person found this helpful