Skip navigation
Currently Being Moderated

How do I tell if a mysql update was successful?

Jul 18, 2010 1:49 PM

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

 
Replies
  • Currently Being Moderated
    Jul 18, 2010 2:13 PM   in reply to sean69

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

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 18, 2010 4:10 PM   in reply to sean69

    25000 queries?

     

    I envisage at the most, 1 query.  Why do you think it would take so many?

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 18, 2010 7:08 PM   in reply to sean69

    I'll give you a hint

     

    select count(*)

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 19, 2010 6:09 AM   in reply to sean69

    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.
     
    |
    Mark as:
  • Currently Being Moderated
    Jul 19, 2010 8:19 AM   in reply to sean69

    What do you consider the "MySQL response"? 

     

    You might consider wrapping your database logic inside a stored procedure.  I'm not very familiar with MySQL so I'm not sure if MySQL stored procudures support output parameters or return values.  You might ask this question on a MySQL specific forum.

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 19, 2010 9:37 AM   in reply to sean69

    I suspect that the string "'1 row was updated" is not exposed to the JDBC driver.  You should be able to get some of the meta data you want from the CFQUERY result object.  You might try using a CFDUMP of the result object to look at the info it contains.

     

     

    CFQUERY documentation:

    http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461 172e0811cbec22c24-7fae.html

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 19, 2010 10:55 AM   in reply to sean69

    Things to try:

     

    1. Can you post your update query code, CFDUMP code, and error message?

     

    2. You may wish to investigate the ROW_COUNT() function.

    http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#func tion_row-count

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 19, 2010 11:24 AM   in reply to sean69

    Try using the result object created by CFQUERY.
    See documentation: http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461 172e0811cbec22c24-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#" />

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 19, 2010 11:38 AM   in reply to sean69

    Things to consider.

     

    1. See if ROW_COUNT can be using with an update in CFQUERY.

     

    <!--- this code NOT tested --->

    <cfquery name="qry" datasource="mydsn">

         UPDATE my_table

         SET my_value = 1

         WHERE my_key = 100;

     

         SELECT ROW_COUNT() AS RecordsChanged

     

    </cfquery>

     

     

    <cfoutput>#qry.RecordsChanged#</cfoutput>

     

    You may need to enable the "allowMultiQueries" setting on your connection string to enable batched SQL queries in MySQL.

    http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configura tion-properties.html

     

     

    2. See if CFQUERY's result.recordCount is populated for updates.  I don't think it is, but it worth looking at.

     

    3. Wrap your UPDATE/INSERT logic in a stored procedure.

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 19, 2010 12:16 PM   in reply to sean69

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

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points