If you got "?" as return, it means there is error (most case in syntax), not "not found".
You get an empty string.
Isempty ( executeSQL (. ). )
would be true if no records were returned.
That is what I thought, but I was unsure.
Thanks very much.
Put EvaluationError() around the ExecuteSQL().
If you get 0 no error.
If you have FMPAdvanced you can error check this way:
Sent from miPhone
1 of 1 people found this helpful
Do yourself a HUGE favor and get a real SQL tool. The maddening and totally unhelpful "?" drives me nuts.
Using a real SQL tool, like RazorSQL to connect to your LIVE FMP database you would get:
1. SQL content assist - query pop-up "tool tips"/"intellinsense" as you type your query.
2. ACTUAL ERROR MESSAGES!
3. Ability to actually "View Data" in columns! (not just a few little rows at the bottom where you have to scroll)
4. Be able to resize respective windows (horizontally, vertically) , change fonts, etc.
5. Do INSERTs, UPDATEs, and DELETEs as well as SQL
6. No need (initially) to use ExecuteSQL. Just type regular select statements.
7. Browse tables and edit data directly.
8. Ability to connect to ANY JDBC-enabled (read: all of them) databases
9. A well supported developer product.
10. Other stuff....
Once you get your query working against your LIVE FMP database, THEN move it back to FMP and add replaceable parameters and such.
IMHO, for a multi-hundred dollar tool (>$500 for FMPA), the data viewer is really more of a calculation checker. FMP should have an actual capability to view data. The "Data Viewer's" teeny tiny window with non-resizable windows, non-changable fonts, no error messages, no content assist is (again, IMHO) a weak part of the product and needs a major re-write. (That's why I call it a "Calculation Checker").
(Many Java IDEs have a "Data pane" where you can connect to your FMP database too in real time). FOR. FREE.
Hopefully FMP 16, or 17 will get a much needed rewrite of the "Data Viewer".
HOPE THIS HELPS.
the data viewer is really more of a calculation checker.
In actual fact, that's exactly what the data viewer is when using the watch expression. It was created before ExecuteSQL was an option and was for the purpose of checking calculations. It's very handy for that purpose, though you've mentioned some improvements here that would be very nice. For something like ExecuteSQL, it's not as useful. They really need a native SQLBuilder utility that does pretty much what you have spelled out, plus:
- Graphic relationship building in order to define joins. (Not an actual change to the relationships in Manage | database, but just within the tool to define what's needed for the join.)
- The ability to drop in real field references that are referenced by their ID numbers (created internally when the field, table and table occurrences are created) rather than as a text based name reference. Such a query then would not break should a developer rename something via Manage | Database at a later date.
Phil ... you're the best!
well stated pmj!