MightyData

FileMaker and WordPress Consultants

  • Company
    • About
    • Contact
    • Team
    • Artists
  • Services
    • Consulting
    • Development
    • Our Process
    • Coaching
    • Training
  • Results
    • Customers
    • Success Stories
    • Testimonials
  • Blog

Tips for Debugging FileMaker SQL Queries

October 11, 2012 by Darren Burgess 3 Comments

Bad SQL query in Data Viewer

Our loyal readers will know that we have published several articles extolling the virtues of the new ExecuteSQL function in FileMaker 12. Well, now it’s time to come clean and tell you the truth about this powerful function and its hidden ability to turn your hair prematurely grey, cause you to lose sleep, or perhaps develop an annoying and embarrassing facial tick.

If you intend to embark on the rewarding path of integrating SQL into your FileMaker development, be warned. And be prepared, as one simple punctuation character, innocent in all other contexts among our many human languages, may lead to premature aging.

? Marks The Spot

This character is the lowly question mark (?), and is the only feedback provided by the FileMaker calculation engine when you write malformed SQL statements within the ExecuteSQL function.  SQL queries must be syntactically perfect in order for them to work. When you start writing SQL in FileMaker, you are stepping out of the safety of FileMaker’s tightly controlled programming environment that, for example, does not allow you to specify fields, scripts or other objects that don’t exist in the system.

Here is a short list of syntax errors that will cause your SQL query to return a question mark and for you to lose a few minutes off of your projected lifespan:

  • Using one of the 250-odd SQL reserved words as a table or field name
  • Using unacceptable characters (like an underscore ‘_’) as the first character of a field name
  • Using SQL syntax that is not accepted by the FileMaker SQL API (there are many versions of SQL)
  • Improper quoting of elements in the query
  • Improper expression of date syntax

Fortunately for you, I have sacrificed some of my naturally colored hair and a few precious minutes off of my lifespan to bring you some useful tips for surviving the FileMaker/SQL learning curve. Most notably, I want to explore how to use the script debugger and data viewer to assist in the debugging of your SQL queries.

SQL for Beginners

First off, this is not going to be an extensive tutorial in using SQL with FileMaker.  If you are just getting started, I would recommend the following resources:

  • Kevin Frank has a number of blog posts and custom function resources at: www.filemakerhacks.com
  • Anders Monsen introduces some SQL ideas in this post.
  • W3Schools.com has a decent SQL beginners tutorial available.
  • FileMaker’s ODBC/JDBC Guide has useful reference information, including a list of those dreaded SQL reserved words.

How to Debug SQL in FileMaker

