Looping Memo.Text

[Henry Olive 5]

I wish everyone a healthy day

 

Memo1.Text has total 3 sql  commands like below

 

- Create Table A  (ID Integer);

 

- Insert

  into A

  Values(1);

 

- Update  A 

  set ID=2

 Where

 ID=1;

 

I want to get each command & execute them one after another by a query

for example for the first line (Create Table A  ID Integer;)

 

var
  X:integer;
  S,Command:string;
begin
  S:=Memo1.Text; //suppose memo1.text in 1 line
  X:=Pos(';',S);
  Command:=Copy(S,1,X-1); 

 

MySqlDataset.CommandText:=Command;

MySqlDataset.Execute;

 

But i want to loop the memo and get 3 commands *or more* in order.

 

Thank You
 
 

 

 

 

[Der schöne Günther 316]

The memo content is just a string. You can split a string into multiple strings (string array).

 

var sqlCommands := Memo1.Lines.Text.Split([';']);

 

[Henry Olive 5]

Thank you so much Gunther

I'm so sorry i forgot to mention that i use Delphi-XE

XE doesnt recognise SPLIT.

[Guest]

Pos does accept another parameter, which is where to start searching from, aka offset of the search

 

Use Pos with last X then update it with each iteration.

[Henry Olive 5]

Thank you so much Kas

If you have time could you please show the codes?

 

[Guest]

22 minutes ago, Henry Olive said:

If you have time could you please show the codes?

Like this

var
  Start, Curr: Integer;
  stQuery: string;
begin
  Start := 1;
  while True do
  begin
    Curr := Pos(';', Memo1.Lines.Text, Start);
    if Curr = 0 then
      Break;
    stQuery := Copy(Memo1.Lines.Text, Start, Curr);

    // We have our Query
    Memo2.Lines.Add(stQuery);

    Start := Curr+1;
  end;
end;

 

[Henry Olive 5]

Thank you SO MUCH Kas

I'm using Delphi XE

I'm getting  Too many actual parameters in below code (it doesnt accept  Start )

Curr := Pos(';', Memo1.Lines.Text, Start);  

[Remy Lebeau 1397]

54 minutes ago, Kas Ob. said:

Pos does accept another parameter, which is where to start searching from, aka offset of the search

That offset parameter is not available in System.Pos() in XE, it was added in XE3.  For D2005-XE2, you can use StrUtils.PosEx() instead.

[Guest]

1 hour ago, Henry Olive said:

I wish everyone a healthy day

 

Memo1.Text has total 3 sql  commands like below

...

Thank You

Warning about "WrapMode = True"!!!

• Determines whether the edit control inserts soft carriage returns so that the text wraps at the right margin.

Edited December 1, 2020 by Guest

[FPiette 383]

3 hours ago, Henry Olive said:

XE doesnt recognise SPLIT

I think XE supports SplitString function in StrUtils unit.

 

[aehimself 396]

If you are willing to look into different components, Zeos has a TZSQLProcessor which does just this. You give it your full query, like

 

"Create Table A  (ID Integer);

Insert into A Values(1);

Update  A set ID=2 Where ID=1;"

 

Then it splits it up and executes them one by one.

 

Other than this, you'll have to write the parser yourself. Beware though, splitting up simply by ; is NOT GOING TO WORK.

 

Examples:

INSERT INTO MYTABLE (MYSTRINGFIELD) VALUES (";")

INSERT INTO MYTABLE (MYSTRINGFIELD) VALUES ("x") -- This is just for fun; DROP TABLE MYTABLE

and so on, you get the point...

[Remy Lebeau 1397]

27 minutes ago, FPiette said:

I think XE supports SplitString function in StrUtils unit.

Yes, it does.

[Guest]

Most Mature DACs have some kind of SQL script component.

The script component will parse the SQL script and understand when ; is swapped to ^ and vice versa.

Then you will probably have to write some event handlers.

SQL scripts being parsed by different processes and sent to a RDBMS often have "special" directives that the parser/engine/component understands (and only that) like connection and library directives.