DoSQL 2

From myFMbutler wiki
Jump to: navigation, search

Main Page > DoSQL 2

Contents

What does it do?

myFMbutler DoSQL is a FileMaker® Pro plug-in for Windows and OSX that allows you to to manipulate your FileMaker data directly from FileMaker calculations.

The plug-in can execute all SQL statements as defined in the ODBC and JDBC Guide published by FileMaker Inc.

FileMaker is a very user-friendly database program, and we normally manipulate data manually or through scripts. But under the hood, FileMaker can be driven through SQL statements as well.

Allthough the above mentioned document describes how to use SQL to access and modify data in a FileMaker database from outside of FileMaker - through ODBC or JDBC - the same FileMaker SQL engine is available as well through FileMaker's plug-in API.

myFMbutler DoSQL is a plug-in that makes the FileMaker SQL engine - also called the FQL engine - available to FileMaker developers, so they can read, write and delete FileMaker data from within their solutions using SQL.

Version History

2.1.3.0 (19-Jan-16)

  • Fixed an issue with 64-bit
  • Fixed an issue with the registration code

2.1.0.0 (26-May-15)

  • Rewrite of the preference dialog from Carbon to Cocoa, to be compatible with Filemaker 14 client on OSX.
  • Compiled with FileMaker 14 plug-in SDK ( 55 )

2.0.0.30 (13-May-13)

2.0.0.30 ( unreleased beta ) adds 3 new functions:

  • mFMb_DoSQL_ShowProgressWindow( windowTitle ; informativeText { ; progressIndex { ; showCancelButton } } )
  • mFMb_DoSQL_HideProgressWindow
  • mFMb_DoSQL_UpdateProgressWindow( progressIndex { ; informativeText { ; windowTitle { ; showCancelButton } } } )

2.0.0.28 (05-Apr-13)

  • Added a function mFMb_DoSQL_ExecutionTime( { queryID } ) to benchmark stuff.
  • Going 64-bit
  • Expanded the dataselectors in mFMb_DoSQL_Version(), mainly for testing the 64-bit server version and understanding the loading sequence from the different extension locations.

2.0.0.26 (05-Mar-13)

  • Version 2.0.0.26 fixes a bug in mFMb_DoSQL_Parameter( <index> ) returning an error 8 ( empty result due to invalid index )

2.0.0.25 (11-Feb-13)

  • Initial Version

Features and benefits

  • create, edit and delete records without having to use lots of scripts and layouts
    • using buttons, with direct result
    • using conditional formatting
    • in tooltips
    • in access privilege calculations
    • in custom menus
    • in schema auto-enter, validation and unstored calculations
    • in custom functions
    • from any script step that has a calculation option
    • from the FileMaker Advanced Data Viewer
    • to easily provide data for complex FileMaker graphs
    • in webviewers
  • limit the number of tables, relationships, fields, and scripts
  • use DoSQL for access and modification logging or record rollback functionality
  • use DoSQL to create customizable portal filters
  • use DoSQL to create script parameters with the correct data type
  • create and drop temporary tables, rebuild indexes
  • keep your code compact and efficient
  • doesn’t require any drivers
  • runs on OSX and Windows, client and server, 32-bit and 64-bit, using Instant Web Publishing, Custom Web Publishing, Server Side Scripts, etc.

Requirements

  • FileMaker Pro 13 or FileMaker Pro 13 Advanced.
  • FileMaker Pro 12 or FileMaker Pro 12 Advanced.
  • FileMaker Pro 11 or FileMaker Pro 11 Advanced.
  • When installing on the server, FileMaker Server 11 or later
  • Windows XP or later, or OSX 10.5.8 Intel or later
  • Version 1.3.1 is for earlier versions 9 and 10, the OSX "fat binary" version runs on Intel and PPC machines.

Installation

Installing DoSQL on a FileMaker Pro or FileMaker Advanced client

FileMaker Pro 15 or FileMaker Advanced 15 client

Use the installer file that comes with the plug-in.

If you want to know the exact location of the installed plug-in:

For FileMaker Pro and FileMaker Pro Advanced 15.0, find all information here: FileMaker Answer ID 10097


FileMaker Pro 14 or FileMaker Advanced 14 client

Use the installer file that comes with the plug-in.

If you want to know the exact location of the installed plug-in:

For FileMaker Pro and FileMaker Pro Advanced 14.0, find all information here: FileMaker Answer ID 10097

FileMaker Pro 13 or FileMaker Advanced 13 client

Use the installer file that comes with the plug-in.

If you want to know the exact location of the installed plug-in:

For FileMaker Pro and FileMaker Pro Advanced 13.0, find all information here: FileMaker Answer ID 10097

FileMaker Pro 12 or FileMaker Advanced 12 client

Use the installer file that comes with the plug-in.

If you want to know the exact location of the installed plug-in:

For FileMaker Pro and FileMaker Pro Advanced 12.0v1 and 12.0v2, find all information here: FileMaker Answer ID 10097

For FileMaker Pro and FileMaker Pro Advanced 12.0v3 and higher versions, use this URL: FileMaker Answer ID 11239

mFMb_DoSQL_Version( "13" ) will return the path of the installed plug-in, to avoid all this confusion.

FileMaker Pro 11 or FileMaker Advanced 11 client

On Windows, make sure to quit FileMaker first if it is running.

Then copy the appropriate version of the plug-in to the Extensions folder of the FileMaker application or to the Application Data folder:

C:\Documents and Settings\[user]\Local Settings\Application Data\FileMaker\Extensions (on Windows XP)
C:\Users\[user]\Local Settings\Application Data\FileMaker\Extensions (On Windows Vista/7)

This is a cool way, it works by using a Windows environment variable

%APPDATA%\..\Local\FileMaker\Extensions

On OSX, copy the appropriate version of the plug-in to the Extensions folder of the FileMaker application or to the Application Support folder:

~/Library/Application Support/FileMaker/Extensions/

On OSX Lion and later, the Library folder is hidden from every day use. You can still access it easily of you know the trick: just hold down the command and option key File:Commandoption.png while having the Go menu in the Finder open.

LibraryFolder.png

In order to remove the plugin, quit FileMaker and remove the plugin file from the same location.

If there are still plug-in in your application extensions folder, now is a good time to move them to this new location.

The application extensions directory was the original place for FileMaker extensions, but this turned out to give a lot of problems with user rights and upgrades.

Don't move any plug-ins that are application version specific, like the AutoUpdate plug-in or the XDBC plug-in.

Installing DoSQL on FileMaker Server

FileMaker Server 13

Enable the plug-in for server side scripts

On OSX, copy the plug-in into:

 /Library/FileMaker Server/Database Server/Extensions/

You will see the XDBC plug-in there already if you have a FileMaker Server Advanced.

On Windows, put the plug-in here:

C:\Program Files\FileMaker\FileMaker Server\Database Server\Extensions\

Enable the plug-in for web publishing and FileMaker WebDirect

To enable a web publishing solution to use a plug-in, place a copy of the plug-in on the machine running the Web Publishing Engine. On OS X, copy the plug-in here:

/Library/FileMaker Server/Web Publishing/publishing-engine/cwpc/Plugins/

On Windows, you need to put it here:

C:\Program Files\FileMaker\FileMaker Server\Web Publishing\publishing-engine\cwpc\Plugins\


The FileMaker Admin Console does not control enabling or disabling IWP plug-ins. After entering

fmsadmin restart WPE -y

from the command prompt on the server, the plug-in will be available for use.

This is not needed when you install the plug-in from a script that runs from a WPE session.

On OS X, there can be a problem with file permissions when installing the plug-in manually. Enter the following at the command line:

chmod g+rx <filepath>

or

chmod g+wrx <filepath>

FileMaker Server 12 (Advanced)

Enable the plug-in for server side scripts

On OSX, copy the plug-in into:

 /Library/FileMaker Server/Database Server/Extensions/

You will see the XDBC plug-in there already if you have a FileMaker Server Advanced.

On Windows, put the plug-in here:

C:\Program Files (x86)\FileMaker\FileMaker Server\Database Server\Extensions\

If your Windows version is not 64-bit yet, ignore the " (x86)" part.

Log in to the Admin console and enable the plug-in in Configuration->Database Server->Server Plug-Ins

EnableServerPlugin.png

In all releases before and including Server 12.0v3 that support server side scripts from the admin console, this admin console does not reflect the situation correctly.

You might have added plug-ins and they don't show, you might have updated plug-ins and they still show the wrong version from the admin console.

In all server versions before 12, the only remedy is to restart the server service, which is most likely undesired, because all connected users must of course be kicked out of the server if you do this, and you will lose a few friends.

From FileMaker Server 12 on, there is this neat little trick that lets you overcome that problem. Page 184 of the

FileMaker Server PDF manual or

http://<server ip>:16000/admin-help/en/wwhelp/wwhimpl/common/html/wwhelp.htm#context=fmshelp&file=fmsh_cmdref.19.15.html

describes how to restart only parts of the FileMaker Server.

Restart the FMSE ( the FileMaker Script Engine ), any running server side script will be aborted. Then restart ADMINSERVER, which is the part that serves your admin console.

So you enter these commands from a terminal console on the server:

fmsadmin restart FMSE -y
fmsadmin restart ADMINSERVER -y

You will then have to relaunch your admin console if it was running while you restarted the ADMINSERVER, but the plug-in list should be correctly displayed after that. Do not forget to enable it, newly installed FMSE plug-ins will not be enabled by default, even when using the "Install Plug-In" script step from a server side script. ( situation 12.0v3 ).

The FileMaker server service will happily continue to serve all connected users.

Unfortunately, the gracetime option -t is not available when you restart FMSE or ADMINSERVER, so make sure you are not interrupting a running script on the server or another person using the admin console.

Enable the plug-in for web publishing

On OSX, copy the plug-in here:

/Library/FileMaker Server/Web Publishing/publishing-engine/wpc/Plugins/

On Windows, you need to put it here:

C:\Program Files (x86)\FileMaker\FileMaker Server\Web Publishing\publishing-engine\wpc\Plugins\

Ignore the " (x86)" if you are not installing on a 64-bit system.

The FileMaker Admin Console does not control enabling or disabling IWP plug-ins. After entering

fmsadmin restart WPE -y

from the command prompt on the server, the plug-in will be available for use.

This is not needed when you install the plug-in from a script that runs from a WPE session.

Enable the plug-in for custom web publishing

This feature works from 12.0v2.

On Windows, you need to put the mfmb_DoSQL.fmx64 file here:

C:\Program Files\FileMaker\FileMaker Server\Web Publishing\publishing-engine\cwpc\Plugins

On OSX, out the plug-in here:

/Library/FileMaker Server/Web Publishing/publishing-engine/cwpc/Plugins

The FileMaker Admin Console does not control enabling or disabling CWP plug-ins. After entering

fmsadmin restart WPE -y

from the command prompt on the server, the plug-in will be available for use.

This is not needed when you install the plug-in from a script that runs from CWP ( e.g. PHP ) code.

Enabling DoSQL server side debug logging

We decided to use the same principle that is used on the client side. The DoSQL log file is located in the logs folder of the FileMaker Server user.

On OSX, this user is "fmserver", so this is:

 /Library/FileMaker Server/Library/Logs/myFMbutler/DoSQL.log

You will have to give yourself enough privileges to access that file. The /Library/FileMaker Server/ is the home directory of the fmserver account, the FileMaker Server runs under that account.

On Windows, things are a bit different. FileMaker Server standardly runs as the system account. A local system account does not have a profile folder, but we can change the account under which the FileMaker Server service runs.

  • Create a new user e.g. "fmserver", and give this user administrative rights on the local machine. Create a safe password and remember it.
  • Stop the FileMaker Server service using the Services control panel item.
  • Change the login of the service to the "fmserver" account and enter the password twice.
  • Start de FileMaker Server service.

EnableFMServerLogon.png

You will now see an additional user folder called "fmserver". On a typical Windows 7 system for example, the log will be created in

C:\Users\fmserver\AppData\Roaming\myFMbutler\DoSQL\

Of course, the log file is not created immediately, but right after you start logging in your FMSE, IWP, WebDirect or CWP scripts.

Example Files

We have supplied some basic example files with the plug-in distribution.

Some other more advanced examples are in the making, and will probably release through the myFMbutler blog.

DoSQL Reference

Important: parameters between {} are optional.

Executing SQL

mFMb_DoSQL( sqlString { ; fileName { ; returnResult { ; executeOnIdle } } } )

This is the main function of the DoSQL plug-in.

sqlString contains the SQL statement you want to execute. A basic knowledge of SQL syntax is required, as opposed to the functions that will follow.

optional parameters

If you omit the fileName parameter you are able to execute an SQL command in the currently focused file. This file is the file that has the frontmost window.

If that parameter given, you can execute the SQL on any file that is open already. You can specify the file name with or without the extension.

If you want to execute your SQL in the front window, but you also want to use any of the other parameters, just pass Get ( FileName ) to this parameter, or just omit it, and specify the next parameter(s).

You can optionally return the result of an SQL SELECT immediately as a bunch of text using True on "returnResult", or retrieve the results later using the appropriate functions, which is more accurate if you are retrieving multiple fields.

If returnResult is True, you get your results as a list of values, where each row is separated by default by a comma, and each row is separated by default by a return ( a ¶ in FileMaker speak ).

This default behaviour can be altered by changing these separators using mFMb_DoSQL_SetColumnSeparator and mFMb_DoSQL_SetRowSeparator.

When not returning a result immediately, you can get the result using mFMb_DoSQL_Result.

The default for "returnResult" is True, to remain compatible with DoSQL 1.

There is one exception where setting returnResult to True still results in a result of the correct data type, as opposed to always text. This is when the result of a SELECT returns only 1 row and 1 column. So the result of a DoSQL ( "SELECT…" ) or a DoSQL_Select() can be a text, a number, a date, a timestamp and even a container field content.

For backward compatibility, there is a "tweak" function.

mFMb_DoSQL_Configure( "output1resultastext" )

will make DoSQL 2 behave like DoSQL 1 and always return text, even when returning only one row and one column.

mFMb_DoSQL_Configure( "output1resultnatively" )

is the default setting and wil configure the DoSQL plug-in to return a one-row-one-column result in its native type.

If you have older development using DoSQL 1 and you do not want it to break, it's a good idea to put the mFMb_DoSQL_Configure( "output1resultastext" ) in the startup script.

Be careful not to concatinate something else with the result of your SQL selection, as you will cast it into text.

if( 1=0 ; "Welcome to the Twilight Zone" ) & mFMb_DoSQL ( "SELECT…" )

will always return text, even when the result is only 1 row and 1 column and the the result of the if() function wil never return any result. Instead, use Let() constructions e.g.

Let ( void = mFMb_DoSQL_SetParameter ( "A" ) ; mFMb_DoSQL ( "SELECT * FROM \"myTable\" WHERE \"myField\" = ?" ) )

Important: performing DoSQL with the returnResult parameter to False will still return the number of records found if you use a SELECT statement.

If you are using DoSQL with the returnResult False parameter, any error will also be directly returned. Those errors always begin with "ERROR", followed by the reason of the error.

The last optional executeOnIdle parameter lets you queue the SQL command to be executed on FileMaker idle. This means that the command will be executed when FileMaker is not busy doing scripts or calculations. The default for "executeOnIdle" is of course False.

If you want to use the executeOnIdle parameter, you have to use the returnResult parameter as well. DoSQL does not use the returnResult parameter, but needs it to know that you are using 2 boolean parameters, so it can make the deduction that the second parameter is the executeOnIdle parameter.


Later in this reference, we simply call the "mFMb_DoSQL" function the DoSQL function.

New DoSQL functions with simplified syntax

Constructing SQL statements with FileMaker 12's ExecuteSQL function or myFMbutler's DoSQL function can result in SQL string calculations that are difficult to construct and read, especially if you are trying to write those calculations in a rock solid way, and you are using the GetFieldName() function to softcode FileMaker field names.

These calculation string become even uglier when you are using SQL injection parameters, you know, the "?" signs inside your SQL do not make it easier to read either.

We decided to make a few convencience functions, for simple SQL statements. As plug-ins are not able to obtain a field name from a passed parameter ( custom function can! ) we still need to use GetFieldName(), but things become a lot easier to read and construct, after some intitial training…:-)

After looking at them below, come back here and learn about an additional tweaks you can make.

These functions were initially performing parameter type checking, before passing the resulting SQL string to the FileMaker FQL engine.

This gave us extra error messages if we did something wrong, e.g. "Parameter 3 and parameter 4 datatypes do not match" etc. The plug-in queried the FileMaker schema tables for each call, and with larger FileMaker templates with lots of occurrences, this "feature" began to seriously slow down the function execution.

Therefore, data type checking is not enabled anymore by default, resulting in performance comparable to the standard DoSQL() function.

To enable data type checking, you can use:

mFMb_DoSQL_Configure( "checkdatatypes" )

and to disable it back again, you can use:

mFMb_DoSQL_Configure( "nocheckdatatypes" )

This can be convenient when debugging.

mFMb_DoSQL_Insert( fieldName ; value { ; fieldName ; value { ; ... } } )

This function simplifies the INSERT sql statement. A basic SQL rule is that you can insert only in one table at a time, so every field name you pass has to come from the same table occurrence.

Compare these two ways of doing things:

mFMb_DoSQL_SetParameters ( 23 ; $data ; myOtherTabel::myField ) &
mFMb_DoSQL (
	"INSERT INTO " & mFMb_DoSQL_Table ( GetFieldName ( myTable::myField ) ) &
	" ( " &
	mFMb_DoSQL_Field ( myTable::myFieldName1 ; True ) & ", " &
	mFMb_DoSQL_Field ( myTable::myFieldName2 ; True ) & ", " &
	mFMb_DoSQL_Field ( myTable::myFieldName3 ; True ) &
	" ) " &	
	"VALUES ( ?, ?, ? )"
)
)
mFMb_DoSQL_Insert (
	GetFieldName ( myTable::myField1 ) ; "23" ;
	GetFieldName ( myTable::myField2 ) ; $data ;
	GetFieldName ( myTable::myField3 ) ; myOtherTable::myField1
)

As you can see, the 2nd way is easier and faster to write down and easier and faster to read as well.

There is no need for parameter preparation, and you don't have to remember the exact syntax of an SQL INSERT statement.

DoSQL_Insert() is a formatting function. The DoSQL plug-in first parses the fields you are passing to it, checks them, and then constructs an SQL INSERT statement.

It then passes that SQL statement to the DoSQL master function. So the results and error handling are the same, the functions that return the last error number and the last SQL work as well after this function call.

It's important that you are passing the fields with their occurrence, the first fieldName parameter will be checked for its table name, the value parameter and the rest and rest of the parameters is not checked, but you still should provide the fieldNames in the form "occurrence::field".

optional parameters

Just continue to add fieldName and value parameters at your heart's desire.

      • IMPORTANT NOTE***

Add a global field to your table that has an auto-enter calculation that simply refers to the... primary key field. Each time a new record is created, this global field will contain the primary key of the last created record on your FileMaker client, even when using the DoSQL_Insert() function. This is a VERY easy way to get a reference to your created record, as the SQL INSERT command does not return anything useful besides an error code.

