Unable to SQL Truncate tables in external FM database

[Will_Logic]

Hi, I have a script to truncate all the data out of the tables in an external FileMaker Database. I confirmed this works for one custom FileMaker database I tested with several tables. It uses the Base Elements plugin SQL TRUNCATE command to truncate all the data.

However, we have many databases coming in written in a custom FileMaker application, let's call it Foobar, where the script workspace is disabled for each of these. For testing purposes, we would like to truncate all data from one Foobar database. I can run Base Elements SQL SELECT commands no problem on any Foobar database from my own separate FM database which runs the script, after I enter the correct username and password. However, the SQL TRUNCATE command in this script is not working at all on the Foobar database. As I said, Foobar is an application written entirely in FileMaker, which disables the script workspace.

I enclose script below, wonder if anyone has a suggestion either a - how to get this SQL Truncate to work with Foobar database, or b - another quick way to truncate data from a Foobar database (there are about 100 tables or more in any Foobar database!). Any suggestion most gratefully received.

Set Variable [ $open_dbs; Value:DatabaseNames ]
Show Custom Dialog [ Message: $open_dbs; Default Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ]
Set Variable [ $fm_filecount; Value:ValueCount ( $open_dbs )]
Set Variable [ $count; Value:1 ]

Loop
    Set Variable [ $fm_file; Value:GetValue ( $open_dbs ; $count )]
    If [ PatternCount ( $fm_file ; "FileMaker" ) = 0 ]
        Set Variable [ $table_names; Value:TableNames ( $fm_file )]
        Set Variable [ $number_tables; Value:ValueCount ( $table_names )]
        
        Show Custom Dialog [ Title: "All Table Names This File: " & $fm_file & "¶Press cancel to Abort"; Message: $table_names; Default
        Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ]
        If [ Get(LastMessageChoice) = 2 ]
            Show Custom Dialog [ Message: "Aborting"; Default Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ]
            Exit Script [ ]
        End If
        
        Set Variable [ $table_count; Value:1 ]
        Loop
            Set Variable [ $table; Value:GetValue ( $table_names ; $table_count )]
            Set Variable [ $sql_truncate; Value:"TRUNCATE TABLE \"" & $table & "\""]
            Set Variable [ $truncate_table; Value:BE_FileMakerSQL ( $sql_truncate ; "," ; "¶" ; $fm_file )]
            Set Variable [ $table_count; Value:$table_count + 1 ]
            Exit Loop If [ $table_count > $number_tables ]
        End Loop
    Else
        Show Custom Dialog [ Title: "This database is the one running the script, so no need to truncate it:"; Message: $fm_file; Default
        Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ]
    End If
    
    Set Variable [ $count; Value:$count + 1 ]
    Exit Loop If [ $count > $fm_filecount ]
End Loop

Exit Script [ ]

 

[IdealData]

Is Foobar enabled for ODBC?

[Will_Logic]

Thanks @IdealData I just realised I can actually access the Security Settings dialogue in Foobar databases, see picture, do I edit setting here to turn on ODBC access or something? FYI, SQL SELECT statements DO work in this database already with these settings (just not SQL TRUNCATE), from a script in external FileMaker database as I tried to explain above. i wonder if I can also turn on script workspace also by editing one of these settings? Thanks yr help anyway.

[IdealData]

Aaaahh... of course you're executing an SQL command however it will use the fmapp privilege set because you're not going through the ODBC driver, it is actually an FMP command, but using the plugin. So, ODBC not required.

Must be a permissions thing.

Interesting you can access the security setting but not the script workspace - custom menus??

[Will_Logic]

Thanks @IdealData, yes there are custom menus in the Foobar app, don't have any experience of how security settings work in FileMaker.