mr_vodka Posted April 4, 2012 Posted April 4, 2012 For those that have field names that start with and underscore such as some do when identifying key fields, the new ExecuteSQL function does not play nice with them directly. Normal escapes with a prime or quotes do not work. You must use the Quote function. ExecuteSQL ( "SELECT " & Quote ( "_kp_ID" ) & " FROM table"; "," ; ¶ ) 3
David Jondreau Posted April 5, 2012 Posted April 5, 2012 Thanks for pointing this out. Also applies if the table name starts with an underscore. And there's likely a bunch of other exceptions too.
marianco Posted April 5, 2012 Posted April 5, 2012 You also have to be careful with field names which have periods (".") in the name. This disrupts conversion from FMKR 7-11 files to the new FMKR12 format. But this may also affect other functions within Filemaker such as the new ExecuteSQL function. Looks like the days of free naming of field names and variables are over. I wish Filemaker could clarify what the naming rules are so we don't have to pull out our hair when bugs occur.
mr_vodka Posted April 5, 2012 Author Posted April 5, 2012 I wouldnt recommend naming fields with periods to being with. I wouldnt mind if FileMaker would go to stricter data types though. It would make SQL integration a whole lot easier as well as the ODBC driver not having to deal with how loose it is.
michael3785 Posted April 8, 2012 Posted April 8, 2012 Many thanks Mr Vodka. I was starting to get a bit worried about this behaviour. Having discovered ExecuteSQL I was all up for chucking out 80% of my TOs, then hit this snag as all my key fields (local and foreign) have underscores. Problem solved albeit making the expressions a bit more complex than ideal. Anyway, thanks for the very useful post. MIchael
Wim Decorte Posted April 8, 2012 Posted April 8, 2012 Looks like the days of free naming of field names and variables are over. I wish Filemaker could clarify what the naming rules are so we don't have to pull out our hair when bugs occur. Free-naming never really was an option if you had to do any kind of integration with other systems. The rules are simple and they cover pretty much everything out there: only use a-z, A-Z and the underscore in your table, TO, field, layout and script names. Don't start your object names with an underscore or a number.
bruceR Posted April 8, 2012 Posted April 8, 2012 You mention A-Z a-z and do not START names with numbers or underscores. But numbers within names are OK?
Wim Decorte Posted April 9, 2012 Posted April 9, 2012 For the most part... I have a vague memory of running into problems with numbers but I can't recall the exact circumstances.
Newbies Scott Howard Posted April 13, 2012 Newbies Posted April 13, 2012 Another issue to watch for is field names that are SQL functions. These too must be wrapped in quotes. I set up a test file to work through the syntax for querying field names with spaces and I kept getting a question mark. After much hair-pulling I wrapped my field "Year" in escaped quotes, "Year", and all worked.
Recommended Posts
This topic is 4624 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now