Developer Forum

CREATE PROCEDURE `scrubAddressBlanks`()
BEGIN
DECLARE addy VARCHAR(200);
DECLARE wfid INT;
DECLARE cRow CURSOR FOR
SELECT workflowstageid, address1
FROM workflowstage;
DECLARE exitLoop INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET exitLoop = 1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'FAILURE IN scrubAddressBlanks' as 'wserr';
END;
START TRANSACTION;
OPEN cRow;
FETCH cRow INTO wfid, addy;
WHILE NOT exitLoop DO
SET addy = TRIM(addy);
WHILE INST(' ', addy) <> 0 DO
SET addy = REPLACE(addy,' ',' ');
END WHILE;
UPDATE workflowstage
SET address1 = addy
WHERE workflowstageid = wfid;
FETCH cRow INTO wfid, addy;
END WHILE;
COMMIT;
CLOSE cRow;
END;

I'd love to help you, but my crystal ball has broken down and no
matter how hard I stare at it I am unable to see WHAT THE SYNTAX ERROR
YOU ARE GETTING IS!!!!

Care to enlighten us?

Sorry to hear your crystal ball is out of wack. You can borrow mine.

There is just a simpe syntax problem (like a missing semi or colon?),
I think. But here's the messages:




Error Code : 1064
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'VARCHAR(200)' at line 3
(0 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'DECLARE wfid INT' at line 1
(0 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'DECLARE cRow CURSOR FOR
SELECT workflowstageid, address1
FROM workflowstage' at line 1
(0 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'DECLARE exitLoop INT DEFAULT 0' at line 1
(0 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET exitLoop = 1'
at line 1
(0 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK' at line 1
(0 ms taken)

(1 row(s)returned)
(0 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'END' at line 1
(0 ms taken)

(0 row(s)affected)
(0 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'OPEN cRow' at line 1
(0 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'FETCH cRow INTO wfid, addy' at line 1
(0 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'WHILE NOT exitLoop DO
SET addy = TRIM(addy)' at line 1
(0 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'WHILE INST(' ', addy) <> 0 DO
SET addy = REPLACE(addy,' ',' ')' at line 1
(0 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'END WHILE' at line 1
(0 ms taken)

Error Code : 1054
Unknown column 'wfid' in 'where clause'
(0 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'FETCH cRow INTO wfid, addy' at line 1
(0 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'END WHILE' at line 1
(0 ms taken)

(0 row(s)affected)
(0 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual

urns out it was a server issue. It ran out of space on disk. Weird.




On Aug 13, 10:39 am, Paulwwu < XXXX@XXXXX.COM > wrote: