Bug #26618 SP displays truncated
Submitted: 25 Feb 2007 5:13 Modified: 13 Mar 2007 22:17
Reporter: Jared S (Silver Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Visual Studio Plugin Severity:S3 (Non-critical)
Version:1.1.2 OS:Windows (WinXP)
Assigned to: CPU Architecture:Any
Tags: qc, stored proc, truncated, Visual Studio

[25 Feb 2007 5:13] Jared S
Description:
Hi, Through backup and restore of DB using Admin and Query Browser respectively, I have a SP that is highly spaced out - this shows truncated in alter stored proc function availible through right click context menu.

How to repeat:
1.  Load this proc using QB
2.  And, then try to alter it in VS plugin

DROP PROCEDURE IF EXISTS `InsertMaster`;
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertMaster`()
BEGIN

DECLARE zNewID INT;

DECLARE zFirstName INT;

	SELECT (SELECT count(id) +1 from `sale-guests` where id = idref) INTO zFirstName;

	-- replace 4 queries

	INSERT INTO `sale-guests` (idpostcode, firstname, lastname, dateadded, ccdate) VALUES(233, '01', CONCAT('M-', zFirstName), NOW(), ADDDATE(CURDATE(), INTERVAL 1 YEAR));

	SELECT LAST_INSERT_ID() INTO zNewID;

	UPDATE `sale-guests` SET idref = zNewID where id = zNewID;

	SELECT zNewID;

END $$

Suggested fix:
Please ensure that SP is load 100% correctly no matter what white space is involved.
[25 Feb 2007 5:15] Jared S
1. Try this proc !!

DROP PROCEDURE IF EXISTS `InsertMaster`;
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertMaster`()
BEGIN

DECLARE zNewID INT;

DECLARE zFirstName INT;

	SELECT (SELECT count(id) +1 from `sale-guests` where id = idref) INTO zFirstName;

	-- replace 4 queries

	INSERT INTO `sale-guests` (idpostcode, firstname, lastname, dateadded, ccdate) VALUES(233, '01', CONCAT('M-', zFirstName), NOW(), ADDDATE(CURDATE(), INTERVAL 1 YEAR));

	SELECT LAST_INSERT_ID() INTO zNewID;

	UPDATE `sale-guests` SET idref = zNewID where id = zNewID;

	SELECT zNewID;

END $$
[25 Feb 2007 5:19] Jared S
Please use file I have provide since all the stuff I copied and paste got CRs deleted ounce posted.
[13 Mar 2007 12:06] Tonci Grgin
Hi Jared and thanks for your report.
I do miss LF (#0D) so my screen in VS doesn't look all that good, but NL, TAB, SPACES and all other chars are (almost) there. I'm using VS2005 Pro and, for this test, c/NET 5.0.3 GA.

Can you be more specific as what to look for?

VS output:
PROCEDURE InsertMaster ()
BEGIN
DECLARE zNewID INT;
DECLARE zFirstName INT;
	SELECT (SELECT count(id) +1 from `sale-guests` where id = idref) INTO zFirstName;
	
	INSERT INTO `sale-guests` (idpostcode, firstname, lastname, dateadded, ccdate) VALUES(233, '01', CONCAT('M-', zFirstName), NOW(), ADDDATE(CURDATE(), INTERVAL 1 YEAR));
	SELECT LAST_INSERT_ID() INTO zNewID;
	UPDATE `sale-guests` SET idref = zNewID where id = zNewID;
	SELECT zNewID;
END
[13 Mar 2007 22:17] Jared S
Appears the output is correct, I was just put of by the missing DELIMETER keyword.  Bug closed then.  But I am opening a new bug about NET 5.0.5 and VS plugin incompatibility.
[14 Mar 2007 7:38] Tonci Grgin
Jared, I think there's one already opened (AFAIR I verified it).