0

This bunch of codes work greatly on localhost(homestead virtual machine and mysql). Actually, the stored procedure code was written in mysql workbench but when I migrate the code to the online version, it does not work and I don't see any error it output. This is the code below

DELIMITER $$
CREATE DEFINER=`homestead`@`%` PROCEDURE `catalog_get_products_on_catalog`(
IN inShortProductDescriptionLength INT,
IN inProductsPerPage INT, IN inStartItem INT)
BEGIN
PREPARE statement FROM
"SELECT p.id, p.name,
IF(LENGTH(p.description) <= ?,
p.description,
CONCAT(LEFT(p.description, ?),
'...')) AS description,
p.price, p.discounted_price, i.name AS img_name
FROM products p, imgs i
WHERE display = 1 OR display = 3
AND p.id = i.product_id
ORDER BY display DESC
LIMIT ?, ?";
SET @p1 = inShortProductDescriptionLength;
SET @p2 = inShortProductDescriptionLength;
SET @p3 = inStartItem;
SET @p4 = inProductsPerPage;
EXECUTE statement USING @p1, @p2, @p3, @p4;
END$$
DELIMITER ;

What might be the possible problem? Is it because it was created with mysql workbench and I created it with phpmyadmin in the cpanel? And how do I fix it

John Conde
  • 217,595
  • 99
  • 455
  • 496
gbenga wale
  • 359
  • 4
  • 23
  • add an error handler and see if it thriws an erro https://stackoverflow.com/a/7765950/5193536 Do you havethe same version of mysql on both servers – nbk Oct 06 '19 at 22:49
  • @nbk That i dont know if I have the same version of mysql server – gbenga wale Oct 06 '19 at 22:54
  • try this on both https://stackoverflow.com/a/55045330/5193536 – nbk Oct 06 '19 at 22:56
  • @nbk where will I run that query? phpmyadmin? – gbenga wale Oct 06 '19 at 23:03
  • workbench or phpmyadmin, every client will do – nbk Oct 06 '19 at 23:05
  • @nbk the sever version is 5.7.27-cll-lve while the local is 5.7.22-0ubuntu18.04.1. I dont really think that is much of a different – gbenga wale Oct 06 '19 at 23:25
  • yes, it doesn't matter, but you had to check. try error handling – nbk Oct 06 '19 at 23:32
  • @nbk you mean php tryand catch? Or does mysql has its own version – gbenga wale Oct 06 '19 at 23:35
  • see my first link in the first comment – nbk Oct 06 '19 at 23:48
  • Why do you use a prepared statement? You can pass the parameters directly in the query. – Shadow Oct 07 '19 at 00:40
  • Does `homestead`@`%` definer exists in your Server? – James Oct 07 '19 at 04:05
  • @james no. I use adans@localhost but I just copied the one on localhost and paste it here – gbenga wale Oct 07 '19 at 05:55
  • @shadow are you saying it might be a possible reason for not working – gbenga wale Oct 07 '19 at 05:56
  • 1
    He said when you have a direct query then why using a complex prepared query unless needed. – James Oct 07 '19 at 06:01
  • Try this code of block to capture warnings/ errors *declare continue handler for sqlwarning, not found, sqlexception begin declare errno int; declare state char(7); declare msg text; get stacked diagnostics condition 1 errno = mysql_errno,state= returned_sqlstate, msg= message_text; select errno,state,msg; end;* place this block in the beginning next to `BEGIN` .... my_code ....prepare statement....then share the error here – James Oct 07 '19 at 06:12
  • a) what does "not working" mean exactly? b) have you verified that the procedure has been created? (in phpmyadmin, it should be listed in the procedure tab/the database tree on the left side) c) a common problem on linux systems is case sensitivity of table names - can you check if your tables names are lowercase? (e.g. "products" and not "Products") – Solarflare Oct 07 '19 at 06:39
  • @solarflare the stored procedure has been. I will check the table names for case sensitivity and at the end try what james wrote – gbenga wale Oct 07 '19 at 08:25

0 Answers0