Link to home
Get AccessLog in
Avatar of GarySB
GarySB

asked on

PHP & Stored Procedures not working anymore

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
Avatar of Terry Woods
Terry Woods
Flag of New Zealand image

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.
Avatar of GarySB
GarySB

ASKER

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.
Avatar of GarySB

ASKER

excuse my typo
It may help to copy and paste more of the code surrounding that line, and also the error message.
Avatar of GarySB

ASKER

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
$$
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?
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.
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.
ASKER CERTIFIED SOLUTION
Avatar of Terry Woods
Terry Woods
Flag of New Zealand image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Wow, I don't know what to say.  Maybe, "Good luck!"