1 Topic by ljhurtado (edited by ljhurtado 2018-04-13 15:13:23)

  • Registered: 2017-06-23
  • Posts: 50

Topic: UPDATE in a field type FILE

Hi,
I have a field of type FILE and a DBFile object configured as Type Linkfile. In the database I already have approximately 2,000 records with a path to the files. Example:\\192.168.5.6\ Documents\ File000001.pdf, ... \\192.168.5.6\ File001945.pdf
The network manager changed the network address by \\172.25.3.5\ Documents. Something I did not expect. Now I have to change the address
\\192.168.5.6 in all registers to \\172.25.3.5. I can do it one by one, but it would take me a long time. How can I do it with a script?
Thanks

  • Registered: 2015-02-24
  • Posts: 446

Re: UPDATE in a field type FILE

Hello ljhurtado,


You can use an SQL query to do this.


I don't remember the exact name of the field generated by MVD for the file path, but suppose it is called my_file_path and you table is called documents, you can do it like this :

SELECT REPLACE(my_file_path,'192.168.5.6','172.25.3.5') FROM documents

I suggest you do it with a SQL tool rather than code a function in MVD to do it.
I also suggest you make a backup of you database file before you proceed smile


Have a nice day


Cheers


Math

I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor
  • Registered: 2017-06-23
  • Posts: 50

Re: UPDATE in a field type FILE

Thanks for your suggestion but I already tried it but it does not work, because it's a calculated field. The field, type FILE, is called "viaDocument" and when creating the calculated field it is named as viaDocument_filename, but this field is updated when the path and file are selected through the DBFile object.

  • Registered: 2014-02-14
  • Posts: 10,294

Re: UPDATE in a field type FILE

Check it out

SQLExecute('UPDATE tablename SET file_filename = REPLACE(file_filename, ''192.168.5.6'', ''172.25.3.5'')');

You should use network name of computer instead IP, to avoid a similar problem in the future.

Dmitry.
  • Registered: 2017-06-23
  • Posts: 50

Re: UPDATE in a field type FILE

I attached the project

https://drive.google.com/file/d/1Tk434_ … sp=sharing

  • Registered: 2014-02-14
  • Posts: 10,294

Re: UPDATE in a field type FILE

Check it out

procedure Form1_Button4_OnClick (Sender: TObject; var Cancel: boolean);
begin
     SQLExecute('UPDATE Documentos SET viaDocumento_filename = REPLACE(viaDocumento_filename, ''F:\MVD Proyectos\Pruebas\lj\Documents\'', ''F:\NewPath\'')');
     Form1.TableGrid1.dbUpdate;
end;
Dmitry.
  • Registered: 2017-06-23
  • Posts: 50

Re: UPDATE in a field type FILE

DriveSoft wrote:

Check it out

procedure Form1_Button4_OnClick (Sender: TObject; var Cancel: boolean);
begin
     SQLExecute('UPDATE Documentos SET viaDocumento_filename = REPLACE(viaDocumento_filename, ''F:\MVD Proyectos\Pruebas\lj\Documents\'', ''F:\NewPath\'')');
     Form1.TableGrid1.dbUpdate;
end;

Sorry but the code you propose does not work because the field "viaDocumento_filename" is a calculated field that is updated when I modify or update  the field "viaDocumento" through the visual component DBFile on Form2. If I manually UPDATE the calculated field "viaDocument_filename", when I want to see the document by clicking on the DBFile visual component, it will not open.
What I need is to UPDATE the field "viaDocumento", with the new path, but with the UPDATE command, so as not to do it one by one through the visual DBFile component in form2.

Thank you very much.

  • Registered: 2014-02-14
  • Posts: 10,294

Re: UPDATE in a field type FILE

It's work. I have tested.

Dmitry.
  • Registered: 2017-06-23
  • Posts: 50

Re: UPDATE in a field type FILE

What a pity. It worked for me. One last question for now: I need to use the REPLACE function with variables like this:

procedure Form1_Button4_OnClick (Sender: TObject; var Cancel: boolean);
var
  cadenainicial : string;
  cambiarpor : string;

begin
     cadenainicial   := form1.Edit1.Text;
     cambiarpor := form1.Edit2.Text;

     SQLExecute('UPDATE Documentos SET viaDocumento_filename = REPLACE( viaDocumento_filename, cadenainicial, cambiarpor)');
     Form1.TableGrid1.dbUpdate;

end;

But it shows an error message: "No such column: cadenainicial"

Thanks for everything

  • Registered: 2014-02-14
  • Posts: 10,294

Re: UPDATE in a field type FILE

Check it out

procedure Form1_Button4_OnClick (Sender: TObject; var Cancel: boolean);
var
  cadenainicial : string;
  cambiarpor : string;
begin
     cadenainicial := ''''+form1.Edit1.Text+'''';
     cambiarpor := ''''+form1.Edit2.Text+'''';

     SQLExecute('UPDATE Documentos SET viaDocumento_filename = REPLACE( viaDocumento_filename, '+cadenainicial+','+ cambiarpor+')');
     Form1.TableGrid1.dbUpdate;
end;
Dmitry.
  • Registered: 2017-06-23
  • Posts: 50

Re: UPDATE in a field type FILE

I am very grateful for your help