mFMb_DoSQL_Update( fieldName ; value { ; fieldName ; value { ; ... } } { ; WHERE { key ; value { ; key ... } } )

Updates table records, setting fieldName parameters to value parameters where key parameters equal value parameters . An example:

mFMb_DoSQL_Update (
	GetFieldName ( myTable::myField1 ) ; 23 ;
	GetFieldName ( myTable::myField2 ) ; $data ;
	GetFieldName ( myTable::myField3 ) ; myOtherTable::myField1 ;
	"WHERE" ;
	GetFieldName ( myTable::myField1 ) ; 22
)

The table occurrence of the first parameter is used as the targetted table.

optional parameters

There are 2 sections of optional parameters.

The first one is where you keep adding fieldName - value pairs, extending what you want to update.

The second one is after the WHERE clause, where you keep adding key - value pairs, narrowing the selection of records you want to update.

mFMb_DoSQL_Delete( fieldName ; value { ; fieldName ; value { ; ... } } )

Deletes records where fieldName equals value. You can optionally define different pairs, but remember to get the field names from the same table.

As with the Update function, the table occurrence of the first parameter is checked and used as the targetted table.

mFMb_DoSQL_Delete (
	GetFieldName ( myTable::myField1 ) ; 22
)
optional parameters

Just keep adding fieldName - value pairs, narrowing the selection of records you want to delete.

mFMb_DoSQL_Select( { DISTINCT ; } data ; { ; data ... } { ; WHERE ; key ; value { key ; … } } { ; GROUP BY ; fieldName ; ...} { ; ORDER BY ; fieldName ; … } { FOR UPDATE } )

There are a whole bunch of options on this function, and still, it doesn't cover all the things you can possibly do with a SELECT statement. But we believe that it covers a very big part, and will be enough in most cases to perform SQL SELECT commands without the hassle of writing the SQL syntax.

FileMaker does not like a plug-in prototype with quoted parameters, so in fact you need "DISTINCT", "WHERE", etc. unless you make custom functions that produce that output as a string. The custom function for update() for example works perfectly, even the space is allowed in the custom function name.

optional first parameter

The first parameter is optional. Setting "DISTINCT" as the first parameter wil remove the doubles from the first column of your result.

required parameter

What follows then is the data you want to retrieve. Data can be field names or literal values. Field names must be formatted as "occurrence::field", so DoSQL can see 2 things in every field name parameter: the table name and the field name.

It's wise to wrap the GetFieldName() function around your field name parameters, just in case their names would change afterwards. This is called "soft coding" your field names.

DoSQL recognizes a field by the "::" inside the field name. Any other values are treated as literal parameters. So be sure to avoid "::" in literal parameters, unless you are willingly specifying a field reference in a hard coded way.

more optional parameters

You can keep adding data parameters for every field name or literal values you want to retrieve.

The "WHERE" parameter marks the end of the this part, which we call the SELECT part, but it's not required. If you don't use it, you just get every record of the table.

You then continue with key-value pairs for this WHERE part These can be field references or literals.

Specifying these key-value pairs narrows down your selection of records.

When DoSQL parses the field name parameters in this WHERE part, it now knows which table names it has to use in the FROM clause of the SQL statement it is constructing for you.

So you don't have to worry about that part yourself.

If you used several different occurrences, the FROM part of the SQL statement will contain several tables as well. It's up to you to construct an SQL SELECT that is accepted by the FileMaker FQL engine.

Then we arrive at the optional "GROUP BY" clause, where you specify by which fields the results have to be grouped, and lastly the optional "ORDER BY" keyword will let you specifiy the ascending sort order of your results.

You always group and order by a 1 or more field names - if you use it, of course.

One final optional clause: "FOR UPDATE". You can use this to check if the records in the result are not locked. But beware that you are not actually locking them.

Between a SELECT clause with the FOR UPDATE clause, and the actual next UPDATE or DELETE statement, the records can become locked.

Only the JDBC and ODBC interfaces to the FQL engine are able to lock records for positioned updates or deletes. This limits the usefullness of the FOR UPDATE clause, but you can still use it to see if the records are not currently locked.

It's comforting to know that an SQL UPDATE will update no records in a selection if one or more of them are locked. So you always check afterwards to see if things went well or not, and act accordingly.

If you have several tables to update, the plot thickens, as you will have to provide some kind of rollback mechanism if things go south halfway in your operations.

mFMb_DoSQL_Select() will always return results as if you were selecting data with the plain DoSQL function with the returnResult True parameter. But, even with returnResult True, the data can still be selectively retrieved as well using the mFMb_DoSQL_ResultData() function.

A simple example:

mFMb_DoSQL_Select( GetFieldName ( myTable::myField ) )

This will select every value of the field in the table, with the default and with the default row separator ¶ this will result in a list of values.

Let's take it one step further:

mFMb_DoSQL_Select(
	GetFieldName ( myTable::myDateField ) ; GetFieldName ( myTable::myField ) ;
	"WHERE" ;
	GetFieldName ( myTable::myDateField ) ; Get ( CurrentDate )
)

This will possibly result in:

2012-03-08,Z
2012-03-08,B
2012-03-08,X

You get the result as a value list, with every result field separated by the column separator ",".

Notice that our dates are in SQL format, that might complicate things.

Allthough we have the result, let's NOT use it. DoSQL has prepared the result for you as wel in another way.

mFMb_DoSQL_Result( 1 ; 1 ) -> 8-3-2012 or 3-8-2012 depending on your system settings ( MDY or DMY )
mFMb_DoSQL_Result( 1 ; 2 ) -> A 
mFMb_DoSQL_Result( 2 ; 1 ) ->…

These results are already in the correct data type. The date is a real date you can continue to work with in FIleMaker.

Next step:

mFMb_DoSQL_Select(
	GetFieldName ( myTable::myDateField ) ; GetFieldName ( myTable::myField ) ;
	"WHERE" ;
	GetFieldName ( myTable::myDateField ) ; Get ( CurrentDate )
	GetFieldName ( myTable::myField ) ; "B"
)

Now we wil get only one record back, as the DateField must match the current date and myField must match "B".

Next step:

mFMb_DoSQL_Select(
	GetFieldName ( myTable::myDateField ) ;
	"ORDER BY" ;
	GetFieldName ( myTable::myField ) ;
)

We're leaving out the WHERE part, so we get every field back, but sorted on myField:

A
A
B
X
Z

Notice the double result A. We can avoid by changing our query just a small bit:

mFMb_DoSQL_Select(
	"DISTINCT"
	GetFieldName ( myTable::myDateField ) ;
	"ORDER BY" ;
	GetFieldName ( myTable::myField ) ;
)

This wil return:

A
B
X
Z

As with the DoSQL function, when only one row and column are returned, DoSQL_Select returns the data in the correct data type.

But what happens with container fields?

You don't have to worry about anything. DoSQL and DoSQL_Select return container data, and if you are using DoSQL, you do not even have to use SELECT GetAs() to retrieve the data in the correct data type.

So there is a difference here with what is written in the ODBC/JDBC guide.

Preparing SQL statements

mFMb_DoSQL_SetParameters( { ; parameter { ; parameter { ; ...} } } )

DoSQL 2 supports "parameter injection" in an SQL statement.

This helps you writing SQL statement without having to put FileMaker values inside the statement itself, because if you do, you have to format them using the correct SQL syntax, which is cumbersome and in some cases even impossible, like when you're using container fields.

Think of the processing of an SQL statement having different stages.

In one stage the SQL statement is prepared to something the SQL engine understands, in the next stage, the parameters are refererenced for their value. In this next stage, we are beyond the SQL syntax though.

Example:

mFMb_DoSQL_SetParameters( customers::name ; 1 ; $zipcode ) &
mFMb_DoSQL( 
	"SELECT * FROM \"contacts\" WHERE 
	\"contacts\".\"name\" = ? AND \"active\" = ? AND \"zipcode\" = ?" 
)

This is much more convenient than:

mFMb_DoSQL( 
	"SELECT * FROM \"contacts\" WHERE 
	\"contacts\".\"name\" = " & mFMb_DoSQL_Value ( customers:name ) & 
	" AND \"active\" = 1 AND
	 \"zipcode\" = " & mFMb_DoSQL_Value ( $zipcode )
)

The number of parameters in this function is unlimited, but the FileMaker SQL engine is 'only' able to use 997 parameters in one SQL statement. Just continue to add them, just make sure they are in the same order as your "?" characters in your SQL statement.

A parameter can be anything you want it to be. A field value of any kind ( text, number, date, time, timestamp, container ), $ or $$ variables, temporary calculation variables in a Let() calculation, parameters in a custom function…

Calling mFMb_DoSQL_SetParameters without any parameter wil clear the existing parameters in plug-in memory.

This is a companion function for the DoSQL function. The other functions DoSQL_Insert, DoSQL_Update and DoSQL_Delete have their parameter injection mechanism built-in.

mFMb_DoSQL_AddParameters( { ; parameter { ; parameter { ; ...} } } )

The difference between mFMb_DoSQL_SetParameters() and mFMb_DoSQL_AddParameters() is that the first function clears all parameters prior to setting them, and the 2nd function does not.

This function allows you to keep on "pushing" parameters e.g. in a script loop, one by one, or as an array.

Example:

mFMb_DoSQL_SetParameters( customers::name ; 1 ; $zipcode ) & mFMb_DoSQL_AddParameters ( $city )

Will result in a parameter count of 3 items.

Looking at the example in the SetParameters() function, you can also create your statement lik this:

mFMb_DoSQL_SetParameters &
mFMb_DoSQL( 
	"SELECT * FROM \"contacts\" WHERE 
	\"contacts\".\"name\" = " & mFMb_DoSQL_AddParameters ( $city ) & " AND \"active\" = ? AND \"zipcode\" = " & mFMb_DoSQL_AddParameters ( $zipcode ) 
)

mFMb_DoSQL_Value( data )

This function is mapped for backward compatibility with the older mFMb_DoSQL_Quote() function, this function single-quoted data so they became compatible for use in SQL statements.

This is still the case, but only for text values.

John Doe becomes 'John Doe' and L'accent becomes 'L''accent'.

This DoSQL 1 function is less needed, because DoSQL 2 is able to perform parameter injection, which is way cooler.

The function remains though because we want to remain compatible with version 1, and there are some situations where you still might want to use it.

In DoSQL 2, this function also accepts numbers, dates, times and timestamps as the data parameter. If you feed it with container data, it will return NULL. If you feed it nothing, it will return NULL. Booleans will be treated like numbers.

mFMb_DoSQL_Value ( Pi ) -> 3.1415926535897932
mFMb_DoSQL_Value ( Get ( CurrentDate ) ) -> DATE '2012-3-10'
mFMb_DoSQL_Value ( Get ( CurrentTime ) ) -> TIME '15:31:12'
mFMb_DoSQL_Value ( Get ( CurrentTimeStamp ) ) -> TIMESTAMP '2012-3-10 15:31:12'

mFMb_DoSQL_Field( fieldname { ; noTableName } )

A convenience function that formats a field e.g. contacts::name to "contacts"."name". This function was previously named "mFMb_DoSQL_QuoteField()".

Just give it the name of the field using GetFieldName(). Unfortunately, you have to do this.

This is because a plug-in is not able to get a field pointer like for example a custom function can.

It would be therefore be wise to make a custom function, instead of using this function. So it's not that good, but still there to remain compatible with DoSQL 1.

optional parameter

The optional noTableName parameter is False by default. When set to True, DoSQL returns the field without the prepending table name.

You need to skip the table name in INSERT and UPDATE statements, because these instructions don't accept "fully qualified column names".

Why not just type the field names or the table names directly in the SQL statement?

This function and the next one appear to be unnecessary. But if you still want to rename your fields or tables afterwards, you better use them.

Example:

mFMb_DoSQL_Field( GetFieldName ( contacts::name ) ; True ) 

returns

"name" ( between double quotes )

Working with repeating fields

One of the pitfals when working with repeating fields is that you have to be careful when referring to other fields.

When a calculation repetition > 1 fires, it will try to referr to another field's same repetition.

If that field has repetitions as well, you will get a value, if that field does not have a repetition that goes as far as your own, you will get nothing back!

Avoid this by using Extend ( fieldName ) when referring to that field. The field wil behave like a field with repetitions, and every repetition will have the same value as the first repetition.

Suppose I have a data field "myDataField" in table "myTable" with 3 repetitions:

a
b
c

When you calculate:

mFMb_DoSQL_Field( GetFieldName(  myDataField ) ; True  )

in another repeating field you will get:

"myDataField"
"myDataField"[2]
"myDataField"[3]

When calculating:

mFMb_DoSQL_Field( GetFieldName(  myDataField )  )

you will get:

"myTable"."myDataField"
"myTable"."myDataField"[2]
"myTable"."myDataField"[3]

When calculating:

mFMb_DoSQL_Field( GetFieldName(  Extend ( myDataField )  ) )

you will get:

"myTable"."myDataField"
"myTable"."myDataField"
"myTable"."myDataField"
The Problem With GetFieldName()

This FileMaker function is very convenient, but it is not without bugs.

The function suffers from the fact that FileMaker wants to check it sometimes to see if you are addressing a related field.

This is of course uncalled for, since you should be able to get the field name of any field, related or not.

Allthough in some situations, this bug has been fixed, it's not fixed in other situations, and can even generate mysterious "parse errors".

What are these "situations"?

First of all, you will have no problem at all in scripts and custom functions by themselves, because FileMaker does not make any assumptions on your occurrence environment.

It can and it will sometimes generate errors when using it in the field definitions, because your occurrence environment is well-defined and strict over there.

Finally, when used in more complex nested formulas with Let() constructions, it can still confuse FileMaker, and will generate a "parse error", this seems to be FileMaker its way of telling us that something is wrong, but it doesn't know what. Move the GetFieldName() function to the first part of the Let() function can solve the issue magically.

GetFieldName() is the only function in the vast FileMaker Functions Arsenal that uses a parameter that should not be checked for context. The existance of the occurrence and the field should be checked, but not if this occurrence is related to our formula context. This is the source of the problem. Probably there is still some code in FileMaker's internal formula parser that are not yet up to that given.

Workarounds
  • move the calculation that uses GetFieldName() function into a custom function ( allthough that can generate the dreaded "parse error" as well )
  • temporary link the unrelated table while creating and committing your calculation formula, you can remove the relation afterwards.
  • when having problems in a Let() function move the GetFieldName() calculation into the assignment part of the Let() function
  • hard code your field name, but do this only as a last resort

mFMb_DoSQL_Table( fieldname )

Slightly related to the previous function, this function gets the name of the table in SQL syntax, provided you feed it with any fieldname of a field of that table.

Example:

mFMb_DoSQL_Table( GetFieldName ( contacts::name ) ) 

returns

"contacts" ( between double quotes )

This function was previously named "mFMb_DoSQL_GetTableFrom()".

mFMb_DoSQL_DataType( data { ; useSQLType } )

This function can help you figure out the data type of anything you throw at it.

So it's not only handy when working with SQL. You can feed it with literal data, fields and even variables, of any type, including containers or images.

It returns the data type as a string.

Possible values are based on the 8 datatypes a plug-in can distinguish: "invalid", "text", "number", "date", "time", "timestamp", "container" and "boolean".

These are theoretical. Practically, you will never encounter any "invalid" or "boolean", because there is no way to feed it with invalid data, and because the FileMaker plug-in engine has a bug that keeps it from recognizing a boolean as such. It will return "number" for a boolean, until FileMaker fixes that bug.

optional parameter

When using the useSQLType boolean parameter True ( it defaults to False ), this function returns "invalid", "varchar", "decimal", "date", "time", "timestamp", "binary", and finally "decimal" again for possible boolean values.

The FQL engine reports "binary" for a container field, but only accepts "BLOB" when you are creating a table

mFMb_DoSQL_SetColumnSeparator( string { ; once } )

When calling DoSQL without the returnResult parameter, or when that parameter is True, the result values in the resulting text are separated by default by a tab character.

This is different from the DoSQL 1 default, which is a § character.

You can change the way columns are separated by e.g.

mFMb_DoSQL_SetColumnSeparator( ¶ ) or mFMb_DoSQL_SetColumnSeparator( 13 )

wich is the same thing, it separates the columns by a return. As you can see, the name of the parameter ( string ) is a bit misleading.

DoSQL will sort out if you are using a string or a number. If you are using a string, which is more comfortable, you can use multiple characters to separate the columns.

If you are using a number, DoSQL picks the appropriate character from the unicode table.

optional parameter

The optional "once" parameter can be used to force another separator values only… once. Which means that after the next DoSQL call, it will revert back to what it was before.

Consider:

mfmb_dosql_setRowSeparator ( " and " ; True ) &
mfmb_dosql_select ( GetFieldName ( rhyme::children ) ) & " went up the hill"

Results in:

Jack and Jill went up the hill

You can use multiple characters as your column separator.

mFMb_DoSQL_SetRowSeparator( string { ; once } )

This does exactly the same as the previous function, but it does it for the row separator.

mFMb_DoSQL_TargetFile( fileName { ; once } )

This function separately set the target file name of your DoSQL instruction. There is no need for it when using the DoSQL function, because this function already accepts the file name as a parameter. But you are able to use it separately, not specifying the file in the DoSQL function that follows it.

It becomes a little more useful in combination with the mFMb_DoSQL_Select(), the mFMb_DoSQL_Update(), the mFMb_DoSQL_Insert() and the mFMb_DoSQL_Delete() functions, because these functions do not accept a target file parameter.

But you will have to hard code your field parameters then, because the GetFieldName() function will not resolve the fields when they belong to another file.

I'ts actually far more interesting to include an external occurrence in your file and work with that one, instead of doing the field hardcoding stuff.

But if you need it, here it is…:-)

