Get physical data path in ADS

Some server management function in Advantage Database Server require the physical file name of the table including the local path. One example could be to get all users of a specific table so you can disconnect them if you require exclusive table access for table maintenance. Unfortunately there’s nothing built into the server. Calling the function database() in SQL just returns the connection path from the client and not the server path.
During discussions with a customer the idea was born to create a table with a specific name and filter the result set of the system procedure sp_MgGetAllTables by that name. In order to be most flexible, the specific table name is built using the NewIdString() function which creates a GUID (global unique identifier).
Since a variable can’t be used as table name, all has to be put into a string which is then executed by using the EXECUTE IMMEDIATE command.
Here’s the resulting stored procedure:

CREATE PROCEDURE GetPhysicalPath(databasepath MEMO OUTPUT)
BEGIN
  DECLARE @sql STRING;
  DECLARE @table STRING;
  @table='T'+NewIdString(N); --create a unique name starting with T
  @sql='DECLARE @c CURSOR AS SELECT * FROM ['+@table+']; '
      +'DECLARE @s STRING; '
      +'DECLARE @i INTEGER; '
      +'CREATE TABLE ['+@table+'](l LOGICAL) AS FREE TABLE; ' --create the table
      +'OPEN @c; ' --open a cursor which selects from that table
      +'FETCH @c; '
        --get the full name of the table including physical location
      +'@s=(SELECT tablename FROM (EXECUTE PROCEDURE sp_MgGetAllTables()) a '
      +'      WHERE tablename LIKE ''%'+TRIM(@table)+'%''); '
      +'@i=POSITION('''+TRIM(@table)+''' IN @s); '
      +'@s=SUBSTRING(@s, 1, @i-1); ' --remove the table name from the path
      +'INSERT INTO __output(databasepath) VALUES (trim(@s)); ' --return the path
      +'CLOSE @c; '
      +'DROP TABLE ['+@table+']';
  EXECUTE IMMEDIATE @sql;
END;

Usage of the procedure:

EXECUTE PROCEDURE GetPhysicalPath()