How to get all Field Names of external table using BE_FileMakerSQL

[Will_Logic]

Hi, I may be running out of lives soon, but after Comment's very helpful answers to previous, have a possibly final sticking point. I want to return all the Field Names in a given external FMP12 table, ie again I must use BE_FileMakerSQL function  or something else, as ExecuteSQL won't work.

Following produces error (returns '?'):

BE_FileMakerSQL ( "SELECT FieldName FROM FileMaker_Fields WHERE TableName = " & Quote($table) ; "," ; ¶ ; $fm_file )

So I assume that FileMaker_Fields is accessible to Execute SQL but not to the Base Elements plugin function BE_FileMakerSQL.

Following doesn't work either, trying the standard SQL query to get field(column) names from a database table:

BE_FileMakerSQL ( "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = " & Quote($table) ; "," ; ¶ ; $fm_file )

So I assume information_schema table is beyond the range of Base Elements SQL commands perhaps? I note from the ZenDesk Base Elements website, there was a function  BE_FileMaker_Fields, which has since been deprecated and then removed, looks like this would have done what I need - https://baseelementsplugin.zendesk.com/hc/en-us/articles/205350587-BE-FileMaker-Fields

Otherwise, the script function FieldNames will only take a FileMaker layout name as parameter, not a table name.

Maybe I could download a previous version of Base Elements just to get the BE_FileMaker_Fields function, but then some other stuff in my code might not work as requires latest version.

Thanks any suggestions?

[comment]

8 minutes ago, Will_Logic said:

So I assume that FileMaker_Fields is accessible to Execute SQL but not to the Base Elements plugin function BE_FileMakerSQL.

Before you jump to such wide-reaching conclusion, open any file and plug this into your data viewer:

BE_FileMakerSQL ( "SELECT * FROM FileMaker_Fields" )

I believe you will see the same thing that I do: that your assumption is wrong. So that means you have a syntax problem. And, AFAICT without seeing what's in your variable, the problem is that you are passing the table name as a string - so it needs to be enclosed in single quotes, not double quotes. IOW, the result of evaluating the query expression should be the following string:

SELECT FieldName FROM FileMaker_Fields WHERE TableName='YourTableName'

--
P.S. I find it's easiest to construct the query in a text field first and test it from there. Once I have it working, I can work on the expression that will return the exact string I have in my text field and that I know will work. 

 

[Will_Logic]

Great thanks so much again Comment! So for my query where I want a variable table name, this does work:

BE_FileMakerSQL ( "SELECT FieldName FROM FileMaker_Fields WHERE TableName = '" & $table & "'" ; "," ; ¶ ; $fm_file )

where $table must be a table name, and $fm_file must be an open FMP12 file name (database name). Another mistake I made was to leave $table inside Quote() function, which adds extra " double quotes which breaks it. In this case, even if the table name begins with a digit, as we're already enclosing in single quotes as a value inside SQL expression, it will be treated as string not integer (welcome correct me if wrong anything there! this my understanding anyway). However, when you want select * from $table you need the Quote() function round the table variable in case the table name begins with integer, as Comment advised before.

FYI, I checked following MySQL type query doesn't work, but as above does, don't need it:

BE_FileMakerSQL ( "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS" ; "," ; ¶ ; $fm_file )

Thanks for tip on breaking up the code also, used to do in Python, getting used to how to do this in FileMaker(!), so I guess this is indeed easier to follow:

Set Variable [$sql_query_string; Value: "SELECT FieldName FROM FileMaker_Fields WHERE TableName = '" & $table & "'"]

and use this string to construct BE command. Thanks kindly.

[comment]

10 minutes ago, Will_Logic said:

In this case, even if the table name begins with a digit, as we're already enclosing in single quotes as a value inside SQL expression, it will be treated as string

I haven't tested that.

 

12 minutes ago, Will_Logic said:

I guess this is indeed easier to follow

I don't think you need another variable for this. You can use the Let () function for better readability. In fact, you might find this style even more convenient:

Let ( [
template = "SELECT FieldName FROM FileMaker_Fields WHERE TableName='<<tableName>>'" ;
sqlStatement = Substitute ( template, "<<tableName>>" ; $table ) ;
columnSeparator = "," ;
rowSeparator = ¶ ;
databaseName = $fm_file
] ;
BE_FileMakerSQL ( sqlStatement ; columnSeparator ; rowSeparator ; databaseName ) 
)