optional parameter

Use The once parameter to use this function only temporarily for the next DoSQL data manipulation function.

Getting the result

mFMb_DoSQL_Result( row ; column { ; queryID } )

It returns the value of a given row and column in the result of a DoSQL function.

The important thing to know here is that this data comes in the correct data type!!

optional parameter

When using the optional querID parameter, you are indicating that you want the value of an earlier saved query using mFMb_DoSQL_SaveQuery.

mFMb_DoSQL_RowCount( { queryID } )

When using the DoSQL function, and you want to get the columns value per value you'll have to loop through the resulset the same number of times as the result has records ( unless you choose not to, of course ).

Since the DoSQL function with the returnResult = False parameter returns the number of rows in the result already in this case, there isn't much need for this function. But you could have used the DoSQL function without that parameter, so it still might come in handy.

optional parameter

When using the optional querID parameter, you are indicating that you want the rowcount of an earlier saved query using mFMb_DoSQL_SaveQuery.

mFMb_DoSQL_ColumnCount( { queryID } )

Returns the number of columns in the result.

optional parameter

When using the optional querID parameter, you are indicating that you want the column count of an earlier saved query using mFMb_DoSQL_SaveQuery.

mFMb_DoSQL_LastResult( { queryID } )

This will return the last result returned by the DoSQL function.

When the DoSQL function is used without returning results, this function will return the number of records, and if an error is raised, it will contain the error messge.

In the default situation ( returnResult = True ) this function will return the result of the last SELECT query.

optional parameter

When using the optional querID parameter, you are indicating that you want the result of an earlier saved query using mFMb_DoSQL_SaveQuery, and not the last result.

mFMb_DoSQL_SaveQuery( { query } )

This function saves the results of the last SQL SELECT query, the parameters of the query, the last error number, the SQL statement itself, the number of rows and the number of columns in the result.

The first time you execute mFMb_DoSQL_SaveQuery, you get 1001 as the numeric reference to the result set.

This number increases with every subsequent call of this function.

Beware! The result set is saved in plug-in memory, and you are responsible for freeing up that memory again using mFMb_DoSQL_DeleteQuery ( <queryID> )

The functions that can use the result of this function are mFMb_DoSQL_DeleteQuery, mFMb_DoSQL_LastErrNum, mFMb_DoSQL_LastResult, mFMb_DoSQL_LastSQL, mFMb_DoSQL_Row, mFMb_DoSQL_Column and mFMb_DoSQL_Result

optional parameter

The query parameter is a bogus parameter, you can put there whatever you want. Allowing mFMb_DoSQL_SaveQuery() to have 1 parameter is to allow for this simple to read notation:

mFMb_DoSQL_SaveQuery ( mFMb_DoSQL_DoSQL ( "…" ) ) which is the same as doing the mFMb_DoSQL_SaveQuery() later on in the formula.

why should you use it

First of all this function allows you to work with multiple found sets in your scripts. You can e.g. make nested loops that process the results of multiple query results.

Another VERY IMPORTANT one is the fact that mFMb_Dosql_Result() is "volatile" if not combined with a query ID.

If for some reason another query gets executed between you performing and then obtaining the result of a SELECT, you could be mistakenly retrieve the result of the other query. Typically this happens when running in debug mode, where unstored calculations on the layout or stuff in the DatavVewer might trigger DoSQL SELECT functions while stepping through the script.

mFMb_DoSQL_DeleteQuery( queryID )

Use this function when you are finished using the resultset referenced queryID, this number can be obtained by mFMb_DoSQL_SaveQuery. This frees up the memory used by the resultset. Due to the memory caching algorithms in the application, this is not always visible when using a memory monitor like "Activity Monitor" on OSX, but the memory is really freed, and subsequent calls for memory will reuse the released memory.

Checking for errors

mFMb_DoSQL_LastErrNum( { queryID } )

A simple functon that returns the last error number returned by the DoSQL function.

For example, if you use the DoSQL function to update a record while it's in use, this function will return 301, which is the standard FileMaker error for "record in use". Another one: you misspell the table name in your SQL statement -> this function will return 100 ( table missing ).

It's good practice to check LastErrNum every time you have just executed some SQL.

When using the optional querID parameter, you are indicating that you want the error of an earlier saved query using mFMb_DoSQL_SaveQuery, and not the last error returned.

mFMb_DoSQL_LastSQL( { queryID } )

Another simple function without any parameters, it returns the last SQL statement you have made using DoSQL.

When parameter injection is used, you will see ?'s instead of the literal values.

If you want to avoid this, use mFMb_DoSQL_Value() for every parameter in your SQL statement instead of mFMb_DoSQL_SetParameters().

When using the optional querID parameter, you are indicating that you want the SQL statement of an earlier saved query using mFMb_DoSQL_SaveQuery, and not the last one.

Developing with DoSQL

mFMb_DoSQL_Debug( level { ; once } )

When the debug level parameter = 1 or 2, a log file called DoSQL.log is filled with information about your DoSQL calls.

On MacOS, this file is located in

~/Library/Logs/myFMbutler/DoSQL.log

On Windows, you can find it by resolving

%APPDATA%\myFMbutler\DoSQL.log

On MacOS, you can easily follow the logging by double clicking the file, it will open up in the Console application. Every log entry has the current time stamp upto a 1/1000 of a second, followed by debug information.

Example:

2012-01-19 10:34:53.674 Select Count ( MAILBOXID_DNR ) from mailboxes where parentmailboxid_dnr = 7009
2012-01-19 10:34:53.674 ERROR: 8310
ERROR: FQL0001/(1:18): There is an error in the syntax of the query.
2012-01-19 10:34:54.043 Running Script: "Read Messages"
2012-01-19 10:34:54.043 SELECT Count ( "messageid" ) FROM "mailboxes_messagereceivers" WHERE "mbdef_id" = 6 AND "contactid" = 1000843 AND "is_unread" = 1
2012-01-19 10:34:54.045 rows in result: 1
2012-01-19 10:34:54.045 columns in result: 1
2012-01-19 10:34:54.046 resultsize: 2
2012-01-19 10:34:54.046 result has been retrieved

Examining this little snippet, we can see that there is something wrong with the first query ( the field quoting should have been done ).

As soon as the entry "rows in result" shows, you know how much time the query has taken. In this case about 2/1000 of a second. The DoSQL function was used without the "returnResult" parameter put to False, so the plug-in has obtained a the results as text and did that in about 1/1000 of second, bringing the whole operation to about 0,003 seconds.

Specifying debug level 1, only errors will be logged. When specifying 2, all SQL calls will be logged.

DoSQL reports the running script, but that does not mean that eventual errors are actually coded in the running script.

During a script, fields can be set that trigger auto-enter calculations, which in turn can trigger DoSQL functions.

Also, your layout can contain unstored calculations or conditional formatting, which can also contain DoSQL functions and therefore can possibly return errors while your script is running.

DoSQL can capture the running script, but it cannot capture in which calculation field or other object your SQL is executing in.

optional parameter

With the once parameter set to True, you indicate that you only want to log the next DoSQL data manipulation function.

mFMb_DoSQL_Alert( message { ; informativeText { ; defaultButton { ; alternateButton { ; otherButton { ; alertKind } } } } } )

This is a convenience function. On OSX, it shows a Cocoa style alert dialog, on Windows it shows something similar, but it tries to respect some Windows interface guidelines.

The result of the alert can be 0, 1, 2 or 3. The defaultButton will return 1, the alternateButton will return 2, the otherButton will return 3. On OSX you can possibly get a 0 as the result. More here.

On FileMaker Server ( FMSE, IWP or CWP) , you will always get 0 as the result, and the alert will not show.

required parameter

This function shows the message in the dialog window first, and on the next line of the dialog it will show the optional informativeText.

optional parameters

The optional alertKind parameter can be "Alert", "Critical" or "Informational". The Alert defaults to alertKind "Alert".

The behaviour of the alert follows the interface guidelines of the platform you are running FileMaker on.

