11 Replies Latest reply on Oct 20, 2013 1:50 PM by sumware

    Wild card searches via ODBC failing

    sumware

      Client has a FileMaker Server Advanced (Windows 2008R2) enabled for ODBC queries and appropriate privileges set. I believe the correct drivers are installed (the ones that came with the installation, which are identical to the current downloads).

       

      This query of the server works:

       

      SELECT p_IDClient, p_nameFirst, p_nameLast

      FROM people

      WHERE p_nameLast = 'Smith'

       

      as does

       

      SELECT p_IDClient, p_nameFirst, p_nameLast

      FROM people

      WHERE p_nameLast LIKE 'Smith'

       

      But, putting in a wild card like so:

       

      SELECT p_IDClient, p_nameFirst, p_nameLast

      FROM people

      WHERE p_nameLast LIKE '%it%'

       

       

      gives an ODBC Error: "There is an error in the syntax of the query".

       

      We have tried every form of the above that we can think, but to no avail. The syntax looks great and, if we fire up the FileMaker query builder in a remote copy of FileMaker Pro, the syntax generated is exactly the same as one of the failing queries we tried.

       

      I feel that this is a driver issue, but not sure where to go from here!

       

      Thanks for any suggestions.

       

      Rob

        • 1. Re: Wild card searches via ODBC failing
          SBerger

          Hi Rob,

           

          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.

           

           

          Slobodan

          • 2. Re: Wild card searches via ODBC failing
            sumware

            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.

             

            Rob

            • 3. Re: Wild card searches via ODBC failing
              taylorsharpe

              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. 

              • 4. Re: Wild card searches via ODBC failing
                sumware

                Hi Taylor,

                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.

                Rob

                • 5. Re: Wild card searches via ODBC failing
                  taylorsharpe

                  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. 

                  • 6. Re: Wild card searches via ODBC failing
                    sumware

                    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.

                    • 7. Re: Wild card searches via ODBC failing
                      taylorsharpe

                      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. 

                      • 8. Re: Wild card searches via ODBC failing
                        taylorsharpe

                        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. 

                        • 9. Re: Wild card searches via ODBC failing
                          sumware

                          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.

                           

                          r

                          • 10. Re: Wild card searches via ODBC failing
                            taylorsharpe

                            Does it exhibit the same issues in ExecuteSQL too?

                             

                            Does it exhibit the same issue when using a different field or different table? 

                            • 11. Re: Wild card searches via ODBC failing
                              sumware

                              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.

                               

                              Rob