Reactor Implementation Guide

Tags

    HistoryEdited Apr 30, 2011 11:25 AM by Sam Sehnert…

    ReactorDoSQL

    ReactorDoSQL( "SQLstatement" ) - executes the supplied SQL and returns a tab-separated list.

    Fields within the SQL statement need to be escaped. e.g.,
    My Table::My Field becomes "My Table"."My Field"

    It's not just field names that need to be escaped; If you're using literal values in your SQL, these need to be escaped correctly too. We're assuming here that these fields have their field type set corresponding to their name. e.g. 'My Timestamp' is a Timestamp field.

    Supposing you are setting the following values in SQL:

    My Table::My String = Today
    My Table::My Number = 1
    My Table::My Date = 28 Jan 2011
    My Table::My Time = 11pm
    My Table::My Timestamp = 28 Jan 2011 11pm

    This is important — trying to set a string '01/28/2010' into a Date field can give you unexpected results, as FileMaker will attempt to convert that string into a date. This can differ with your localization settings. For instance, in New Zealand, the date format is Date/Month/Year. This means that the string to date conversion wouldn't necessarily produce the expected result — particularly if the date was ambiguous, e.g., '01/02/2010'.

    The correct way to format your data is as follows:
    "My Table"."My String" = 'Today'
    "My Table"."My Number" = 1
    "My Table"."My Date" = DATE '2011-01-28'
    "My Table"."My Time" = TIME '23:00:00'
    "My Table"."My Timestamp" = TIMESTAMP '2011-01-28 23:00:00'

    This formatting applies wherever you are using these literal values - be that in an UPDATE statement, or in the WHERE part of the SQL Statement.

    In FileMaker 11, you may even get an error when trying to set a value escaped as a string into a date field, and vice-versa.

    Examples


    An efficient way to search for multiple values in the same field:
    SELECT "My Table"."My Field" FROM "My Table" WHERE ( NULL, 0, 1, 2, 3, 'etc...' ) IN "My Table"."My ID"

    A good way to blank out a field:
    UPDATE "My Table" SET "My Table"."My Field" = NULL

    A good way to exclude a record with a field value from a search:
    SELECT "My Table"."My Field" FROM "My Table" WHERE "My Table"."My Value" <> 'Exclude'

    A good way to search for empty fields:
    SELECT "My Table"."My Field" FROM "My Table" WHERE "My Table"."My Value" IS NULL

    A good way to search for fields that aren't empty:
    SELECT "My Table"."My Field" FROM "My Table" WHERE "My Table"."My Value" IS NOT NULL

    Adding sort orders to a SELECT request:
    SELECT "My Table"."My Field" FROM "My Table" WHERE "My Table"."My ID" = 'abc' ORDER BY "My Table"."My Sort" ASC

    Using FileMakers internal Record ID - Get ( RecordID ):
    SELECT "My Table"."My Field" FROM "My Table" WHERE "My Table".rowid = 123

    Using Wildcard searches:
    SELECT "My Table"."My Field" FROM "My Table" WHERE "My Table"."My Value" LIKE 'abc%'

    Using a SQL Statement with ReactorDoSQL


    If you're hard coding the SQL statement within a calculation, you'll need to wrap the whole SQL statement in double quotes, so that the calculation engine knows it's a string, and can send the SQL statement through to ReactorDoSQL for execution of the statement; (The blue highlight shows what filemaker regards as the string in each case).

    // This won't work, as the SQL string is not quoted.
    ReactorDoSQL( SELECT * FROM "My Table" WHERE "My Table"."My Status" = 'Active' )
    // This also won't work, as there are other double quotes in the middle of the statement.
    ReactorDoSQL( "SELECT * FROM "My Table" WHERE "My Table"."My Status" = 'Active'" )
    // This works correctly, because the SQL statement is correctly quoted, and and escaped.
    ReactorDoSQL( "SELECT * FROM \"My Table\" WHERE \"My Table\".\"My Status\" = 'Active'" )

    Often, its much easier to define your SQL statement in a field earlier, as this reduces the need for you to do the heavy escaping as above. If our SQL statement from before was put into a field called Home::SQL, here's how we'd use the ReactorDoSQL function:


    // No quoting needed, as we are passing through the fields contents, not the field name.
    ReactorDoSQL( Home::SQL )

    Comments

    /groups/docs/search/index.rss?sort=modifiedDate&sortDirection=reverse&tag=blackboxlist/groups/docs/search/?sort=modifiedDate&sortDirection=reverse&tag=blackboxBlackBox ControlsCustomTagSidebarCustomTagSidebar?sort=modifiedDate&sortDirection=reverse&tag=blackbox0/groups/docs/sidebar/CustomTagSidebarmodifiedDate5CustomTagSidebarreverseblackboxBlackBox Controlscustom/groups/docs/search/index.rss?tag=hotlist/groups/docs/search/?tag=hotWhat’s HotHotListHot!?tag=hot9/groups/docs/sidebar/HotListadded a to version number for consistency.samSam Sehnert2012-01-16 00:15:15+00:002012-01-16 00:15:15updated11wendyWendy Wethey2012-01-12 22:05:22+00:002012-01-12 22:05:22updated10wendyWendy Wethey2012-01-12 20:40:54+00:002012-01-12 20:40:54updated9wendyWendy Wethey2012-01-12 20:00:23+00:002012-01-12 20:00:23updated8wendyWendy Wethey2012-01-12 20:00:04+00:002012-01-12 20:00:04updated7craigCraig Saunders2012-01-11 09:24:57+00:002012-01-11 09:24:57updated6Added tag - release notescraigCraig Saunders2012-01-11 09:24:50+00:002012-01-11 09:24:50addTag5Removed tag - delcraigCraig Saunders2012-01-11 09:24:47+00:002012-01-11 09:24:47removeTag4Added tag - delcraigCraig Saunders2012-01-11 09:24:40+00:002012-01-11 09:24:40addTag3Added tag - hotcraigCraig Saunders2012-01-11 09:24:33+00:002012-01-11 09:24:33addTag2First createdcraigCraig Saunders2012-01-11 09:16:25+00:002012-01-11 09:16:25created1wiki2012-01-16T00:15:15+00:00groups/docs/wiki/d40e1FalseRelease Notes for v3.5.8a/groups/docs/wiki/d40e1/Release_Notes_for_v358a.htmlSam Sehnert11 updatesRelease Notes for v3.5.8a Reactor v3.5.8a incorporates a bunch of tweaks and fixes along with a couple of new features. The installer for Mac OS X Lion now works under non...Falsesam2012-01-16T00:15:15+00:00added applying anchorsamSam Sehnert2012-01-16 00:08:46+00:002012-01-16 00:08:46updated21samSam Sehnert2012-01-16 00:08:16+00:002012-01-16 00:08:16updated20punctuationsamSam Sehnert2012-01-16 00:05:18+00:002012-01-16 00:05:18updated19Updated imagesamSam Sehnert2012-01-16 00:04:31+00:002012-01-16 00:04:31updated18samSam Sehnert2012-01-16 00:03:01+00:002012-01-16 00:03:01updated17samSam Sehnert2012-01-16 00:02:18+00:002012-01-16 00:02:18updated16Refined instructionssamSam Sehnert2012-01-15 23:14:10+00:002012-01-15 23:14:10updated15Added tag - troubleshootingsamSam Sehnert2011-06-27 02:23:28+00:002011-06-27 02:23:28addTag14Removed tag - blackbox managementsamSam Sehnert2011-06-27 02:23:25+00:002011-06-27 02:23:25removeTag13Added tag - blackbox managementsamSam Sehnert2011-06-27 02:23:24+00:002011-06-27 02:23:24addTag12Removed tag - debugsamSam Sehnert2011-06-27 02:23:23+00:002011-06-27 02:23:23removeTag11Added tag - customizationsamSam Sehnert2011-06-27 01:40:54+00:002011-06-27 01:40:54addTag10samSam Sehnert2010-09-30 23:27:31+00:002010-09-30 23:27:31updated9Added tag - hotsamSam Sehnert2010-08-16 05:12:06+00:002010-08-16 05:12:06addTag8Removed tag - advancedsamSam Sehnert2010-08-16 05:12:03+00:002010-08-16 05:12:03removeTag7Added tag - advancedsamSam Sehnert2010-08-16 05:12:01+00:002010-08-16 05:12:01addTag6samSam Sehnert2010-08-16 03:54:43+00:002010-08-16 03:54:43updated5Added tag - debugsamSam Sehnert2010-08-08 22:32:01+00:002010-08-08 22:32:01addTag4Added tag - overridesamSam Sehnert2010-08-08 22:31:54+00:002010-08-08 22:31:54addTag3Added override manager description.samSam Sehnert2010-08-08 22:30:38+00:002010-08-08 22:30:38updated2First additionsamSam Sehnert2010-08-08 22:15:13+00:002010-08-08 22:15:13created1wiki2012-01-16T00:08:46+00:00groups/docs/wiki/f072fFalseOverride Manager/groups/docs/wiki/f072f/Override_Manager.htmlSam Sehnert21 updatesOverride Manager The Override Manager is a useful tool available in all BlackBoxes which support overrides. It will list all available overrides, and help you und...Falsesam2012-01-16T00:08:46+00:00Added anchors for lite and advanced.samSam Sehnert2012-01-15 23:18:07+00:002012-01-15 23:18:07updated12wendyWendy Wethey2011-10-03 21:53:23+00:002011-10-03 21:53:23updated11wendyWendy Wethey2011-09-01 22:58:47+00:002011-09-01 22:58:47updated10wendyWendy Wethey2011-09-01 22:53:29+00:002011-09-01 22:53:29updated9samSam Sehnert2011-06-30 07:02:03+00:002011-06-30 07:02:03updated8samSam Sehnert2011-06-30 06:52:51+00:002011-06-30 06:52:51updated7samSam Sehnert2011-06-30 05:25:13+00:002011-06-30 05:25:13updated6Added links through to lite blackbox pages.samSam Sehnert2011-06-30 04:49:43+00:002011-06-30 04:49:43updated5Added quick descriptions, but still need to link through to pages.samSam Sehnert2011-06-30 02:23:37+00:002011-06-30 02:23:37updated4Added tag - blackbox managementsamSam Sehnert2011-06-30 01:33:31+00:002011-06-30 01:33:31addTag3Added tag - hotsamSam Sehnert2011-06-30 01:33:26+00:002011-06-30 01:33:26addTag2First createdsamSam Sehnert2011-06-30 00:37:46+00:002011-06-30 00:37:46created1wiki2012-01-15T23:18:07+00:00groups/docs/wiki/5f2d4FalseBuilt in BlackBoxes/groups/docs/wiki/5f2d4/Built_in_BlackBoxes.htmlSam Sehnert12 updatesBuilt in BlackBoxes Reactor comes bundled with two types of BlackBoxes. Ones that are available with the free version of Reactor, and ones that are only available w...Falsesam2012-01-15T23:18:07+00:00Added Norwegian creditssamSam Sehnert2011-12-20 21:14:49+00:002011-12-20 21:14:49updated14Added tag - overridesamSam Sehnert2011-06-27 02:12:40+00:002011-06-27 02:12:40addTag13Added german to creditssamSam Sehnert2011-04-19 22:00:52+00:002011-04-19 22:00:52updated12Added italian localization creditssamSam Sehnert2011-04-17 22:59:47+00:002011-04-17 22:59:47updated11samSam Sehnert2011-04-15 08:30:13+00:002011-04-15 08:30:13updated10samSam Sehnert2011-04-15 08:29:54+00:002011-04-15 08:29:54updated9fix typo in sp of SwedishcraigCraig Saunders2011-04-15 02:19:30+00:002011-04-15 02:19:30updated8samSam Sehnert2011-04-15 00:44:15+00:002011-04-15 00:44:15updated7Added first documentationsamSam Sehnert2011-04-15 00:43:32+00:002011-04-15 00:43:32updated6Added tag - customizationsamSam Sehnert2011-04-15 00:09:51+00:002011-04-15 00:09:51addTag5Added tag - languagesamSam Sehnert2011-04-15 00:09:41+00:002011-04-15 00:09:41addTag4Added tag - localizationsamSam Sehnert2011-04-15 00:09:39+00:002011-04-15 00:09:39addTag3Added tag - hotsamSam Sehnert2011-04-15 00:09:31+00:002011-04-15 00:09:31addTag2First createdsamSam Sehnert2011-04-15 00:05:20+00:002011-04-15 00:05:20created1wiki2011-12-20T21:14:49+00:00groups/docs/wiki/088b5FalseBlackBox Localization/groups/docs/wiki/088b5/BlackBox_Localization.htmlSam Sehnert14 updatesBlackBox Localization We've developed a standard localization file for use with our BlackBoxes, which covers the most commonly used error messages, labels and date for...Falsesam2011-12-20T21:14:49+00:00removed beta message.samSam Sehnert2010-02-02 10:06:44+00:002010-02-02 10:06:44updated46Added tag - gantt chartwendyWendy Wethey2009-12-01 01:31:38+00:002009-12-01 01:31:38addTag45Removed tag - unfinishedwendyWendy Wethey2009-12-01 01:31:29+00:002009-12-01 01:31:29removeTag44wendyWendy Wethey2009-12-01 01:02:43+00:002009-12-01 01:02:43updated43wendyWendy Wethey2009-12-01 01:01:07+00:002009-12-01 01:01:07updated42wendyWendy Wethey2009-12-01 00:58:30+00:002009-12-01 00:58:30updated41wendyWendy Wethey2009-11-30 23:26:36+00:002009-11-30 23:26:36updated40wendyWendy Wethey2009-11-30 23:25:37+00:002009-11-30 23:25:37updated39wendyWendy Wethey2009-11-30 23:06:25+00:002009-11-30 23:06:25updated38wendyWendy Wethey2009-11-30 23:06:05+00:002009-11-30 23:06:05updated37wendyWendy Wethey2009-11-30 23:04:55+00:002009-11-30 23:04:55updated36wendyWendy Wethey2009-11-30 23:03:12+00:002009-11-30 23:03:12updated35wendyWendy Wethey2009-11-30 23:02:09+00:002009-11-30 23:02:09updated34wendyWendy Wethey2009-11-30 23:01:36+00:002009-11-30 23:01:36updated33wendyWendy Wethey2009-11-30 23:00:25+00:002009-11-30 23:00:25updated32added filter field name note.samSam Sehnert2009-11-30 02:34:32+00:002009-11-30 02:34:32updated31wendyWendy Wethey2009-11-24 01:49:03+00:002009-11-24 01:49:03updated30wendyWendy Wethey2009-11-24 01:30:28+00:002009-11-24 01:30:28updated29wendyWendy Wethey2009-11-24 01:29:47+00:002009-11-24 01:29:47updated28wendyWendy Wethey2009-11-24 01:15:57+00:002009-11-24 01:15:57updated27wendyWendy Wethey2009-10-27 04:03:11+00:002009-10-27 04:03:11updated26wendyWendy Wethey2009-10-27 03:40:10+00:002009-10-27 03:40:10updated25wendyWendy Wethey2009-10-27 03:18:03+00:002009-10-27 03:18:03updated24Final Beta notessamSam Sehnert2009-10-15 21:34:58+00:002009-10-15 21:34:58updated23craigCraig Saunders2009-10-14 09:13:14+00:002009-10-14 09:13:14updated22samSam Sehnert2009-09-15 01:57:58+00:002009-09-15 01:57:58updated21samSam Sehnert2009-09-15 01:56:58+00:002009-09-15 01:56:58updated20samSam Sehnert2009-09-15 01:56:21+00:002009-09-15 01:56:21updated19Added tag - unfinishedsamSam Sehnert2009-09-15 01:56:15+00:002009-09-15 01:56:15addTag18samSam Sehnert2009-09-15 01:44:00+00:002009-09-15 01:44:00updated17Added tag - betasamSam Sehnert2009-09-09 00:44:10+00:002009-09-09 00:44:10addTag16samSam Sehnert2009-09-09 00:44:01+00:002009-09-09 00:44:01updated15craigCraig Saunders2009-09-04 09:52:55+00:002009-09-04 09:52:55updated14added beta notessamSam Sehnert2009-09-04 04:21:58+00:002009-09-04 04:21:58updated13Added tag - extendedwendyWendy Wethey2009-09-04 04:19:53+00:002009-09-04 04:19:53addTag12Added tag - blackboxwendyWendy Wethey2009-09-04 04:19:27+00:002009-09-04 04:19:27addTag11wendyWendy Wethey2009-09-04 04:15:08+00:002009-09-04 04:15:08updated10wendyWendy Wethey2009-09-04 04:10:54+00:002009-09-04 04:10:54updated9wendyWendy Wethey2009-09-04 04:07:22+00:002009-09-04 04:07:22updated8wendyWendy Wethey2009-09-04 04:02:54+00:002009-09-04 04:02:54updated7wendyWendy Wethey2009-09-04 03:59:54+00:002009-09-04 03:59:54updated6wendyWendy Wethey2009-09-04 03:56:55+00:002009-09-04 03:56:55updated5wendyWendy Wethey2009-09-04 03:53:54+00:002009-09-04 03:53:54updated4wendyWendy Wethey2009-09-04 03:50:54+00:002009-09-04 03:50:54updated3wendyWendy Wethey2009-09-04 03:48:53+00:002009-09-04 03:48:53updated2First additionwendyWendy Wethey2009-09-04 03:45:53+00:002009-09-04 03:45:53created1wiki2011-10-10T23:25:34+00:00groups/docs/wiki/9c8ccFalseGanttChartPro/groups/docs/wiki/9c8cc/GanttChartPro.htmlWendy Wethey46 updatesGanttChartPro GanttChartPro is a complete overhaul of the GanttChartLite. It enables tasks to be viewed from a resource (e.g. person, room) pers...Falsewendy2011-10-10T23:25:34+00:00hot/groups/docs/search/index.rss?sort=modifiedDate&kind=all&sortDirection=reverse&excludePages=wiki/welcomelist/groups/docs/search/?sort=modifiedDate&kind=all&sortDirection=reverse&excludePages=wiki/welcomeRecent ChangesRecentChangesListUpdates?sort=modifiedDate&kind=all&sortDirection=reverse&excludePages=wiki/welcome0/groups/docs/sidebar/RecentChangesListmodifiedDateallRecent ChangesRecentChangesListUpdateswiki/welcomeNo recent changes.reverse5search