On MacOS:

  • The buttons go from right to left on western system software, and the other direction on e.g. Arabic systems.
  • Pressing escape will always press the button that has the localized string of "Cancel", pressing command-D will choose the button that has a localized string of "Don't Save".
  • Leaving the defaultButton blank or omitting it, wil show a localized string of "OK". Pressing return wil choose the defaultButton.
  • The position of the alternateButton differs, depending on the otherButton. Leaving these button names blank will omit them from the dialog.
  • The message is shown in bold, under it, the informativeText is show in plain text. If the message is blank, it defaults to the localized string of "Alert", if no alertKind is specified. If the alertKind is "Critical", it shows "Critical", if the alertKind parameter is "Informational", it shows "Informational". Setting alertKind to "Critical" will have the alert show with an alert triangle bearing a small FileMaker icon on top of it, in all other situations the alert is shown with a large application icon. This icon is the icon of the running application.

On Windows:

  • The buttons for from left to right. So the defaultButton is always the left one.
  • Pressing escape will always press the second button. Pressing return will choose the defaultButton. Leaving the defaultButton blank wil show "OK" on that button.
  • Whatever the position of the buttons shown, luckily the index of the button return is 1 for the defaultButton, 2 for the alternateButton and 3 for the otherButton, much like the standard Get ( LastMessageChoice ) function behaves with a standard "Show Custom Dialog" script step in FileMaker.
  • To remain cross platform compatible , the message and the informativeText are concatinated inside the messagebox, separated by a return. There is no dialog title.
  • The "Critical" parameters will have the alert show with a Stop icon, and will use the according Windows sound if it's configured.
  • The "Informational" parameter wil have the alert show with an "i" icon. Specifying no alertKind will have the alert show with a question icon.

Comparing this function with the "Show Custom Dialog", these are the differences:

  • The dialogs shrink or expand when there is more or less text to display.
  • You cannot resize the dialogs. But hey, you never have to…:-)
  • Dialogs cannot have titles, but they have 2 sections in the message, the effect is most noticeable on OSX.
  • Buttons shrink and grow according to the text you put in them
  • Button text can be a calculation
  • The dialogs have icons, and look more professional

DoSQL_Alert Caveats

  • We honestly don't know what happens if you put too much text in the dialogs. If you feel suicidal, just try it.
  • On OSX this is a real Cocoa dialog, and therefore it does not suspend all activities in FileMaker itself. The standard "Show Custom Dialog" script step is a… script step and cannot be invoked by a calculation like the DoSQL_Alert function. With other words, because script steps can only be performed one step at a item, unlike calculations, there is no potential problem here.

Because FileMaker can calculate things like build up layouts etc. in the background, it can also ask for another dialog to the plug-in while the plug-in is still displaying the first one and waiting for an answer. This could possibly result in lots of dialogs stacked upon each other. We don't want to click away dialogs until the end of time, so DoSQL will return a 0 if FileMaker tries to do this. This will result in "loss" of dialogs, but then again, consider the alternative.

mFMb_DoSQL_Alert() is a function that keeps running, even when the DoSQL plug-in demo period has expired.

mFMb_DoSQL_SupressAlerts( { boolean { ; once } } )

Use this function to show or supress DoSQL error dialogs. By default, the DoSQL function will always show an error alert if something goes wrong.

The SQL statement causing the error is shown, and the suspected position of the error is shown within the SQL statement by the string "<***ERROR***>".

If the error occurs when a script is running, the name of that script is shown as well.

This is a strong indication that the erroneous code is in the script, but it could also be in the formula of a calculation that was triggered during the script, but not necessarily part of the script's code.

Unfortunately the plug-in cannot detect from which calculation it was called, so we cannot log this.

If you don't want these alerts to show up, use mFMb_DoSQL_SupressAlerts( True ).

If you want to disable the alert for just one SQL statement, use mFMb_DoSQL_SupressAlerts( True ; True )

If you want to know if you have disabled alerts or not, use mFMb_DoSQL_SupressAlerts without any parameters and it will return 1 if they are supressed, and 0 if not.

mFMb_DoSQL_ErrorSQL( sqlString { ; fileName } )

This function can be used for debugging as well, it is similar to the Debug function, but there are significant differences.

While the debug function logs to a file on your client machine, and is able to log errors or all SQL calls, the ErrorSQL function is designed for logging in your application tables. Let's explain by example.

mFMb_DoSQL_ErrorSQL (
	"INSERT INTO " & mFMb_DoSQL_Table ( GetFieldName ( DoSQL Errors::error number ) ) &
	" ( " &
	mFMb_DoSQL_Field ( GetFieldName ( DoSQL Errors::error number ) ; 2 ) & ", " &
	mFMb_DoSQL_Field ( GetFieldName ( DoSQL Errors::error string ) ; 2 ) & ", " &
	mFMb_DoSQL_Field ( GetFieldName ( DoSQL Errors::SQL ) ; 2 ) & ", " &
	mFMb_DoSQL_Field ( GetFieldName ( DoSQL Errors::error position ) ; 2 ) & "," &
	mFMb_DoSQL_Field ( GetFieldName ( DoSQL Errors::script name ) ; 2 ) & ", " &
	mFMb_DoSQL_Field ( GetFieldName ( DoSQL Errors::file name ) ; 2 ) &
	" ) VALUES ( ?, ? , ?, ?, ?, ? )" ;
	"MyApplicationLogDatabase"
)

With this function, I'm telling DoSQL that it should log into the "DoSQL Errors" table of the "MyApplicationLogDatabase" file.

When DoSQL encounters an error, it will execute the SQL statement and provide the parameters realtime, always in the same order: the error number, a descriptive error string from the FQL engine, the last SQL statement, the position of the error in the SQL, the script that was running when the error occurred, and finally the file name of the file of the focused window.

You can of course add other fields to the error table like a creation time stamp, and account name etc. You can also change the SQL field's auto-enter calculation so it will nicely highlight the error in the SQL at the error position indicated by the error field.

That said, the FQL engine does not always precisely indicate the correct position of your SQL error. Try to "think" like the FQL engine, and where it starts having problems with the SQL string.

While the Debug function is convenient when your developing your database, the ErrorSQL function comes in handy if you want to monitor your application afterwards and log errors from all users.

Your imagination is the limit from this point on. You can e.g. mail the records to your developer email account using a server side script.

Allthough it's improbable you would use this function for other purposes than logging, the SQL statement can be anything, you could for example use an UPDATE statement to update a status record in your application.

Also notice that you don't have to use all 6 parameters, but you cannot change the order if you only need e.g. the last ones.

While the script name logging can be done by an auto-enter Get( ScriptName ), the Get( FileName ) auto-enter function can only return the file name of the database in which you are logging, which is not really convenient if your active application is not residing in that file.

optional parameter

The SQL statement can be optionally executed in another open file.

mFMb_DoSQL_ShowProgressWindow( windowTitle ; informativeText { ; progressIndex { ; showCancelButton } } )

Available from DoSQL 2.0.0.30. This is also a convenience function.

It allows you to return some feedback to the other side of the keyboard. Some SQL queries or scripts that loop through a lot of SQL queries can take some time.

Unlike an Alert window, a progress window runs concurrently with your script, it does not stop script execution.

We also do not want FileMaker to overlap our progress window when it's bringing FileMaker windows to front, so the progress window floats over all windows.

On OSX, it will even float over all application windows.

required parameters

The windowTitle is the title of the window, the informativeText is the text displayed in the progress window itself. You can leave one or both blank ( "" ) if you don't want to use them.

When you display a progress window using only the windoTitle and the informativeText an indeterminate progress window is shown, with a Cancel button.

The Cancel button will be in the language of the operating system.

Set Variable [ $r ; Value:  mFMb_DoSQL_ShowProgressWindow( "Indeterminate Progress Bar" ; "Indeterminate does not mean undetermined."  ) ]

Progresswindow.png

optional parameters

The progressIndex defaults to 0, which means that it default to an indeterminate state, barber pole style on older OSX versions or whatever animation the OS shows for something that has an undetermined amount of time to complete.

Since we do not know how long certain SQL operation can take, this is a good instruction to put right in front of longer SQL script steps, but of course you can use it for non-SQL operations as well.

If we are scripting a loop through a number of records, set the progressIndex to the Get ( FoundCount ) status function at the start of the loop, and increase the progressIndex in the loop using the mFMb_DoSQL_UpdateProgressWindow() function.

The showCancelButton boolean parameter defaults to True, which means that you have to set it to False or 0 if you do not want the Cancel button to show in the progress window.

considerations when using progress windows

Since this progress window is an OS type progress window ( as opposed to a built-in FileMaker custom window ) there is some behaviour you must consider.

Pressing the Esc ( escape ) key will close the progress window it has a Cancel button.

But pressing Esc when the User Abort State of a script is set to "on" ( the default for a FileMaker script ) it will also stop the script.

This has some ramifications, but maybe they are not entirely unwelcome.

The DoSQL plug-in checks to see if the progress window was started in the context of a script. If the script stops, the plug-in will hide the progress window automatically. This is convenient, because you never have to worry about hiding the progress window at the end of the script.

When the User Abort State is on, pressing Esc will therefore exit the script AND close the progress window.

Consider the following example script:

Allow User Abort [ On ]
Set Variable [ $r ; Value:  mFMb_DoSQL_ShowProgressWindow( "Inderminate Progress Bar" ; "Indeterminate does not mean undetermined."  ) ] 
Loop
	Exit Loop If [ mFMb_DoSQL_ProgressWindowInfo( 2 ) = 1 ] 
End Loop
Set Variable [ $r ; Value: mFMb_DoSQL_Alert ( Get ( ScriptName ) ; "You Cancelled the progress window." ; "" ; "" ; "" ; "Informational" ) ]

If FileMaker's User Abort State is on, the script will not continue to the alert window.

With the first script line set with the "off" parameter, it will.

mFMb_DoSQL_UpdateProgressWindow( progressIndex { ; informativeText { ; windowTitle { ; showCancelButton } } } )

When using a DoSQL progress window in a script loop, this function wil let you update the progress window from your script.

required parameter

The progressIndex. When in a record loop, use Get ( RecordNumber ) here.

optional parameters

You can change the informativeText, the windowTitle and the showCancelButton flag as well.

considerations when updating progress windows

This is a costly operation, that can slow down your script considerably. You might want to update the progress window less often.

To do this in a record loop, you could e.g. only update the progress window when Mod ( Get ( RecordNumber ) ; 10 ) = 0 to update only every 10 records.

Have a look at the following example script:

Set Variable [ $windowName ; Value: "Progress Window" ] 
Set Variable [ $information ; Value: "This is an example of a progress window." ] 
Set Variable [ $maxValue ; Value: 50000 ] 
Set Variable [ $allowAbort ; Value: If ( IsEmpty ( Get ( ScriptParameter ) ) ; Get ( AllowAbortState ) ; GetAsNumber ( Get ( ScriptParameter ) ) ) ] 
If [ not $allowAbort ] 
	Allow User Abort [ Off ]
End If
Set Variable [ $r ; Value: mFMb_DoSQL_ShowProgressWindow( $windowName ; $information; $maxValue ; $allowAbort ) ] 
Loop
	If [ Mod ( $i ; 100 ) = 0 ] 
		# redrawing a message in a cocoa window takes a very long time, we update the message every 100 records
		Set Variable [ $message ; Value: $information & ¶ & $i ] 
		Set Variable [ $err ; Value: EvaluationError ( mFMb_DoSQL_UpdateProgressWindow( $i ; $message ) ) ] 
	Else
		Set Variable [ $err ; Value: EvaluationError ( mFMb_DoSQL_UpdateProgressWindow( $i ) ) ] 
		# the progress window will close automatically when passing a value over $maxValue
	End If
	# Calculations in script steps never raise LastError, otherwhise we could have had simpler code
	Exit Loop If [ $err = 1 ] 
	Exit Loop If [ Let ( $i = $i + 1 ; $i > $maxValue + 1 ) ] 
