distal-attribute
distal-attribute
distal-attribute
distal-attribute

Stored Procedure error when using \n

User, date Message
Written by combuilder
1 year ago
Category: General
30 posts since Thu, 08 Nov 12
Hi!
I use a variable when creating stored procedures to include a carriage return.
If you put this:
BEGIN
# Versión 1.3.0 - 20130201
SET @message := ''; -- Mensaje
SET @sep := '\n'; -- Separador de Mensajes

SET @message := CONCAT(@message, 'Linea 1', @sep);
SET @message := CONCAT(@message, 'Linea 2', @sep);

# Mensaje de ejecución de CREACION de TABLAS CRUZADAS
CALL common_schema.prettify_message('Ejemplo', common_schema.trim_wspace(@message));

END

You'll get this:
BEGIN
# Versión 1.3.0 - 20130201
SET @message := ''; -- Mensaje
SET @sep := '
';
-- Separador de Mensajes

SET @message := CONCAT(@message, 'Linea 1', @sep);
SET @message := CONCAT(@message, 'Linea 2', @sep);

# Mensaje de ejecución de CREACION de TABLAS CRUZADAS
CALL common_schema.prettify_message('Ejemplo', common_schema.trim_wspace(@message));

END
Written by combuilder
1 year ago
30 posts since Thu, 08 Nov 12
I try the SHOW CREATE PROCEDURE in MySQL Workbench and the function is OK!

Also i have problems when using ' on CONCAT, i mean, when using ''

I belive this behaviour didn't occur in previous versions!
Written by ansgar
1 year ago
4940 posts since Fri, 07 Apr 06
This is a server issue. HeidiSQL takes the procedure code from IS:

SELECT `ROUTINE_DEFINITION` FROM information_schema.`ROUTINES` WHERE `ROUTINE_SCHEMA`='yourdatabase' AND `ROUTINE_NAME`='yourroutinename' AND `ROUTINE_TYPE`='PROCEDURE';



Escaped input gets stored unescaped by MySQL. See
http://bugs.mysql.com/bug.php?id=58342
http://bugs.mysql.com/bug.php?id=62129
Written by ansgar
1 year ago
4940 posts since Fri, 07 Apr 06
See also issue #3107.
Written by combuilder
1 year ago
30 posts since Thu, 08 Nov 12
Thank you for your answer, but I am sure that a few days ago i didn't suffer this problem either with ' neither with \n

I don't know if it's because i'm using MySQL 5.6.10 (previourly i was using MySQL 5.5.28).
Written by ansgar
1 year ago
4940 posts since Fri, 07 Apr 06
Please read all comments there. Issue #3103 and issue #3104 are also relevant here. I had to move from the SHOW CREATE PROCEDURE approach to the SELECT .. FROM IS.VIEWS approach as it's now, to fix several other bugs in my parser. So, I fixed a few bugs and ran into a server bug unfortunately. I hope the server bug gets fixed at some point, as I don't want to break the other fixes again.
Written by combuilder
1 year ago
30 posts since Thu, 08 Nov 12
Thanks again.
I have solved all issues related to ' by usin ' and "

The issue with \n is notr crucial, because i have located in a variable!
Written by combuilder
1 year ago
30 posts since Thu, 08 Nov 12
The server bug is also present in MySQL Workbench
Written by ansgar
1 year ago
4940 posts since Fri, 07 Apr 06
Yes, this should not be serious at all, as the server just makes a formal change on escaped characters. The server does not break any character. Though it's really a bit of a quirky bug I think, should be highly prioritized by MySQL.
 

Please login to leave a reply, or register at first.