?
Solved

Using cfloop and cfquery

Posted on 2003-03-18
8
Medium Priority
?
389 Views
Last Modified: 2013-12-24
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>
0
Comment
Question by:IndyB
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 15

Expert Comment

by:danrosenthal
ID: 8162373
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
 
LVL 17

Expert Comment

by:anandkp
ID: 8164294
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
 
LVL 15

Expert Comment

by:danrosenthal
ID: 8168360
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 17

Expert Comment

by:anandkp
ID: 8171572
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
 
LVL 11

Accepted Solution

by:
hart earned 300 total points
ID: 8193084
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
 
LVL 17

Expert Comment

by:anandkp
ID: 8244947
hello - ne-luck yet ???

K'Rgds
Anand
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10940712
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

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question