FM SQL template

[Darren Burgess]

I am not sure if this ever got officially standardized, but I think I have found a flaw in the sql template as proposed here:

http://filemakerstandards.org/display/props/ExecuteSQL+Abstraction

This is referring to the final code suggestion by Matt (sorry):

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

   )

)

This is broken because SQLDebugResult - at least the version I am using - will never return "?", making the final IF condition always evaluate to true.  Interestingly enough, I think the first proposal at the top of the page got this part of it correct.  Bottomline is that SQLDebugResult is only needed while I am working on building a properly formed SQL statement.  Once I get to that point, I no longer need the function and it can be removed from the code.  So this leaves me to one of two possibilities.

1.  Leave SQLDebugResult in the SQL template code, but comment it out as below. This allows for quick transfer to dataviewer and uncommenting of the condition branch.  SQLDebugResult is then removed from $sqlResult assigment.

...
...
; If ( 
          $sqlResult = "?" 
        ; False // SQLDebugResult ( $sqlResult )
        ; True )
    
)

2.  Modify SQLDebugResult so that it accepts a second debug parameter such as:

SQLDebugResult ( debug ; _executeSQL )
If ( 

//the sql call results in an error, return empty so the error will be returned

_executeSQL = "?" and debug ; "" ; 

//the sql call is executed correctly, just return the result

_executeSQL

)

Given option 2, the template would be as such (please do ignore the leading semicolons, if you can ;-)

Let ( [ 

  ~sql = List (

    "SELECT ~field"

  ; "FROM ~table1 AS t1"

  ; "JOIN ~table2 AS t2"

  ; "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 ( false ; ExecuteSQL ( 

    $sqlQuery 

  ; "" 

  ; "" 

  ; $value 

  ; $value[$n]

  )

)

]

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

    

)

[Perren Smith]

Hi Darren,

I just checked the page history and the code on the page proper (not the last comment by Matt) is the code that should be used and fixes what you’re finding with SQLDebugResult ():

Can you still break things with the code snip at the top of the page (which looks very much like what you’re proposing)?

Hope this helps,

—Perren

--
You received this message because you are subscribed to the Google Groups "FileMaker Development Standards" group.
To unsubscribe from this group and stop receiving emails from it, send an email to fmstandards...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Matt Petrowsky]

I guess I should update these things. Been busy… :)

Here is what I currently use Darren. I’ll put it up on the web site. It includes a commented line, which you can uncomment to preview the SQL and a debug option for using SQLDebugResult.

Matt

Let ( [ ~sql = "
    SELECT t1.~field
    FROM ~table1 t1
    JOIN ~table2 t2
    ON t1.~field = t2.~field
    WHERE ~field LIKE '%~value%'
    AND ~field=?
    ORDER BY ~field";

    $sqlQuery = Substitute ( ~sql ;
        [ "~table1" ; SQLTableName ( Table1::fieldName ) ];
        [ "~table2" ; SQLTableName ( Table2::fieldName ) ];
        [ "~field" ; SQLFieldName ( Table1::fieldName ) ];
        [ "~value" ; Table::field ]
    );

    $sqlResult = ExecuteSQL ( $sqlQuery ; "" ; "" ;
        $value;
        $value[2];
        $value[$n]
    )
];
    //Substitute ( $sqlQuery ; "    " ; "" ) &¶& // sql preview
    If ( $sqlResult = "?" ;
        Let ( ~debug = False ; If ( ~debug ; SQLDebugResult ( $sqlResult ) ; False ) );
        $sqlResult
    )
)

On 7 Jan 2016, at 10:01, Perren Smith wrote:

Hi Darren,

I just checked the page history and the code on the page proper (not the last comment by Matt) is the code that should be used and fixes what you’re finding with SQLDebugResult ():

Can you still break things with the code snip at the top of the page (which looks very much like what you’re proposing)?

Hope this helps,

—Perren

1. Leave SQLDebugResult in the SQL template code, but comment it out as below. This allows for quick transfer to dataviewer and uncommenting of the condition branch. SQLDebugResult is then removed from $sqlResult assigment. ... ... ; If ( $sqlResult = "?" ; False // SQLDebugResult ( $sqlResult ) ; True )

)

2. Modify SQLDebugResult so that it accepts a second debug parameter such as: SQLDebugResult ( debug ; _executeSQL ) If (

To unsubscribe from this group and stop receiving emails from it, send an email to fmstandards...@googlegroups.com fmstandards...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout https://groups.google.com/d/optout.

[Darren Burgess]

Yup, like this latest version you are using Matt.  Only rev I would make for my purposes is to would be to use the List() function for the lines of the SQL query.

[Matt Petrowsky]

Interesting, so you don’t mind seeing a multi-line statement then?
I’ll have to try that out. Will switch up my template and see how it
goes.

[Matt Petrowsky]

I think the extra sets of quotes, because of the List(), will get in the
way of composing a clean statement.

On 7 Jan 2016, at 11:06, Darren Burgess wrote:

[Darren Burgess]

Agreed.

I prefer a multiline statement for readability if I interrogate $sqlQuery.

I think though you suggested previously to use substitute() to generate
a readable $sqlQuery. Saw something I think in the comment thread on FM
standards.

That said, it may depend on where the programmer is at in terms of SQL
query writing skills. Back when I was learning this stuff fresh, I
needed all the help I could get to pull off working queries.

Darren

[Darren Burgess]

List() allows for easy commenting out of lines in the query.  Makes for easier debugging.

[Matt Petrowsky]

True.