ExecuteSQL as custom function?

[David Nelson]

I have been acquiring my user information via ExecuteSQL so I don't have to attach the Staff table everywhere in the universe.  I write the same sql statement over and over so I thought I might store it somewhere for easy access since its perspective never changes.  Maybe a custom function?

 

Do others think this is a bad idea or how do you store some of these queries that you reuse over and over or do you just make yourself memorize them?

 

99% of my queries are very simple.  I wish I could figure how to take those pieces and write a generic statement where I can insert those pieces, as script parameter or calc parameter.  Example of what I have to write over and over to use in script is similar to:

ExecuteSQL (
"SELECT ID
FROM Staff
WHERE accountName=?" 
; "" ; "" ; Session::accountName
)

Or does my wish to make it easier mean I'm just lazy?   BTW my first test when I created a custom function seemed to work but just because something works doesn't mean it's right to do so that is why I am asking those with extensive experience what THEY would do.  Thank you!

[mr_vodka]

Why not just set the result to a global variable?

[David Nelson]

Hi John!  Thank you for responding!

 

That would work and I do that for the ID - poor example above.  But there are other values and the only difference is the final field - the 'where equal to'

 

So if I want the User's hire date no matter where I was in system, I could use cf similar to:

 

FindUserID ( whereAs )

 

same as above except I could change the whereAS to the final parameter referencing the correct table/field which in above example is:  Session::AccountName.

 

I would load globals with this information but there can be 1,000 users and probably 25 fields so I didn't want to load that all to global variables.  So this would be to find the UserID then I could have another cf to find their other info.  Maybe I could even figure how to replac the other two parts.  This would be only for simple stuff and only the user table.

[mr_vodka]

Why not load all their information into one global variable and then you can always parse out that info since you should only ever have one row returned.

ExecuteSQL (
"SELECT 
      ID,
      firstname,
      lastname,
      title,
      date_hire
FROM 
      Staff
WHERE 
      accountName = ?" 

; "¶" ; "" ; Session::accountName
)

Then you can use GetValue ( ) to parse out specific information.

[David Nelson]

WOW.  I didn't know about using the carriage return with a list of fields like this.  YOU ROCK!!

[Darren Burgess]

David,

 

First off I would suggest you adopt a standard in your SQL that is immune to field renaming.  While it would make your SQL calcs  bit more complex, I have found that the extra work is well worth getting to take advantage FileMaker's renaming capabilities.   I have even gone through old solutions and reworked all the SQL to conform to the standard.

 

Here is the format I use, which was developed by the folks over at www.fmstandards.org.  Note that there are a few custom functions

https://github.com/jbante/FileMaker-Techniques/blob/master/CustomFunctions/SQL/SQLTableName.fmfn?source=cc

https://github.com/jbante/FileMaker-Techniques/blob/master/CustomFunctions/SQL/SQLFieldName.fmfn?source=c

http://www.fmfunctions.com/fid/335

 

 

Let ( [ 

 

sql = "

SELECT ~field

FROM t1.~table1

JOIN t2.~table2

ON t1.~field = t2.~field

WHERE ~field = ?

ORDER BY ~field

";

    

$sqlQuery = Substitute ( sql ;

[ "~table1" ; SQLTableName ( Table1::fieldName ) ];

[ "~table2" ; SQLTableName ( Table2::fieldName ) ];

[ "~field" ; SQLFieldName ( Table1::fieldName ) ]

) ;

    

$sqlResult = SQLDebugResult ( ExecuteSQL ( 

$sqlQuery ; "" ; "" ;

$value ;

$value[2] ;

$value[$n]

)

)

];

 

If ( $sqlResult = "?" ; False ; True )

    

)

 

 

Darren Burgess

http://www.mightydata.com