SQL problem

[Attila Kovacs 629]

4 minutes ago, limelect said:

OUT OF MEMORY

guess work, because we can't see your code:

remove the fields from the query component, leave it empty, set fetchall to false, do not use "select *", fetch the blobs separately, add a key field to the table, set the keyfield in the query component

Edited November 17, 2022 by Attila Kovacs

[limelect 48]

Just now, Lars Fosdal said:

Did you try going 64-bit?

no, since it gives me other problems.

[Attila Kovacs 629]

Just now, limelect said:

P.S did you start with D1 like me? And much much before.

I wrote the battle of marathon back in 490 BC, yes.

[Sherlock 663]

Everybody cool down please, and don't get personal. Thank you.

[limelect 48]

For the time being, I will give the user a choice like or INSTR which will solve my memory problem.

Until I will come up with a different coise

[tgbs 14]

 FDQuery4.SQL.add('WHERE MainDirectory STARTING WITH ' +QuotedStr(jvDirectoryEdit1.Text))  ?

[limelect 48]

@tgbs There is a test source (zip)very simple to change in the source did you try it?

 

Edited November 17, 2022 by limelect

[limelect 48]

Error

 

I put a zip test do not guess

Edited November 17, 2022 by limelect

[haentschman 92]

by the way...

FDQuery1.SQL.add('WHERE (MainDirectory LIKE ''%' + Edit1.Text + '%'') OR INSTR(''' + Edit1.Text + ''', MainDirectory) > 0'  );

you know what SQL Injection is?

https://en.wikipedia.org/wiki/SQL_injection

The german site is better... With delphi examples: https://de.wikipedia.org/wiki/SQL-Injection

 

🙄 Always use parameters!

Edited November 18, 2022 by haentschman

[Fr0sT.Brutal 900]

11 minutes ago, haentschman said:

Always use parameters!

AFAIK parameters couldn't be used inside clauses like LIKE

[haentschman 92]

Quote

AFAIK parameters couldn't be used inside clauses like LIKE 

...no.

FDQuery1.SQL.Text := 'select * from tab where code LIKE :Code';
FDQuery1.ParamByName('code').AsString := '%123%';
FDQuery1.Open; 

...the "%" is in the parameter!

 

Other Example:

Qry.ParamByName('REN').AsString := '%[_]' + Pair.Key + '[_]' + Pair.Value + '%';

 

Edited November 18, 2022 by haentschman

[limelect 48]

@haentschman I do not see how it applies to my case

furthermore doing too much with SQL in my a[pplication

have a lot of memory problems.

My solution, for the time being, is to split

LIKE and Lars Fosdal sugestion INSTR

I could not use the statement

FDQuery1.SQL.add('WHERE (MainDirectory LIKE ''%' + Edit1.Text + '%'') OR INSTR(''' + Edit1.Text + ''', MainDirectory) > 0'  );

As it gives me memory problems.

 

[limelect 48]

@haentschman why don't you guys try this on my demo??

does not work

  FDQuery1.SQL.Text := 'select * from Files where MainDirectory LIKE :Code';
FDQuery1.ParamByName('code').AsString := '%G:\Delphi Projects\DelphiArea\DelphiComponenets\simplegraphxx%';
FDQuery1.Open;

 PLZ stop guessing

[haentschman 92]

Quote

I do not see how it applies to my case

It is a basic problem with only text in the sql statement!

 

Quote

PLZ stop guessing 

SQL.Text:
select A.ID,A.DistributorID,A.DocumentTypeString,A.DocumentCaption,A.OriginalFileName,A.SendTypeFolder, A.SendTypeUSB, A.SendTypeMail,
       A.ReceiptDate,A.ServiceDate, A.ReceiptNumber,A.Store,A.ReceiptReceiver,A.ServicePartner,
       A.AddDate,A.AddName,A.ModifiedName,A.ModifiedDate, 
       D.Name1 as StoreName,D.Name2 as StoreName_1,D.LKZ as StoreCountry,D.PLZ as StorePostCode,D.Ort as StoreLocation,
       D.Strasse as StoreStreet,D.Telefon as StorePhone,R.Name1 as ReceiverReceiverName
       from Documents A
       full outer join ADR D on A.Store = D.ixADR
       full outer join ADR R on A.ReceiptReceiver = R.ixADR
  where A.OriginalFileName like :REN
  order by ID desc
...  
Qry.ParamByName('REN').AsString := '%[_]' + Pair.Key + '[_]' + Pair.Value + '%';

is from me in my the original code. Works! (FireDAC, MSSQL)

 

 

 

Edited November 18, 2022 by haentschman

[limelect 48]

@haentschman put it in my demo and checked it in 3 lines to change that it

 

[haentschman 92]

SQL:
 

Qry := TFDQuery.Create(Self);
Qry.Connection := Self.AC;

Qry.SQL.Text := 'select * from Documents where OriginalFileName like :PAR';
Qry.ParamByName('PAR').AsString := '%1001\%';  // with "\"
Qry.Open;

 

Qry := TFDQuery.Create(Self);
Qry.Connection := Self.AC;

Qry.SQL.Text := 'select * from Documents where OriginalFileName like :PAR';
Qry.ParamByName('PAR').AsString := '%1001%';  // without "\"
Qry.Open;

 

...it works...i not like guessing.

 

Edited November 18, 2022 by haentschman

[limelect 48]

@haentschman can you plz try on my demo?

the zip is in this communication.

Otherwise, I am too tired of trying solutions.

1 solution too many 

 

[Serge_G 87]

23 hours ago, Fr0sT.Brutal said:

AFAIK parameters couldn't be used inside clauses like LIKE 

Depends of SGBD I think, but if not it's possible to, use Firedac macros

 

21 hours ago, limelect said:

the zip is in this communication.

 

1- This zip contains a SQLite database not a MySQL one as indicated first !

2- The database contains a really poor lines for testing ! And nothing to do with directories

 

Suggestion for Button2.OnClick
 

uses System.IoUtils;

procedure TForm1.Button2Click(Sender: TObject);
var FileListe : TArray<String>;
    i: integer;
begin
FileListe:=TDirectory.GetFiles(Edit1.Text);
for var s: String in FileListe
  do i:=i+FDQuery1.ExecSQL('INSERT INTO Files(maindirectory) Values (:s)',[S]);
Showmessage(i.ToString+' records added');
end;

Suggestion for Query

procedure TForm1.Button1Click(Sender: TObject);
begin
  FDQuery1.Close;
  FDQuery1.SQL.Text:='SELECT Maindirectory from files WHERE Maindirectory LIKE &S';
  FDQuery1.MacroByName('S').AsRaw:=Quotedstr(Edit1.Text+'%');
  FDQuery1.Open;
end;

As you can see, this also include ability to use macro char % in the like clause value

so for MySQL INSTR function, you can set Edit1.Text to   '%AEnvoyer'

 

TAKE CARE of SQL Injection with macro usage 
 

22 hours ago, haentschman said:

...it works

Ah, yes 👍
so Button2.Onclick can be wrote on different ways

procedure TForm1.Button1Click(Sender: TObject);
begin
// FDQuery1.Close;
// By Macro Need FDQuery1.Close and FDQuery1.Open
// FDQuery1.SQL.Text:='SELECT Maindirectory from files WHERE Maindirectory LIKE &S'; 
// FDQuery1.MacroByName('S').AsRaw:=Quotedstr(Edit1.Text+'%');
// By Parameter Need FDQuery1.Close and FDQuery1.Open
// FDQuery1.SQL.Text:='SELECT Maindirectory from files WHERE Maindirectory LIKE :p';
// FDQuery1.ParamByName('P').AsString:=Edit1.Text+'%';
// FDQuery1.Open;

// By Parameter No Need of FDQuery1.Close and FDQuery1.Open, my preference 
 FDQuery1.Open('SELECT Maindirectory from files WHERE Maindirectory LIKE :p',
               [Edit1.Text+'%']) ;
end;

 

Edited November 19, 2022 by Serge_G

[limelect 48]

First thanks to everybody. Especially @Lars Fosdal as I used his solution (INSTR)

my main problem was SQL memory problems.

I hope I fixed most of it.

Now to my specific solution

 

DataSource1.DataSet := ProjectsFDTable; RESET Grid.
FDLocalSQL1.DataSets.Clear;
FDQuery4.Close;
 FDQuery4.SQL.Clear;

 FilesFDTable.Filtered:=False;
 FilesFDTable.Filter:=
'(MainDirectory LIKE ''%' + JvDirectoryEdit1.Text + '%'') OR INSTR(''' + JvDirectoryEdit1.Text + ''', MainDirectory) > 0' ;
  FilesFDTable.Filtered:=True;

  FDLocalSQL1.DataSets.Add(FilesFDTable);
  FDLocalSQL1.Active := True;
  ProjectsFDTable.LocalSQL:=FDLocalSQL1; << otherwise I had problems

  FDQuery4.SQL.add('Select SectionsId,MainDirectory,MyDateTime,ProjectName,ProjectrealName,Category,FileMask ,Description,FilesIndex');
  FDQuery4.SQL.add('from FilesFDTable,Projects');
  FDQuery4.SQL.add(' WHERE SectionsId=FilesIndex'); 
  FDQuery4.Open;<< use  Query on the grid

 DataSource1.DataSet := FDQuery4;

 

1. I added  LocalSQL to my  ProjectsFDTable;

2. I filter my file table. (NO memory problem) as per  @Lars Fosdal  suggestion with OR

3. I added to my LocalSQL FDLocalSQL1.DataSets.Add(FilesFDTable); after filtering

4. I query   FDQuery4.SQL.add('from FilesFDTable,Projects'); 

 

Now no memory problems  OR until the next problem on a large software.

And thanks again

Edited November 21, 2022 by limelect

[Lars Fosdal 1792]

You are welcome.

 

So, SQLite wants to load a lot of data unless you filter?  Good to know.

[limelect 48]

@Lars Fosdal If I used the logic on SQL I had a memory 

problem I used the table to filter with the OR logic. For example

I got 3 records out of 750. Using these records I did the SQL with

SectionsId=FilesIndex' and this did it.

For the time being.

 

It seems that using tables (if you can) is better at least in my case.

Edited November 21, 2022 by limelect