I tested your SQL statement in my environment and it worked properly.
My setup is :
Server - FMS Advanced 120V4
OS - Mac OSX 10.7.5
Client - SQuirrelSQL 3.5.0
One thing to mention - SQuirrelSQL uses jdbc driver.
I've seen this kind of behaviour, but when I restart fmxdbc_listener, everything works well.
Thanks for your comment. I wonder if we can use the jdbc driver instead? Might look into this. Also, we will restart the fmxdbc_listener, just for kicks, but if that were the problem, I would have expected all queries to fail.
I would suspect you are having an ODBC driver issue. Have you made sure you have the most recent ODBC driver version? And, yes, you could use a JDBC driver too.
Yep, we have double checked the driver. We have tried both the driver from the clean 12.0v4 installer which is identical (at least in byte count) to the updated installer on the downloads page.
I'm suspicious because 2 separate computers in different sites are having the same issue.
I take it you are trying a FileMaker import and that is where you are making the import query. Out of curiosity, does it work in ExecuteSQL in the FM calculation engine? Does it work if you use another program like importing from FM Server to Excel or Access? Sberger got it to work with SQuirrelSQL, albeit with a different (JDBC) driver.
Hi Taylor, We are not doing a FileMaker import. This is a 3rd party application that is querying FileMaker Server.
As a _test_, we have tried the FileMaker import with the same results and checked out the syntax with query builder. query builder also fails on wildcard searches.
I would reinstall FileMaker Server and the ODBC driver. I have performed the Like SQL call in many queries on FileMaker and it works. So for it to not work for you means something under the hood is not working. Out of curiosity, does it work with the ExecuteSQL function in the FileMaker calculation engine? My guess still is that it is the ODBC driver. But if you do a clean install of FileMaker server and the ODBC driver, that should clean things up. Actually, if I were you, I would uninstall FMSA, then upgrade to the OS to 10.8, and then reinstall FMSA. The Server Admin for Mac OS X is better in 10.8 than 10.7 with everything in one place. It seems pretty obvious to me that Apple released 10.7 server without having the tools complete, which is why you had to use previous tools in addition to the new ones to get all of the fuctions. They are all consolidated in 10.8. Just my 2 cents.
Oops, after posting this, I realized, it was Sberger using Mac OS X. You are using Windows 2008, R2, which is just fine. Don't upgrade that OS. Just reinstall FileMaker Server and the ODBC driver.
Thanks for that suggest Taylor. We had already clean installed FileMaker Server as well as the ODBC drivers at the production site and at the test site and both still exhibit the issue.
This is why I am looking at the drivers. I do observe that the FileMaker downloads site lists "xDBC update for FileMaker Pro and FileMaker Server" as an update for FileMaker Server 12.0v3 and yet FileMaker Server 12.0v4 is the current version.
Does it exhibit the same issues in ExecuteSQL too?
Does it exhibit the same issue when using a different field or different table?
Hmmm...just fired up FileMaker on the test machine, ran ExecuteSQL and it also failed.
Then tried a similar query on a different table (same file) and it worked.
I smell corruption.
Recovering the file on the test machine detected an issue <sigh!>. Reloaded onto the server and the sql queries (and the executesql) is now working.
Thanks for the ideas, guys.