[ciapug] More MySQL issues

[Dave J. Hala Jr.]

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

Open Source Information Systems, Inc. (OSIS)
Dave J. Hala Jr., President <dave at osis.us>
641.485.1606