Using cfloop and cfquery

I need a way to query a database and over and over until a new record is added.  The record will be added by another application, so my app just needs to wait until the new record is created, then immediately redirect my user to a page where they can edit that new record.

I think I'm on the right track with the code I have and the help I've received from the Macromedia forums.  But I don't know how to run the query over and over...or if there is another way to do this.  Setting up a scheduled task is not the answer as this could happen at any time.

Here's the code I have so far:
<!--- User clicks a button triggering an update to the user table, then an outside app see this new update to the user
table, and ads a new record to the activity table --->

<cfif IsDefined("FORM.submit")>
  <cfquery datasource="slx">
    UPDATE sysdba.USERINFO SET TICKETNOTIFY = 'T', ACCOUNTINGUSERID = '#FORM.CONTACTID#' WHERE USERID = '#FORM.userid#'
  </cfquery>
         
  <!--- There is a field in the ACTIVITY table called CREATEDATE that is the date on which the record gets created.  I
  figured, if I set a variable for current time, I can then some how check the CREATEDATE against the currentTime variable, thus knowing a new record exists. --->

  <cfset currentTime = Now()>

  <!--- Not sure what to do with this.  I need to query the ACTIVITY table and see if a new record has been created since "currentTime" --->
         
  <cfquery name="activCnt" datasource="slx">
    SELECT ACTIVITYID, CREATEDATE
    FROM sysdba.ACTIVITY
  </cfquery>

  <!--- Once I do determine that a new record has been completed, I redirect my user to the edit form where they can edit this newly created record. Don't know if this is correct either.--->
         
  <cfif #activCnt.CREATEDATE# GT #currentTime#>
    <cflocation url="#ulcURL#slx/activEdit.cfm?user=#url.user#&activityID=#activCnt.ACTIVITYID#">
  </cfif>
         
</cfif>
IndyBAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

danrosenthalCommented:
You should use a screen refresh after an arbitrary amount of seconds (say 2 for instance).  So every 2 seconds the screen will reload until the record you are looking for is found at which point it will redirect using cflocation.

You can use the META tag to refresh or use CFHEADER like this:
<CFHEADER name="refresh" value="2; url=pagename.cfm">
0
anandkpCommented:
hi what u cld also do is :

have a query on ur page to chk for the status of "TICKETNOTIFY" - if its "T" then redirect else do nothing !

<CFHEADER name="refresh" value="2; url=pagename.cfm">
put this in ur head tag of ur page & change ur code on the page to :

<cfquery name="x" datasource="slx">
   Select sysdba.USERINFO where TICKETNOTIFY = 'T'
And USERID = '#FORM.userid#'
 </cfquery>

<cfif x.recordcount gt 0>
    redirect the user ...
</cfif>

K'Rgds
Anand
0
danrosenthalCommented:
anandkp...
I don't think that will work.
IndyB is first setting the value TICKETNOTIFY = 'T' in the USERINFO table to have some other process add a record.

Also, for clarification the CFHEADER tag can go anywhere on the page, it doesn't have to be put in the HEAD tag.

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

anandkpCommented:
yeah i wasnt impying abt teh CFHeader tag to be ONLY in head tag - its a good practice to keep it there - so i just mentioned it.

Also the query part is just a logical explanation of what he cld do ... i just took up a eg to make him understand - i hope he gets it :)

PS : pls dont think i am trying to overrule ur comment ... no offence intended ... excuse me if it has caused ne-problems to u

Cheers
Anand
0
hartCommented:
i would suggest that you use triggers.

after update, after insert.

Then you don't have to refresh the page and send the user directly to the next page as the triggers will be fired as soon as the update occurs.

hope this helps,

if you require help in creating triggers then let me know.

Also you could go with the page refreshing funda if required.

Regards
Harish(Hart)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anandkpCommented:
hello - ne-luck yet ???

K'Rgds
Anand
0
mrichmonCommented:
No comment has been added lately, so it's time to clean up this question.
I will leave the following recommendation in the Cleanup topic area:

Accept hart

Please leave any comments here within the next four days.

mrichmon
EE Cleanup Volunteer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.