Custom Functions for FQL

[Kevin Frank]

The attached demo file, "SQL Sandbox" includes four custom functions that I demoed at POE NYC last month.

Q - wraps an expression in single quotes -- I wrote this because I hate visually parsing single quotation marks

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.)

GFN - like GetFieldName(), but only returns the field name itself -- essential if you want to protect your code from the hazards of field renaming. Also, FQL has over 300 reserved words, including "date", "group" and "action". 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.

DateToSQL - takes a date in a variety of formats, and renders it in this format: DATE '2011-02-28'

Specifically, it can handle US-style dates (MM/DD/YYYY), European-style dates (DD/MM/YYYY), and SQL-style dates (YYYY-MM-DD).

FoundSetToSQL - helps SQL queries to operate on your current found set (normally SQL has no concept of a found set)

FWIW,

Kevin

SQL Sandbox.zip

[Kevin Frank]

Incidentally, more info about SQL Sandbox and the custom functions is available here:

http://www.filemakerhacks.com/?p=1065

-- Kevin

[john renfrew]

Kevin

In the event that the found set is more than the recursion limit this little SM function might work instead...

Could do with some testing with a large data set

// RipSQL_ID ( fm_field )

// 11/11/20 JR

// v1.0

// pass fm_field as qualified field name





def sqo = { txt ->

"'" + txt + "'"

}

num = fmpro.evaluate("Get(FoundCount)").toInteger()

list = ''

for (i in 1..num){

id = sqo(fmpro.evaluate("GetNthRecord($fm_field; $i)"))

list = list + (list ? ',' : '') + id

}

return list

The CF then becomes

RipSQL_ID( GetFieldName(primaryKey) )

[john renfrew]

And a first version of a function timeToSQL to do TIME '12:00:00'

/*

 * 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, 300 PM

 * 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 ; Right ( "00"  & GetValue( _a ; 1 ) ; 4 ) & Case ( PatternCount(pTime ; ",") ; _fraction ; PatternCount(pTime ; ",") ; _fraction ; "00" ) ; Right ( "00"  & GetValue( _a ; 1 ) ; 6 ) ) ;

_a = Case ( Length ( GetValue( _a ; 1 ) > 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 ) + If ( PatternCount ( pTime ; "PM" ) ; 12 ; 0 ) & ":" & 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



)

[rtentinger]

I have downloaded the SQL Sandbox and in the sandbox trying to run on the first name

ExecuteSQL("SELECT first FROM customers WHERE first='Rita'" ;"";"") with no results ...why can I then run:

ExecuteSQL("SELECT zip FROM customers WHERE zip ='93105'" ;"";"") and get results?? both are text fields??

Thanks in advance

[beverly]

The WHERE is case-sensitive. If you don't have an EXACT match of 'Rita' in the first field, it will not return results.

Perhaps you need the LIKE:

WHERE LOWER(first) LIKE 'rita%'

... will match on any case, because the comparison converts the field to lower case and compares. The LIKE is not the "=" (exact match) by adding the "%" wild card character. So "Rita", "rita", "Ritamia" will be found.

HTH,

Beverly