[SOLVED] SQLite insert multiple record

[Pe3s]

Hello, I use sqlite database and I want to make a playlist and here the question arises, how can I add and delete many files?
Regards

Code: Pascal  [Select][+][-]

1. procedure TForm1.AddFile(AName: String);
2. begin
3.   ZQuery1.Close;
4.   ZQuery1.SQL.Clear;
5.   ZQuery1.SQL.Text := 'INSERT INTO lista(File) values(:File)';
6.   ZQuery1.ParamByName('File').AsString := AName;
7.   ZQuery1.ExecSQL;
8.  
9.   ZQuery1.Close;
10.   ZQuery1.SQL.Clear;
11.   ZQuery1.SQL.Text := 'SELECT * FROM lista';
12.   ZQuery1.Open;
13. end;                            
14.  

Code: Pascal  [Select][+][-]

1. procedure TForm1.Button1Click(Sender: TObject);
2. begin
3.   if OpenDialog1.Execute then
4.   begin
5.     AddFile(OpenDialog1.FileName);
6.   end;
7. end;
8.  

[Чебурашка]

You could modify addfile to addfiles, and make it receive a files:tstrings, where you put the list of files you want store in db (repeat insert query for each item in tstrings). Then see how to make open file dialog to get a selection of multiple files.

[cdbc]

Hi
To explain @Чебурашка's idea... 
It goes something like this:

Code: Pascal  [Select][+][-]

1. procedure TForm1.AddFiles(aFiles: TStrings);
2. var
3.   S: string;
4. begin
5.   ZQuery1.Close;
6.   ZQuery1.SQL.Clear;
7.   ZQuery1.SQL.Text := 'INSERT INTO lista(File) values(:File)';
8.   for S in aFiles do begin
9.     ZQuery1.ParamByName('File').AsString := S;
10.     ZQuery1.ExecSQL;
11.   end;
12.   ZQuery1.Close;
13.   ZQuery1.SQL.Clear;
14.   ZQuery1.SQL.Text := 'SELECT * FROM lista';
15.   ZQuery1.Open;
16. end;                            
17.  

And then you have to feed it:

Code: Pascal  [Select][+][-]

1. procedure TForm1.Button1Click(Sender: TObject);
2. begin
3.   if OpenDialog1.Execute then
4.   begin
5.     AddFiles(OpenDialog1.Files);
6.   end;
7. end;
8.  

Of course, you have to set OpenDialog1.Options to include "doMultiSelect",
in the Object Inspector... (...or something like that, can't remember exactly).
HTH 
Regards Benny

[Pe3s]

What do I need to fix to make dropping files on the form work?

Code: Pascal  [Select][+][-]

1. procedure TForm1.FormDropFiles(Sender: TObject; const FileNames: array of string);
2. var
3.   i: Integer;
4. begin
5.   for i := Low(FileNames) to High(FileNames) do
6.   begin
7.     AddFile(FileNames[i]);
8.   end;
9. end;  
10.  

[TRon]

What do I need to fix to make dropping files on the form work?

Convert your FIleNames (array of string) to a TStringList and feed that to your AddFiles() routine.

Something like:

Code: Pascal  [Select][+][-]

1. procedure TForm1.FormDropFiles(Sender: TObject; const FileNames: array of string);
2. var
3.   sl: tstringlist;
4. begin
5.   sl := TStringlsit.Create;
6.   sl.Addstrings(Filenames)
7.   AddFiles(sl);
8.   sl.Free;
9. end;  
10.  

[Pe3s]

Thank you for your help
Regards

[Zvoni]

Or if there is no "direct" User-Input (like a TEdit) you can forgo Parameters and the Loop
Untested

Code: Pascal  [Select][+][-]

1. Uses SysUtils; //For TypeHelper
2. procedure TForm1.AddFiles(AFiles: String);
3. begin
4.   ZQuery1.Close;
5.   ZQuery1.SQL.Clear;
6.   ZQuery1.SQL.Text := 'INSERT INTO lista(File) values'+AFiles;  //This results in something like INSERT INTO Lista(File) VALUES('Song0'),('Song1'),('Song2'),('Song3'),('Song4'),('Song5'),('Song6'),('Song7'),('Song8'),('Song9')
7.   ZQuery1.ExecSQL;
8.  
9.   ZQuery1.Close;
10.   ZQuery1.SQL.Clear;
11.   ZQuery1.SQL.Text := 'SELECT * FROM lista';
12.   ZQuery1.Open;
13. end;    
14.  
15. procedure TForm1.FormDropFiles(Sender: TObject; const FileNames: array of string);
16. var
17.   i:Integer;
18.   s:String;
19.   a:Array Of String;
20. begin
21.   SetLength(a,Length(FileNames));
22.   For i:=Low(FileNames) To High(FileNames) Do
23.     a[i]:='('+QuotedStr(FileNames[i])+')';  //This results in Something like ('Song0'),('Song1'),('Song2'),('Song3'),('Song4'),('Song5'),('Song6'),('Song7'),('Song8'),('Song9')
24.   s:=s.Join(',',a);        
25.   AddFiles(s);  
26. end;