Jump to content

BE_FileMaker SQL Delimiter ?changed? accept only 1 character


This topic is 1438 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 )

 

 

  • Like 1
  • Thanks 1
Link to comment
Share on other sites

This topic is 1438 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.