[SOLVED] How to send line breaks to database?
lazarus / 16-Databases

----------------------------------------------------------------------
Slawek · 2023-06-06 11:54

I use code like this to write MariaDB stored procedures:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
TDataBase.ExecuteDirect(sSQL: String): Boolean;var err: Integer;begin 
Result := False;  try    FConn.Open;    try        if FConn.Connected
then begin          FTrans.StartTransaction;         
FConn.ExecuteDirect(sSQL);          FTrans.Commit;          Result :=
True;        end;    except      on E : Exception do
ShowMessage(E.Message);    end;  finally   FConn.Close;  end;end;
Where:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i sSQL :=
'CREATE PROCEDURE `zm_SET_COMPUTER`(IN `p_pcname` VARCHAR(64)) '+    
        'NO SQL '+              'BEGIN '+              
'END';Unfortunately, in PhPMyAdmin this procedure is displayed in one
long line.
But when I insert line breaks I get an syntax error
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i sSQL :=
'CREATE PROCEDURE `zm_SET_COMPUTER`(IN `p_pcname`
VARCHAR(64))'+Lineending+              'NO SQL'+Lineending+          
  'BEGIN'+Lineending+              Lineending+              'END';Is
there any way to fix this?

----------------------------------------------------------------------
cdbc · 2023-06-06 12:07

Hi
Could it be as simple as forgetting the spaces, when you introduce
linebreaks?
Something like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i sSQL :=
'CREATE PROCEDURE `zm_SET_COMPUTER`(IN `p_pcname` VARCHAR(64))
'+Lineending+              'NO SQL '+Lineending+              'BEGIN
'+Lineending+' '+              Lineending+              'END'; Added
space:
- after procedure signature
- after NO SQL
- after BEGIN
- between the 2 linebreaks
Dunno, but might help 
Regards Benny

----------------------------------------------------------------------
Slawek · 2023-06-06 14:36

Hi,
Unfortunately, that doesn't work either. I tried with spaces.

Quote from: cdbc on June 06, 2023, 02:07:20 pmSomething like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i sSQL :=
'CREATE PROCEDURE `zm_SET_COMPUTER`(IN `p_pcname` VARCHAR(64))
'+Lineending+              'NO SQL '+Lineending+              'BEGIN
'+Lineending+' '+              Lineending+              'END'; 
All I have to do is add one LineEnding or #13 or #10 and I get a
syntax error.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i     sSQL :=
'CREATE PROCEDURE `zm_SET_COMPUTER`(IN `p_pcname` VARCHAR(64))
 '+Lineending+' '+                  ' NO SQL '+                 
'BEGIN '+                       'END'; Adding a space doesn't help. I
also tried '\'#13

----------------------------------------------------------------------
rvk · 2023-06-06 14:42

Quote from: Slawek on June 06, 2023, 01:54:50 pmBut when I insert line
breaks I get an syntax error
WHAT syntax error do you get?

(Always mention the exact errors.)

Reference manual for CREATE PROCEDURE in MySQL:
https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

----------------------------------------------------------------------
Slawek · 2023-06-06 16:48

But there is no syntax error in the procedure itself.
The syntax error only appears after adding LineEnding.

When I'm not using LineEnding (or #13 or #10), the procedure is
executed correctly (also empty). It's just hard to modify in
phpMyAdmin because it comes in one line. I would like this routine to
be written in multiple lines and formatted with proper indentation.

----------------------------------------------------------------------
Soner · 2023-06-06 17:52

I think the error is from this ` charakter.

Try this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i sSQL :=
'CREATE PROCEDURE ''zm_SET_COMPUTER''(IN ''p_pcname'' VARCHAR(64))
'+Lineending+              'NO SQL '+Lineending+              'BEGIN
'+Lineending+' '+              Lineending+              'END'; 
I used twice this charakter ' not this ". I don't know what they named
in english.

----------------------------------------------------------------------
rvk · 2023-06-06 17:55

I thought maybe the ExecuteDirect would split the lines into separate
statements.
But it shouldn't do that.

What happens if you do this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i sSQL :=
'CREATE PROCEDURE zm_SET_COMPUTER(IN p_pcname VARCHAR(64))' +
Lineending +  'NO SQL' + Lineending +  'BEGIN' + Lineending + 
'END';(so omitting the empty line)

(The backtick character should be supported but I try to avoid it if
possible. Just use regular characters and no reserved keywords, then
it shouldn't be needed.)

----------------------------------------------------------------------
Slawek · 2023-06-06 20:39

Quote from: Soner on June 06, 2023, 07:52:00 pmI think the error is
from this ` charakter.

Try this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i sSQL :=
'CREATE PROCEDURE ''zm_SET_COMPUTER''(IN ''p_pcname'' VARCHAR(64))
'+Lineending+              'NO SQL '+Lineending+              'BEGIN
'+Lineending+' '+              Lineending+              'END'; 
I used twice this charakter ' not this ". I don't know what they named
in english.

Unfortunately, the error does not come from that ` character.

The procedure name cannot be enclosed in single quotes. Even
PhPMyAdmin doesn't accept it.
#1064 - Something is wrong in your syntax near ''zm_SET_COMPUTER'(IN
'p_pcname' VARCHAR(64)
              NO SQL
         ...'

----------------------------------------------------------------------
Slawek · 2023-06-06 21:16

Quote from: rvk on June 06, 2023, 07:55:11 pm(The backtick character
should be supported but I try to avoid it if possible. Just use
regular characters and no reserved keywords, then it shouldn't be
needed.)

It works:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i sSQL :=
'CREATE PROCEDURE `testProc`() '+        'BEGIN '+//LineEnding+      
'END;';It does not work:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i sSQL :=
'CREATE PROCEDURE `testProc`() '+        'BEGIN '+LineEnding+       
'END;';

----------------------------------------------------------------------
rvk · 2023-06-06 21:21

What component is FConn?

----------------------------------------------------------------------
Slawek · 2023-06-06 21:23

Wait. I think I know.

It's my mistake. 
I didn't post the whole code because it's big. In short, I omitted a
very important part, in which sSQL is first written to the TStringList
type buffer, and only from this buffer to ExecuteDirect. I completely
forgot about this buffer.

I'm sorry everyone for wasting your time, but I've been looking for
this bug for hours and couldn't figure out what was going on.

----------------------------------------------------------------------
rvk · 2023-06-07 03:59

That's why it's always good practice to post as much code as you can.
And otherwise create a small reproducible example.

Usually when creating such example you already stumble on the solution
yourself

----------------------------------------------------------------------
Slawek · 2023-06-07 10:08

You are absolutely right. I had the wrong approach to testing this
problem. This is what sometimes happens when you try to modify old
code. I got lost but you helped me a lot anyway as I finally started
to understand that the problem was in my code.
Thank you again