I'm just gearing up to using fmp12's magnificent new ExecuteSQL command, and am considering how best to exploit it.
1. What are good field and record delimiters to use?
I am looking for field and record delimiters that can (almost virtually) always be used. Kind of "one size fits all", so that the functions that process the SQL-results can be generic (see Q2).
The standard tab (Char(9)) and "¶" return characters are nice for debugging and easy-to-read, quick-results tables, but seeing as both of these characters occur in the data that I'll be reading with the ExecuteSQL command, they are not suitable for all queries.
For example, what about any of the codes within the ASCII 0-0x1F range?
What about Char( 31 ) and Char( 30 )?
Here is a table of the low ASCII-values and some contenders:
|0x0000||0||NULL||NUL||␀||ctrl-@||0||often used for "End of String"|
|0x0001||1||START OF HEADING||SOH||␁||ctrl-A|
|0x0002||2||START OF TEXT||SOT||␂||ctrl-B|
|0x0003||3||END OF TEXT||EOT||␃||ctrl-C|
|0x0004||4||END OF TRANSMISSION||EOT||␄||ctrl-D|
|0x0008||8||BACKSPACE||BS||␈||ctrl-H||See Wikipedia: ASCII for discussion of Backspace/Delete chars|
|0x000A||10||LINE FEED||LF||␊||ctrl-J||A line feed is used to delimit records when exported as tab-delimited on a mac|
|0x000B||11||VERTICAL TABULATION||VT||␋||ctrl-K||v||A new line (FileMaker "¶") in a field is encoded as a vertical tabulation when exported as tab-delimited|
|0x000D||13||CARRIAGE RETURN||CR||␍||ctrl-M||FileMaker "¶"|
|0x0010||16||DATA LINK ESCAPE||DLE||␐||ctrl-P|
|0x0011||17||DEVICE CONTROL ONE||DC1||␑||ctrl-Q||(a.k.a. XON)|
|0x0012||18||DEVICE CONTROL TWO||DC2||␒||ctrl-R|
|0x0013||19||DEVICE CONTROL THREE||DC3||␓||ctrl-S||(a.k.a. XOFF)|
|0x0014||20||DEVICE CONTROL FOUR||DC4||␔||ctrl-T|
|0x0017||23||END OF TRANSMISSION BLOCK||ETB||␗||ctrl-W|
|0x0019||25||END OF MEDIUM||EM||␙||ctrl-Y|
|0x001D||29||GROUP SEPARATOR||GS||␝||ctrl-]||The Group Separator character is used as FileMaker repetition delimiter when exported as tab-delimited|
|0x007F||127||DELETE||DEL||␡||ctrl-?||See Wikipedia: ASCII for discussion of Backspace/Delete chars|
|0x2424||9252||SYMBOL FOR NEWLINE||NL||␤||-||A unicode character symbolising a "newline"|
Does anybody have any suggestions or experience on which delimiter-characters/strings are good and may be suitable?
2. What are good methods of processing large SQL-text results?
I notice that there is very limited text processing in the supported SQL (no substitute or replace functions).
Thus I am wondering what the best way is to process the results of the SQL command, for example:
- a function to return column i of a multi-column result, or
- a function to convert all returned numbers to german formatting with comma instead of a dot.
I am aware of the limitations of custom function calls to 10000 (or 50000 for CFs using end recursion), and am thus considering other methods, as these limits may well be reached.
Does anybody have experience or suggestions with good (reliable and quick) plugins, shell scripts or other techniques?
3. And finally, what are good uses of the ExecuteSQL command?
I can see very good opportunities to use ExecuteSQL in the following areas:
- Generation of chart-data
- Generation of multiline keys
- Reminders (avoiding required TO-context or layout-hopping)
- TO-context-independent querying of user-privileges, thus avoiding layout-changing or spaghetti-graphs
Any other good ideas?
Thanking you for your helpful answers!
Greetings, as ever, from Hamburg in Germany
Message was edited by: mrwatson-gbs