Choosing Delimiters for Use with the fm_sql_select Function

Posted May 29th, 2008 by grantwood

Hello,

What column and row delimiters do you personally use with the fm_sql_select function? It might be tempting to use a return as the row delimiter, but if some of the data contains returns (notes, for example), then using list-related functions like GetValue and ValueCount will cause problems.

Similarly, when choosing a column delimiter, you have to be careful not to use a character that appears in any of the data. Some candidates might be the pipe character, the caret, or the tilde, but all of those are pretty common IMO.

So it sounds like a good idea to pick obscure characters. Which characters do you use?

Regards,
Sean

Choosing Delimiters for Use with the fm_sql_select Function

On May 30th, 2008 Micah Woods says:

Hello Sean,

Funny you should ask, I just ran into the same issue recently. In my case, I was able to ensure that no pipe or return characters were present. I did take the extra step to add an auto-enter calc to a comment field so that pipes are removed and returns are replaced with a space. The other fields I was selecting were number fields and so for those I added field validation to only allow numeric values.

In the future, I think I'll try character 161 (¡) for columns and 191 (¿) for rows. I personally have never used these characters and I'm guessing it would be OK to automatically remove them in most cases (using an auto-enter calc for this does a great job).

You can of course take your chances that the obscure characters you choose won't be used but then there's no guarantee that things will always work. To be safe, I think you have to take the extra step to ensure that your delimiters won't be present in the fields you're selecting.

There's a handy reference for ASCII characters here:

http://www.web-source.net/symbols.htm

Let me know if you come up with a solution you like.

Regards,
Micah

more delimiters...

On June 1st, 2008 sethellis says:

We've had to deal with this as well. If I can guarantee there are no pipe symbols in our text, I use pipes for field delimiters, as it makes the raw text easier to decipher in a text editor. However, we have multi-line text in abundance, so using carriage returns for record delimiters won't work.

I've always found that non-typable ascii chars are best, if the system lets you get away with them.

For my current project, I've settled on ascii STX for fields, and ascii ETX for records:

fm_sql_select($query, 002, 003);

It's straightforward to manipulate the components using explode and implode:

$a_records = explode("\003", $table_data);

$a_values = explode("\002", $record);
$record= implode("\002", $a_values);

Remember to double-quote the ascii value, so it isn't treated as a literal. YMMV depending on buffer limits without carriage returns, but so far I haven't hit any problems with the plug-in.

(As an aside, if fm_sql_select allowed multi-character delimiters, the returned data could be more readable in a text editor.)

rick

Thanks

On June 4th, 2008 Micah Woods says:

