Advanced SQL Update

Advanced SQL Update

  
Hi,

I am relatively new to OS and am implementing an optimistic locking strategy using an Entity Encapsulation pattern and using a last updated timestamp on the entity. 

For the pattern my SQL Query has an additional where clause to detect if someone else has updated the row in the database between the time I retreived it and the time I updated it.

UPDATE {Address}
Set 
{Address}.[Line1] = @AddressLine1,
{Address}.[Line2] = @AddressLine2,
{Address}.[City] = @AddressCity,
{Address}.[CreateDateTime] = getdate()
WHERE
{Address}.[Id] = @AddressRecordId
AND
{Address}.[CreateDateTime] = @AddressTimeStamp

My logic is working fine. Adds and Updates work, or don't work as expected. But I am having trouble detecting when the Update works and Updates the record, and when it doesn't Update the record, so that I can provide appropriate feedback to the User.

The output from my Query is just Boolean and is always the same i.e. False

As far as I can see, using the debugger, the result of executing the SQL statement is exactly the same whether it updates a record or when the update fails because the where condition is not satisfied. It does not appear that it throws an exception either, as suggested by some articles on the site.

Does anyone know how to detect this condition?

I can do it programmatically I know but would rather just detect it.

Thanks

Keith
Hi Keith

Have you considered using the GetForUpdate and Update Entity actions instead?

This will allow you to lock a record for update, and then update that record safely, without anyone updating it first.

For instance, if you have an Edit Record that is populated by a query or an Get Entity action, in the update/save button action you should do a GetForUpdate of that same record, validate that the record hasn't changed, and then do an update with the new values safely. If you detect that the record has change, just update it as is to release the lock.

This will allow you to implement a safe locking strategy for individual entity records.

Does this helps you in achieving your goal?

Cheers
Hi Miguel,

Yes I am aware of that pattern, and have already coded it that way as an alternate approach. But as discussed in several threads on the forum it is a waste of a database call, which in a high transaction volume system could be significant. 

The pattern I am looking for is the prevalent pattern in DB programming (in thirty years of DB programming I have never used a GetForUpdate) and I am just looking for a way to use feedback from the database to detect whether the Update actually updates or fails to find a row to update so I can provide appropriate response to the User.

Other threads suggest using a boolean value? or catching a database exception?

But the update call does not seem to throw an exception and I am not sure how I would use a boolean value within a simple datanase update call.

Thanks for the thought but I am really just looking for an answer to the question I posed.

Regards

Keith
Change the output structure of your advanced query to return an integer instead of a boolean and add "SELECT @@ROWCOUNT" as the last SQL statement in your query.  If the update succeeds, @@ROWCOUNT will be greater than 0 and if it fails it will be 0.  That will help you to determine if the update succeeded or failed.

Regards,

Izak
Izak Joubert wrote:
Change the output structure of your advanced query to return an integer instead of a boolean and add "SELECT @@ROWCOUNT" as the last SQL statement in your query.  If the update succeeds, @@ROWCOUNT will be greater than 0 and if it fails it will be 0.  That will help you to determine if the update succeeded or failed.

Regards,

Izak
 Izak,

Thanks that worked fine.

Thanks very much.

Keith
 
Hi Keith and Izak,

I am trying to use the SELECT @@ROWCOUNT and I am having compilation issues:

Internal Error
 
Invalid parameter 'ROWCOUNT' in (l65Xalwuk0yzy__T0DAbtA)Administracion.(0dYHy2tcuUCxtYXDq1yJvw)ChangeStatus.(eeuphGC7s0qYh8P6TyJ_Ig)ChangeEntityStatus advanced query.

Thanks a lot for any help
The compiler sees anything starting with the @ sign as an input parameter.  The way to get around that is:
 
1 - add a text parameter e.g. "ReturnValue" as an input parameter to the query
2 - use "RETURN @ReturnValue" as the last statement in the query
3 - pass "@@ROWCOUNT" as the value of the parameter.

This way the compiler stays happy and the SQL engine gets the right instructions.
Johannes Correa wrote:
Hi Keith and Izak,

I am trying to use the SELECT @@ROWCOUNT and I am having compilation issues:

Internal Error
 
Invalid parameter 'ROWCOUNT' in (l65Xalwuk0yzy__T0DAbtA)Administracion.(0dYHy2tcuUCxtYXDq1yJvw)ChangeStatus.(eeuphGC7s0qYh8P6TyJ_Ig)ChangeEntityStatus advanced query.

Thanks a lot for any help
 Johannes,

Thi is how I got it to work, slightly different than Izak so I guess they both work.

Hope it works for you.

Keith

 
 
Keith Matthews wrote:
Johannes Correa wrote:
Hi Keith and Izak,

I am trying to use the SELECT @@ROWCOUNT and I am having compilation issues:

Internal Error
 
Invalid parameter 'ROWCOUNT' in (l65Xalwuk0yzy__T0DAbtA)Administracion.(0dYHy2tcuUCxtYXDq1yJvw)ChangeStatus.(eeuphGC7s0qYh8P6TyJ_Ig)ChangeEntityStatus advanced query.

Thanks a lot for any help
 Johannes,

Thi is how I got it to work, slightly different than Izak so I guess they both work.

Hope it works for you.

Keith

 
 
 ... you may need this part too, its really just an integer, a boolean works also, but this gives you more information in case you need it especially if it is a mass delete or something.
 
Thanks a lot Keith and Izak. It works perfectly fine. 
Altough the question has been answered to the poster's liking I'll just add for future reference that:
  • SQL Server 2008+ has an OUTPUT clause that can be added to obtain a list of attributes from records affected by an UPDATE statement.
  • Oracle has a RETURNING clause for the same effect.
João Pedro Abreu wrote:
Altough the question has been answered to the poster's liking I'll just add for future reference that:
  • SQL Server 2008+ has an OUTPUT clause that can be added to obtain a list of attributes from records affected by an UPDATE statement.
  • Oracle has a RETURNING clause for the same effect.
Could you perhaps share an example?
Maybe this is something OS should consider adding to the return of any database call.
 
This is a sample of UPDATE with a OUTPUT clause, this advanced query returns all the changed records , you could use this to archive the records.



DELETED returns the records before the change, INSERTED will return the records with the changed values.

Does anyone have an example using the Oracle Returning clause to return the number of rows affected. I just can't seem to get the syntax working. 
Keith Matthews wrote:
João Pedro Abreu wrote:
Altough the question has been answered to the poster's liking I'll just add for future reference that:
  • SQL Server 2008+ has an OUTPUT clause that can be added to obtain a list of attributes from records affected by an UPDATE statement.
  • Oracle has a RETURNING clause for the same effect.
Could you perhaps share an example?
Maybe this is something OS should consider adding to the return of any database call.
 
 João 

Do you have an example of using Oracle RETURNING you could share?

Thanks
 
Hi,
I am looking for a similar thing, can you please share an example of this for Oracle.

Regards,
Kaushal