BE_FileMakerSQL: Select All from Variable Table Name

[Will_Logic]

Hi again, very grateful for help on last question with this Base Elements function. Now I am trying to cycle through all tables in an external .fmp12 file, and SQL select all from each table.
I want to get external data from tables in an application built in FileMaker, which disables the script space in that database. As mentioned in my other topic 105263 here, this involves opening the tables with Open File step, which will then ask me for user and password which I have. Once the files are open, script can access tables with BE_FileMaker SQL function (but not with ExecuteSQL), as my script is running from a different file.

For a static table name, this code works when $fm_file is set to relevant database:

Set Variable [$sql_select; Value: BE_FileMakerSQL ( "SELECT * FROM ITEM" ; "," ; ¶ ; $fm_file )]

and I get a non-null result set in the string.

But now I want to set ITEM as a variable table name, and don't know how to get the right syntax. I tried following, query returns ? for same table and database as above:

Set Variable [$sql_select; BE_FileMakerSQL ( Evaluate ( Quote( "SELECT * FROM "&$table_name&"" )) ; "," ; ¶ ; $fm_file )]

Thanks kindly any suggestions or comments.
(Fyi looked at this page for some hints about using Quote and Evaluate, not sure if need Evaluate here or not: https://community.filemaker.com/en/s/question/0D50H00006h99Tu/how-to-pass-down-variables-in-befilemakersql )

Actually, I'm not clear yet if the BE_FileMakerSQL() function and FileMaker native ExecuteSQL() function take exactly the same syntax, not sure if this is relevant here though.

 

 

 

[comment]

Perhaps I am missing something here, but ...  the SQL query is nothing more than a text string. If you know the name of the table, you can enter it as literal text; if you have it in a variable, you can simply concatenate the variable to the rest of text, so that instead of:

"SELECT * FROM ITEM"

you have:

"SELECT * FROM " & $table_name

Adding both Quote() and Evaluate() to the mix does not accomplish anything. Evaluate ( Quote ( text ) ) returns the original text unchanged. However, if the name of the table does not begin with an alphabetical character, you must enclose it in double quotation marks. Here, the Quote() function can be helpful:

 "SELECT * FROM " & Quote ( $table_name )

 

So to summarize, setting the variable to:

BE_FileMakerSQL ( "SELECT * FROM " & Quote ( $table_name ) ; "," ; ¶ ; $fm_file )

should work for you.

 

Edited October 27, 2019 by comment

[Will_Logic]

Yes this works best, thanks! It didn't help I had got variable name of my table wrong though..