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.

    A
    Anonymous
    July 19, 2010

    You might try the INSERT ON DUPLICATE KEY UPDATE syntax

    http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

    Disclaimer: I am not a MySQL expert.

    Bob;

    thanks, while not the answer that I was looking for, that method will work [I can alter one of the columns to be a unique index] and it's actually going to be more efficient than what I was planning in the first place ....

    Now, this isn't the first time I've needed to catch the MySQL response - any ideas on how to do that [or is it a limitation of the driver?]

    -sean

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