Solved

How to continue loop on error?

Posted on 2000-04-05
15
375 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
  • 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
 

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
Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

 

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 camera licenses with purchase of My Cloud NAS

Milestone Arcus software is compatible with thousands of industry-leading cameras for added flexibility. Upon installation on your My Cloud NAS, you will receive two (2) camera licenses already enabled in the software. And for a limited time, get additional camera licenses FREE.

Question has a verified solution.

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

Suggested Solutions

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

895 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now