End Loop

The script loops through 5000 iterations. Depending on Get ( AllowAbortState ) or a script parameter, we decide if we are going to show the Cancel button or not.

Notice that DoSQL puts in another safety net here. If the initial progress index is lower that what you are setting it to, the progress window will automatically close.

The script as above is very slow. This is because it update the progress window in EVERY loop iteration.

Just comment out the

Set Variable [ $err ; Value: EvaluationError ( mFMb_DoSQL_UpdateProgressWindow( $i ) ) ] 

line and watch it fly.

mFMb_DoSQL_ProgressWindowInfo

A simple function that gives the scripter some information about the progress window.

It returns two values in a list: visible and cancelled.

visible=1
cancelled=0

These values will reflect the current state of the progress window. You can use this to determine if the user has cancelled the progress window.

mFMb_DoSQL_HideProgressWindow

This function hides the progress window. Remember that you do not have to close a progress window at the end of a script.

DoSQL will hide the progress window automatically when it was starting to show in a script.

mFMb_DoSQL_Parameter ( index { ; queryID } )

Not really a function for SQL query preparation or getting results from a query, so this belongs to the more generic "Developing with DoSQL" section.

The function gets a previously set parameter at the numeric position index of a parameter array. Example:

mFMb_DoSQL_SetParameters( customers::name ; 1 ; $zipcode, $city )

To get the 3th parameter, which is the $zipcode variable:

mFMb_DoSQL_Parameter( 3 )

When using the optional queryID parameter, you can retrieve a parameter from an earlier saved query using mFMb_DoSQL_SaveQuery.

mFMb_DoSQL_ParameterCount( { queryID } )

Also a generic parameter function that you can use to know how many items are currently in the parameter array.

mFMb_DoSQL_SetParameters( customers::name ; 1 ; $zipcode, $city )

To get the number of items in the array:

mFMb_DoSQL_ParameterCount

Which wil return 4.

optional parameter

When using the optional queryID parameter, you can get the parameter count from an earlier saved query using mFMb_DoSQL_SaveQuery.

mFMb_DoSQL_ExecutionTime( { queryID } )

Available from DoSQL version 2.0.0.28, returns the number of seconds the following functions needed to to interact with the FileMaker FQL engine:

  • mFMb_DoSQL()
  • mFMb_DoSQL_Select()
  • mFMb_DoSQL_Insert()
  • mFMb_DoSQL_Delete()

The simple function helps you detect how much time SQL calls to FileMaker take. It is based on the OS platform ticks immediately before and right after the call to the engine.

See also http://en.wikipedia.org/wiki/System_time

A very fast query ( like a select from filemaker_tables ) can return 0 on OSX, as the tick count on this platform has a resolution of 1/60th of a second.

optional parameter

When using the optional queryID parameter, you can get the execution time from an earlier saved query using mFMb_DoSQL_SaveQuery.

Configuring DoSQL

mFMb_DoSQL_Configure( dataSelector )

This is a generic myFMbutler function used to configure the licensing of our plug-ins. Without registering a valid license, the DoSQL plug-in will function for 1 hour, after which you have to restart FileMaker if you want to use it again.

Some of the plug-in functions will continue to work though, check out the Licensing topic to see which functions do.

Registering the plug-in

mFMb_DoSQL_Configure ( "register|[reg_name]|[reg_code]|[reg_userCount]|[reg_licenseType]" )

This function allows you to register DoSQL with the several parts of the licensing information separated by a piping character.

The actual parameters depend on what kind of DoSQL license you have. These parameters are shown in your registration confirmation email.

It will return the number of users if registration is fine, or otherwise will return ‘0’ if the registration information is invalid.

Saving the registration info

The function mFMb_DoSQL_Configure ("save" ) allows you to permanently save the current registration info to the DoSQL preferences, as in the example below:

Let ( 
	[ 
	regName ="Peter Wagemans" ; // your registration name
	regCode = "ABCDEFGHIJK" ; // your registration code
	regUserCount = "Unlimited" ; // the number of users for your license
	regLicenseType = "Developer" ;// Enter your license type
	register = mFMb_DoSQL_Configure ( "register|" & regName & "|" & regCode & "|" & regUserCount & "|" & regLicenseType ) ; // should return regUserCount
	save = If ( register = regUserCount ; mFMb_DoSQL_Configure ( "save" ) )
	] ;
	register & save
)

Tweaking the plug-in settings

These parameters allow for a slightly different behaviour of the plug-in.

mFMb_DoSQL_Configure( "output1resultastext" )

will make DoSQL 2 behave like DoSQL 1 and always return text, even when returning only one row and one column.

mFMb_DoSQL_Configure( "output1resultnatively" )

is the default setting and wil configure the DoSQL plug-in to return a one-row-one-column result in its native type.

This function is for backward compatibility. You should only use it if your existing solutions that used DoSQL 1 would break otherwhise, and consider rewriting some calculations and scripts.

If you use the simplified DoSQL functions, you can optionally enable or disable data type checking. This checking requires querying the database schema, and WILL slow down the execution if your solution contains a lot of occurrences and/or fields.

To enable data type checking, you can use:

mFMb_DoSQL_Configure( "checkdatatypes" )

and to disable it back again, you can use:

mFMb_DoSQL_Configure( "nocheckdatatypes" )

mFMb_DoSQL_Version( dataSelector )

Returns DoSQL license Info: You can get the appropriate information by specifying the correct data selector.

  • data selector "" returns the plug-in name
  • data selector "0" returns the four digits of the version number
  • data selector "1" to "4" return 4 separate digits of the version number
  • data selector "5" returns the license type "Demo", "Regular", "Developer", "Site License"
  • data selector "6" returns the user license count - "1", "5", "10", "25", "50", "Unlimited"
  • data selector "7" returns the registrar name
  • data selector "8" returns the activation code of the plug-in

There are a few extra codes that are used for technical purposes.

  • data selector "9" returns "32-bit" or "64-bit", depending on which mode the plug-in is running under
  • data selector "10" returns the version of the plug-in SDK wrapper library. 64-bit plug-ins are supported from version 53 upwards.
  • data selector "11" returns information about the application flavour the plug-in is running in. Here's a an overview of possible results:
0: FileMaker Pro Advanced
1: FileMaker Pro
2: FileMaker Runtime
3: FileMaker Server version < 12
4: Custom Web Publishing
7: Server Side Script
8: Instant Web Publishing
  • data selector "12" returns the processID of the process that is using the plug-in ( all server side scripts share the same processID! )
  • data selector "13" returns the installed path of the loaded plug-in, this makes it clearer which plug-in is loaded when you have accidently copied it in multiple locations.

mFMb_DoSQL_VersionAutoUpdate

Provides plug-in information for FileMaker Server's AutoUpdate function. Returns 8 digit number to represent an AutoUpdate version.

This function helps a developer update the plug-in by comparing the value of the current version with the version available.

e.g. for version 1.4.3.1 the value ‘01040301’ will be returned. This string is easy to compare.

Errors returned by DoSQL

DoSQL will return errors which are in turn returned by the FileMaker FQL engine or the plug-in API.

Errors are reported by mFMb_DoSQL_ErrNum(), and mFMb_DoSQL_LastResult().

If ErrNum = 0, your SELECT query wil put the result of the SELECT in LastResult.

If you have specified returnResult = False, your SELECT query will contain the number of rows in the result.

All other SQL queries will return nothing in LastResult.

If ErrNum <> 0, your SELECT query will put nothing in LastResult if your returnResult parameter = False, it will put "ERROR: " & lastErrNum in the first line of lastResult, on the second line it will put something more descriptive.

This can be a description of the FQL error code, or a description from the plug-in.

When the plug-in reports a 1200 error, there is something wrong with the parameters you have just fed it.

When the plug-in reports error number 8, this means that your are referring to a non-existant result, like for example mFMb_DoSQL_Result ( 1 ; 5 ) when there are only 4 columns in the result, or when using mFMb_DoSQL_Result ( 1 ; 4 ; 1025 ) when saved query 1025 has been deleted already.

When a SELECT query does not return a result because nothing was found, the DoSQL plug-in does not return any error. It's for you to decide if this is to be considered an error condition.

Here's an overview of the errors

ErrNum LastResult
-7777 ERROR: demo version timed out.
8 ERROR: Empty result.
1200 ERROR: incorrect number of parameters.
1200 ERROR: even parameters should be fully qualified field names.
1200 ERROR: first parameter should be a fully qualified field name.
1200 ERROR: parameter ^0 and ^1: datatypes do not match.
1200 ERROR: a WHERE clause parameter is not a fully qualified field name.
1200 ERROR: GROUP BY parameter should be a fully qualified field name.
1200 ERROR: ORDER BY parameter should be a fully qualified field name.
4000.. ERROR: FileMaker plug-in engine problem
8309 ERROR: semantic error ( something wrong with the SQL )
8310 ERROR: syntax error ( the FQL engine did not understand at all )

Errors are reported by the mFMb_Debug() function and the mFMb_ErrorSQL() function.

And finally, errors are by default reported by dialogs.

SimpleError.png

When a dialog like this shows, expect an ErrNum of 8309. When the DoSQL function has returnResult set to False, you can find

ERROR: 8309
ERROR: FQL0007/(1:7): The column named "fieldname" does not exist in any table in the column reference's scope.

As a direct result of your function call.

Unofficial FileMaker FQL error list

We found a recent list of the possible FQL errors:

FQL Error Error reported by FQL engine
FQL0001 There is an error in the syntax of the query.
FQL0002 The table named "?0" does not exist.
FQL0003 The table named "?0" already exists in this query.
FQL0004 The query is too complex. The maximum number of tables has been exceeded.
FQL0005 Expressions involving aggregations are not supported.
FQL0006 "The column named "?0" appears in more than one table in the column reference's scope."
FQL0007 The column named "?0" does not exist in any table in the column reference's scope.
FQL0008 The table named "?0" does not exist in the column reference's scope.
FQL0009 The column named "?1" does not exist in table "?0".
FQL0010 The literal value "?0" is not a valid DATE, TIME or TIMESTAMP.
FQL0011 Predicate must contain a logical operation (=, <, OR, AND, IS NULL, ...).
FQL0012 The ordinal reference "?0" in the ORDER BY clause is not valid.
FQL0013 Incompatible types in assignment.
FQL0014 The number of values in a VALUES row value constructor does not match the number of values in the target.
FQL0015 The number of values in an INSERT...SELECT statement does not match the number of values in the target.
FQL0016 A subquery contains an illegal outer reference to a column in the INSERT's target table.
FQL0017 An expression contains data types that cannot be compared.
FQL0018 An expression contains incompatible data types.
FQL0019 The result data type of a CASE expression cannot be inferred; they are all NULL.
FQL0020 An invalid number of parameters was supplied to the function "?0"
FQL0021 Parameter number ?0 to the function "?1" is not of the correct type.
FQL0022 A subquery expression must have exactly one value in the SELECT list.
FQL0023 A CAST expression requested an invalid data type conversion.
FQL0024 A reference to ROWID must be qualified if more than one table is present in the query.
FQL0025 All non-aggregated column references in the SELECT list and HAVING clause must be in the GROUP BY clause.
FQL0026 The number of columns in both inputs to a UNION operation must be the same.
FQL0027 The data types of corresponding columns in the inputs to a UNION operation must be the same.
FQL0028 Field repetitions must be numeric and between 1 and ?0.
FQL0029 A field repetition in the SET clause of an UPDATE statement must be a constant.
FQL0030 "?0" is an invalid function.
FQL0031 The the parameter's type cannot be inferred in this context. At least one query parameter must be an expression, a column or a constant.
FQL0032 A query may contain either named parameters or dynamic parameters, but not both.
FQL0033 Column names in FROM clause subqueries must be unique.
FQL0034 The number of output columns in a FROM clause subquery must match the number of columns in the table's name list.
FQL0035 Cursor support is not enabled for this query.
FQL0036 A cursor with the name "?0" already exists.
FQL0037 There is no cursor with the name "?0".
FQL0038 The cursor "?0" is already open.
FQL0039 The cursor "?0" is not open.
FQL0040 The target cursor "?0" does not reference a query that is valid for WHERE CURRENT OF .
FQL0041 The target cursor "?0" does not reference the same table as the current statement.
FQL0042 The default value for column "?0" does not match the column's data type.
FQL0043 The string "?0" is not a valid stream name.
FQL0044 The column "?0" is not valid in this context. The targets of GETAS and PUTAS must be Container fields.

