Skip to main content
S
sockerdad
Inspiring
October 19, 2011
Answered

Fail to Abend when Row Not Found for Update?

  • October 19, 2011
  • 1 reply
  • 824 views

I'm using Coldfusion 9 against a IBM mainframe DB2 database.  I have a condition that when a row is not found for update in a cfquery, execute an insert cfquery.  Sounds logical and easy, right?  Not so!  Error trapping is failing to return an error condition when the row was not found for update! 

Here's the code:

<cftry>

     <cfinclude template="qry_update_table.cfm">

     <cfcatch type="any">

          <cfinclude template="dsp_errorInformation.cfm">

          <cfabort>

     </cfcatch>

</cftry>

<cfdump var=#tmec04_result#><cfabort>

The Query code is:

<cflock timeout="15" name="UpdateTUID04Lock" type="exclusive">

     <cfquery datasource="#APPLICATION.DB2_DSN#" name="update_tuid04" result="tmec04_result">

          UPDATE #APPLICATION.SQLID#.TUID04

               SET   MON_BEG_TIME  = <cfqueryparam value = #NEW_MON_BEG# cfsqltype="cf_sql_time">

                      , MON_END_TIME  = <cfqueryparam value = #NEW_MON_END# cfsqltype="cf_sql_time">

          WHERE  USER_ID    = <cfqueryparam value = #NEW_USER_ID# cfsqltype="cf_sql_varchar">

     </cfquery>

</cflock>

There is no row for the NEW_USER_ID, so I would expect an error condition.  No such luck.  The dump of temc04_result just shows me the SQL, the record count (0), the execution time (0), and the cached status (false).  No SQL code.  And a dump of CFCATCH shows that all is well.  I expected to get cfcatch.nativeerrorcode = 100 (Row not found).  Nope.  Got a zero.

Any suggestions.

This topic has been closed for replies.
Correct answer Dan_Bracuk

Regarding this:

There is no row for the NEW_USER_ID, so I would expect an error condition.

Your expectation is incorrect.  What actually happens is that the query executes and no rows get updated.

What has to happen is that you have to see whether or not the record is there, and if not, insert it.  There are lot's of ways to do that.  I think the simplest is an insert query,

insert into yourtable

(field1, field2, etc)

select distinct value1, value2, etc

from some small table

where not exists (something)

Followed by the update query.

1 reply

D
Dan_BracukCorrect answer
Inspiring
October 20, 2011

Regarding this:

There is no row for the NEW_USER_ID, so I would expect an error condition.

Your expectation is incorrect.  What actually happens is that the query executes and no rows get updated.

What has to happen is that you have to see whether or not the record is there, and if not, insert it.  There are lot's of ways to do that.  I think the simplest is an insert query,

insert into yourtable

(field1, field2, etc)

select distinct value1, value2, etc

from some small table

where not exists (something)

Followed by the update query.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices