FileMaker@ATL



Post Reply 
 
Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
ExecuteSQL( )
03-08-2013, 10:16 AM (This post was last modified: 03-08-2013 10:23 AM by lhoong.)
Post: #1
ExecuteSQL( )
Thanks to all the folks who attended my demo and made it such a fun evening!

I've uploaded my demo files as a .zip archive, which contains:

ExecuteSQL101 - Keynote file used during the demo
PDF of the slides for those who do not have Keynote or are on Windows
Contacts.fmp12 - the database used for the demo

The resources sections for ExecuteSQL101 and the PDF contain live links - click to open the URL in your browser.

In addition, here are some sample SQL queries you can try (e.g. via the Data Viewer) using Contacts.fmp12. We covered some of these during the demo:


ExecuteSQL ( "
SELECT \"CONTACT ID MATCHING FIELD\"
FROM Contacts

WHERE GroupName='Jedi'" ;

"" ;
"" )

Results:

1
2
3
5
9

__________________________________________

How many Jedis? Using an aggregate function (count):

ExecuteSQL ( "
SELECT Count (\"CONTACT ID MATCHING FIELD\")
FROM Contacts
WHERE GroupName='Jedi'" ;

"" ;
"" )

Results:

5

_________________________________________

ExecuteSQL ( "
SELECT FirstName+' '+LastName
FROM Contacts
WHERE GroupName='Sith'" ;

"" ;
"" )

Results:

Darth Vader
Darth Maul
Darth Sidious

_______________________________________________________

ExecuteSQL ( "
SELECT FirstName+' '+LastName
FROM Contacts
WHERE \"Job Title\" LIKE '%Jedi%'" ;

"" ;
"" )

Results:

Luke Skywalker
Obi Wan Kenobi
Yoda
Qui-Gon Jinn
Mace Windu

______________________________________________________

ExecuteSQL ( "
SELECT FirstName+' '+LastName
FROM Contacts
WHERE \"Job Title\"=?" ;

"" ;
"" ;
Contacts::Job Title )

Results:

List of contacts with same Job Title as current record.

_____________________________________________________

Show only unique values:

ExecuteSQL ( "
SELECT DISTINCT \"Job Title\"
FROM Contacts
WHERE GroupName='Jedi'" ;

"" ;
"" )

Results:

Jedi Grand Master
Jedi Knight
Jedi Master

________________________________________

Example of constructing XML - also return the results by group name and last name (descending order):

ExecuteSQL ( "
SELECT '<contact>¶ <fname>'+FirstName+'</fname>¶ <lname>'+LastName+'</lname>¶ <gname>'+GroupName+'</gname>¶</contact>'
FROM Contacts
ORDER BY GroupName, LastName DESC, FirstName" ;

"" ;
"" )

Results:

<contact>
<fname>Mace</fname>
<lname>Windu</lname>
<gname>Jedi</gname>
</contact>
<contact>
<fname>Luke</fname>
<lname>Skywalker</lname>
<gname>Jedi</gname>
</contact>
<contact>
<fname>Obi Wan</fname>
<lname>Kenobi</lname>
<gname>Jedi</gname>
</contact>
<contact>
<fname>Qui-Gon</fname>
<lname>Jinn</lname>
<gname>Jedi</gname>
</contact>
<contact>
<fname>Yoda</fname>
<lname></lname>
<gname>Jedi</gname>
</contact>
<contact>
<fname>Leia</fname>
<lname>Organa</lname>
<gname>Royalty</gname>
</contact>
<contact>
<fname>Padme</fname>
<lname>Amidala</lname>
<gname>Royalty</gname>
</contact>
<contact>
<fname>Darth</fname>
<lname>Vader</lname>
<gname>Sith</gname>
</contact>
<contact>
<fname>Darth</fname>
<lname>Sidious</lname>
<gname>Sith</gname>
</contact>
<contact>
<fname>Darth</fname>
<lname>Maul</lname>
<gname>Sith</gname>
</contact>

____________________________________________

Create a self-join relationship on-the-fly:

ExecuteSQL ( "
SELECT c1.\"Contact Name\", c2.FirstName+' '+c2.LastName
FROM Contacts AS c1, Contacts AS c2
WHERE c1.\"CONTACT ID MATCHING FIELD\"=c2.TrainedBy" ;

" trained " ;
"" )

Results:

Yoda trained Luke Skywalker
Jinn, Qui-Gon trained Obi Wan Kenobi
Sidious, Darth trained Darth Vader
Sidious, Darth trained Darth Maul

________________________________________________________


Have fun exploring SQL! Post your questions here in the forum.

Lee.
=================
Binary Assist
220 Gaines Oak Way
Suwanee, GA 30024
Voice: (678) 313-5604
Internet: lhoong@binaryassist.com
Website: http://www.binaryassist.com

FileMaker Certified Developer


Attached File(s)
.zip  ExecuteSQL demo files.zip (Size: 1.93 MB / Downloads: 2)
Find all posts by this user
Quote this message in a reply
04-11-2013, 08:36 AM
Post: #2
RE: ExecuteSQL( )
At the ATL FM Developers Meeting last night, there was a question about debugging errors when using the ExecuteSQL( ) function - normally the user will see "?" displayed, which of course, is so very informative. There is a way to return the error description using the following custom function viewed in the data view (FileMaker Pro Advanced required):

http://fmcustomfunctions.com/fid/335

Description of the custom function from the link above:

Quote:This function will help you to debug your SQL statement if it returns a "?". Unfortunately this technique only works in the dataviewer (and not even with the "Edit Expression" window open, so you need to click on "Monitor" first).

Actually what we discovered is that when you send an ExectuteSQL ( ) calculation to a custom function, when evaluated in the dataviewer, you will actually see the error returned by the internal FQL engine of FileMaker.

Enjoy!

Lee.
Find all posts by this user
Quote this message in a reply
04-11-2013, 11:28 AM
Post: #3
RE: ExecuteSQL( )
I just tried it, it works! Very cool.
Find all posts by this user
Quote this message in a reply
04-11-2013, 02:03 PM
Post: #4
RE: ExecuteSQL( )
(04-11-2013 11:28 AM)jesse Wrote:  I just tried it, it works! Very cool.

Still kinda kludgy tho…why isn't this available outside the data viewer? - it's like FMI assumes we're going to write perfect SQL queries every single time...
Find all posts by this user
Quote this message in a reply
Post Reply 


Forum Jump:


User(s) browsing this thread: 1 Guest(s)