Basic ExecuteSQL Issue
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 ))) ;
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
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.