Recent

Author Topic: [SOLVED] How to send line breaks to database?  (Read 1571 times)

Slawek

  • New Member
  • *
  • Posts: 43
[SOLVED] How to send line breaks to database?
« on: June 06, 2023, 01:54:50 pm »
I use code like this to write MariaDB stored procedures:

Code: Pascal  [Select][+][-]
  1. function TDataBase.ExecuteDirect(sSQL: String): Boolean;
  2. var err: Integer;
  3. begin
  4.   Result := False;
  5.   try
  6.     FConn.Open;
  7.     try
  8.         if FConn.Connected then begin
  9.           FTrans.StartTransaction;
  10.           FConn.ExecuteDirect(sSQL);
  11.           FTrans.Commit;
  12.           Result := True;
  13.         end;
  14.     except
  15.       on E : Exception do ShowMessage(E.Message);
  16.     end;
  17.   finally
  18.    FConn.Close;
  19.   end;
  20. end;

Where:
Code: Pascal  [Select][+][-]
  1. sSQL := 'CREATE PROCEDURE `zm_SET_COMPUTER`(IN `p_pcname` VARCHAR(64)) '+
  2.               'NO SQL '+
  3.               'BEGIN '+
  4.  
  5.               '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][+][-]
  1. sSQL := 'CREATE PROCEDURE `zm_SET_COMPUTER`(IN `p_pcname` VARCHAR(64))'+Lineending+
  2.               'NO SQL'+Lineending+
  3.               'BEGIN'+Lineending+
  4.               Lineending+
  5.               'END';
Is there any way to fix this?

« Last Edit: June 06, 2023, 11:42:34 pm by Slawek »

cdbc

  • Hero Member
  • *****
  • Posts: 1025
    • http://www.cdbc.dk
Re: How to send line breaks to database?
« Reply #1 on: June 06, 2023, 02:07:20 pm »
Hi
Could it be as simple as forgetting the spaces, when you introduce linebreaks?
Something like this:
Code: Pascal  [Select][+][-]
  1. sSQL := 'CREATE PROCEDURE `zm_SET_COMPUTER`(IN `p_pcname` VARCHAR(64)) '+Lineending+
  2.               'NO SQL '+Lineending+
  3.               'BEGIN '+Lineending+' '+
  4.               Lineending+
  5.               'END';
  6.  
Added space:
- after procedure signature
- after NO SQL
- after BEGIN
- between the 2 linebreaks
Dunno, but might help  %)
Regards Benny
If it ain't broke, don't fix it ;)
PCLinuxOS(rolling release) 64bit -> KDE5 -> FPC 3.2.2 -> Lazarus 2.2.6 up until Jan 2024 from then on it's: KDE5/QT5 -> FPC 3.3.1 -> Lazarus 3.0

Slawek

  • New Member
  • *
  • Posts: 43
Re: How to send line breaks to database?
« Reply #2 on: June 06, 2023, 04:36:56 pm »
Hi,
Unfortunately, that doesn't work either. I tried with spaces.

Something like this:
Code: Pascal  [Select][+][-]
  1. sSQL := 'CREATE PROCEDURE `zm_SET_COMPUTER`(IN `p_pcname` VARCHAR(64)) '+Lineending+
  2.               'NO SQL '+Lineending+
  3.               'BEGIN '+Lineending+' '+
  4.               Lineending+
  5.               'END';
  6.  

All I have to do is add one LineEnding or #13 or #10 and I get a syntax error.

Code: Pascal  [Select][+][-]
  1.     sSQL := 'CREATE PROCEDURE `zm_SET_COMPUTER`(IN `p_pcname` VARCHAR(64))  '+Lineending+' '+
  2.                   ' NO SQL '+
  3.                   'BEGIN '+
  4.      
  5.                   'END';
Adding a space doesn't help. I also tried '\'#13 :(


rvk

  • Hero Member
  • *****
  • Posts: 6109
Re: How to send line breaks to database?
« Reply #3 on: June 06, 2023, 04:42:24 pm »
But 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

« Last Edit: June 06, 2023, 04:45:16 pm by rvk »

Slawek

  • New Member
  • *
  • Posts: 43
Re: How to send line breaks to database?
« Reply #4 on: June 06, 2023, 06:48:43 pm »
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.
« Last Edit: June 06, 2023, 07:14:19 pm by Slawek »

Soner

  • Sr. Member
  • ****
  • Posts: 305
Re: How to send line breaks to database?
« Reply #5 on: June 06, 2023, 07:52:00 pm »
I think the error is from this ` charakter.

Try this:
Code: Pascal  [Select][+][-]
  1. sSQL := 'CREATE PROCEDURE ''zm_SET_COMPUTER''(IN ''p_pcname'' VARCHAR(64)) '+Lineending+
  2.               'NO SQL '+Lineending+
  3.               'BEGIN '+Lineending+' '+
  4.               Lineending+
  5.               'END';
  6.  

I used twice this charakter ' not this ". I don't know what they named in english.

rvk

  • Hero Member
  • *****
  • Posts: 6109
Re: How to send line breaks to database?
« Reply #6 on: June 06, 2023, 07:55:11 pm »
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][+][-]
  1. sSQL := 'CREATE PROCEDURE zm_SET_COMPUTER(IN p_pcname VARCHAR(64))' + Lineending +
  2.   'NO SQL' + Lineending +
  3.   'BEGIN' + Lineending +
  4.   '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

  • New Member
  • *
  • Posts: 43
Re: How to send line breaks to database?
« Reply #7 on: June 06, 2023, 10:39:40 pm »
I think the error is from this ` charakter.

Try this:
Code: Pascal  [Select][+][-]
  1. sSQL := 'CREATE PROCEDURE ''zm_SET_COMPUTER''(IN ''p_pcname'' VARCHAR(64)) '+Lineending+
  2.               'NO SQL '+Lineending+
  3.               'BEGIN '+Lineending+' '+
  4.               Lineending+
  5.               'END';
  6.  

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

  • New Member
  • *
  • Posts: 43
Re: How to send line breaks to database?
« Reply #8 on: June 06, 2023, 11:16:54 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][+][-]
  1. sSQL := 'CREATE PROCEDURE `testProc`() '+
  2.         'BEGIN '+//LineEnding+
  3.         'END;';
It does not work:
Code: Pascal  [Select][+][-]
  1. sSQL := 'CREATE PROCEDURE `testProc`() '+
  2.         'BEGIN '+LineEnding+
  3.         'END;';

rvk

  • Hero Member
  • *****
  • Posts: 6109
Re: How to send line breaks to database?
« Reply #9 on: June 06, 2023, 11:21:01 pm »
What component is FConn?

Slawek

  • New Member
  • *
  • Posts: 43
Re: How to send line breaks to database?
« Reply #10 on: June 06, 2023, 11:23:12 pm »
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.  :(
« Last Edit: June 06, 2023, 11:41:13 pm by Slawek »

rvk

  • Hero Member
  • *****
  • Posts: 6109
Re: [SOLVED] How to send line breaks to database?
« Reply #11 on: June 07, 2023, 05:59:37 am »
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

  • New Member
  • *
  • Posts: 43
Re: [SOLVED] How to send line breaks to database?
« Reply #12 on: June 07, 2023, 12:08:07 pm »
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  :)

 

TinyPortal © 2005-2018