StrataFrame Forum » StrataFrame Database Deployment Toolkit » Issues » Update Using Stored Procedure Issue

Update Using Stored Procedure Issue

Larry Caylor — Mon, 11 Feb 2008 14:30:08 GMT

I've run into an issue with update stored procedures generated by DDT are not matching up with the update parameter list created by the framework. I have a table with a non-identity integer pk and a SQL timestamp column that I've configured in DDT to generate insert and update procs.

After mapping the table to an object in BO mapper I've dropped it on a form and set the appropriate properties to use stored procedures for update and insert, set auto ncrement pk = false and configured the BO to use optimstic timestamp concurrency.

When I run the app inserts work. When I try to update I get an error stating the update procedure has too many arguments specified. Looking at the parameters that were generated in debug mode, it shows a "..._ORGPK" parameter containing the original pk value. It would appear that the framework is failing to recognize that timestamp concurrency is being used and is not supressing the original pk parameter or DDT is failing to include it in the proc.

-Larry

RE: Update Using Stored Procedure Issue

Trent L. Taylor — Mon, 11 Feb 2008 14:30:08 GMT

Great to hear! :)

RE: Update Using Stored Procedure Issue

Larry Caylor — Mon, 11 Feb 2008 13:33:35 GMT

Just installed the latest beta... problem fixed:D Thanks!

RE: Update Using Stored Procedure Issue

Trent L. Taylor — Sat, 09 Feb 2008 11:47:24 GMT

This has now been corrected and will be included in the update I post later in the day. Thanks for the heads up :) A new setting called Primary Key Is Updatable has been added, which by default should just be left alone as the BOs new property PrimaryKeyIsUpdatable defaults to False as well.

RE: Update Using Stored Procedure Issue

Larry Caylor — Fri, 08 Feb 2008 17:21:14 GMT

This is still messed up in 1.65 beta. There is a mismatch between DDT and the Framework where an object has a non-autoincrementing integer PK and the DDT is asked to create Insert & Update sprocs. Attached are screenshots of the sp created by DDT, the exception when the program is run, and the sql debug.

-Larry

RE: Update Using Stored Procedure Issue

StrataFrame Team — Wed, 08 Aug 2007 09:02:44 GMT

OK, I'll change the DDT so that it maches what's in the SqlDataSourceItem within the next release. Thanks, Larry.

RE: Update Using Stored Procedure Issue

Larry Caylor — Tue, 07 Aug 2007 16:17:51 GMT

Looks like my initial test of the fix was flawed:blush: It turns out that the updated DDT is appending "_ORG" to the primary key name when it creates the update stored procedure for a non-identiy PK. The framework is appending "_ORGPK" when it creates the SQL command, so of course it fails. For now I've just modified SqlDataSoruceItem to append "_ORG" since I have the source and that is easier than modifying the stored procedures.

-Larry

RE: Update Using Stored Procedure Issue

StrataFrame Team — Thu, 19 Jul 2007 09:09:50 GMT

Hrm... no idea what that could have been, but yeah, the last few days have been DDT days... several fixes made their way in ;)

RE: Update Using Stored Procedure Issue

Larry Caylor — Wed, 18 Jul 2007 12:55:32 GMT

Thanks Ben. That fixed the problem. Looks like there were some other changes too. I was having an issue with role base security that went awaywhen I redeployed the database using the new DDT.

-Larry

RE: Update Using Stored Procedure Issue

StrataFrame Team — Wed, 18 Jul 2007 09:01:37 GMT

OK, here's the fix that now properly includes the _ORG pk value in the update sproc.

RE: Update Using Stored Procedure Issue

StrataFrame Team — Wed, 18 Jul 2007 08:52:10 GMT

OK, looks like this is a DDT problem... it should be adding PK_ORG to the parameter list, just in case anyone wants to change their primary key on a record (which they can since it's not an IDENTITY column... if you want to change it you might have a design problem, but by allowing this, we cover all of the bases). I'll fix the DDT and post the update here (there seems to be a lot of DDT fixes in the last few days ;)).

RE: Update Using Stored Procedure Issue

StrataFrame Team — Wed, 18 Jul 2007 08:43:29 GMT

Yep, it sure did get overlooked... I'm checking it right now, Larry.

RE: Update Using Stored Procedure Issue

Larry Caylor — Tue, 17 Jul 2007 19:21:56 GMT

Did this one get missed?

RE: Update Using Stored Procedure Issue

Larry Caylor — Wed, 11 Jul 2007 17:25:24 GMT

I did a little more checking and found that DDT is not adding the original pk value parameter (_ORGPK) to any of the update procs, regardless of the data type.

-Larry