9 Replies Latest reply on Nov 21, 2012 3:08 PM by mrwatson-gbs

    ExecuteSQL: best delimiters, best practice, best use?

    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

        • 1. Re: ExecuteSQL: best delimiters, best practice, best use?
          mrwatson-gbs

          Wow - here is an excellent "FM ExceuteSQL Reference" resource: http://www.filemakerhacks.com/?p=6605

          • 2. Re: ExecuteSQL: best delimiters, best practice, best use?
            beverly

            Thanks, Mr Watson! The article, PDF and examples, don't address your questions, though.

             

            1. What are good field and record delimiters to use?

            any character(s) not in your fields. I'm always cautious using characters that are invisible or non-printable. You seem to have made a good list to test!

             

             

            2. What are good methods of processing large SQL-text results?

              I suppose that really depends on what you want to do with the TEXT results. FileMaker has some really good parsing functions and the article points to some custom functions that may help.

             

             

            3. And finally, what are good uses of the ExecuteSQL command?

              anywhere you can use TEXT results, format/parse as desired. And the question of speed vs. "native" scripts and functions may also play a factor. The context-independence is my biggest +1.

             

            Beverly Voth

            • 3. Re: ExecuteSQL: best delimiters, best practice, best use?
              mrwatson-gbs

              Thank you for the reply Beverly, and nice work!

               

              You are probably right about the invisible characters.

               

              In your PDF you say that the ROUND SQL Function doesn't do it's job right, but it works fine in my examples. You might want to check if it has been corrected in the latest 12.0v3 :-)

               

              Happy programming!

               

              MrWatson

              • 4. Re: ExecuteSQL: best delimiters, best practice, best use?
                wimdecorte

                I tend to avoid low ascii characters, some plugins don't work very well when they come across those.  Keep in mind that your delimiter does not have to be one character, it can be a phrase.  That's what I find myself using because it's more guaranteed to be unique.  Something like "||zzzFieldDelimzzz||"

                • 5. Re: ExecuteSQL: best delimiters, best practice, best use?

                  In addition to Beverly's great article, this is fabulous resource for ExecuteSQL().

                   

                  http://fmforums.com/forum/topic/83765-solutions-sql-builder/

                  • 6. Re: ExecuteSQL: best delimiters, best practice, best use?
                    beverly

                    The ROUND() sql function does not "pad" with zeros, as I had expected, if they don't exist.

                    I just tested with the fm12v3 update and still get the same results:

                     

                    ExecuteSQL() ROUND TESTS:

                    ====

                    ROUND(14.57,2)=>

                    14.57

                    ROUND(1234,2)=>

                    1234

                    ROUND(34356,-3)=>

                    34000

                    ROUND(98765.345,-1)=>

                    98770

                    ROUND(345.3/100,2)=>

                    3.45

                     

                    FileMaker Round() tests:

                    ======

                    Round(14.57;2)=>

                    14.57

                    Round(1234;2)=>

                    1234

                    Round(34356;-3)=>

                    34000

                    Round(98765.345;-1)=>

                    98770

                    Round(345.3/100;2)=>

                    3.45

                     

                    SO... the results from the ROUND SQL function is the same as the Round() FileMaker Function. I had just expected it to append 0's (1234 rounded 2 decimal places would be 1234.00, so I could get the formatting - alignment - that I wanted). But that's not the case here.

                     

                    NOTE the use of comma (,) in the SQL and the semi-colon ( in the FileMaker function. Otherwise pretty much the same.

                     

                    I just work in several other applications, where "round(num,decimal)" does pad....

                     

                    Beverly

                    • 7. Re: ExecuteSQL: best delimiters, best practice, best use?
                      jrenfrew

                      Beverly

                       

                      Isnt that the job of a decimal tab??

                       

                      John

                      • 8. Re: ExecuteSQL: best delimiters, best practice, best use?
                        beverly

                        Yes. For display that works well. Several people are wanting to format for reports that just look better with the decimals lined up and the same number of decimal places (zero padded, if necessary). I just expected round() to do that. Wishful thinking

                         

                         

                        -- sent from my iPhone4 --

                        Beverly Voth

                        --

                        • 9. Re: ExecuteSQL: best delimiters, best practice, best use?
                          mrwatson-gbs

                          Thank you Beverley, Wim et al for the good tips!

                           

                          Let me clarify the discussion a little, by defining my objectives, which guide the delimiters I shall choose:

                           

                          1. Only characters should be used that work/are visible on both Mac and Windows platforms.
                          2. Only characters should be used that work with the functions/plugins that process the SQLResult.
                          3. The SQLResult should LOOK (at least a bit) like a table, so that it is still possible for humans to read - and debug - the  SQLResult. Thus:
                            1. The delimiters should not be too visually disturbing. E.g. Where possible they should be made of symbol characters, that are easily recognisable as delimiters, not data.
                            2. The delimiters should not be too long.
                            3. Rows should start on a new line, if this is possible.
                            4. If possible, field columns should be orderly.
                          4. The delimiters should be so construed, that processing operations, such as extracting a column, or converting to an HTML table, or parsing the data, are straightforward.
                            1. It should be as easy as possible to encode or process any ¶s embedded in the data
                            2. It should be as easy as possible to encode or process any TABs embedded in the data
                          5. The delimiters should, of course, be (as good as) guaranteed to be unique.

                           

                          The trick is to find the right balance of short, visual, processability and likely uniqueness ... and that is exactly the point of this discussion.

                           

                          An idea: What about using the LINE FEED character, Code(10), in the row-delimiter?

                           

                          At the minute I am considering using a line feed character, Code(10), as part of the RowDelimiter.

                           

                          This seems to have a number of advantages:

                           

                          • It achieves objective 3.3: the rows are displayed in a table-like way on separate lines.
                          • It achieves objective 4:
                            • Any ¶s present in the data can simply be removed or encoded using Substitute( $SQLResult ; "¶" ; "…whatever…" )
                            • Rows can nevertheless be accessed via the GetValue function, pretty much as normal.
                          • I believe it also achieves objective 5, because a Char(10) should never occur in data. At least not in normal DB-usage, but I would be happy on comments on this bit.
                            • I shall have further characters in the RowDelimiter, so that SHOULD a Char(10) appear in the code, I can detect which are DATA and which are DELIMITER.

                           

                          One disadvantage, is that one mustn't forget to either remove the line feeds or convert them to ¶s.

                           

                          Any comments on this idea?