The use of leading underscore for key fields was recommended in the
FileMaker Development Conventions paper published by FMI (see page 22).
(This recommendation was made, in part, because a leading underscore causes a field to sort to the top of the list...useful when working with key fields.)
As a result, there are many systems that have fields with leading underscores.
Years later FM introduced fmSQL...which does not allow leading underscores...unless you escape them.
Q1) What is less expensive...
Renaming fields to remove the leading underscores? (dev cost)
Escaping the fields as is when building fmSQL queries? (run time cost?)
Q2) What other areas might there be where a leading underscore is problematic?
You could write JDBC logic that would use DDL (alter table) to rename fields with any leading "_" characters. Or use JDBC to do the SQL which may not have that issue at all.
I could write that JDBC logic to do the DDL in an hour or so. That code would also be generic so it would not be tied to your particular database. And, if you, say, wanted to run that code on MySQL or other database (even though those platforms don't lop that silly naming restriction on you), you could just change the DB connection and the same JDBC code would work mostly unchanged. Again, generically so the code would learn the table names and field names using Metadata classes.
Not sure that helps you, but this is, programmatically, a simple problem to solve.
"Weeks of coding can save you hours of planning."
- Unknown
When building queries to be supplied to ExecuteSQL, I always use a custom function to dynamically obtain the field name from an actual field reference. I do the same for table occurrence names.
The functions that I use for this also take care of quoting their output, which is sufficient for allowing names such as those containing leading underscores to be used within the SQL statement.
As such, the leading underscores (or most other naming conventions) don't pose any appreciable problem for me during my work, and I don't associate any appreciable runtime cost with using a custom function to determine and quote the target field/table names. The readability of the SQL in my code does suffer somewhat as a cost of obtaining the schema names dynamically, but this is a price that I consider well worth the gained protection against a schema rename breaking the functionality.
As for Q2: It's been a while since I have used them, but I would take a look at the older CWP XML API and PHP API to see if leading underscores present any problem there.
Again: Thanks for posting this. It's good to see you active here in this forum, and I always appreciate reading what you have to share.
Regarding Q1, whether I use Field Names starting in _ or not, I always use custom functions to dynamically obtain the field names as @steve_ssh suggests . That is what some call defensive coding. When you rename fields, this is something that happens, then your ExecuteSQL are note broken because a field name was changed !
Like most here, we use custom functions for both the field and table name for robust coding reasons.
However, we don’t include escaping within these. The underscore recommendations were part of a different era, which we did adhere to, but now we’d rather rename the fields, It is worth noting that we’re usually reusing our framework masters, hence we get the benefits of reusing the changes many times over.
Our personal preference, and I don’t believe there is any right or wrong here as long as some form of protection against schema or name changes breaking code.
What is less expensive is a different question altogether from what design has your preference.
To know what is less expensive, a simple DDR letting compare how many fields are prefixed in such a way vs how many calls to executeSQL your solution has will do the trick.
That said, I would argue the desirable design is the one where the fields are escaped by a custom function that will get the field name dynamically: protecting you from both a field name being renamed and a field name conflicting with SQL. (Having your SQL statement fail in a way you may not anticipate also has its cost).
@tonywhitelive, once people will have voiced their opinion, I'm still curious about what option you will go with. Please don't update us when you feel there is sufficient input to reach a conclusion.
Renaming objects can be very disruptive, so as a rule I don't want to do that. A single error here could be much more expensive that the effort needed to code this.
On the other hand, I have great confidence in the speed of computation. Until there is an obvious run time cost being caused by your field escaping routines I wouldn't consider it. And I build my queries using functions to handle table and field names, as others have done. (I'm wondering how slowly your escaping routine would have to be for users to notice ).
I never loved the 'beginning underscore', mostly because its not available on the german keyboard on the first level
We do not rename fields, we escape the SQL parts. New solutions (concerning naming) are made with respect of this - means no special characters that need escaping, no reserved words...
We have a lot of systems built over the years that have field names with leading underscores.
When we use fmSQL (which for us is rarely), we use custom functions that both quote/escape and protect against changes to field and table occurrence names.
At the moment we do not see a compelling reason to change the code that is in place.
Like many others here, we use custom functions to escape the field name. Generally, everything we do is designed in such a way that renaming a field or table occurrence will cause no breakage.
Also, as a related side comment, we use a custom function to build an entire Where clause, that can be used like this:
SQL_Where ( MyTable::MyField ; “=” ; $myVar )
That function not only escapes the field name, but also looks at the field’s type to escape/convert the value. Very useful.
Yes, yes, I know we could use “?” and add parameters to the ExecuteSQL function, but the trade-off is worth it to us.
I should clarify that this doesn’t build the “where” operator itself, so you can do the following:
“WHERE “
& SQL_Where ( MyTable::MyField1 ; “=” ; $myVar1 )
& “ AND “
& SQL_Where ( MyTable::MyField2 ; “>=” ; $myVar2 )