Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to continue loop on error?

Posted on 2000-04-05
15
Medium Priority
?
399 Views
Last Modified: 2013-12-24

Context

CF 4.0, Oracle 73.3.4

I have an insert statement within a CFLOOP tag:

<CFLOOP query="Q">
      <CFQUERY name="INSERT" datasource="D"
      username="X" password=Y">
      INSERT INTO Table (SSN)            
      VALUES(<CFOUTPUT>#ssn#'</cfoutput>)
      </cfquery>
</cfloop>

Some records might already be in the table and the loop stops after the dup row is rejected (error:  [Oracle][ODBC][Ora]ORA-00001: unique constraint (P.PK_TABLE) violated).

Question:

How can I get the loop to go to the next record and continue processing? I am thinking in terms of the On Error Resume Next      feature of VBA.

Thanks in advance for your time and help.
0
Comment
Question by:smoyano
[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
  • 6
  • 5
  • 2
  • +1
15 Comments
 
LVL 1

Expert Comment

by:deepchanda
ID: 2687195
I assume that Query Q fetches all the SSN values which is been used in the INSERT statement. The incident of inserting a duplicate value can be prevented if list of SSN values can be pro-actively filtered by not including the values that already exist in the SSN table.. The Query Q would have a sub-select like ..
select ssn from .... where ssn not in (select ssn from ssn)

hope this helps

DC
0
 
LVL 3

Expert Comment

by:dapperry
ID: 2687201
Unless this would take too long and affect performance, why don't you just check for the duplicate record before you insert it?  Something like this:

<CFLOOP query="Q">

<cfquery name="checkforDupe" datasource="D">
SELECT SSN
FROM Table
WHERE SSN='#ssn#'
</cfquery>

<cfif checkforDupe.recordcount is 0>

<CFQUERY name="INSERT" datasource="D"
username="X" password=Y">
INSERT INTO Table (SSN)            
VALUES(<CFOUTPUT>#ssn#'</cfoutput>)
</cfquery>

</cfif>
</cfloop>

Let me know if this methodology works for you.

:) dapperry
0
 
LVL 1

Accepted Solution

by:
bigbadb earned 600 total points
ID: 2687333
do a cftry catch so...



<CFLOOP query="Q">
<cftry>
<CFQUERY name="INSERT" datasource="D"
username="X" password=Y">
INSERT INTO Table (SSN)            
VALUES(<CFOUTPUT>#ssn#'</cfoutput>)
</cfquery>

<CFCATCH TYPE="Database">
   (do nothing or write to log)
   </cfloop>
</cftry>
</cfloop>

This will allow u to skip that dup rec

hope this helps

0
Simplify Your Workload with One Tool

How do you combat today’s intelligent hacker while managing multiple domains and platforms? By simplifying your workload with one tool. With Lunarpages hosting through Plesk Onyx, you can:

Automate SSL generation and installation with two clicks
Experience total server control

 

Author Comment

by:smoyano
ID: 2687995
Thanks everybody.

deepchanda and dappery: I should have made it clear that my aim was to understand more about CF error handling and loops. It would be more efficient to avoid the error in the first place but I need to figure out the syntax.

bigbadb: when I run your suggested solution I get:

An extraneous end tag </CFtry> has been encountered at document position (33:5) to (33:12). End tags cannot be present in CFML templates without a matching start tag.

Can you advise? Thanks again.
0
 
LVL 1

Expert Comment

by:deepchanda
ID: 2688207
bigbadb missed the closing </cfcatch> tag
heres the code :


<CFLOOP query="Q">
<cftry>
<CFQUERY name="INSERT" datasource="D"
username="X" password=Y">
INSERT INTO Table (SSN)              
VALUES(<CFOUTPUT>#ssn#'</cfoutput>)
</cfquery>

<CFCATCH TYPE="Database">
   (do nothing or write to log)
   </cfloop>
</cfcatch>
</cftry>
</cfloop>

0
 
LVL 1

Expert Comment

by:bigbadb
ID: 2689735
oops.. forgot that tag.  my bad.  hope it works now
0
 

Author Comment

by:smoyano
ID: 2690522
Thanks deepchanda.

Adding </cfcatch> generates and error:

An extraneous end tag </CFcatch> has been encountered at document position (33:2) to (33:11). End tags cannot be
 present in CFML templates without a matching start tag.

<CFCATCH TYPE="Database">
                       (do nothing or write to log)
                    </cfcatch>
                    </cftry>
                    </cfloop>

Does work. The second </cfloop> following <CFCATCH TYPE="Database">seems to cause a problem.

0
 

Author Comment

by:smoyano
ID: 2690529
Thanks everybody.
0
 

Author Comment

by:smoyano
ID: 2690562

I forgot to ask dapperry a question. We are loading records from one database to another.  checkforDupe.recordcount will always be greater than zero/

Is it possible to use cfquery checkforDupe in a subselect in cfquery insert?

Thanks again.

0
 
LVL 3

Expert Comment

by:dapperry
ID: 2690602
Ok let me get this straight before I start babbling on. #ssn# is a delimited list of social security numbers?  And the field SSN, does that hold one social security number, or is it meant to hold the whole list?

:) dapperry
0
 
LVL 3

Expert Comment

by:dapperry
ID: 2690672
Oh, or are you trying to do something like:

<cfquery name="insert" datasource="q">
    INSERT INTO Table (SSN)
    VALUES(SELECT SSN FROM Table2)
</cfquery>

In that case you probably want to use DeepChandra's idea:

<cfquery name="insert" datasource="q">
    INSERT INTO Table (SSN)
    VALUES(SELECT SSN FROM Table2
    WHERE SSN NOT
    IN(SELECT SSN FROM Table))
</cfquery>

:) dapperry
0
 

Author Comment

by:smoyano
ID: 2690818

Thanks dapperyy.

 WHERE SSN NOT
                        IN(SELECT SSN FROM Table))  would be ideal but Table is another database. So we would have to combine two odbc cfqueries in a single sql statement:

<cfquery name="insert" datasource="q">
                        INSERT INTO Table (SSN)
                        VALUES(SELECT SSN FROM Table2
                        WHERE SSN NOT
                        IN(SELECT SSN FROM cfquery checkdups))
                    </cfquery>

The senior does not want to use attached tables.
0
 

Author Comment

by:smoyano
ID: 2691214
bigbadb:

I spoke to soon. What is the correct syntax of your solution? I can't get any of these variations to work.
0
 
LVL 3

Expert Comment

by:dapperry
ID: 2691259
Ok if we are talking two different datasources the easiest way is this

<cfquery name=getSourceSSNs" datasource="a">
     SELECT SSN
     FROM SourceTable
</cfquery>

<cfloop query="getSourceSSNs">
      <cfquery name="checkDupe" datasource="b">
        SELECT SSN
        FROM DestTable
        WHERE SSN='#getSourceSSNs.SSN#'
      </cfquery>
      <cfif checkDupe.recordcount gt 0>
        <cfquery name="insert" datasource="b">
          INSERT INTO Table (SSN)
          VALUES('#getSourceSSNs.SSN#')
        </cfquery>
      </cfif>
</cfloop>

Theres other ways to do it with lists or arrays, but I think this os the most straight forward.

:) dapperry
0
 
LVL 3

Expert Comment

by:dapperry
ID: 2691263
OOps.  This line:

<cfif checkDupe.recordcount gt 0>
       
should be:

<cfif checkDupe.recordcount is 0>

Sorry,

:) dapperry
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
What You Need to Know when Searching for a Webhost Provider
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Screencast - Getting to Know the Pipeline

610 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