Reactor4へのアップグレード方法

You’ll likely need to make changes to your custom BlackBoxes to work correctly with the new Plugin, and FM13. We’re now using FileMakers updated SQL engine, which is much more strict in terms of data types, and expects your queries to be formatted differently.

Here’s the basic list of changes you’ll need to watch out for:

DATE ‘2013-12-12’ becomes CAST(‘2013-12-12’ AS DATE)
TIME ’00:00:00’ becomes CAST(‘00:00:00’ AS TIME)
TIMESTAMP ‘2013-12-12 00:00:00’ becomes CAST(‘2013-12-12 00:00:00’ AS TIMESTAMP)

You’ll also need to make sure ALL values for fields used in WHERE clauses, UPDATE statements, or CREATE statements have the correct escaping.

Formatting for different FileMaker field types:

‘text’ type fields must be escaped using single quotes (the SQL engine won’t typecast at all).

Correct:
SELECT `table`.`field` FROM `table` WHERE `table`.`field` = ‘1234’;
SELECT `table`.`field` FROM `table` WHERE `table`.`field` = ‘2013-12-12’;

Incorrect:
SELECT `table`.`field` FROM `table` WHERE `table`.`field` = 1234;
SELECT `table`.`field` FROM `table` WHERE `table`.`field` = CAST( ‘2013-12-12’ AS DATE);

‘number’ type fields must be unescaped, and international numbers must be normalised to use a ‘.’ as the decimal separator.

Correct:

SELECT `table`.`field` FROM `table` WHERE `table`.`field` = 1234;
SELECT `table`.`field` FROM `table` WHERE `table`.`field` = 1.5;

Incorrect:

SELECT `table`.`field` FROM `table` WHERE `table`.`field` = ‘1234';
SELECT `table`.`field` FROM `table` WHERE `table`.`field` = ‘1.5';

SELECT `table`.`field` FROM `table` WHERE `table`.`field` = 1,5;

‘date’, ’time’ and ’timestamp' type fields must be CAST as DATE, TIME and TIMESTAMP respectively.

Correct:
SELECT `table`.`field` FROM `table` WHERE `table`.`field` = CAST(‘2013-12-12’ AS DATE)
SELECT `table`.`field` FROM `table` WHERE `table`.`field` = CAST(’00:00:00’ AS TIME)
SELECT `table`.`field` FROM `table` WHERE `table`.`field` = CAST(‘2013-12-12 00:00:00’ AS TIMESTAMP)

Incorrect:

// Date
SELECT `table`.`field` FROM `table` WHERE `table`.`field` = ‘2013-12-12’
SELECT `table`.`field` FROM `table` WHERE `table`.`field` = DATE ‘2013-12-12’
SELECT `table`.`field` FROM `table` WHERE `table`.`field` = CAST(‘2013-12-12 00:00:00’ AS TIMESTAMP)

// Time
SELECT `table`.`field` FROM `table` WHERE `table`.`field` = '00:00:00’
SELECT `table`.`field` FROM `table` WHERE `table`.`field` = TIME '00:00:00’
SELECT `table`.`field` FROM `table` WHERE `table`.`field` = CAST('2013-12-12 00:00:00’ AS TIMESTAMP)

// Timestamp
SELECT `table`.`field` FROM `table` WHERE `table`.`field` = ‘2013-12-12 00:00:00’

SELECT `table`.`field` FROM `table` WHERE `table`.`field` = TIMESTAMP ‘2013-12-12 00:00:00’

The FRToolBox’s FRTB.find, .update, .select, methods etc will automatically determine the fields data-type and escape any data correctly. If you’re passing through an SQL string for the .where method, you’ll need to make sure that it’s escaped correctly. FileMakers internal rowid is considered a Number type field.

The older FRTB_FM* methods will not automatically determine field types, and your SQL parts will need to be manually updated.