We help IT Professionals succeed at work.

PHP & Stored Procedures not working anymore

GarySB
GarySB asked
on
73 Views
Last Modified: 2016-05-23
My stored procedures and php files worked great.
My web hosting company upgraded servers to:
PHP version 5.2.9
MySQL version 5.0.96-community
and now the same exact code that worked before returns nothing to my VB 6 program.  I was able to tell that one of the php instructions it returns an error on is:    declare MyVarible varchar(5);   this worked before the upgrade.

Using the same code i created the procedure with now returns a syntax error on the declare statement above.  How should this be re-written ?
Does a guide exist that you know of that will show changes to make in this newer version ? Thank You
Comment
Watch Question

Terry WoodsWeb Developer, specialising in WordPress
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Which version did you upgrade from? There is a guide here if you upgraded from 4.1: http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-previous-series.html

Is it PHP or MySQL returning the syntax error? The "PHP instruction" you quoted is MySQL rather than PHP, though it may be code in PHP that it's trying to run through MySQL.

Author

Commented:
Your correct, that instruction is from my stored procedure routine.  My company cannot tell me what version it was before, but judging from the existing syntax that worked I was hopping you could tell.

Author

Commented:
excuse my typo
Terry WoodsWeb Developer, specialising in WordPress
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
It may help to copy and paste more of the code surrounding that line, and also the error message.

Author

Commented:
Below a decision is made      
case cntstats
when 0 then programtype.product_name
else concat (programtype.product_name, char(65), cntstats)

It always comes up as zero even when a valid email is provided.  The ELSE should really execute.

My procedure as it used to work.

delimiter $$
create procedure get_lessons (user_email varchar(96), userrange as integer)

begin
declare cntstats varchar(5);

select count(customer.customerid) into cntstats
from customer,
orders,
orderproduct,
programtype
where
customer.customerid=orders.customerid and
orders.orders_id = orderproduct.orders_id and
orders.ordersvalue = 2 and
customer.useremailaddress = user_email;

select
programtype.productid as lid,
case cntstats
when 0 then programtype.product_name
else concat (programtype.product_name, char(65), cntstats)
END as mytitle,
programtype.product_data as ltext
from customer,  
programtype,
product
where
customer.useremailaddress = user_email and
programtype.productid > userrange-26 and
programtype.productid < userrange and
programtype.productid = product.productid and
product.productvalue=1;
END
$$
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
First of all, check whether your stored procedures work. Use the Work Bench, Toad or a MySQL managment tool of your choice to verify this. Use the strict mode to hunt down subtle bugs in your code.

Have you also verified that the necessary PDO libs (or what data access do you use) was also properly updated? Have you also verified the configuration for your data access?
Most Valuable Expert 2011
Author of the Year 2014

Commented:
Run this script, shown here in its entirety, on both the old system and the new system.  Compare the outputs.  It's a lot of information to sift through, but it will at least tell you the differences.  Once we know the differences we can advise on how to get the new installation up to your requirements.
<?php phpinfo();

Open in new window

Also, why in the world would anyone be "upgrading" to a PHP 5.2 release?  PHP 5.2 is not supported at all any more (has not been for several years) and is no longer even considered for security fixes.  In other words, PHP 5.2 is obsolete.  The current versions of PHP are listed right on the PHP.net home page.  PHP 5.5+ is current.  I believe that MySQL 5.7+ is current.
Terry WoodsWeb Developer, specialising in WordPress
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Ray, we're dealing with a hosting business here; they are probably under pressure from customers to move slowly on upgrades because it means work for the customers in fixing their code (even though more work might be needed by other customers who suffer data loss or other issues by running an old version... often those wanting the status quo seem to be given priority in my experience!).

Anyway, that also means the old version won't be available to test on.
Web Developer, specialising in WordPress
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Most Valuable Expert 2011
Author of the Year 2014

Commented:
Wow, I don't know what to say.  Maybe, "Good luck!"
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.