InstantForum 2017-1 FinalStrataFrame Forumhttp://forum.strataframe.net/StrataFrame ForumWed, 09 Sep 2026 00:19:56 GMT20http://forum.strataframe.net/FindPost20213.aspxHi,</P><P>I don't use the DDT and let SF's standard CRUD processing do the heavy lifting. So, when I change a column in a row and save the change every column in the BO is included in the update irrespective of&nbsp;if they have been changed or not. What I would prefer&nbsp;is&nbsp;for the Update command&nbsp;to&nbsp;only include in the Update the column(s) that have changed. I know I can achieve what I want&nbsp;by adding entries in the ExcludeFromUpdate list or using my own stored proc to do the update but I was hoping there may be a 'magic property' somewhere that can turn this facility on.</P><P>Cheers, PeterThu, 23 Oct 2008 13:21:23 GMTPeter Joneshttp://forum.strataframe.net/FindPost20298.aspxThank you guys, much appreciated all the explanations.Thu, 23 Oct 2008 13:21:23 GMTEdhy Rijohttp://forum.strataframe.net/FindPost20286.aspxAlso, Edhy, using CRUD is faster than using standard UPDATE commands.&nbsp; The parger the table, number of columns, and database size, the more significant the performance becomes.&nbsp; To put it in perspective, we use INSERT and UPDATE sprocs for every single BO in every application that we write (except for the few times that we want to sidestep the standard update logic).&nbsp; There is really no downside to creating UPDATE and INSERT sprocs...unless you consider performance gains a downside :DThu, 23 Oct 2008 10:12:22 GMTTrent L. Taylorhttp://forum.strataframe.net/FindPost20277.aspxHi Greg,</P><P>Thanks for that - obviously I didn't read the Help file closely enough - back to the books!</P><P>Edhey - the reason I was interested in this is that I have a&nbsp;BO that is used in lots of places and, in one form (BO instances), I wanted to turn off concurrency control and only allow the user to update one column (while showing many in the UI). Rather than put lots of column names in the 'exclude from update' list I wanted to use a proc for no other reason is seemed like the easiest thing to do.</P><P>In terms of procs rather than using SF's standard CRUD I was very interested in this in the early days of our move to SF because our system supports processing in a factory were response time is critical and we can get transaction rates&nbsp;averaging&nbsp;around&nbsp;on insert&nbsp;a second (with quite a bit of ancillary processing via triggers) while still supporting reporting etc. The existing system, which we are redeveloping, does this well using stored procedures. The CRUD option appeared attractive but I didn't think it would have the performance we were looking for but, after quite a bit of testing, I couldn't see any material difference between the two approaches. I believe the reason for this is that SF&nbsp;parameterises its CRUD SQL code. This makes the database performance quicker (in our case the execution plans remain in cache because they are run so frequently) and it also ensures&nbsp;that text input is treated as text literals and therefore eliminates the problems of SQL injection attacks when an ad-hoc sql statement is generated as string&nbsp;of text and executed.</P><P>This is the only time we have ever wanted to move away from SF standard CRUD processing. In terms of Greg's comments re complex processing we have quite a bit of that but we always try and handle that in the database via triggers.</P><P>Cheers, Peter&nbsp;Wed, 22 Oct 2008 20:41:10 GMTPeter Joneshttp://forum.strataframe.net/FindPost20276.aspxThanks Greg,</P><P>So unless there is special need, having the CRUD&nbsp;without SP would be acceptable for any application?</P><P>Just want to be on the safe side, since I do have the DDT, but the tables where designed using xCase with not SP for CRUDs.Wed, 22 Oct 2008 17:36:41 GMTEdhy Rijohttp://forum.strataframe.net/FindPost20275.aspxI don't know why Peter is wanting to use sprocs, but here are a couple of common reasons:<br> <br> 1. Security. Often DBAs will disallow any access to tables. All access must be via views and sprocs. <br> <br> 2. Complexity. If you need to do additional processing during a CRUD action, then a sproc is a fast server side option. E.g. a complex trigger needs to be executed when a record is added, complex enough that a normal trigger won't do it. <br> <br> I'll be interested in other reasons... :DWed, 22 Oct 2008 17:15:51 GMTGreg McGuffeyhttp://forum.strataframe.net/FindPost20274.aspxHi Greg, Peter</P><P>On the same topic, what&nbsp;would be&nbsp;the advantage of using SP for the CRUD?Wed, 22 Oct 2008 17:05:27 GMTEdhy Rijohttp://forum.strataframe.net/FindPost20273.aspxPeter, I can answer some of your questions:<br> <br> [quote]2)Does using custom update stored procedures require the use of the DDT?[/quote]<br> <br> No, it is completely independent of the DDT. The DDT can automagically create the CRUD sprocs for you, but you can create them yourself. I believe many users are doing it this way.<br> <br> [quote]3) If the DDT isn't needed how do I pass my paramenter values to the update stored proc at run time?[/quote]<br> <br> You don't. The framework does it for you automatically. The help topic indicates how to structure the signatures for each of the types of sprocs, based on concurrency type. If you setup the sproc correclty and setup the BO to use the sprocs (insert, update and delete), then when you call Save or Delete the indicated sproc is automatically called, passing the current values of the current record in the BO.<br> <br> Note you can use sprocs for none or as many of the CRUD functions as needed. If a sproc isn't used, then the standard SF generated SQL is used instead.<br> <br> Hope that helps!Wed, 22 Oct 2008 16:41:02 GMTGreg McGuffeyhttp://forum.strataframe.net/FindPost20271.aspxHi Trent,</P><P>Yes, I have seen the Help samples. Maybe my question wasn't clear enough:</P><P>1) I don't use the DDT.</P><P>2)&nbsp;Does using custom update stored procedures require the use of the DDT?</P><P>3) If the DDT isn't needed how do I pass my paramenter values to the update stored proc at run time?</P><P>Cheers, PeterWed, 22 Oct 2008 15:24:33 GMTPeter Joneshttp://forum.strataframe.net/FindPost20265.aspxThere is a sample of what is produced by the DDT in the help that you can reference.</P><P>Database Deployment Toolkit -&gt; Stored Procedures -&gt; Table CRUD Stored Procedures</P><P>This may not be 100% what you are trying to do, but it does show a sample of an UPDATE stored procecure and what the update sproc will look like (for the most part).&nbsp;Wed, 22 Oct 2008 09:11:18 GMTTrent L. Taylorhttp://forum.strataframe.net/FindPost20260.aspxHi Trent,</P><P>I decided to take a look at using the UpdateStoredProcedureName and UpdateUsingStoredProcedure properties. I have created a proc with the required parameters but I couldn't see how I would pass in the paramters at runtime. I checked the forum and help and I'm now left with the impression that this&nbsp;may be&nbsp;a DDT only facility (which we don't use at this stage). If this isn't the case can you point me a description of how to pass paramters to the update&nbsp;proc?</P><P>Cheers, Peter<FONT size=2></P></FONT>Tue, 21 Oct 2008 19:16:35 GMTPeter Joneshttp://forum.strataframe.net/FindPost20222.aspxNope, no magic property.&nbsp; Sorry :ermm:</P><P>This is a relatively large change.&nbsp; This is something that we may consider in a future update, but the issue primarily comes when using CRUD settings.&nbsp; You cannot use standard UPDATE or INSERT sprocs if you do this.&nbsp; You&nbsp;would in essence have to have an UPDATE sproc for every potential combination, which obviously isn't feasible.&nbsp; Now if you are not using CRUD settings, then this could be done more easily.&nbsp; In either case, though, this is not an option without using the methods you mentioned.&nbsp; It is something that we have discussed and may address in teh future, but for now you will have to use the Exclude properties.Mon, 20 Oct 2008 09:22:45 GMTTrent L. Taylor