ExecuteSQL() and the separation model

[normanicus]

Does ExecuteSQL() enable a more efficient, simple to implement separation model while still using Filemaker to store the data? I expect this will largely depend on how efficiently the function is implemented,

Norman

[Wim Decorte]

Maybe to some extent... but remember that you can only use it to SELECT data. Doesn't work for INSERT or UPDATE

[iDEViate]

You cannot do anything that alters schema in anyway with the new function.

[David Jondreau]

At first blush I thought ExecuteSQL() was going to be the capstone to the separation model...after all you can define "context free" calculations. In a script, for example, you can do a "find" or a "lookup" without navigating anywhere and without a relationship. So why not calculations that use ExecuteSQL() instead of related data, doing away with 80% of all relationships?

Unfortunately, you can't use an unrelated field in a field calculation! Aargh.

You can still wrap the field name in an Evaluate(), but you lose some flexibility there. I think it's a great tool for the separation model, depending on your tolerance for Evaluate() wrapping.

[mr_vodka]

Also with using a few carefully placed global fields in relationship and stuffing the results into variables, it help with the SM a lot. I only use SM in my solutions and the number of TOs have gotten reduced bigtime. However, with the speed issues of the layout renders, I am sticking to 11 until they work it out ( or if they ever work it out )

[normanicus]

So, given the issues that make ExecuteSQL() of limited use for the separation model but the promise it holds out, does the plug-in potential of Filemaker 12 offer the possibility of someone producing a 'Super' ExecuteSQL() that overcomes these problems?

[Vaughan]

However, with the speed issues of the layout renders, I am sticking to 11 until they work it out ( or if they ever work it out )

Just wait 18 months for the hardware to catch up. Remember how much slower FMP 7 was than FMP 6, and all the problems it had.

[normanicus]

Yes, I regarded FMP7 as FM8 alpha. It was full of good ideas but awful. If Microsoft had brought out something like that their share price would have bombed. How does Apple continue to live in its halo now it is the behemoth?

[Vaughan]

Yes, I regarded FMP7 as FM8 alpha. It was full of good ideas but awful. If Microsoft had brought out something like that their share price would have bombed. How does Apple continue to live in its halo now it is the behemoth?

That's an interesting question. IMHO it is proof of the principle that SHIPPING product beats all other alternatives.

Remember that FMI is NOT Apple.

[Darren Burgess]

One must be careful when using SQL in a solution with a mind for testing for performance.  SQL can be excruciatingly slow over the WAN.  In those cases, I have found that FileMaker relationships can be much faster.  http://www.mightydata.com/blog/results-vs-techniques-filemaker-sql-wan-performance-troubles/

 

Darren Burgess

[dbfreq]

This is my first post, so, please go easy on me...  I've only been using FM for 3 weeks or so.

 

I've got a separation model.  In the data file, I have a CON__Contact entity, with EML__Email, ADD__Address, PHN__Phone, etc., all of which are linked to the primary key of CON__Contact via a foreign key of _id_contact.  

 

In my interface file, I want to show a Tab Control, with a separate tab for each of phone, email, address, etc.  For the phone tab, I have two text fields -- one for the value and one for the key.  The ExecuteSQL statement is set to insert CR at the end of each record, so, I get one record per line.  The keys align visually with the values, which are returned via a similar ExecuteSQL statement (pasted below).

 

In my data file, in the CON_Contact entity, I've created calculation fields for keys and values for each of phone, address, etc. (i.e., z_phoneKeys, z_phoneValues).  The calculation is an ExecuteSQL that returns either the phone's key ("Home", "Work", "Mobile") or its value ("555-555-1212").  Both are ordered by a separate field, sortIndex, to keep the key with the correct value.

 

One of the menu items in my main menu is Contacts.  Clicking Contacts brings up the ContactsLayout (the table for which is in the UI file).  The ContactsLayout is a master-detail view, with a portal on the right listing all contacts.  When a contact in that portal is clicked, its _id is set to a global ("contact_SEL_g"), which updates the detail portal to show the first, middle, last, company name, etc.  So, that's all working fine.

 

The problem is that the phone, email, address, etc. data in the tab panel in the detail view is not updating.  I could have sworn it was working yesterday, but, today, it isn't.  

 

I've copied and pasted the ExecuteSQL statement into the Data Viewer in both the data and the UI file and it works perfectly every time.  WIth each click, the global variable updates and the result of the ExecuteSQL statement in the Data Viewer shows the correct information.  But, the tab control on the detail portal isn't updating with that new information.  It never changes.

 

The calculation fields being fed by the ExecuteSQL statements are neither stored nor global.

 

This is the ExecuteSQL statement, copied from the Data Viewer.  It works as expected in both files.

 

ExecuteSQL (

"

SELECT z_addressOneLine FROM ADD__Address WHERE "_id_contact" = ? ORDER BY sortIndex

"

; "" ; Char(13) ; CON__Contact::_id)

 

 

I copied this variation from the Data Viewer in the UI file.  It also works as expected (the difference between the two is that in the function below, I'm referencing the global variable for the selected contact's id).  

 

 

ExecuteSQL (

"

SELECT z_addressOneLine FROM ADD__Address WHERE "_id_contact" = ? ORDER BY sortIndex

"

; "" ; Char(13) ; LCON__ContactsLayout::_id_contact_SEL_g)

 

 

