StrataFrame Forum » StrataFrame Database Deployment Toolkit » Database Deployment Toolkit (How do I?) » Stored Procedures - General

Stored Procedures - General

Gary C Wynne — Mon, 27 Mar 2006 09:36:00 GMT

Hi,

I have played around with the Database Toolkit and noticed the auto-generated stored procedures for Update and Insert actions. The Insert procedures return @@IDENTITY for the PK but we use SCOPE_IDENTITY() for total accuracy. We have found that returning @@IDENTITY can cause confusion ie., return the wrong value, particularly where inserts are made in triggers that are triggered by the initial insert. Any thoughts?

Is row versioning handled in any way for updates?

How is concurrency handled - after checking the generated stored procedures, there appears to be no specific provision made for row version conflicts or generally concurrency checking.

In Strataframe, is the mapping of business object properties to stored procedure parameters handled automatically?

Thanks,

-=Gary

RE: Stored Procedures - General

Trent L. Taylor — Mon, 27 Mar 2006 09:36:00 GMT

The framework now uses SCOPE_IDENTITY and has for some time now. I thought I would just update this thread so anyone reading would know.

RE: Stored Procedures - General

StrataFrame Team — Thu, 01 Dec 2005 08:53:25 GMT

Yes, the mapping of the DataColumns to the stored procedure parameters is handled automatically.

For concurrency, the auto-generated stored procedures do not handle concurrency in any way, shape, or form. In the Application Framework, if you set the concurrency to optimistic on a business object, it will not use stored procedures for updates, but rather, will dynamically build an update command that uses either "all fields" or a "timestamp column" for the concurrency. You could, of course also use transactions to implement pessimistic concurrency, but very few ever want to do that ;)

On the @@IDENTITY vs SCOPE_IDENTITY(), we have considered using SCOPE_IDENTITY(), but @@IDENTITY will return the proper value unless you are inserting on multiple tables on the same connection. We have not, however, considered inserting new records on a trigger; we will consider this and most likely change to SCOPE_IDENTITY(). Thanks for the heads up.