11 Replies Latest reply on Jan 29, 2017 4:34 PM by user19752

    JDBC Encoding

    alr2

      I use JDBC to access FileMaker Server.

       

       

      It seems to have an error with the encoding but I don't know how to handle this.

       

       

      When I run this query: 'SELECT "qté" FROM "facture" FETCH FIRST 1 ROWS ONLY'

       

       

      I get this error:

       

       

      Failed to execute: SELECT qt├® FROM "facture" FETCH FIRST 1 ROWS ONLY because: [FileMaker][FileMaker JDBC] FQL0001/(1:17): There is an error in the syntax of the query.

      Caught: com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC] FQL0001/(1:17): There is an error in the syntax of the query.

       

       

       

       

      When I run select * it returns qté as column name.

       

       

      Is there a way to tell the driver to use utf-8 ? Should I configure something in FMP to set the encoding ? I'm lost here.

       

       

      Thanks in advance

        • 1. Re: JDBC Encoding
          user19752

          In the error message, table name is quoted but column is not. How do you make the query string?

          • 2. Re: JDBC Encoding
            alr2

            I ran with and without and they both failed maybe it's the when I ran the query unquoted. If I select a column without special char it works. Also I quote my column and table name because sometime there's space in them.

             

            I use Groovy instead of Java, but it's basically the same thing since they both run on the jvm.

            Here's example of the code

            def connectToFMP() {

                def url = 'jdbc:filemaker://127.0.0.1/facture'

                def user = 'username'

                def password = 'password'

                def driver = 'com.filemaker.jdbc.Driver'

                return Sql.newInstance(url, user, password, driver)

            }

             

             

            def sqlFMP = connectToFMP()

             

             

            sqlFMP.eachRow('SELECT qté FROM "facture" FETCH FIRST 1 ROWS ONLY', { row ->

                println row

            })

             

            I also tried this code

            sqlFMP.eachRow('SELECT "qté" FROM "facture" FETCH FIRST 1 ROWS ONLY', { row ->

                println row

            })

             

            and this one

             

            sqlFMP.eachRow('SELECT "qt\u00e9" FROM "facture" FETCH FIRST 1 ROWS ONLY', { row ->

                println row

            })

             

            this will work:

             

            sqlFMP.eachRow('SELECT "product" FROM "facture" FETCH FIRST 1 ROWS ONLY', { row ->

                println row

            })

            • 3. Re: JDBC Encoding
              user19752

              I can get result using quoted form

              SELECT "qté" FROM...

               

              Did you compare Code("qté") of field name with result of "select * it returns qté as column name." ? (combined character have different representation)

              • 4. Re: JDBC Encoding
                fmpdude

                This is an example of where I'd use a SQL environment that can connect to FileMaker and try the query there first. If that doesn't work, then JDBC won't work either.

                 

                In any case, a quick Duck-Duck-Go search found several links like this:

                 

                java - JDBC character encoding - Stack Overflow

                 

                HOPE THIS HELPS.

                • 5. Re: JDBC Encoding
                  alr2

                  user19752 : You're lucky. I just retried with double quoted and still doesn't work. But yes if I do a select * the qté is retrun but not when I do the other Maybe we have something different in our FMP server setting for encoding. My script is in UTF8 but I really does`t understand. Driver version maybe ? But I thought I got the last one.

                   

                  The column named "qt├®" does not exist in any table in the column reference's scope.

                  Caught: com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC] FQL0007/(1:7): The column named "qté" does not exist in any table in the column reference's scope.

                  com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC] FQL0007/(1:7): The column named "qté" does not exist in any table in the column reference's scope.

                   

                  fmpdude : I already search google. The encoding param for the connection string is for the mysql driver. Now this is file maker driver and it seems to work differently.

                  • 6. Re: JDBC Encoding
                    alr2

                    I've rewritten my script in Java instead of Groovy, and was able to make it works. So there's something special with Groovy that I don't understand that break the encoding.

                    1 of 1 people found this helpful
                    • 7. Re: JDBC Encoding
                      alr2

                      And now it works in groovy if I write it like this:

                       

                      'SELECT "qt\u00e9" FROM "facture" FETCH FIRST 1 ROWS ONLY'

                       

                      I just don't understand why I have to write my é like this. But it's more a groovy question than a FMP question I think.

                      • 8. Re: JDBC Encoding
                        fmpdude

                        I hear you.

                         

                        I stopped using FMP's JDBC (and that part of FMP) driver a while ago due to still severe and yet-to-be-addressed bugs (with container fields) and ridiculously and needlessly hampered functionality (for example, the JDBC driver ONLY works on the same machine with FMP). I also found over and over that FMP's SQL performance is a scant fraction of MySQL or other DB servers making it a no-go for most projects I have. I have documented those tests, with data, here on the forum one or more times.

                         

                        In any case, FMI's lack of support to fix bugs and their marketing-inspired-hampering functionality are yet more examples, my opinion, where FMI makes a mistake trying to "nudge" you into FMS.

                         

                        I'm more or less nudged away from FMP (one app remaining and I'm converting that one now).

                         

                        Good luck.

                        • 9. Re: JDBC Encoding
                          planteg

                          Hi alr2,

                           

                          even though I speak French, I live in the province of Québec, I refrain from using accented characters in naming columns, tables, etc., because most of the time it brings issues. And not only in FileMaker, but with any development language. It's been like this for years .

                          • 10. Re: JDBC Encoding
                            alr2

                            planteg Thanks, I know it's not ideal to use special char. But I have to work with a legacy system that I didn't do and it can be updated.

                             

                            That's why I try to use another programming language to extract data from it.

                            • 11. Re: JDBC Encoding
                              user19752

                              I tested it using ScriptMaster plug-in. So the code is written in FM text field.

                               

                              "é" is exact same 2 bytes that "é" in UTF-8 (hex C3 A9). So it looks your Groovy interpreter (?) doesn't think it is written in UTF-8.

                               

                              In the guide https://fmhelp.filemaker.com/docs/15/en/fm15_odbc_jdbc_guide.pdf , encoding option is wrote in ODBC DSN settings but not in JDBC. Java applications (and FM itself) use always Unicode in internal encoding, so there may not be selection of "SYSTEM or UTF-8".