Skip to main content
A
Anonymous
July 18, 2010
Answered

How do I tell if a mysql update was successful?

  • July 18, 2010
  • 1 reply
  • 12834 views

How do I tell if a mysql update was successful?

I need to know if an update was run or if the record was not found....  is there some way that coldfusion can use that traps success/fail resoponses from mysql [linda like myquery.RecordCount ]?

basically I am trying to update a row, if no row was updated - the record must not exist so I then need to do an insert...

-any ideas?

-sean

    This topic has been closed for replies.
    Correct answer JR__Bob__Dobbs

    here is the test query:

    <cfquery name="qry" datasource="#application.dsn#">
         update DISC_CUST set DISC_PriceChange = '222222', DISC_TaxablePriceChange = '2222222'
         where DISC_ProdID = '1129'
    </cfquery>

    <cfdump var="#qry#" />

    the error is "Variable  QRY is undefined."

    if you remove the dump the debug results for the query show:


              Debugging Information
    ColdFusion Server Enterprise8,0,1,195765
    Template/Assets/Import/index.cfm
    Time Stamp19-Jul-10 02:19 PM
    LocaleEnglish (US)
    User AgentMozilla/5.0 (Windows; U; Windows NT 5.1; en-US;  rv:1.9.2.4) Gecko/20100611 Firefox/3.6.4 ( .NET CLR 3.5.30729)
    Remote IP192.168.1.100
    Host Name192.168.1.100

    Execution Time
    Total TimeAvg TimeCountTemplate
    5 ms5 ms1top level /data/vhome/xxxl/httpdocs/Assets/Import/index.cfm
    3324 ms STARTUP,  PARSING, COMPILING, LOADING, & SHUTDOWN
    3329 ms TOTAL EXECUTION  TIME
    red =  over 250 ms average execution time
    SQL  Queries qry (Datasource=dsn, Time=1ms,  Records=0) in  /data/vhome/xxx/httpdocs/Assets/Import/index.cfm @  14:19:19.019
         update DISC_CUST set DISC_PriceChange = '222222', DISC_TaxablePriceChange = '2222222'
         where DISC_ProdID = '1129'

    Try using the result object created by CFQUERY.
    See documentation: http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7fae.html


    <cfquery name="qry" datasource="#application.dsn#" result="updateResult">
         update DISC_CUST set DISC_PriceChange = '222222', DISC_TaxablePriceChange = '2222222'
         where DISC_ProdID = '1129'
    </cfquery>

    <cfdump var="#updateResult#" />

    1 reply

    D
    Dan_Bracuk
    Inspiring
    July 18, 2010

    check for the record first, then decide what to do.

    A
    Anonymous
    July 18, 2010

    then I would be running about 25,000 extra queries that I do not need to run ....  that's not going to be an option.

    J
    JR__Bob__Dobbs
    Inspiring
    July 19, 2010

    ahhhh isn't that interesting;

    <cfquery name="qry" datasource="#application.dsn#" result="myResult">
         update DISC_CUST set DISC_PriceChange = '222243622', DISC_TaxablePriceChange = '22222422'
         where DISC_ProdID = '11299';
         update DISC_CUST set DISC_PriceChange = '22245222', DISC_TaxablePriceChange = '22422222'
         where DISC_ProdID = '11299';
          SELECT ROW_COUNT() AS RecordsChanged
    </cfquery>

    #qry.recordcount#
    <cfdump var="#qry.RecordsChanged#" />
    <cfdump var="#myresult#" />

    allowing batching [something new to me!] enables the ROW_COUNT() which shows the correct number of updated rows,  however the

    #qry.recordcount#

    just returns the 1 record which this query actually returnds - the row count...  or "1"

    interesting.

    -sean


    If you include multiple statements in a CFQUERY batch ROW_COUNT() will only get the rows affected by the last statement (I think).

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