Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 404
  • Last Modified:

How to continue loop on error?


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
smoyano
Asked:
smoyano
  • 6
  • 5
  • 2
  • +1
1 Solution
 
deepchandaCommented:
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
 
dapperryCommented:
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
 
bigbadbCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
smoyanoAuthor Commented:
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
 
deepchandaCommented:
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
 
bigbadbCommented:
oops.. forgot that tag.  my bad.  hope it works now
0
 
smoyanoAuthor Commented:
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
 
smoyanoAuthor Commented:
Thanks everybody.
0
 
smoyanoAuthor Commented:

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
 
dapperryCommented:
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
 
dapperryCommented:
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
 
smoyanoAuthor Commented:

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
 
smoyanoAuthor Commented:
bigbadb:

I spoke to soon. What is the correct syntax of your solution? I can't get any of these variations to work.
0
 
dapperryCommented:
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
 
dapperryCommented:
OOps.  This line:

<cfif checkDupe.recordcount gt 0>
       
should be:

<cfif checkDupe.recordcount is 0>

Sorry,

:) dapperry
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 6
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now