| Author |
Message
|
| KrotaZ |
 Posted: Wed Jun 22, 2011 3:46 pm Post subject: Validating updated row |
 |
|
Novice
Joined: 30 Mar 2011
Posts: 11
|
Hi.
I've a the need to check if an update statement (using PASSTHRU) has updated any row when executed. I don't need to know how many rows were updated, I just need to know if any row was updated. SQLCODE, SQLSTATE doesn't help.
I'm using Broker 7 with db2 9.7, sql 2k8 and oracle 10g
thanks in advance |
|
| Back to top |
|
 |
| kimbert |
| Posted: Thu Jun 23, 2011 3:43 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| I'm not an expert in database stuff, but I wonder whether you need to wrap your UPDATE query in a stored procedure, and return an error code to message broker. Presumably the stored procedure would be able to find out whether any rows were updated easily enough. |
|
| Back to top |
|
|
| KrotaZ |
| Posted: Thu Jun 23, 2011 6:26 am Post subject: |
|
|
Novice
Joined: 30 Mar 2011
Posts: 11
|
@kimbert: Thanks for your reply. As you say an SP es a good way of having the work done, but I just want t know if there's a "broker native way" of solving my problem. Sadly it seems there's no "native way" of doing it so I guees I'll have to use an SP.
Thanks again. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Jun 23, 2011 6:28 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Can you turn around and reselect on the data you updated? |
|
| Back to top |
|
|
| KrotaZ |
| Posted: Thu Jun 23, 2011 6:33 am Post subject: |
|
|
Novice
Joined: 30 Mar 2011
Posts: 11
|
jeff: it's kind of hard to do that, I've nearly 6,000,000 of registers to process (daily registers) as you guess that will be a serious overhead to the database.
Thanks |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Jun 23, 2011 6:42 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
I thought that usually database Update statements did end up returning the number of rows affected.
Are you not capturing the results of your passthru?
| Code: |
| rows = PASSTHRU(....); |
|
|
| Back to top |
|
|
| KrotaZ |
| Posted: Thu Jun 23, 2011 7:04 am Post subject: |
|
|
Novice
Joined: 30 Mar 2011
Posts: 11
|
I've tried jeff's idea but with pain in my heart I've to say that doesn't work either. With this code:
| Code: |
| SET Environment.Variables.respass = PASSTHRU ('UPDATE Fernando.DDD AS T SET DDDD = 666 WHERE T.DDDD = 123' TO Database.AJUA); |
the database is updated but at the end of the road respass doesn't even exist.
any other idea?? By the way. Thanks jeff for your idea. |
|
| Back to top |
|
|
|
|
