[ciapug] More MySQL issues

[Carl Olsen]

I got a book on C# several years ago, and it used stored procedures for
everything.  I tried it and liked it, so I've never thought about doing it
any other way.  When PHP and MySQL came out with these same functions, I
immediately started coding that way because it was easier for me to keep
track of what I was doing.  I didn't like PHP until version 5 came out, and
I didn't like MySQL until version 4.1.3 came out.  If you are not accustomed
to doing things this way, I'm sure it would be a pain in the *** to start
doing it.  I'm not sure I'd recommend it.  I spent a lot of time learning
how to do stored procedures in SQL Server and PostgreSQL, so learning them
in MySQL seems fairly trivial to me.  The biggest problem I'm having is that
MySQL stored procedures are more limited in functionality than the others
that have been doing it for a much longer time.  I'm expecting them to get
better, and they really work fairly well now.  

--
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 11:13 AM
To: PHP List
Subject: RE: [ciapug] More MySQL issues

I was just curious, as to why you did it that way... It was just a  way
of doing that was differant than the way I do things and like I said, I
was just curious...

I try new stuff all time, thats why I asked... it might be something
that I'd try sometime...



On Fri, 2006-07-14 at 11:07, 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
-- 

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

_______________________________________________
ciapug mailing list
ciapug at cialug.org
http://cialug.org/mailman/listinfo/ciapug