Solved

How to continue loop on error?

Posted on 2000-04-05
15
388 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 200 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

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

Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

729 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