Don't let this list intimidate you, in normal everyday use we will probably see only a small portion of these possible error messages. But it proves that FileMaker is taking it's FQL engine seriously.

Some of these errors, you can never encounter using DoSQL. For example, the FQL engine does not provide cursor support to the plug-in API, so you'll never get any cursor error.

Supported SQL statements

DoSQL supports all SQL statements that are supported by FileMaker’s ODBC and JDBC client drivers:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • CREATE TABLE
  • ALTER TABLE
  • CREATE INDEX
  • DROP INDEX

You can refer to the FileMaker documentation for more detailed information about using these statements. The document is available here .

Another great general resource about SQL can be found at W3 Schools.com .

The SQL instruction DROP TABLE is supported as well, but still not documented in the abovementioned guide. The option IF EXISTS is not supported.

DoSQL compared with ExecuteSQL

FileMaker 12 has a new calculation function called ExecuteSQL.

This function uses Filemaker's FQL engine, just like the myFMbutler DoSQL plug-in, and will produce similar results.

The function

ExecuteSQL ( "SELECT foo from bar" ; "" ; "" )

will return exactly the same result as

mFMb_DoSQL ( "SELECT foo from bar" )

You must always specify the row and column separator parameters when you use the ExecuteSQL function. If you want to use the default ones - comma and ¶, just like DoSQL - you still have to pass empty string parameters.

The function

ExecuteSQL ( "SELECT foo from bar" ; "xxx" ; "yyy" )

wil return exactly the same as

mFMb_DoSQL_SetRowSeparator ( "xxx" ) & mFMb_SQL_SetColumnSeparator ( "yyy" ) & mFMb_DoSQL ( "SELECT foo from bar" )

This time the DoSQL syntax is a lot longer. Call it a design choice.

FileMaker 12's ExecuteSQL function only supports SQL SELECT queries, and always returns them as strings separated by the 2nd and 3th parameter. It can only query the file represented by the focused ( front ) window.

myFMbutlers's DoSQL 2 function supports all SQL queries, and can return data in any FileMaker data type. It can query any open FileMaker file.

The error handling is also a bit different. ExecuteSQL only returns 8309 and 8310 error numbers ( what did they mean again? ), DoSQL also returns the full error string and the position of the error in the SQL statement as reported by the FQL engine.

Under the hood

Here's some extra information about the inner workings of FileMaker's SQL engine, also called the FQL engine.

FileMaker's schema tables

The FQL Engine keeps 2 special tables for each FileMaker file. You can query them to obtain schema information. These tables are read-only.

FileMaker_Tables

FieldName FieldType Information
TableName text The name of the table occurrence in the FileMaker Relation Graph ( FRG )
TableID number The internal ID of the table occurrence, see also the FileMaker Function TableIDs ( Get ( FileName ) )
BaseTableName text The FileMaker table this occurrence is based on
BaseFileName text The file name this table occurrence has it's base table located
ModCount number The number of times the schema of this table has been modified

If you want to get this information using DoSQL, just do this:

mFMb_DoSQL ( "SELECT * FROM \"FileMaker_Tables\"" )

If you want the base tables from your file, consider:

mFMb_DoSQL ( "SELECT DISTINCT \"BaseTableName\" FROM \"FileMaker_Tables\"" )

does not return base tables that could have the same name, but are located in another file.

Workaround:

Let (
	[
	myFileName = Get ( FileName ) ;
	myHostName = Get ( HostName ) ;
	myBaseFileName = myFileName & if ( not Isempty ( myHostName ) ; " (" & myHostName & ")" ) ;
	void = mFMb_DoSQL_SetParameters ( myBaseFileName )
	] ;
	mFMb_DoSQL ( "SELECT DISTINCT \"BaseTableName\" FROM \"FileMaker_Tables\" WHERE \"BaseFileName\" = ?" )
)

The BaseFileName is indicated by it's name and the possible host name.

If you want the names of every first occurrence ( the PTOs or Primary Table Occurrences ) of the base tables of your file, do this:

Let (
	myParameter = mFMb_DoSQL_SetParameters ( Get ( FileName ) & If ( not IsEmpty ( Get ( HostName ) ) ; " (" & Get ( HostName ) & ")" ) ) ;
	mFMb_DoSQL ( "SELECT TableName FROM FileMaker_Tables WHERE TableID IN ( SELECT MIN(TableID) from FileMaker_Tables WHERE BaseFileName = ? GROUP BY BaseTableName )"  )
)

This is a nice example of a more complex SQL query and shows of the raw force of being able to understand what SQL can do for you.

FileMaker_Fields

FieldName FieldType Information
TableName text The name of the table occurrence this field belongs to
FieldName text The name of the field
FieldType text The SQL type of the field
FieldID number The internal ID of the field, returns the same ID as the FileMaker function FieldIDs()
FieldClass text Can be Normal, Calculated
FieldReps number The repetitions defined for this field
ModCount number The number of times the properties of this field have been modified

Suppose I want to list the field names of the table ENGLISH_NATURE that is hosted on a FileMaker Server, but I want to make sure I'm not listing fields of duplicate occurrences or occurrences of a base table with the same name that could have an external occurrence in my file.

Let (
	[
	myTableName = "ENGLISH_NATURE" ;
	myFileName = Get ( FileName ) ;
	myHostName = Get ( HostName ) ;
	myBaseFileName = myFileName & If ( not IsEmpty ( myHostName ) ; " (" & myHostName & ")" ) ;
	void = mFMb_DoSQL_SetParameters ( myTableName ; myBaseFileName )
	] ;
	mFMb_DoSQL ( 
		"SELECT FileMaker_Fields.FieldName FROM FileMaker_Fields, FileMaker_Tables " &
		"WHERE " &
		"FileMaker_Tables.BaseTableName = ? AND " &
		"FileMaker_Tables.TableName = FileMaker_Fields.TableName AND " &
		"FileMaker_Tables.BaseFileName = ?"
	)
)

Notice I'm not bothering here to include the field names quotes, because the schema table's fields are without spaces or other incompatible characters. They cannot be renamed, so I'm safe there as well. And lastly, I just cannot use GetFieldName() because these occurrences are not available outside the FQL engine.

The SQLtype field also the maximum number of character that can be entered in a field.

varchar(3) as an SQLType means you can only enter 3 characters into that field.

Another example:

I want to get field names based on the TableID and the FieldID of a given field. Since TableID's and FieldID's do not change that easily in a schema, I have a fairly good method of accessing field names in another file, since I cannot use GetFieldName() when doing that.

Let (
	[ 
	myFileName = "Customers" ;
	void = mFMb_DoSQL_SetParameters ( 1065089 ; 3 ) ;
	void = mFMb_DoSQL_SetColumnSeparator ( "::" ; True ) 
	] ;
	mFMb_DoSQL ( 
		"select T.tableName, F.fieldName from filemaker_fields F, filemaker_tables T where 
		T.tableID = ? and T.tableName = F.tableName and F.fieldID = ?" ;
		myFileName
		)
)

This function will easily convert into a custom function CF_GetFieldName ( FileName; TableID ; FieldID ). It will not produce a very readable SQL syntax, so that could be a bad thing…:-)

Some extra advice: What You Should Know

You are using a plug-in

DoSQL 2.0 relies on an ExecuteFileSQL method in the FileMaker plug-in SDK. Therefore, it is important to know that the plug-in has certain limitations, inherent to the use of that method.

Until FileMaker fully supports its FQL engine without the use of a plug-in like DoSQL, keep the following in mind.

Record locking and the ‘UPDATE’ statement

Using the SQL engine through a plug-in is behaving like another user using your file with the same credentials you have.

It's as if it is accessing your data from another window.

This means you have to be careful when modifying records (using the ‘UPDATE’ statement). You could be in the process of modifying it yourself manually, or within a script.

FileMaker does not return a record locking error when that happens. The FQL engine reports the error to the plug-in and the plug-in will tell you the record is locked.

Therefore, use the UPDATE statement with caution, always check right after using it that everything went right.

Also, performing a SELECT query on FileMaker data will only return the data that has been committed to the file. There is a difference with FileMaker 12's ExecuteSQL function here.

Modifying schema: the CREATE, ALTER and DROP statements

If you want to use the CREATE, DROP or ALTER statements, the parameter executeOnIdle is required.

If you use these functions in a script, FileMaker can deadlock itself, waiting for idle to return the changed schema to the client, while your script step is waiting for FileMaker.

Avoid this mexican stand off by adding the executeOnIdle parameter to your DoSQL statement, and set the next scrip step to a small timed pauze.

This generates a small idle gap in which FileMaker can be made happy again, and your script can continue.

Also, remember that when you drop an index using DROP INDEX FileMaker changes the schema so that the field with the dropped index does no reindex automatically. This is a bit of a bummer actually, you could have systematically cleaned out indexes from your solution and have the solution reindex when needed. So be warned.

If the message is bad, don't kill the messenger

DoSQL is just a gateway between FileMaker's FQL engine and FileMaker's calculation functions. myFMbutler did not invent the FQL engine, and we are not SQL experts. The FQL engine is fairly new, and has a number of bugs and limitations. If a DoSQL query does not yield a correct result, don't immediately blame it on the plug-in. You can test if the SQL statement is OK or in 4 other ways; FileMaker 12's ExecuteSQL function, the ODBC interface, the JDBC interface or another SQL plug-in. If it's having a problem there as well, the cullprit is probably in the FQL engine itself, since it serves all 3 APIs. If it's not having a problem there, there could still be something wrong with the way the plug-in engine handles the call. Keep that in mind before contacting us for technical support.

Plug-in memory usage

In the current version of FileMaker, plug-in memory usage is limited to the 32-bit memory space, being 4GB, but you have to take into account the memory space used by other plug-ins and the overhead of the plug-in engine itself.

This is extremely important when processing SELECT queries that return massive amounts of data. FileMaker organises the result of a SELECT query in structures that take up a lot of plug-in memory.

Before the plug-in can even begin to return the result of a query, the result of the query must fit in plug-in memory.