Thanks Rick, I hadn't though to use those characters and that seems like a safe bet and it's really great not to have to make any table changes (to ensure delimiters don't exist in the data).

Regarding multi-character delimiters, I'm afraid we're limited by the function provided by FileMaker's plug-in API.

Regards,
Micah

A Partial Solution Perhaps

On June 3rd, 2008 grantwood says:

@rick, @micah

I have perhaps a partial solution, and it leverages rick's use of non-typable ASCII characters. I would be happy to explain what I've developed so far. It's just that I would need a few days to write something up, but here's an overview. After performing a query, using ascii(2) and ascii(3) as column/row delimiters per rick's suggestion, I manipulate the returned data using PHP into the following format:

[DELIM]row1.field1[DELIM][DELIM]row1.field2[DELIM]...[DELIM]row1.fieldN[DELIM][RETURN]
[DELIM]row2.field1[DELIM][DELIM]row2.field2[DELIM]...[DELIM]row2.fieldN[DELIM][RETURN]
:
:

Each piece of data is sandwiched between a chosen delimiter, which can be a "regular" one, like the pipe character. I've been using this format more and more in my solutions, like when passing multiple parameters to a script. An example query might return:

|George||Bush||president of the United States|
|Mickey||Mouse||Disney character with his own club|
|Peter||Parker||superhero|

Then I loop thru this return-delimited list, using the GetValue function, and extract the data as needed.

If any piece of data contains returns and/or delimiters, I substitute random strings, and then perform reverse substitutions when looping thru the return-delimted list.

If Peter Parker's notes field contained "student|photographer|superhero.[RETURN]Lives with his Aunt May.", then the data returned would look like:

|George||Bush||president of the United States|
|Mickey||Mouse||Disney character with his own club|
|Peter||Parker||student[SUB1]photographer[SUB1]superhero.[SUB2]Lives with his Aunt May.|

[SUB1] and [SUB2] can be any random string of any length (longer lengths are better), just as long as neither string contains a return or a delimiter.

I hope the above makes some sense.

Regards,
Sean

A Partial Solution Perhaps

On June 4th, 2008 Micah Woods says:

Hello Sean,

Seems like a good solution. I've typically used HTML entities like ¶ as my "random" strings. The key is to use something that won't be found in the data.

Using an array as mentioned by Rick works great as long as you stay in PHP, the problem seems to be when you return the text string back to FM.

If you just need to iterate through the data back in your FM script, you could use the "keepMemory" param with PHP_Execute and that would allow you to make multiple trips back to PHP and grab the next row from any array, etc. As long as keepMemory is set to 1, the array will remain available.

Thanks for the example.

Regards,
Micah

My SQL.Select Custom Function

On June 4th, 2008 grantwood says:

OK, here's the latest version of a custom function I use to do SQL queries against FileMaker data.

I have a number of related custom functions that serve as constants, which help with abstraction:
Const.COL_DELIM = "|"
Const.ROW_DELIM = "¶" // SmartPill maps this to \r?
Const.COL_DELIM_SUB = "k51U1J7KQS4F8pM5sqDakb9Ph"
(doesn't have to be alphanumeric)
Const.ROW_DELIM_SUB = "6LAnYkdI2LtSfmVCmhZfBslMJ"
Const.SQL_COL_DELIM = 2 (per Rick)
Const.SQL_ROW_DELIM = 3 (per Rick)

=================================
SQL.Select ( _query ; _colDelim ; _rowDelim )
=================================

Let (
[
$fm_query = _query ;

// setting default delimiters. Just call SQL.Select ( $query ; "" ; "" ) to use defaults
$fm_colDelimSQL = Case ( not IsEmpty ( _colDelim ) ; _colDelim ; Const.SQL_COL_DELIM ) ;
$fm_rowDelimSQL = Case ( not IsEmpty ( _rowDelim ) ; _rowDelim ; Const.SQL_ROW_DELIM ) ;

_code =
"
$query = fm_evaluate('$fm_query');¶
$colDelimSQL = fm_evaluate('$fm_colDelimSQL');¶
$rowDelimSQL = fm_evaluate('$fm_rowDelimSQL');¶
$colDelim = fm_evaluate('Const.COL_DELIM');¶
$rowDelim = fm_evaluate('Const.ROW_DELIM');¶

$results = fm_sql_select($query, $colDelimSQL, $rowDelimSQL);¶
$error = fm_get_last_error(1);¶

// returning a return-delimited list to the FileMaker calling script, so I can take further action. The first value is the error code.¶
if($error != 0) {¶
echo $error . \"\n\" . fm_get_last_error();¶
} else {¶
// creating an array from the search results. Using the chr() function since $rowDelimSQL is an ascii value¶
$resultsArray = explode(chr($rowDelimSQL), $results);¶

// for each value in the array, we are 'encoding' any returns and delimiters with random strings¶
$resultsArray = str_replace($rowDelim, fm_evaluate('Const.ROW_DELIM_SUB'), $resultsArray);¶
$resultsArray = str_replace($colDelim, fm_evaluate('Const.COL_DELIM_SUB'), $resultsArray);¶

// for each value in the array, we are adding the sandwich characters¶
$resultsArray = str_replace(chr($colDelimSQL), $colDelim . $colDelim, $resultsArray);¶

// the above step produces something like: field1||field2||field3¶
// we need to add sandwich characters to the beginning and end of each value in the array¶
// not sure if array_walk() is the best function to use. Maybe using preg_replace() would be better¶
array_walk($resultsArray, create_function('&$value, $key, $delim', '$value = $delim . $value . $delim;'), $colDelim);¶

echo $error . \"\n\" . implode(\"\n\", $resultsArray);¶
}¶
"
] ;

PHP_Execute( _code )
)

--
Regards,
Sean Mills
Grantwood Technology LLC
http://www.grantwoodtechnology.com

Sample Video

On June 5th, 2008 grantwood says:

FYI, I'm using the above technique in a video series I'm creating for FileMaker Magazine. I created a basic overview movie, and even though I kinda ramble, you'll see that your SmartPill plugin plays a key role.

Overview Video

--
Regards,
Sean Mills
Grantwood Technology LLC
http://www.grantwoodtechnology.com

Sample Video

On June 13th, 2008 Micah Woods says:

Always great to have SmartPill mentioned, thanks. We've done a lot of work on localization but our approach is a bit different. It's a bit complicated to explain but perhaps we can chat at DevCon if you're going?

Regards,
Micah

Devcon

On June 13th, 2008 grantwood says:

Yeah, I'm attending this year. I'm definitely interested in how you are approaching localization. Do you have any sample databases you could email me?

--
Regards,
Sean Mills
Grantwood Technology LLC
http://www.grantwoodtechnology.com

Devcon

On June 18th, 2008 Micah Woods says:

Catch up with me at DevCon and I'll be happy to show you what we've done. It's for a client so I can't share it.

Regards,
Micah