How do I tell if a mysql update was successful?

[sean69]

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

[Dan_Bracuk]

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

[sean69]

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.

[Dan_Bracuk]

25000 queries?

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

[sean69]

it's a product database of about 25,000 items, each has to be updated regularly & if not exist - then insert

ed...

[Dan_Bracuk]

I'll give you a hint

select count(*)

[JR__Bob__Dobbs]

You might try the INSERT ON DUPLICATE KEY UPDATE syntax

http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.htmlDisclaimer: I am not a MySQL expert.

[sean69]

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

[JR__Bob__Dobbs]

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.

[sean69]

Hadn't considered a atored proceedure....

About the response - MySQL responds to every operation [as any DBS would I assume] whether it's success, error, a record set etc-etc-etc.....

So far I can see CFMX is smart enough to figure this much out:

update (Datasource=dsn, Time=13ms, Records=1) in 
/var/www/vhosts/xxx.com/httpdocs/Assets/Import/index.cfm @ 1:29:23.023
     update DISC_CUST set DISC_PriceChange = '0', DISC_TaxablePriceChange = '0'
     where DISC_ProdID = '13062'
               

It knows that MySQL performed an update on 1 row and how long it took ....  that's what I am looking for, the info that MySQL returns saying '1 row was updated'

-sean

[JR__Bob__Dobbs]

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/WSc3ff6d0ea77859461172e0811cbec22c24-7fae.html

[sean69]

no - it's not ... what you do get [if you turn on error/debuggin] is this

'qry (Datasource=dsnTime=1ms,  Records=0)'

So at some point CFMX is 'aware' about the status of the database operation - but where and how do I get that info?

-I did look through the docs looking for info returned with cfquery - but nothing that applies ...  if you try to dump the results from an update query you get an error....

-sean

[JR__Bob__Dobbs]

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#function_row-count

[sean69]

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 Enterprise	8,0,1,195765
Template	/Assets/Import/index.cfm
Time Stamp	19-Jul-10 02:19 PM
Locale	English (US)
User Agent	Mozilla/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 IP	192.168.1.100
Host Name	192.168.1.100

---

Execution Time

Total Time	Avg Time	Count	Template
5 ms	5 ms	1	/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'

---

[JR__Bob__Dobbs · Accepted solution]

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#" />

[sean69]

not sure how ROW_COUNT() is going to help unless contained in a subquery [not supported

on unpdate statements???]

however, browsing the update syntax: "UPDATE returns the number of rows       that were actually changed."  - so My=SQL IS returning the info I want - but coldfusion is ignoring it? [http://dev.mysql.com/doc/refman/5.1/en/update.html]

hmmmmmmmm.....

-sean

[JR__Bob__Dobbs]

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-configuration-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.

[sean69]

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

[JR__Bob__Dobbs]

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

[sean69]

this gives me everything I need....  perfect, thanks.

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

<cfdump var="#myresult#" />

-sean