What are the safe limits? That is hard to say. 5 million records with 1 field of about 40 characters take about 2GB of temporary plug-in RAM. A result with 2 fields with the same average size and halve the records will probably take more then 1GB because of the overhead of the data structure.

Because DoSQL caches this result for retrieval with mFMb_Result( row, column ), this memory remains in use until the next SELECT statement is executed, in which case the cache is first cleared to release the memory, followed by the execution of the new SELECT statement.

The FileMaker 12 ExecuteSQL() function does not use plug-in memory, but does not return a structure that respects the data types of the result either. It just returns a text string. But for large queries, it could be a better choice.

Concurrent execution of DoSQL functions

Allthough you would not immediately expect it, the FileMaker engine does some threading in it's calculations. A good example is this:

A script goes to a layout and executes a DoSQL statement in that layout. The function returns an error and the script displays the error using a DoSQL_Alert() dialog. While the dialogs shows, the script is waiting for the dialog function to complete, but FileMaker will start to render the layout in the background. The layout can contain a number of calculations ( conditional, unstored… ) and these calculations can in turn contain DoSQL queries.

Since DoSQL_LastErrNum gives that last error result the plug-in has calculated, your script continues after you dismiss the dialog, and you wonder why the error = 0 while you should have gotten something else.

Something similar happens when you use the FileMaker Advanced debugger. FileMaker constantly refreshes the layout on every script step and might ruin your dataset in the script.

You can avoid such mishaps by saving the result of your SQL queries using mFMb_DoSQL_SaveQuery, IMMEDITATELY after you execute the SQL, preferrably in the same calculation.

By referring to your results using the queryID returned, you avoid using the results of any DoSQL query that has been running in parallel execution.

Do not forget to delete your query afer using it using mFMb_DeleteQuery, because you will run out of memory sooner or later, and FileMaker could crash.

Backward compatibility with DoSQL 1

FileMaker 8 up to 12 are able to use the old FQL engine, but FileMaker now clearly states that this old engine is deprecated, and will probably not exist anymore in the next major FileMaker release.

FileMaker 11 and up already have the newer FQL engine, but are already starting to get picky with the syntax of SQL queries done to the old engine.

When you have a solution that uses DoSQL 1, and you want to start using DoSQL 2, there's a number of things you have to take into account.

The mFMb_DoSQL() and the other DoSQL 1 bases functions nicely map to the newer functions, this part will not give you problems.

Problems arise when you are migrating your pre-11 solution using DoSQL 1. The old FQL engine has much less trouble with improperly quoted SQL statements. DoSQL 2 only uses the new FQL engine, so if you have these statements in your pre-11 solution, they will break.

Luckily, you can fix them first when your solution is still running pre-11, the old FileMaker FQL engine also accepts SQL statements that are correctly quoted…:-)

If you migrate you solution to 11, some older OSX PPC machines can still use the old 11 client, but not the DoSQL 2 plug-in, because it's Intel only. This will have you end up with some clients running DoSQL 2, and some using DoSQL 1.

This will work, but these client machines have to stay away from code that is using the new DoSQL 2 functions. Let's just say you will be forced to have these clients only use the base functionality of your solution, and keep them away from some more advanced stuff.

In both cases, consider putting mFMb_DoSQL_Configure( "output1resultastext" ) in your startup script, you might have some code that returns 1 row 1 column, which DoSQL 1 returns as a text string, but which DoSQL 2 will return in its native format, unless you use the beforementioned function.

Good practice

Using field names

The FileMaker FQL engine accepts unquoted field names, as long as they do not contain "special" characters like spaces - and a whole bunch of others.

For data source and field names with spaces, you need to escape your spaces using quotes: e.g. select "My Field Name", "My Table"."My Other Field" from "My Table"

You are encouraged to use the function mFMb_DoSQL_Field( fieldname ) to convert the "FileMaker-style" syntax like my Table::my Field to SQL syntax like "my Table.my Field".

Try to always pass field names by using the GetFieldName() function. You or somebody else might rename the field afterwards. If you use GetFieldName(), that's OK.

If you don't, your precious code will not function anymore.

Using fields

Fields themselves are special objects in FileMaker. They have 2 properties, their name and their value.

When passing a field to a custom function as a prameter for example, you can get the value of it by… just using it in your calculation.

But if you use GetFieldName() on the parameter, you still get the field name of the field parameter.

Remember to take advantage of this duplicity. Using

myTable::myField * 2

Is just shorthand for

GetField ( GetFieldName ( myTable::myField ) ) * 2

Saving you the trip of going to Paris through Rome.

Avoid making stored calculations that do SQL queries

Avoid making stored calculations that execute SQL, because they will not work reliably. Use auto-enter calculations instead.

When a FileMaker file is hosted, some field calculations are executed by the host, so you would have to install DoSQL on the host. Installing DoSQL on a FileMaker Server is possible.

Even then, consider that stored calculations are re-evaluated if you alter them. You don't want that to happen if you have several thousand records in your table.

Using DoSQL server side

Server side scripts and web publishing are able to use the DoSQL plug-in, but keep the following in mind:

  • DoSQL alerts wil not display and the mFMb_DoSQL_Alert() function will always return 0 as the button result.
  • Remember to use mFMb_DoSQL_Configure() to register your plug-in on the server side as well

The server side FileMaker engine shares plug-in memory. This has serious consequences when you are running multiple server side scripts at the same time.

The DoSQL plug-in has a number of temporary "session" variables that store your settings and results. When multiple server side scripts are running simultaneously, they can disturb each other.

Avoid this by adjusting your schedules so they do not overlap. This behaviour is the one we observe when writing the documentation, and it could change in subsequent releases of FileMaker Server.

Using custom functions

If you are a professional FileMaker Developer, it's a very good practice to move DoSQL statements as much as possible into custom functions.

By making this abstraction you are separating how things are done from your application, bringing this logic together in a more easy to maintain list of custom functions.

Another advantage, by example: allthough the mFMb_DoSQL_Field() and mFMb_DoSQL_Table() function are convenient for field name parsing, you can write custom functions that use these functions, but perform the GetFieldName() parsing as well. Plug-ins are not able to retrieve the field name of a parameter, and custom functions can.

This wil make your main formula a lot shorter and easier to read.

To use or not to use, that's the question

One of the biggest assets of FileMaker as an application to develop wonderful solutions, is that you don't have to use SQL.

Think about that last sentence. Using SQL in your application may look cool, but always consider if things can't be done just as easy - or even easier - using regular FileMaker script steps that don't use SQL.

SQL adds to your toolbox, it doesn't replace it.

Licensing

DoSQL wil continue to run in demo mode for 1 hour, after which you have to restart FileMaker if you want to use it again.

DoSQL will return error -7777 if you are using its expired functions.

A large number of functions keep on working, and some of them are useful even when you are not using DoSQL for its SQL functionality.

Executing the core DoSQL functions on a FileMaker Server using server side scripts or web publishing, requires a DoSQL Site or Developer license, they do not run in demo mode.

Important: trying to register DoSQL using the v1 registration code will invalidate any existing valid registration and the DoSQL 2 plug-in will fall back into demo mode!

So remember to hunt down all places in your solutions where you have registration script steps, and replace them with the correct registration code. I've been there. Experience is the result of mistakes made.

A good tool to find these scripting lines is Chris Dragon's Developer Assistant plug-in.

Function Unlicensed/Demo Unlicensed/Expired Regular License Developer Site License
mFMb_DoSQL() Client/Server - Client Client/Server Client/Server
mFMb_DoSQL_Insert() Client/Server - Client Client/Server Client/Server
mFMb_DoSQL_Update() Client/Server - Client Client/Server Client/Server
mFMb_DoSQL_Select() Client/Server - Client Client/Server Client/Server
mFMb_DoSQL_Delete() Client/Server - Client Client/Server Client/Server
mFMb_DoSQL_ErrorSQL() Client/Server - Client Client/Server Client/Server
mFMb_DoSQL_Configure() Client/Server Client/Server Client/Server Client/Server Client/Server
mFMb_DoSQL_Version() Client/Server Client/Server Client/Server Client/Server Client/Server
mFMb_DoSQL_VersionAutoUpdate Client/Server Client/Server Client/Server Client/Server Client/Server
mFMb_DoSQL_SetColumnSeparator() Client/Server Client/Server Client/Server Client/Server Client/Server
mFMb_DoSQL_SetRowSeparator() Client/Server Client/Server Client/Server Client/Server Client/Server
mFMb_DoSQL_LastErrNum Client/Server Client/Server Client/Server Client/Server Client/Server
mFMb_DoSQL_LastSQL Client/Server Client/Server Client/Server Client/Server Client/Server
mFMb_DoSQL_LastResult Client/Server Client/Server Client/Server Client/Server Client/Server
mFMb_DoSQL_SaveQuery Client/Server Client/Server Client/Server Client/Server Client/Server
mFMb_DoSQL_DeleteQuery Client/Server Client/Server Client/Server Client/Server Client/Server
mFMb_DoSQL_SetParameters() Client/Server Client/Server Client/Server Client/Server Client/Server
mFMb_DoSQL_AddParameters() Client/Server Client/Server Client/Server Client/Server Client/Server
mFMb_DoSQL_Parameter() Client/Server Client/Server Client/Server Client/Server Client/Server
mFMb_DoSQL_ParameterCount Client/Server Client/Server Client/Server Client/Server Client/Server
mFMb_DoSQL_Value() Client/Server Client/Server Client/Server Client/Server Client/Server
mFMb_DoSQL_Field() Client/Server Client/Server Client/Server Client/Server Client/Server
mFMb_DoSQL_Table() Client/Server Client/Server Client/Server Client/Server Client/Server
mFMb_DoSQL_TargetFile() Client/Server Client/Server Client/Server Client/Server Client/Server
mFMb_DoSQL_RowCount Client/Server Client/Server Client/Server Client/Server Client/Server
mFMb_DoSQL_ColumnCount Client/Server Client/Server Client/Server Client/Server Client/Server
mFMb_DoSQL_Result() Client/Server Client/Server Client/Server Client/Server Client/Server
mFMb_DoSQL_Alert() Client Client Client Client Client
mFMb_DoSQL_DataType() Client/Server Client/Server Client/Server Client/Server Client/Server
mFMb_DoSQL_Debug() Client/Server Client/Server Client/Server Client/Server Client/Server
mFMb_DoSQL_SupressAlerts() Client/Server Client/Server Client/Server Client/Server Client/Server

The Developer License has all the features of the site license, but the mFMb_DoSQL_Configure( "save" ) function will not be able to save the registration information.

This means that a FileMaker developer has to register the DoSQL plug-in in the startup script of any solution he makes for customers.

Credits

I would like to thank SQL engine developer Galt Johnson from FileMaker, plug-in developers Thomas and HOnza (24U), Jesse (360Works) and Peter (Troi Automatisering) for helping me out when I was stuck, and believe me, that was a lot of times. The source code of the Goya BaseElements plug-in also helped me understand a lot of stuff, thanks to Nick Orr and Mark Banks for maintaining that project. Also thanks to my collegues for testing and our customers for their patience while I ironed out the bugs. Without all these people, the DoSQL 2 plug-in would have been an impossible project.

-- Peter Wagemans

FileMaker 7-13 Certified Developer

Any questions? Contact us via www.myfmbutler.com. DoSQL © 2006-2015 myFMbutler - Clarify

Personal tools