SEO

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;

On 13 Aug, 13:28, Paulwwu <skibum...@excite.com> wrote:
> 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 that
corresponds to your MySQL server version for the right syntax to use
near 'CLOSE 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 'END' at line 1
(0 ms taken)



On Aug 13, 10:26 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 13 Aug, 13:28, Paulwwu <skibum...@excite.com> wrote:
>
>
>
>
>
> > 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?- Hide quoted text -
>
> - Show quoted text -

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




On Aug 13, 10:39 am, Paulwwu <skibum...@excite.com> wrote:
> 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 that
> corresponds to your MySQL server version for the right syntax to use
> near 'CLOSE 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 'END' at line 1
> (0 ms taken)
>
> On Aug 13, 10:26 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
> > On 13 Aug, 13:28, Paulwwu <skibum...@excite.com> wrote:
>
> > > 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?- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -