4 Replies Latest reply on Apr 10, 2014 9:01 AM by MorkAfur

    Basic ExecuteSQL Issue

    MorkAfur

      Title

      Basic ExecuteSQL Issue

      Post


           I'm using FMP 13 and find the ExecuteSQL to be very strange. Often, you just get a "?" with zero explanation or you get nothing. Also the "data viewer" is really tough to use since you still have to use the awful ExecuteSQL syntax to do basic queries -- no simple way to just query data. I also found that the teeny tiny output window cannot be resized making this even more difficult. Why isn't there a usable SQL console built in to this expensive tool???

           --

           To the issue at hand...

           Using the ExecuteSQL in "Lesson 39" in the FMP 13 Pro training course (from FMP) - ExecuteSQL and Virtual Lists, their suggested SQL does not work (see page 541).

           “ExecuteSQL (
  "SELECT Salesperson, Count(Salesperson),
      

           Sum(\"Total\")  
FROM \"ORDER\"  
WHERE DateOrdered>=? and 


           DateOrdered<=? 
GROUP BY Salesperson" ;

           
"|" ; "¶" ; 
Date ( 1 ; 1 ; Year (Get(CurrentDate ))) ; 
Get (CurrentDate)
           )”

           ----


           It returns ... nothing or a ?. There is data for the data interval specified.

           What's going on?

           -----

           Using an external SQL console and connecting to FMP via JDBC (a method that usually works all the times and is SO Much easier), I get an error in SQL Syntax for trying something like:

           Select * from \"ORDER\"

           (There is an error in the syntax of the query)

           Why they created a table with a reserved word is one issue, but other SQL statements (from the remote JDBC console) work fine.

           Select * from Address

           Select * from contact

           etc.

           ---

            

           So, no matter what I try, either the ExecuteSQL from the book or using SQL against the FMP database via JDBC does not work with the "Order" table.

           Any suggestions would be appreciated.

            

           Thanks,

            

           -m

        • 1. Re: Basic ExecuteSQL Issue
          philmodjunk

               I"m puzzled as to why you would get "nothing or a ?". A question mark result is supposed to indicate a SQL syntax error so you should get that result every time if there is an error in the SQL. Did you try clicking/tabbing into the field when you got that result? (a question mark result can mean that the data in the field cannot be displayed due to the field being too small to show all the data...)

               There shouldn't be a syntax error with:

               Select * from \"ORDER\"

               as the fact that the table name is in quotes will keep this from having issues with the fact that ORDER is a keyword.

               If you aren't getting results at all, can you confirm that DateOrdered is defined as a field of type date and not text? Is text specified as the result type for this calculation (assuming you defined a calculation field)?

               PS. ExecuteSQL is very new--introduced in FileMaker 12. We didn't have any built in ability to query with SQL prior to this. It definitely could benefit from a Query builder tool that lets you see your query results and to graphically build join clauses.

          • 2. Re: Basic ExecuteSQL Issue
            MorkAfur

                 Hey Phil,

                 Turns out I had accidentally deleted some data so it was correct that the ExecuteSQL was returning nothing. blush

                 I had forgotten that the remote JDBC console does not require the "\" before the reserved (table, in this case), variable ORDER.

                 Thus, Select * from "ORDER" works fine.

                 Virtual lists are an interesting way to build layouts from SQL queries.

                 ---

                 Thanks as always for your reply. :)

                 -m

                 Hopefully there will be a nice SQL Console where you can just type in raw SQL for quick data mining in 14.

                  

                  

            • 3. Re: Basic ExecuteSQL Issue
              philmodjunk

                   It wouldn't hurt to submit that as a feature request: http://www.filemaker.com/company/contact/feature_request.html

              • 4. Re: Basic ExecuteSQL Issue
                MorkAfur

                     Good idea...done.

                     Thanks,