Recently I’ve mentioned several custom functions that can make your life easier when working with FileMaker’s internal SQL engine (a.k.a. FQL). This article’s accompanying demo file, SQL Sandbox, includes some sample data, an interface for testing SQL code, a number of code examples, and the following four custom functions:
Q – wraps an expression in single quotes. I created this because I hate visually parsing single quotation marks. As I wrote last month,
...first I want digress for a moment to complain about discuss single quotes, a.k.a. apostrophes. They can be difficult to see when they appear adjacent to double quotes — I liken them to an annoying cloud of gnats — so to avoid confusion, I wrote a custom function which I call simply “Q”:
In a nutshell, this CF supplies the surrounding single quotation marks so we don’t have to type them or think about them, and ironically, the above image illustrates the problem perfectly. A single quote surrounded by double quotes is extremely difficult to parse visually. But once you’ve written the CF, you may never have to deal with this problem ever again (until you edit another developer’s SQL code at any rate).
Additionally, this CF escapes any internal single quotes or apostrophes by prepending another one… e.g., O'Malley becomes O''Malley (yes, this is a good thing if you don’t want SQL code to break… don’t worry: the extra apostrophe won’t show up in your data).
GFN – like GetFieldName(), but only returns the field name itself — essential if you want to protect your code from the hazards of field renaming. Note: since this CF uses the GetFieldName function, it requires FM 10 or later.
Guess which one breaks when someone decides to rename creditCardType to ccType, or idCustomer to idContact? On a related note, in addition to protecting you from ex post facto field renaming, GFN also prevents you from misspelling or entering non-existent field names in the first place.
Also, FQL has over 250 reserved words, including “date”, “action” and “group” — you can see the complete set of reserved words on pages 55–57 of the FileMaker 11 OBDC and JDBC Guide. If you’ve used a reserved word as a field name in your FM database, you normally have to remember to wrap that field name in double quotes… GFN does away with all that by automatically wrapping your field name in quotes for you. There’s no harm done if the field name isn’t a reserved word, and that way you don’t need to worry about it.
(I once wasted an hour debugging a SQL statement referencing a field named “group”, before realizing “group” was a reserved word. An hour of my life, gone forever. But it inspired me to add the quote wrapper to GFN, so something good came out of it.)
GFN’s protective quote wrapper will also deliver you from certain other evils, e.g., in FM 11 (with its newly-rewritten SQL parser), the necessity to escape field names if they begin with an underscore, e.g., “_pk_serial”.
DateToSQL – takes a date in a variety of input formats, and outputs it in this format: DATE '2011-02-28', which is apparently the only format that is acceptable to both the new (FM 11) SQL parser, and the SQL parser used in FM 10 and earlier versions.
The input formats are: US-style dates (MM/DD/YYYY), European-style dates (DD/MM/YYYY), and SQL-style dates (YYYY-MM-DD). For example, say you want to grab a list of foreign keys for all customers who have placed an order in the last 30 days…
Assuming today’s date is 26 March 2011, the final line will be rendered as DATE '2011-02-24' and the SQL gods will smile on you. It’s true, they will.
The preceding code examples have consisted of simple SELECT statements; here’s a more complex UPDATE statement, utilizing all three of the CFs we’ve looked at so far. The aim here is to update the tax rate on any unbilled or future-dated invoice line items, if the user changes the default sales tax rate in Settings (this is a single-user system, so I have the luxury of not having to worry about potential record locks by other users).
And finally, we come to a custom function I don’t actually need very often, but that sure comes in handy when I do…
FoundSetToSQL – the concept of a found set does not exist in SQL, but sometimes we need a SQL command to operate only on the records in our current found set. What to do? Well, it turns out that we can say, “Hey, SQL, only process records if the value in a certain field matches one of the values in this list: a,b,c,d,e,…”, and we can build that list from values in the current FileMaker found set. Take for example this found set of 14 records:
FoundSetToSQL ( customers::id ) will return the following…
'000940','000540','001443','001545','000458','000639',
'000794','001629','000048','000973','000735','001139',
'001224','001449'
…as one unbroken string of text. And this can be used by the SQL “IN” operator as part of a WHERE clause, as per this statement, which generates a list of unique cities in the found set.
Warning: FoundSetToSQL is a) not fast with large found sets, and b) subject to the standard 10,000 iteration recursion limit… you don’t want to point it at a found set with more than 10,000 records. There are various ways to work around these limitations, but that is a topic for another day (the second half of this article addresses ways to build a multiline key of values from the current found set).
In closing, I will risk stating the obvious: these custom functions are not the last word on the subject by any means (especially FoundSetToSQL). Feel free modify and extend them as you see fit, and as always, use at your own risk.
Thank you so much for SQL Sandbox – it’s just the tool I need for now, along with the wonderful 2EmpowerFM SQL Runner plugin.
But I have an embarrassingly simple question… The results in the Customer, Result field look like they have CRs when it’s a list of results.
However with Substitute() etc, it turns out that this character isn’t the CR that FM recognises. How do I identify it and therefore replace it, please?
Hi Mardi,
You need to add this to the end of your epSQLExecute statement:
From what I can tell SQL Runner doesn’t provide a way to set default row and column separators. It appears that you have to specify them with each query, if you want them to be different than the defaults… which in the case of the row separator, is a “pilcrow” character rather than a true hard return.
You can read about this on page 9 of the SQL Runner v2.5 Manual — incidentally, I recommend this informative document to anyone interested in FQL (FileMaker’s internal SQL engine), whether they use SQL Runner or not.
http://www.dracoventions.com/products/2empowerFM/family/sql.php
Hope this helps,
Kevin
Yep, like Kevin says, there’s a section on the use of the “Pilcrow” (what I usually call a capital P with two vertical bars) in the 2empowerFM SQL Runner manual. I basically use that character instead of a literal line break because the values being returned may contain literal line breaks, but they’ll rarely contain “Pilcrows”. The manual also suggests some even rarer characters you could use that should never appear in your FileMaker data.
If you’re using FileMaker 11, you can avoid the confusion of row/column separator characters by calling epSQLExecute(…; “useSQLResult=Yes”) and then use the epSQLResult(; ) function to get each value you’re interested in.
On the other hand, to answer your original question, you can wrap your epSQLExecute statement in a Substitute like so:
Hi Kevin,
I just came across this post. I’ve been working on some similar stuff as we use SQL more and more on our FileMaker projects (both internally within FileMaker Pro and externally with 360 Works excellent jdbc plug-in).
Really like what you’ve done here. Nice work!
Thanks Brian!
Kevin
This doesn’t work properly if the date is stored/sent as text rather than a date and is in UK format
Made the following change to the CF by adding a pFormat parameter to indicated dd/mm/yy, mm/dd/yy, or yy/mm/dd formats
// amendements J Renfrew
// added pFormat parameter
// pFormat <= 1 = dd/MM/yyyy; 2 = MM/dd/yyyy; 3 = yyyy/MM/dd
Let (
[_a = Substitute ( GetAsText ( pDate ) ; "'" ; "" ) ; _b = Substitute ( _a ; ["/" ; ¶] ; ["-" ; ¶ ] ) ; _c = Case ( pFormat = 3 ; GetValue( _b ; 1 ) & "-" & GetValue( _b ; 2 ) & "-" & GetValue( _b ; 3 ) ; pFormat = 2 ; GetValue( _b ; 3 ) & "-" & GetValue( _b ; 1 ) & "-" & GetValue( _b ; 2 ) ; GetValue( _b ; 3 ) & "-" & GetValue( _b ; 2 ) & "-" & GetValue( _b ; 1 ) ) ; _d = If (Middle ( _a ; 5 ; 1 ) = "-" and Middle ( _a ; 8 ; 1 ) = "-" ; True ) ] ;
"DATE '" & If ( _d ; _a ; _c ) & "'"
)
Hi John… I appreciate your comment and suggestion!
Kevin
For you to test – the same thing as your date function with all time formats to ISO8601
/*
* timeToSQL(pTime)
* 11_11_18 JR
* v1.0
*
* input: (time/ string), (number)
* return: (string) converts a FileMaker or text time to a SQL-friendly time
*
* NOTE: can take 123000Z, 3:45:00+4, 12:00 AM, 1230.5
* see http://en.wikipedia.org/wiki/ISO_8601 for allowed formats
* TODO adjust values for offsets
*/
Let ( [
_origTime = Substitute ( GetAsText ( pTime ) ; "'" ; "" ) ; // strip off single quotes, if any, in case this is a SQL-style date
_a = Substitute ( _origTime ; [":" ; ¶ ]; ["." ; ¶ ]; ["," ; ¶ ]; ["Z" ; ¶ ]; ["+" ; ¶ ]; ["-" ; ¶ ]; [" " ; ¶ ] ) ; // create arrayList
_b = GetValue( _a ; 1 ) ;
_fraction = ( GetValue( _a ; 2 ) / 100 ) * 60 ;
_c = Case ( Length ( _b ) 5 ) ; Left ( _c ; 2 ) & ¶ & Middle ( _c ; 3 ; 2 ) & ¶ & Right ( _c ; 2 ) ; Length ( GetValue( _a ; 1 ) > 3 ) ; Left ( _c ; 2 ) & ¶ & Middle ( _c ; 2 ; 4 ) & ¶ & GetValue ( _a ; 2 ) ; _a ) ; // deals with UTC time
_adjTime = Right ( "00" & GetValue( _a ; 1 ) ; 2 ) & ":" & Right ( "00" & GetValue( _a ; 2 ) ; 2 ) & ":" & Right ( "00" & GetValue( _a ; 3 ) ; 2 ) ;
_sqlTime = If (Middle ( _origTime ; 3 ; 1 ) = ":" and Middle ( _origTime ; 6 ; 1 ) = ":" ; True )
] ;
"TIME '" & If ( _sqlTime ; _origTime ; _adjTime ) & "'" // if already SQL format, pTime, else enum time
)
Hey Kevin,
Nice work on all this stuff. I have one question about your GFN function.
Why only return the field Name and not the fully qualified name, including the table name?
in other words why not this?
GFN(Contacts::FirstName) -> “Contacts”.”FirstName”
instead of this?
GFN(Contacts::FirstName) -> “FirstName”
Sometimes you need the fully qualified name. And I don’t think it causes any harm when your don’t.
Just curious…
Todd
Hi Todd,
Some combination of laziness and fear I suppose. As you know, there was no road map for early FQL (FM/SQL) adopters, so a lot of this stuff was figured out by trial and error. I say “fear” because I have several years worth of legacy code in a variety of projects using some variation of GFN and don’t dare change it now in existing projects for fear that something somewhere will break.
But I think what you propose would be fine… at any rate, I can’t think of a good counter-argument at the moment.
FWIW,
Kevin
Thanks For the reply. I just wanted to make sure you hadn’t come across a reason NOT to do it. Most of the SQL wrapper code in other Languages like PHP, will produce fully qualified names. I am pretty sure that it is standard stuff.
Thanks again
Todd
Great site. I really learn a lot from visiting. Thank you for creating it!
I am using FM 12 Adv.
In my single user app I have an import script that allows the current / target to receive source data into the correct tables . It works.
Part of that script creates Source::$$Filename from whatever Source file the user selects to import.
However, if I add a table in the Target it is not necessarily in the Source, and the import gets all screwed up.
I need a way to look into the Source and see if that table is present. I thought I would use ExecuteSQL but it REQUIRES that the Source file be on the Relationship Graph of the Target. Since I have no way of knowing what the file name of the Source prior to the import script being run, I need a way to get that Target::$$Filename into the Relationship Graph or a way to use ExecuteSQL to read Target::$$Filename *without* it being on the Relationship Graph.
(Everyone else says I am ‘out of luck’ and ‘you can’t get there from here’. ) I hope you have an idea you can share.
Thanks
Ron