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

[Carl Olsen]

Right off, I see it requires xsl and I can't see that in the configuration
settings.  The server administrator is on vacation this week, so I guess I'm
back to prepared statements and stored procedures.  I have most of my code
already written, so it's probably not a good idea to switch to Propel right
now.  I'll get it up and running next week when the server administrator
returns from vacation.  Or, I may simply install it at home on my own server
just to see how it works.

All I really need right now is a function that deletes items in a child
MyISAM table when parent records are deleted from an InnoDB table.  I was
using cascade deletes, but that doesn't work with MyISAM tables.  I can't
figure out how to return a record set of child rows inside a stored
procedure and then loop through them to delete the rows that match the
parent table record being deleted.  I can do that easily with a prepared
statement, but I just can't find any information on how to do it in a stored
procedure.

--
Carl Olsen, MCSE
Web Developer, CMS Implementation
Marketing and Communications
Drake University
2507 University Avenue
115 Old Main
Des Moines, Iowa 50311-4505
Phone: 515-271-2986
Fax: 515-271-3798
Carl.Olsen at drake.edu
www.drake.edu

-----Original Message-----
From: ciapug-bounces at cialug.org [mailto:ciapug-bounces at cialug.org] On Behalf
Of Tony Bibbs
Sent: Monday, July 24, 2006 12:32 PM
To: ciapug at cialug.org
Subject: Re: [ciapug] Re: Re: MySQL 5 Stored Procedures (correction)

Excellent.  Fire over any questions if you have a hard time setting it 
up.  If you want to wire it up to an existing database you'll need to 
run 'creole' target with you execute propel-gen.  That generates a the 
meta-data for your database in a file called schema.xml.  From there you 
would run propel-gen with no target specified and that will generate all 
the persistable Propel model objects.

First time setup can be a bit frustrating as you get use to the various 
configuration files but once you figure it out it's a snap.

--Tony

Carl Olsen wrote:
> Okay, you have me convinced.  I'm downloading Propel right now and reading
> about Creole.
> 
> --
> Carl Olsen, MCSE
> Web Developer, CMS Implementation
> Marketing and Communications
> Drake University
> 2507 University Avenue
> 115 Old Main
> Des Moines, Iowa 50311-4505
> Phone: 515-271-2986
> Fax: 515-271-3798
> Carl.Olsen at drake.edu
> www.drake.edu
> 
> 
> -----Original Message-----
> From: ciapug-bounces at cialug.org [mailto:ciapug-bounces at cialug.org] On
Behalf
> Of Tony Bibbs
> Sent: Monday, July 24, 2006 11:51 AM
> To: ciapug at cialug.org
> Subject: Re: [ciapug] Re: Re: MySQL 5 Stored Procedures (correction)
> 
> 
> 
> 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
> _______________________________________________
> ciapug mailing list
> ciapug at cialug.org
> http://cialug.org/mailman/listinfo/ciapug
> 
> _______________________________________________
> ciapug mailing list
> ciapug at cialug.org
> http://cialug.org/mailman/listinfo/ciapug
_______________________________________________
ciapug mailing list
ciapug at cialug.org
http://cialug.org/mailman/listinfo/ciapug