2 Replies Latest reply on Nov 8, 2013 9:31 AM by Dave_3

    Using SQL through PHP to FMpro 11v3 Server, with special characters ?

    Dave_3

      Title

      Using SQL through PHP to FMpro 11v3 Server, with special characters ?

      Your post

           Hi All,

           I am not a FileMaker user (at least since version 3).

           I am attempting to connect to our company's FileMaker Pro server from our web server (Running on Windows Server 2010 with IIS, but also testing locally on Windows 7 with Apache too). I am using the FileMaker xDBC drivers (11.3.81) .

           I have written my code in PHP. Saved as UTF8.

           I can successfully connect and construct SQL queries to FileMaker and receive data back.

           BUT, I simply cannot get FileMaker to reply to any query where I am looking for a special character in a field. I know that the value exists in my database. As I am using German data it is essential I can construct queries on, and find fields that contain special characters.

           An example of my code is :

           SELECT "Acronym Original", Originalsprache, Kategorie, Status, Veraltet FROM "Terminologie d_f_e" WHERE "Acronym Original" IS NOT NULL AND "Status"='freigegeben' AND LOWER("Acronym Original") LIKE 'öa%' ORDER BY LOWER("Acronym Original")

            

           I have the value : ÖA in a field. I get no results back. I also checked without transforming to lower case, it does not return any value. And also tested with changing LIKE to ='öa' and ='ÖA' with no results. This code works for all other normal characters. The SQL string is created using UTF8.

           Is this a limitation of the xDBC driver, or am I doing something wrong ?

           Many thanks for any feedback. Hit a dead end with this.

           Dave

            

        • 1. Re: Using SQL through PHP to FMpro 11v3 Server, with special characters ?
          Dave_3

               Perhaps a simpler question ?

               Is it actually possible, using the xDBC drivers to connect to FileMaker and do a search on a database field that will contain special characters (German language mostly, but also French, Swedish etc.)?

               Like this :

          SELECT * FROM Terminology where Acronym='Ö%'

               And I have these values in my database for Acronym :

          ÖA

          ÖBA

          ÖHB

               I have tried DSN connection, DSN-less connection, using advanced language in the driver : auto, and then UTF-8 and my query cannot locate these entries - returns an empty set.

               The user of the database can search from within FileMaker and finds these 3 entries.

          Is it actually possible to search for special characters using the FileMaker 11.3.81 xDBC driver ?

               I ran a test using my code to connect to a MySQL database and it searches fields that contain special characters with no problem at all.

               Any feedback greatly received.

               D.

          • 2. Re: Using SQL through PHP to FMpro 11v3 Server, with special characters ?
            Dave_3

                 OK, I found a solution.

                 In my PHP files (which I saved as UTF-8) I had these statements at the top :

                 header('Content-Type: text/html; charset=UTF-8');
                 mb_internal_encoding("UTF-8");

                 I was trying to keep all my working in UTF-8.

                 The trick I found was to actually submit the SQL string to FileMaker as ISO-8859-1 as below !

                  $sql = mb_convert_encoding($sql , "ISO-8859-1"); // Convert the SQL string being sent to Filemaker.

                  $rs=odbc_exec($conn,$sql); // Execute the SQL to FileMaker

                 And this was leaving the driver's advanced language settings set to UTF-8.

                 Hope this may prove useful to anyone else who runs into the nightmare that is encoding.

                 D.