0

I am trying to make a stored procedure in phpmyadmin. I do this in PHP MyAdmin for instance

 UPDATE financeplusacct SET bal = bal - 5000 WHERE accNum = 00343297587;
UPDATE financeplusacct SET bal = bal + 5000 WHERE accNum = 00343995977

It posts fine without worries then i try to make it a stored procedure so my Javascript REST api can call it without hassle, Looks like this

UPDATE financeplusacct SET bal = bal - amount WHERE accNum = accNum1;
UPDATE financeplusacct SET bal = bal + amount WHERE accNum = accNum2;

And i am getting this as an Error

One or more errors have occurred while processing your request:
The following query has failed: "CREATE DEFINER=`root`@`localhost` PROCEDURE `ExecDebitCredit`(IN `accNum1` VARCHAR(150), IN `accNum2` VARCHAR(150), IN `amount` DECIMAL(18,2)) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER UPDATE financeplusacct SET bal = bal - amount WHERE accNum = accNum1; UPDATE financeplusacct SET bal = bal + amount WHERE accNum = accNum2;"

MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE financeplusacct SET bal = bal + amount WHERE accNum = accNum2' at line 2

Please is there something i am not getting rightly?

MikeBlue
  • 3
  • 3
  • 1
    Show complete SP code. Do you remember about DELIMITER reassign? – Akina Nov 03 '21 at 08:48
  • I did not use a SP code. I used routines instead. – MikeBlue Nov 03 '21 at 08:59
  • You need to wrap your code - the two `UPDATE` calls - with `BEGIN` ... `END`. – MarcinJ Nov 03 '21 at 09:11
  • @MarcinJ, That part worked , now i am getting this as Error The following query has failed: "SET @p0='00343995977'; SET @p1='00343297587'; SET @p2='3000'; CALL `ExecDebitCredit`(@p0, @p1, @p2); " MySQL said: #1267 - Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '=' When I try to execute a test – MikeBlue Nov 03 '21 at 09:20
  • For collation mix; https://stackoverflow.com/questions/3029321/troubleshooting-illegal-mix-of-collations-error-in-mysql – Ergest Basha Nov 03 '21 at 09:22
  • Thanks guys, changing to utf8mb4_general_ci worked thanks everyone – MikeBlue Nov 03 '21 at 09:30
  • *I did not use a SP code. I used routines instead.* The term "routine" in MySQL means "stored procedure or function". – Akina Nov 03 '21 at 16:10

0 Answers0