Raymond Camden wrote on 05/08/07 5:50 PM

Your question doesn't make sense. A stored procedure is a WAY to talk to the database. A CFC is a way to organize your code. So you can easily call a stored proc from within a CFC.

John wrote on 05/08/07 7:57 PM

this was a question I was asked in an interview. I responded by saying. in single homed application it would make sense to keep the logic inside a .cfc using inline queries for reuse. In a situation where you have many applications all accessing the same data source (c# programs, web apps, etc..) it would be best to maintain business logic in a stored procedure on the database as opposed to the application server.

Raymond Camden wrote on 05/08/07 9:05 PM

Um, but that still isn't the point. You _can_ use stored procs as you describe. Just put the stored proc calls in the CFC.

Sana wrote on 05/09/07 5:02 AM

Database engine are design for handling complex tasks, so simple anything which can run much better within db engine should not be in CFC.

Trust me you are getting wrong idea of stored procedures, procedures are more flexible , reliable , fast than having long sql querires in your CFC.

Ok now business logic, it feels me a joke... business logic can be into your database section to achieve best performance and stability.

transactions control in database is much better, faster than handling in CF.

Raymond Camden wrote on 05/09/07 8:12 AM

Sana, you aren't getitng the point. Stored procedures have to go somewhere. Right? So put the _call_ in the CFC. I'm not talking about inline queries at all. I'm really shocked no one here is getting that. A CFC can use both inline queries or stored procs. You are right that it is a good thing to use stored procs, but using stored procs does NOT mean you don't use CFCs. It's apples and oranges people!

Sana wrote on 05/09/07 8:38 AM

Correct me if I am wrong, I am talking about business logic which I clearly said that business logic can be split and could be implement in database section as well. I am aware of CFC can call stored_procedure + can have your business logic.

There are many scenarios where your database engine can perform well to achieve desired task/goal. CSV file read and write is much better/faster in database section, transactional control + try/catch + exception logging inside of procedures + calling mulptile procedures + having cursor.

All these things are ment to be in database section.
Database engine are desinged for complex tasks, JDBC are only for fetch the data, I have came across where we have dynamic queries really messed up; Sure I will forward you that link where other people having same problem.
Under heavy load CFMX7 JDBC does not perform well with dynamic queries.

@RAY sure CFC is a place where we call stored-procedure.

[quote]
I am of the mind that since stored procedures run threw the same engine an inline queries do that you might as well encapsulate on the cfc
[/quote]

I am talking about dynamic queries should be implemented inside database (procedures, triggers, cursors, custom-datatype, custom-functions). It depends on your requirement how much business logic you can split between CFC and stored-procedure.

Raymond Camden wrote on 05/09/07 8:51 AM

Maybe I'm just being too picky about language here. I get your point about putting the logic in the db. I'm not sure I agree (I think the db is great for queries but business rules belong in the app layer), but my main problem is how things are being worded here. You can absolutely put a heck of a lot of logic in the db layer and still use CFCs to call the db. The question isnt - "Do I use stored procs or CFCs", but "Do I put business logic in my db". Are you talking about _completely_ skipping CF? If so - I get your point, even if I don't agree. But if you are still talking about using CF then why would you NOT want your CFC to handle your calls to the db.

Terrence Ryan wrote on 05/09/07 9:07 AM

I'll jump in here, and say that the best answers to this question start with "It depends."

There are many considerations:

Is your organization database centric (strong DBA's who know what they're doing) or is your expertise more on the CF side.

Will you potentially want to move the front end to a different code base? (new framework, update of ColdFusion, etc)

Would your SQL benefit by being written with SQL tools, versus just writing them inline.

I think there's isn't an right formula that can be applied across the board. It really depends on your organization.

John wrote on 05/09/07 9:34 AM

I'll provide a little more context here.


the people interviewing me exist in a distributed environment with websphere. They have several Coldfusion apps that all use similar business logic. The single point in common is the DB interface. In the interview I answered the question of where would I put my business logic with stored procedures, because it seemed to be the most logical answer. Later at the time of writing this blog I wonder why saying a cfc would have been a poor answer.

David Fekke wrote on 05/09/07 9:44 AM

John,

I have a <a href="http://www.fekke.com/blog/index.cfm/2007/5/9/Encapsulating-your-Business-Logic-in-CFCSs-vs-Stored-Procedures" target="_blank">post</a> about this on my <a href="http://www.fekke.com/blog/" target="_blank">blog</a>.

android apps wrote on 12/16/11 2:16 AM

Great resources Wish i could find more info like this from others!   



nice post