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
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
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.
ASKER
excuse my typo
It may help to copy and paste more of the code surrounding that line, and also the error message.
ASKER
Below a decision is made
case cntstats
when 0 then programtype.product_name
else concat (programtype.product_name,
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)
from customer,
orders,
orderproduct,
programtype
where
customer.customerid=orders
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,
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
$$
case cntstats
when 0 then programtype.product_name
else concat (programtype.product_name,
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)
from customer,
orders,
orderproduct,
programtype
where
customer.customerid=orders
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,
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?
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();
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.
Anyway, that also means the old version won't be available to test on.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Wow, I don't know what to say. Maybe, "Good luck!"
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.