Skip navigation

Database Access

sean69

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

2967 Views 19 Replies Latest reply: JR \"Bob\" Dobbs, Jul 19, 2010 12:16 PM

Replies

Currently Being Moderated

1. Dan Bracuk,

Jul 18, 2010 2:13 PM in reply to sean69

Report

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

|
Mark as:
Currently Being Moderated

2. sean69,

Jul 18, 2010 2:22 PM in reply to Dan Bracuk

Report

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.

|
Mark as:
Currently Being Moderated

3. Dan Bracuk,

Jul 18, 2010 4:10 PM in reply to sean69

Report

25000 queries?

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

|
Mark as:
Currently Being Moderated

4. sean69,

Jul 18, 2010 4:31 PM in reply to Dan Bracuk

Report

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

|
Mark as:
Currently Being Moderated

5. Dan Bracuk,

Jul 18, 2010 7:08 PM in reply to sean69

Report

I'll give you a hint

select count(*)

|
Mark as:
$JR \$

Currently Being Moderated

6. JR \"Bob\" Dobbs,

Jul 19, 2010 6:09 AM in reply to sean69

Report

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

7. sean69,

Jul 19, 2010 8:10 AM in reply to JR \"Bob\" Dobbs

Report

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

|
Mark as:
$JR \$

Currently Being Moderated

8. JR \"Bob\" Dobbs,

Jul 19, 2010 8:19 AM in reply to sean69

Report

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

9. sean69,

Jul 19, 2010 8:35 AM in reply to JR \"Bob\" Dobbs

Report

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

|
Mark as:
$JR \$

Currently Being Moderated

10. JR \"Bob\" Dobbs,

Jul 19, 2010 9:37 AM in reply to sean69

Report

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

11. sean69,

Jul 19, 2010 10:32 AM in reply to JR \"Bob\" Dobbs

Report

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

|
Mark as:
$JR \$

Currently Being Moderated

12. JR \"Bob\" Dobbs,

Jul 19, 2010 10:55 AM in reply to sean69

Report

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

13. sean69,

Jul 19, 2010 11:20 AM in reply to JR \"Bob\" Dobbs

Report

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'

Mark as:

$JR \$

Currently Being Moderated

14. JR \"Bob\" Dobbs,

Jul 19, 2010 11:24 AM in reply to sean69

Report

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

15. sean69,

Jul 19, 2010 11:26 AM in reply to JR \"Bob\" Dobbs

Report

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

|
Mark as:

Currently Being Moderated

16. sean69,

Jul 19, 2010 11:31 AM in reply to JR \"Bob\" Dobbs

Report

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

Mark as:

$JR \$

Currently Being Moderated

17. JR \"Bob\" Dobbs,

Jul 19, 2010 11:38 AM in reply to sean69

Report

Things to consider.

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


<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

18. sean69,

Jul 19, 2010 11:49 AM in reply to JR \"Bob\" Dobbs

Report

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

Mark as:

$JR \$

Currently Being Moderated

19. JR \"Bob\" Dobbs,

Jul 19, 2010 12:16 PM in reply to sean69

Report

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:

Forum guidelines

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

Database Access

How do I tell if a mysql update was successful?

Jul 18, 2010 1:49 PM

More Like This

Bookmarked By (0)

Answers + Points = Status