Jump to content

Custom Functions for FQL


This topic is 3761 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

  • 4 weeks later...
  • 7 months later...

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

Link to comment
Share on other sites

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



)

Link to comment
Share on other sites

  • 10 months later...
  • Newbies

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 3761 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.