13 Replies Latest reply on Jul 7, 2011 8:53 PM by IwaoAVE!

    Cannot retrieve text value from FM data source via ODBC (with 11.3.76 driver)

    IwaoAVE!

      Summary

      Cannot retrieve text value from FM data source via ODBC (with 11.3.76 driver)

      Product

      FileMaker Pro

      Version

      11v2

      Operating system version

      Windows XP SP3

      Description of the issue

      I am still unable to get text values from FM data source via ODBC if the 'maximum number of characters' validation option is not specified for the field.
      I have installed the new FileMaker ODBC client driver (11.03.76.00).

      Steps to reproduce the problem

      1. Create new database (test.fp7).
      2. Create a text field 'f1' in the table 'test'.
      3. Input 'abc' in the field 'f1' in browse mode.
      4. Publish the file using ODBC/JDBC protocol.
      5. Create a VBScript 'fm11_ado.vbs' (see below).
      6. Execute the script (double click .vbs in explorer).

      [fm11_ado.vbs]
      Set Cn = CreateObject("ADODB.Connection")
      Set Rs = CreateObject("ADODB.RecordSet")

      Query = "select f1 from test"
      ConnectionString = "Provider=MSDASQL;" & _
      "Persist Security Info=False;" & _
      "AutoDetectEncoding=No;" & _
      "MultiByteEncoding=UTF-8;" & _
      "UseLongVarchar=Yes;" & _
      "User Id=admin;" & _
      "Password=;" & _
      "DRIVER={FileMaker ODBC};" & _
      "SERVER=127.0.0.1;" & _
      "DATABASE=test;"

      Cn.Open ConnectionString
      Rs.Open Query, Cn

      Do While not Rs.EOF
        Wscript.Echo "Result is " & Rs.Fields(0).Value
        Rs.MoveNext
      Loop

      Rs.Close
      Cn.Close

      Expected result

      A dialog box with a string "Result is abc" is displayed.

      Actual result

      A dialog box with a string "Result is " is displayed (retrieved data is an empty string).

      Exact text of any error message(s) that appear

      No error occurs.

      Configuration information

      I have tried several different combination of connection string parameters, but none of them worked.

      Workaround

      Set 'maximum number of characters' validation option to field 'f1'.
      There is no workaround for a calculation field because the option is not available.

        • 1. Re: Cannot retrieve text value from FM data source via ODBC (with 11.3.76 driver)
          TSGal

          Iwao AVE!:

          Thank you for your post.

          When you set up the DSN for the new driver, did you check the option to describe text fields as long varchar?

          TSGal
          FileMaker, Inc.

          • 2. Re: Cannot retrieve text value from FM data source via ODBC (with 11.3.76 driver)
            IwaoAVE!

            I am sorry for a late reply.

            I set up those options in the connection string to make it reproducible without configuring DSN.
            And regarding the 'describe text fields as long varchar' option, I had expected the line 'UseLongVarchar=Yes' had the same effect.

            In case it's better to use DSN to configure the options, here's the corresponding DSN configuration and a VBScript used with the DSN.

            Data source name: fmtest
            Description:
            Host: localhost
            Database: test
            Describe text fields as long varchar: Yes
            Auto-detect language: No
            Multibyte text encoding: UTF-8
            Save long-running queries to a log file: No

            [fm11_ado_dsn.vbs]
            Set Cn = CreateObject("ADODB.Connection")
            Set Rs = CreateObject("ADODB.RecordSet")

            Query = "select f1 from test"
            ConnectionString = "Provider=MSDASQL;" & _
            "Data Source=fmtest;" & _ 
            "User Id=admin;" & _ 
            "Password=;"

            Cn.Open ConnectionString
            Rs.Open Query, Cn
            Do While not Rs.EOF
              Wscript.Echo "Result is " & Rs.Fields(0).Value
              Rs.MoveNext
            Loop

            Rs.Close
            Cn.Close


            Please let me know if you cannot reproduce the problem on your environment.

            Thanks,
            Iwao

            • 3. Re: Cannot retrieve text value from FM data source via ODBC (with 11.3.76 driver)
              TSGal

              Iwao AVE!:

              My apologies for the late reply.

              First, make sure the field is a Text field and not a calculation field resulting in text.  Assuming it is a Text field, remove Visual Basic from the equation and try accessing via Excel.

              TSGal
              FileMaker, Inc.

              • 4. Re: Cannot retrieve text value from FM data source via ODBC (with 11.3.76 driver)
                IwaoAVE!

                TSGal,

                Thank you for a reply.
                I think I should provide some background information about this report.

                One of our company's products uses ADO (ActiveX Data Objects) to insert/retrieve data to/from FileMaker (more precisely, it inserts a string into a text field and retrieves another string from a calculation field whose result type is text).
                It works with FileMaker version 7 thru 10, but doesn't work with 11 (insertion works, but querying the caluculation field returns an empty string).

                Basically, under the following conditions, we cannot retrieve the field data correctly; the result is always an empty string.

                - Using ADO to connect to FileMaker (ADO internally uses ODBC).
                - The field is a text field or a calculation field whose result type is text.
                - The maximum length validation is not set for the field (this is always true for a calculation field, obviously).

                And the VBScript is a simple test case which depicts the issue in a reproducible form.
                Once the bug is fixed, the test case would run fine and our product should work as expected.
                That's my goal.

                # I am not sure what you meant by 'remove Visual Basic from the equation', but you don't need Visual Basic to run the script. You can use Notepad.exe to save the script and run it by double-clicking the .vbs file in Explorer.

                Thanks,
                Iwao

                • 5. Re: Cannot retrieve text value from FM data source via ODBC (with 11.3.76 driver)
                  TSGal

                  Iwao:

                  Sorry for the confusion.

                  When I mean remove Visual Basic from the equation, I mean to not use Visual Basic, and instead use Excel or another program that was tested.  This will let us know if the problem is data related, driver related, database related, etc.

                  We did change ODBC vendors for FileMaker Pro 11, so we need to be more thorough and find out what ADO is doing differently than other products.

                  TSGal
                  FileMaker, Inc.

                  • 6. Re: Cannot retrieve text value from FM data source via ODBC (with 11.3.76 driver)
                    IwaoAVE!

                    TSGal,
                    Thank you for the explanation.

                    With the latest ODBC client driver (11.3.81.0) and the latest FileMaker Server Advanced (11v3), I can get the field data (even from a calculation field) via ADO=ODBC connection.
                    # I forgot to update my ODBC client driver on my Windows machine, sorry.

                    But...
                    While testing on the latest environment, I noticed that some Japanese characters are not correctly transferred.
                    For example, an UTF8 character 'E3 82 82' becomes 'E3 82 00' when it's retrieved from ADO dataset (I will cite a few more example at the end of this comment).
                    Is this a known issue?
                    # The symptom reminds me of a report from a ruby developer in the following thread.
                    FileMaker Server

                    And it works as expected in Excel 2007 with the same DB and the same ODBC DSN (i.e. those Japanese characters are correctly retrieved).

                    --

                    Examples of data corruption ([field data] -> [data retrieved from ADO]):
                    * 'E3 82 83' -> 'E3 82 00'
                    * 'E3 82 93' -> 'E3 82 00'
                    * 'E3 82 83 E3 82 93' (two characters) -> 'E3 82 81 45 82 93'

                    Environment:
                    * Host: Mac OS X 10.5.8 / FileMaker Server Advanced 11v3
                    * Client: Windows XP SP3 / FileMaker ODBC 11.03.81.00

                    Thanks,
                    Iwao

                    • 7. Re: Cannot retrieve text value from FM data source via ODBC (with 11.3.76 driver)
                      TSGal

                      Iwao AVE!:

                      Thank you for the additional information.  I have forwarded your information to our Development and Software Quality Assurance (Testing) departments for further review.  I will keep you posted as information becomes available to me.

                      TSGal
                      FileMaker, Inc.

                      • 8. Re: Cannot retrieve text value from FM data source via ODBC (with 11.3.76 driver)
                        TSGal

                        Iwao AVE!:

                        Our tester would like to know if you are using Japanese native XP or English XP with the Japanese language pack?  The tester is getting slightly different results using the basic script, so he would like to see your FileMaker Pro file and a console output file "script fm11_ado_dsn.vbs > output.txt".  Please check your Inbox at the top of this page for instructions where to send the files.

                        TSGal
                        FileMaker, Inc.

                        • 9. Re: Cannot retrieve text value from FM data source via ODBC (with 11.3.76 driver)
                          IwaoAVE!

                          Hi TSGal,

                          I believe it's a Japanese native XP (possibly be a OEM version bundled with the PC).
                          System Property says it's 'Microsoft Windows XP Professional Version 2002 Service Pack 3' to be precise.

                          I have received your message and will send you the requested files within a few days.
                          Is there any particular characters that the tester want me to input for the test?
                          Or I will pick one of the examples I had reported in my last post. 

                          Thank you,
                          Iwao

                          • 10. Re: Cannot retrieve text value from FM data source via ODBC (with 11.3.76 driver)
                            TSGal

                            Iwao AVE!:

                            Thanks for the information.

                            If you could provide the characters that emphasize the problem, that would be ideal.  The example originally reported would also be good.  In other words, a few examples would be helpful so we can determine what needs fixing.

                            TSGal
                            FileMaker, Inc.

                            • 11. Re: Cannot retrieve text value from FM data source via ODBC (with 11.3.76 driver)
                              TSGal

                              Iwao AVE!:

                              I received your files.  Thank you.

                              The files have been sent to our Testing department.  I will keep you updated as information becomes available to me.

                              TSGal
                              FileMaker, Inc.

                              • 12. Re: Cannot retrieve text value from FM data source via ODBC (with 11.3.76 driver)
                                TSGal

                                Iwao AVE!:

                                Our Tester has confirmed the problem.  The Tester did mention that you had configured UTF-8 for the character set for client application, but the ActiveX Data Objects used the Japanese system encoding which was not UTF-8.  Either way, the information has been sent on to Development for additional review.

                                TSGal
                                FileMaker, Inc.

                                • 13. Re: Cannot retrieve text value from FM data source via ODBC (with 11.3.76 driver)
                                  IwaoAVE!

                                  Hi TSGal,

                                  Glad that the problem was confirmed.
                                  Thanks a lot for your support!

                                  In your comment...

                                  > The Tester did mention that you had configured UTF-8 for the character set for client application, but the ActiveX Data Objects used the Japanese system encoding which was not UTF-8.

                                  Does this mean that the test case should work as expected if the DSN was configured to use Japanese system encoding?

                                  I actually had tried the other encoding settings ('Auto-detection enabled' and 'WnJapanese'), but then the query always returns an empty string even when the field contains only ASCII characters, so I gave up and tried using UTF-8.
                                  If there are any tips or hints for making it work with WinJapanese encoding setting, could you share them with us please?

                                  Thank you,
                                  Iwao