[ciapug] Re: Re: MySQL 5 Stored Procedures (correction)

[Tony Bibbs]

Daniel.Juliano at wellsfargo.com wrote:
> Furthermore (from the original article):
> 
> "I too find writing code like string s = "SELECT * FROM Foo WHERE Bar =
> " + barValue; in your code not the right thing to do. However the
> alternative is not stored procedures, it's a component that generates
> this SQL on the fly so you don't have the disadvantages of stored
> procedures and have the advantages of generating the SQL you need.
> Writing such a component is a one-time effort, you can reuse that
> component each time you access a database. Stored procedures are not the
> answer, Dynamic SQL is."

Which is exactly what Propel gives you.  Dynamic, portable SQL in the 
form of prepared statements.  In fact, much of your code won't even have 
SQL embedded in it:

// Fetch an object, change it and save back to database.
$myObj = new SomePropelObject()
$myObj->setSomePrimaryKeyField($myPKValue);
$myObj->get();

$myObj->setSomeField($someValue);
$myObj->setSomeOtherField($someOtherValue);
$myObj->save();

// Delete the object
$myObj->delete();

So under the hood Propel generates all the prepared statements to do the 
work above.

Also, I don't agree with the all-or-nothing approach (e.g. either 
prepared statements or stored procedures).  I look at SP's as a tool for 
performance tuning an application.  Many times I start up with 100% 
dynamic SQL using Propel and we hit bottlenecks where the query is 
simply too complex to do efficiently with dynamic SQL so I'll move that 
to an SP.  Developers should be able to understand the 'why' part.

And I'm sorry about yet more Propel rhetoric...I'm even annoying myself 
with it.  But it is really worth a good once over for any PHP5 developer.

--Tony