mrwatson-gbs

ExecuteSQL: best delimiters, best practice, best use?

Discussion created by mrwatson-gbs on Nov 20, 2012
Latest reply on Nov 21, 2012 by mrwatson-gbs

Hallo folks,

 

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:

 

HexASCIINameShortUnicode Charctrl-keyEscapedComment
0x00000NULLNULctrl-@0often used for "End of String"
0x00011START OF HEADINGSOHctrl-A

0x00022START OF TEXTSOTctrl-B

0x00033END OF TEXTEOTctrl-C

0x00044END OF TRANSMISSIONEOTctrl-D

0x00055ENQUIRYENQctrl-E
Question...
0x00066ACKNOWLEDGEACKctrl-F
...Answer="Yes"
0x00077BELLBELctrl-Ga'ting!'
0x00088BACKSPACEBSctrl-H See Wikipedia: ASCII for discussion of Backspace/Delete chars
0x00099HORIZONTAL TABULATIONHTctrl-I TAB
0x000A10LINE FEEDLFctrl-JA line feed is used to delimit records when exported as tab-delimited on a mac
0x000B11VERTICAL TABULATIONVTctrl-KvA new line (FileMaker "¶") in a field is encoded as a vertical tabulation when exported as tab-delimited
0x000C12FORM FEEDFFctrl-L
0x000D13CARRIAGE RETURN CRctrl-MFileMaker "¶"
0x000E14SHIFT OUTSOctrl-N

0x000F15SHIFT INSIctrl-O

0x001016DATA LINK ESCAPEDLEctrl-P

0x001117DEVICE CONTROL ONEDC1ctrl-Q
(a.k.a. XON)
0x001218DEVICE CONTROL TWODC2ctrl-R

0x001319DEVICE CONTROL THREEDC3ctrl-S
(a.k.a. XOFF)
0x001420DEVICE CONTROL FOURDC4ctrl-T

0x001521NEGATIVE ACKNOWLEDGENAKctrl-U
...Answer="No"
0x001622SYNCHRONOUS IDLESYNctrl-V

0x001723END OF TRANSMISSION BLOCKETBctrl-W

0x001824CANCELCANctrl-X

0x001925END OF MEDIUMEMctrl-Y

0x001A26SUBSTITUTESUBctrl-Z

0x001B27ESCAPEESCctrl-[e
0x001C28FILE SEPARATORFSctrl-/

0x001D29GROUP SEPARATORGSctrl-]
The Group Separator character is used as FileMaker repetition delimiter when exported as tab-delimited
0x001E30RECORD SEPARATORRSctrl-^

0x001F31UNIT SEPARATORUSctrl-_

------






0x002032SPACESP-

0x007F127DELETEDELctrl-?
See Wikipedia: ASCII for discussion of Backspace/Delete chars
------






0x24249252SYMBOL FOR NEWLINENL-
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
  • Statistics
  • Generation of multiline keys
  • Calenders
  • 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

 

Mr. Watson

 

Message was edited by: mrwatson-gbs

Outcomes