BE_FileMaker SQL Delimiter ?changed? accept only 1 character

[Will_Logic]

Hi, I have been using Base Elements command BE_FileMakerSQL in FileMaker script to SQL select data, and return with '<c>' as field(column) delimiter, and '<r>' as the row delimiter. The below line worked before, I dumped a whole FM database with these delimiters showing in text files. But somehow, now if I run below line, it seems to have changed to ?only allow? 1 character for the delimiter, which of course doesn't make sense anyway with varied characters in the data:

BE_FileMakerSQL ( $sql_select;  "<c>" ; "<r>" ; $fm_file )

The string this returns now looks like for example: 352265<gwgwh<474848<<wegwgw<65755<gwgw ....

i.e. it has only taken the first character of the delimiter, I changed delimiter string to test with eg "A≠" instead of "<c>", same problem, it then only inserts "A" as delimiter. Just wonder if anyone can guess what might have happened here?

Thanks kindly any suggestions!

[comment]

The documentation states clearly that the separators are single characters only.

I am not a frequent user of this function and I don't know if this is something new or if it was always like that.

You could designate a pair of special characters (characters that will never appear in the data) as the separators and use Substitute() on the result to replace them with the strings you want.

 

 

[Wim Decorte]

The native ExecuteSQL() can use multi-character delimiters, any particular reason you can't/won't use that one?

[Will_Logic]

Thanks @consultant @Wim both your comments are helpful to make me recheck, think through again and confirm not losing my mind just yet.. ExecuteSQL can use with multi character delimiters yes, unfortunately for this one executing against another script-locked FM database, so forced to use BE_FileMakerSQL. The docs do indeed state BE_FileMakerSQL only accepts 1 character delimiters. However, I  used this same code less than 2 weeks ago on other FM database and worked, I can see the text files output with <c> <r> delimiters in them. So I guess only thing is environment changed somehow.

In case anyone else sees this and struggling to find 1 character delimiter that they can guarantee won't be in the data.. I suppose for a data delimiter, you really want the most obscure character you can find..

My data is large and varied - as I was just looking main FM code window, my first best guess was: ≥ and ≠ 

Probably 50% chance working without any exception for data I have, but I speak Chinese, and then realised maybe 1 Chinese character will be accepted by FM script as a 1 character delimiter (I kept thinking unicode codepoints more than 1 character, which indeed they are), lo and behold present: 

䶫 and 䥮

which I'm fairly sure most native Chinese people won't understand without a dictionary! In fact wiktionary.org doesnt even have definition yet for either! Do have a few Chinese artwork names in this data, but even so chance of this coming up in more than 1 artwork is infinitesimal I think! Of course FM is in utf-8 coding by default, so any 1 unicode character (not code point) should work I think?

Then after this, as you say use Substitute() on result, still within the FM script, perform a couple of Substitute() lines to convert these to save to CSV format.

[comment]

1 hour ago, Will_Logic said:

In case anyone else sees this and struggling to find 1 character delimiter that they can guarantee won't be in the data.. I suppose for a data delimiter, you really want the most obscure character you can find..

A better alternative, IMHO, is to use characters designated for such purpose. Here are some you can choose from:

• Char ( 31 ) - UNIT SEPARATOR
• Char ( 30 ) - RECORD SEPARATOR
• Char ( 29 ) - GROUP SEPARATOR
• Char ( 28 ) - FILE SEPARATOR
   
• Char ( 8233 ) - PARAGRAPH SEPARATOR
• Char ( 8232 ) - LINE SEPARATOR

There are also non-characters "intended for process-internal uses":

• Char ( 65534 )
• Char ( 65535 )

 

 

[Will_Logic]

Thanks, I hadn't used Char() function in FileMaker before, it's more readable also.