Fast delete of all records via an ESS connection

[Fred_S]

Hi,

I currently have a FileMaker file loaded onto Server 19. The file has an ESS connection to a MySQL server at a different host. I need to delete all of the records in the MySQL database once a week and upload fresh data via the FM Server. I got it to work on the server using a FileMaker layout and the Delete All Records script step. I'd like to speed up the deletion of the files on MySQL server via the ESS connection. I have tried to use the ExecuteSQL script step with DELETE FROM mytable, but nothing happens. I also notice that when I try to specify the ODBC Data Source, it is empty and I can't add anything, although the Filemaker files has two connections. What am I doing wrong? Any help is appreciated.

[Søren Dyhr]

35 minutes ago, Fred_S said:

I'd like to speed up the deletion of the files on MySQL server via the ESS connection.

Provided you're willing to use a plugin: 

https://www.mbsplugins.eu/component_SQL.shtml

...as an alternative to using ESS.

--sd

[comment]

I know practically nothing about ODBC, so I cannot help you there.

But perhaps it might be sufficient to use the Truncate Table script step instead? According to the help:

Quote

This script step may be faster than the Delete All Records script step for deleting a large number of records.

 

Edited April 17, 2024 by comment

[Fred_S]

1 hour ago, comment said:

I know practically nothing about ODBC, so I cannot help you there.

But perhaps it might be sufficient to use the Truncate Table script step instead? According to the help:

 

Thanks, @comment but it does not show ESS Tables, just the internal FileMaker files.

 

 

[Fred_S]

10 hours ago, Søren Dyhr said:

Provided you're willing to use a plugin: 

https://www.mbsplugins.eu/component_SQL.shtml

...as an alternative to using ESS.

--sd

Thanks, @Søren Dyhr  MBS( "FM.ExecuteFileSQL"; ""; "DELETE FROM JitBit_Data") deletes twice as fast as the Delete All Records script step!

Now, I'm trying to figure out if there's a TRUNCATE option.

[Søren Dyhr]

1 hour ago, Fred_S said:

Now, I'm trying to figure out if there's a TRUNCATE option.

I do know next to nothing about the scenario here, but I've learned that while you directly can read and write, between the RDBS'es is it good practice instate a broker-file in between, where audit logging is made to ensure errors is caught ... to be acted upon. It's Murphys law, if anything can go wrong, it eventually does it! ...and oftentimes is it networking. All syncing of tables in different places is basically wrong when speaking of relational databases although it's done all the time.

But i wish to convey here, was that if you sync over a broker-system ... is the mirroring done pretty often, and records are never deleted, in either of the RDBS's - but just marked as not active.

--sd

Edited April 18, 2024 by Søren Dyhr

[Fred_S]

In this case, the data is not mission critical if there is an error, the process can just be restarted. We just need the most up-to-date information available on a public facing website which is driven by a MySQL database and a PHP script. Each upload is about 1500 records.