Thank you guys, much appreciated all the explanations.

Edhy Rijo

Also, Edhy, using CRUD is faster than using standard UPDATE commands.  The parger the table, number of columns, and database size, the more significant the performance becomes.  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).  There is really no downside to creating UPDATE and INSERT sprocs...unless you consider performance gains a downside

Hi Greg,

Thanks for that - obviously I didn't read the Help file closely enough - back to the books!

Edhey - the reason I was interested in this is that I have a 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.

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 averaging around on insert 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 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 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 of text and executed.

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.

Cheers, Peter 

Thanks Greg,

So unless there is special need, having the CRUD without SP would be acceptable for any application?

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.

Edhy Rijo

I don't know why Peter is wanting to use sprocs, but here are a couple of common reasons:



1. Security. Often DBAs will disallow any access to tables. All access must be via views and sprocs.



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.



I'll be interested in other reasons...

Hi Greg, Peter

On the same topic, what would be the advantage of using SP for the CRUD?

Edhy Rijo

Peter, I can answer some of your questions:







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.







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.



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.



Hope that helps!

Hi Trent,

Yes, I have seen the Help samples. Maybe my question wasn't clear enough:

1) I don't use the DDT.

2) Does using custom update stored procedures require the use of the DDT?

3) If the DDT isn't needed how do I pass my paramenter values to the update stored proc at run time?

Cheers, Peter

There is a sample of what is produced by the DDT in the help that you can reference.

Database Deployment Toolkit -> Stored Procedures -> Table CRUD Stored Procedures

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). 

Hi Trent,

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 may be 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 proc?

Cheers, Peter