Solved

How to continue loop on error?

Posted on 2000-04-05
15
372 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

Scale it in WD Gold

With up to ten times the workload capacity of desktop drives, WD Gold hard drives employ advanced technology to deliver among the best in reliability, capacity, power efficiency and performance.

Join & Write a Comment

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…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

746 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