Jump to content
Sign in to follow this  
limelect

SQL problem

Recommended Posts

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 by Attila Kovacs

Share this post


Link to post
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.

Share this post


Link to post

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

Share this post


Link to post

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

Share this post


Link to post

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

 

Edited by limelect

Share this post


Link to post

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...:classic_cool: With delphi examples: https://de.wikipedia.org/wiki/SQL-Injection

 

🙄 Always use parameters!

Edited by haentschman
  • Like 3
  • Thanks 1

Share this post


Link to post
Quote

AFAIK parameters couldn't be used inside clauses like LIKE 

...no. :classic_cool:

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 by haentschman
  • Like 2
  • Thanks 1

Share this post


Link to post

@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.

 

Share this post


Link to post

@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

Share this post


Link to post
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 

:classic_blink:

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 by haentschman

Share this post


Link to post

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;

Like.thumb.PNG.15b5b58dfafc391fc47c403f762fdfab.PNG

 

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;

Like1.thumb.PNG.0f54ce32c96d8707d9c2e6fa7696d5fe.PNG

 

...it works...i not like guessing. :classic_huh:

 

Edited by haentschman
  • Like 2

Share this post


Link to post
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;

image.png.7c1ee1c0c4f247ae558f9d79214fb5db.png
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 by Serge_G
  • Like 1

Share this post


Link to post

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 by limelect

Share this post


Link to post

You are welcome.

 

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

Share this post


Link to post

@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 by limelect

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×