Efficient debugging of SQL queries is best accomplished by building the query within a Let function. Here is an example calculation whose job is to determine if the record about to be created in the script “Create Conversion” is the first record of a certain type to be created after a particular date in the Person’s record called the InquiryResetDate:
Let ( 
// variables
[
  type = Q( $type ) ;
  resetDate = Q( PeopleLayout » People::inquiryResetDate ) ;
  pID = Q( PeopleLayout » People::id ) ;
  SQL = "
    SELECT COUNT ( id )
    FROM Conversions
    WHERE type = " & type & "
    AND id_Person = " & pID & "
    AND creationDate >= " & resetDate ] ;

// calculation 
Case ( ExecuteSQL ( SQL ; "" ; "" ) ≥ 1 ; 0 ; 1 )

//DEBUGGING
//type
//resetDate
//pID
//SQL
//ExecuteSQL ( SQL ; "" ; "" )

)   // end LET
In this case, the SQL query is relying on data values from various local script variables and fields, so we start our Let function by pushing these various values into Let variables. Any of these values can drive unexpected results in the SQL query if they are not properly set up by the script and calculation, so pushing the values into Let variables improves our debugging chances later.  (Note that Q() is a custom function courtesy of Kevin Frank that wraps a value in single quotes):
Let ( 
// variables
[
  type = Q( $type ) ;
  resetDate = Q( PeopleLayout » People::inquiryResetDate ) ;
  pID = Q( PeopleLayout » People::id ) ;
Next, the SQL query itself is set to a Let variable by combining SQL statements with other defined Let variables to construct an SQL query. Note that careful, readable formatting of the query allows for simplified debugging later:
SQL = "
  SELECT COUNT ( id )
  FROM Conversions
  WHERE type = " & type & "
  AND id_Person = " & pID & "
  AND creationDate >= " & resetDate
Finally, in the calculation section of the Let function, we return a calculation and include, commented out, each of the elements that make up the Let function:
// calculation 
Case ( ExecuteSQL ( SQL ; "" ; "" ) ≥ 1 ; 0 ; 1 )

//DEBUGGING
//type
//resetDate
//pID
//SQL
//ExecuteSQL ( SQL ; "" ; "" )

)   // end LET

Debugging in Action

And now the fun can begin!  If my ‘Create Conversion’ script is not working, and I suspect a faulty SQL query, I run the script with debugger on. When the debugger runs the step in question, the value for variable $isFirstAfterResetDate will return a “?”:

Bad SQL query in Data Viewer

I then open the script, grab the calculation from the step, and drop it into the data viewer. Since the script is currently running, I am assured that all of the conditions of layout context and field/variable values that may be used in the calculation are in force. I then use the Let function to return the values of the various elements in the calculation by uncommenting them in the data viewer.

Here is the original calculation, returning the result the script is expecting:

SQL calculation with valid syntax

We can return one of the Let variables to make sure a valid and properly quoted value exists for that variable:

Record ID with single quotes

And here we return the SQL query itself, to ensure that it is properly formed.  (Of course the usefulness of this assumes that I know what properly formed SQL statement looks like!):

Check SQL query syntax

Once you have started creating SQL queries in FileMaker that actually work, save them in a library.  I use Notational Velocity to maintain my library of valid, working SQL queries. Having this as a resource is extremely valuable when it comes time to create a new query of particular type or style that already exists. It is perhaps the most valuable method for debugging SQL – which is simply using proven calculations – and makes it much more likely that your SQL queries will not be buggy and lead to that dreaded, hair-greying, insomnia-producing and lifespan shortening punctuation mark. I wish you the best of luck in the hard work of learning SQL. Post a comment if you have any questions!

Filed Under: Calculations Tagged With: Best practices, ExecuteSQL, Notational Velocity

Getting Faster: Create a Development Reference

June 21, 2012 by Darren Burgess 1 Comment

Calculation functions in Notation Velocity

As a developer looking to deliver projects to my customers with the highest value, I am constantly looking for ways to increase my development speed. To that end I have created a developers’ reference library using a handy (and free) little application called Notational Velocity.  NV is an OS X application that allows for rapid creation and search of notes. While I have not tested such, apparently ResophNotes is an equivalent Windows application.  Both applications sync with SimpleNote, so you can get your notes on the web and on an iOS device.

Notation Velocity Library

NV uses a search-while-you-type field to allow you to search your database of notes. If the application does not find a match, you can simply hit enter and it creates a new note with the title you typed in the search field. Notes are stored as text files in a directory of your choosing and the app provides the necessary keyboard shortcuts for the power user.

SQL reserved words in Notation Velocity

My NV library currently has about 300 entries that include:

  • All of the FileMaker error codes
  • About half of the calculation functions
  • Various tested SQL statements
  • SQL reserved words
  • Commonly used calculations

Naming Conventions

Using naming conventions helps to organize the library. For example,  the FileMaker calculation function notes are named “fmfk” + FunctionType + Function Name, so that I simply can type “fmfkdes val” if I want to find the note regarding the ValueListIDs function:

Calculation functions in Notation Velocity

Hint: NV stores notes in a database in the application support folder on OSX. In preferences, you can change the location and choose to store the notes as plain text files.

My Development Library

So, who wants to collaborate on building a more comprehensive reference library?  I would be happy to share what I have created so far.  We could expand the library to include:

  • FileMaker Server error codes
  • The balance of the calculation functions
  • Windows and Mac keyboard shortcuts
  • Port numbers
  • Script steps
  • And more

You can download the current version of the library below.  If you have a suggestion to add to the library, please post a comment below.

20120626 Developers Reference

Filed Under: Rapid Development Tagged With: Demo file, Development standards, Notational Velocity, Productivity, Software tools

Let’s get started on your project.

It will be more fun than you think.

Get in Touch

  • Company
  • Services
  • Results
  • Blog

Copyright © 2023 · Parallax Pro Theme on Genesis Framework · WordPress · Log in