I've tried adding a script step to Refresh Window and I've tried setting a variable to itself to get the tab control's contents to refresh, but neither worked.

 

 

To summarize, I think the function is fine and I'm seeing what I want in the Data Viewer in both the UI and the data file, but the text fields in the tab control are not updating to show the information that's being displayed in the Data Viewer.

 

Any thoughts?

 

Thanks.

 

Brad

[dbfreq]

Quick follow-up:

 

If I put the ExecuteSQL statements in calculation variables in the UI file (in the LCON__ContactLayout entity), things work fine (again?) in the UI file / tab control.

 

I was hoping to do the ExecuteSQL in the data file, thinking that it might execute on the server and only return the results to the clients.  It makes sense to me that that's the way it would work, but can someone confirm that?

 

Any arguments in favor of trying to make things work with the ExecuteSQL statements in the data file, instead of the UI file?

[ggt667]

You cannot do anything that alters schema in anyway with the new function.

What do you mean by this? New function vs old function vs alter schema.

Does this mean that some versions of FQL does not support CREATE/DROP TABLE?

[Ron Cates]

I find the most annoying thing about ExecuteSQL() is that, although it functions without the need for FileMaker relationships. The calculation dialog boxes will not allow you to include an unrelated field in a calculation. So you either have to wrap it up in Evaluate() or create temporary relationships to be able to get the calculation dialog to accept the calculation and close. But then if they are temporary and you remove them afterwards, if you ever need to edit the calculation you have to recreate the relationships to edit them. So the biggest pain I find is in FileMaker's error checking of calculations. They need to adjust it to recognize that whatever is within an ExecuteSQL() function is not subject to non related field errors.

 

At least that's been my understanding of it. Unless I'm missing something. I have admittedly only recently started using ExecuteSQL() because I had very little prior experience with SQL. But as I'm learning it I have found some extremely convenient applications for it. Maybe the error checking will be addressed in the next version.

[eos]

This (hopefully) shows the difference between retrieving field and table names as references, and the need to read field values you want to pass as parameters.

ExecuteSQL ( 
  " SELECT " & Quote ( GetValue ( Substitute ( GetFieldName ( aTOTotallyUnrelatedToTheCurrentContext::field1 ) ; ":" ; ¶ ) ; 2 ) ) &
  " FROM " & Quote ( GetValue ( Substitute ( GetFieldName ( aTOTotallyUnrelatedToTheCurrentContext::field1 ) ; ":" ; ¶ ) ; 1 ) ) & 
  " WHERE " & Quote ( GetValue ( Substitute ( GetFieldName ( aTOTotallyUnrelatedToTheCurrentContext::field2 ) ; ":" ; ¶ ) ; 2 ) ) & 
  " = ?" ;
  "" ; "" ; ATOThatNeedsToBeRelatedAtThisPoint::aFieldFrom 
 )

For obvious reasons you will want to abstract the calculations into two CFs.

 

They have the added benefit that you don't bury your field references in a string, so the field name expressions are monitored by the Calculation Engine and updated on TO or field name changes. 

 

But if there was really the need to get the value of a currently out-of-context field, instead of Evaluate() use GetField ( GetFieldName ( someField::SomeUnrelatedTO ) ), where using GetFieldName() delays the evaluation.

 

Put this expression into the Data Viewer, and you'll be able to close the dialog (though the result will be ?). Switch context to SomeUnrelatedTO, and you'll see the field value.

 

That's my (probably) limited understanding of affairs.

[Wim Decorte]

I find the most annoying thing about ExecuteSQL() is that, although it functions without the need for FileMaker relationships. The calculation dialog boxes will not allow you to include an unrelated field in a calculation.

 

 

I don't run into that at all.  Are you talking about the calculation dialogs for calculated fields?  ( I don't use ExecuteSQL() calls in field definitions, only in scripts).

 

This is the calc I use and it lets me specify any field from anywhere, regardless of my context.

"_FieldRef_getField" is a custom function that does what EOS refers to ( the "True" in that CFs is for quoted or not)

Let(

[
_field = _FieldRef_getField ( <any field goes here> ; True ) ;
_table = _FieldRef_getTO ( <any field goes here> ; True ) ; 
_matchfield = _FieldRef_getField ( <any field goes here> ; True ) ;

_sql =
"SELECT " & _field & 
" FROM " & _table & 
" WHERE " & _matchfield & " = ?" 


];


ExecuteSQL( _sql ; "" ; "" ; something )


)

[Matthew F]

Wim, do you mind giving the definition of the custom functions in your post? 

[Wim Decorte]

Here's the custom function to get the field:

 

Let(
[
_fieldRef = If(
PatternCount( fieldRef ; "::" ) = 0 ; GetFieldName( fieldRef ) ;
fieldRef
) ;
_split = Substitute ( _fieldref ; "::"; "¶") ;
_field = GetValue( _split ; 2 )
];

If(
Quoted = True ; Quote( _field ) ;
_field
)

)

The one to get the TO is the same except it gets the first value.

This custom function will work with either just a field name or a reference to a field.

[Ron Cates]

Yes, my experience has been when using it in a calculated field.

[Matthew F]

Thanks for defining the custom function.