[ciapug] More MySQL issues

[Tony Bibbs]

Nah, I don't think anybody is faulting you for that.

My issue with SP's has always been the fact they tie you to the hip with 
the DBMS you are using.  So as long as your organization is fine with 
using MySQL for a long time into the foreseeable future then do what you 
are doing.  Many organizations can make that decision and that's great.

Where I have the problem is when people turn right around and hook up 
things like PDO, PEAR::DB, etc to run all those SP's.  I mean, if you 
are tied at the hip with the DBMS because of the heavy use of SP's, you 
might as well go a step further and use the native PHP drivers (e.g. 
mysqli) get eak out a bit better performance.

I've been doing this now for 9 years and I have had to migrate databases 
from one DBMS to another and I will say there is nothing more painful 
and time consuming when you run into things that just aren't portable. 
With the advent of PDO, opcode caches, SQL caches, etc I don't see 
performance issues as much and as a result I tend to use database 
abstraction libraries and put the SQL in code ensuring to make things as 
portable as possible.  Do I use SP's?  Sure, but then ones I have are 
monsters that crunch a bunch of data that would otherwise take an 
eternity via code.

One other caveat to all this is that I try to ensure SP's are doing just 
the create, read, update, delete sort of stuff.  As soon as you have an 
ounce of business logic in an SP then I think it has been taken way too far.

Just my take on it...

--Tony

Carl Olsen wrote:
> I'm curious why people on this list question why you would want to use this
> or that technology when the question being asked is how that technology
> works, not whether someone should use it.  If you want to learn how
> something works, is there some way of doing that without trying it?
> 
> --
> Carl Olsen, MCSE
> 
> Web Developer, CMS Implementation
> 
> Marketing and Communications
> Drake University
> 2507 University Avenue
> 316 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 Dave J. Hala Jr.
> Sent: Friday, July 14, 2006 10:56 AM
> To: PHP List
> Subject: Re: [ciapug] More MySQL issues
> 
> Why would you want to user a stored procedure to return a result set to
> a php script?
> 
> 
> On Fri, 2006-07-14 at 10:45, Carl Olsen wrote:
>> Apparently, MySQL Stored Procedures don't support the LIMIT function
>> very well.  In order to get a Stored Procedure to return a set or rows
>> using the LIMIT function, you have to do some procedural gymnastics,
>> such as:
>>
>>  
>>
>> DELIMITER $$;
>>
>>  
>>
>> DROP PROCEDURE IF EXISTS `news`.`sp_News_ReturnApprovedArticles`$$
>>
>>  
>>
>> CREATE PROCEDURE `sp_News_ReturnApprovedArticles`(IN sp_var1 INT, IN
>> sp_var2 CHAR(10), IN sp_var_skip INT, IN sp_var_count INT)
>>
>> BEGIN
>>
>> SET @String1 = concat(concat(concat("SELECT * FROM news_article WHERE
>> cid = ",sp_var1," AND sdatedisplay >= '",sp_var2,"' AND sdatedisplay <
>> DATE_ADD('",sp_var2,"',INTERVAL 1 MONTH) AND iapproved = 1 ORDER BY
>> iorder ASC, sdatedisplay DESC LIMIT "),sp_var_skip,","),sp_var_count);
>>
>> PREPARE Stmt FROM @String1; 
>>
>> EXECUTE Stmt; 
>>
>> SET @String1 = "";
>>
>> END$$
>>
>>  
>>
>> DELIMITER ;$$
>>
>>  
>>
>> When you run this as a query, it returns a set of records as you would
>> expect.  However, when I call it from a PHP prepared statement, I get
>> extremely unusual results.  I get the correct number of rows each
>> time, but the data is scrambled beyond recognition.  I get some
>> characters that don't display correctly.  The data is all numbers,
>> except for the characters that it doesn't recognize (it just displays
>> them as an empty square).  I'm thinking it returning binary data or
>> something bizarre like that.  I finally gave up on this and skipped
>> the stored procedure and ran the query inside a prepared statement. 
>> Prepared Statements seems to work for anything I can come up with, but
>> Stored Procedures seem to have limited functionality.
>>
>>  
>>
>> --
>> Carl Olsen, MCSE
>>
>> Web Developer, CMS Implementation
>>
>> Marketing and Communications
>> Drake University
>> 2507 University Avenue
>> 316 Old Main
>> Des Moines, Iowa 50311-4505
>> Phone: 515-271-2986
>> Fax: 515-271-3798
>> Carl.Olsen at drake.edu
>> www.drake.edu
>>
>>  
>>
>>
>>
>> ______________________________________________________________________
>> _______________________________________________
>> ciapug mailing list
>> ciapug at cialug.org
>> http://cialug.org/mailman/listinfo/ciapug