7 Replies Latest reply on Sep 4, 2013 1:39 AM by ranja

    FMSA11 FileMaker ODBC returns nulls for text fields

    ranja

      Title

      FMSA11 FileMaker ODBC returns nulls for text fields

      Your post

      FileMaker Server Advanced v11
      Windows Server 2008R2 32bit (Japanese)
      Visual Studio 2010
      FileMaker ODBC v11.00.61.00

      When I access to FileMaker Server 11 via OLEDB, the values of text fields are not returned in certain cases.
      Here is my sample code:

      #include "stdafx.h"
      #include <atldbcli.h>
      class CGetUserList
      {
      public:
      DEFINE_COMMAND_EX(CGetUserList, L"SELECT u.UserID, u.FullName, u.Privilege FROM UserAccount AS u ORDER BY u.UserID")

      WCHAR m_UserID[11];
      WCHAR m_FullName[41];
      LONG m_Privilege;

      BEGIN_COLUMN_MAP(CGetUserList)
      COLUMN_ENTRY(1, m_UserID)
      COLUMN_ENTRY(2, m_FullName)
      COLUMN_ENTRY(3, m_Privilege)
      END_COLUMN_MAP()
      };
      int _tmain(int argc, _TCHAR* argv[])
      {
      LPCWSTR conn = L"Provider=MSDASQL.1;Password=MyPassword;Persist Security Info=True;User ID=Admin;Extended Properties=\"DRIVER={FileMaker ODBC};SERVER=MyServer;DATABASE=MyDatabase\"";
      HRESULT hr = ::CoInitialize(NULL);
      if(hr == S_OK){
      CDataConnection dc;
      hr = dc.Open(conn);
      if(hr == S_OK){
      CCommand<CAccessor<CGetUserList> > cmd;
      hr = cmd.Open(dc, NULL, NULL);
      if(hr == S_OK){
      while(cmd.MoveNext() == S_OK)
      {
      ::wprintf(cmd.m_UserID);
      ::wprintf(cmd.m_FullName);
      }
      cmd.Close();
      }
      dc.CloseDataSource();
      }
      }
      ::CoUninitialize();
      return 0;
      }

      Well, the above code works fine. But after I changed the SQL to something like
      SELECT u.UserID, 'Test' AS FullName, u.Privilege FROM UserAccount AS u ORDER BY u.UserID
      then not only cmd.m_FullName but also cmd.m_UserID becomes empty. Numeric fields (like the third 'Privilege' field) have no problem.
      Also, when the SQL contains 'JOIN' (INNER JOIN or OUTER JOIN), all the text fields return nulls.
      Of course, FMSA v10 had no such problem. Universal SQL Editor and Access 2010 (via ODBC) do not have that phenomenon, either.
      What is wrong?




























        • 1. Re: FMSA11 FileMaker ODBC returns nulls for text fields
          IwaoAVE!

          I'm having the similar issue on retrieving Japanese characters via ODBC, although the host runs on Mac OS X.
          How do you configure the 'Advanced Language' option in your DSN configuration?

          • 2. Re: FMSA11 FileMaker ODBC returns nulls for text fields
            ranja

            Hi, Ave san.


            >How do you configure the 'Advanced Language' option in your DSN configuration?
            The 'Advanced Language' option?  I don't know. The FileMaker ODBC Driver option panel doesn't show us any advanced options.
            I'm using the connection string for Microsoft OLE DB Provider for ODBC Drivers as shown in my previous sample code.
            Besdies, the text data that I'm using contain no Japanese characters.

            • 3. Re: FMSA11 FileMaker ODBC returns nulls for text fields
              IwaoAVE!

              Sorry, I didn't read your post in detail and thought you're using data source in the connection string.


              Isn't it possible for you to use a data source in your program?
              It should solve your problem if the data contains only ASCII characters.

              When configuring the data source, you need to uncheck the 'auto-detect' option and select 'UTF-8' for the multi-byte text encoding in the Advanced language setting ('Encode settei' in Japanese version).

              # Please see the document 'FileMaker 11 ODBC JDBC guide' for how to define a data source in ODBC Administrator.


              Once the data source is configured, you can use it as a part of the connection string.
              Assuming the data source name is 'MY_DSN', the connection string ('conn' in your example) would look like..

              Provider=MSDASQL.1;Password=MyPassword;Persist Security Info=True;User ID=Admin;Data Source=MY_DSN;

              Hope this helps,
              Iwao



              • 4. Re: FMSA11 FileMaker ODBC returns nulls for text fields
                ranja

                Thank you so much, Ave san.
                It worked!
                However, the problem is that I want to construct the connection string dynamically because the number of target databases increases every year, like MyDB2009, MyDB2010, MyDB2011, ...

                I've tried several connection strings such as
                L"Provider=MSDASQL.1;Password=****;Persist Security Info=True;User ID=Admin;Extended Properties=\"DRIVER={FileMaker ODBC};AutoDetectEncoding=No;MultiByteEncoding=UTF-8;Server=MyServer;DATABASE=MyDB2009\""
                but none of them has succeeded yet. (I assumed the parameter names from the names of the registry keys used for System DSN.)
                Does someone know how to specifty the Advanced language setting within an OLEDB connection string?
                I would greatly appreciate it.

                • 5. Re: FMSA11 FileMaker ODBC returns nulls for text fields
                  ranja

                  I found that when I specifty both the DSN and the database name in a connection string, the database setting overrides that in DSN.
                  Thus, by using a connection string such as
                  "Provider=MSDASQL.1;Password=***;Persist Security Info=True;User ID=Admin;Data Source=MyDSN;DATABASE=MyDB2009",
                  I am now able to specifty the target database dynamically along with the multibyte text encoding option which is specified in the ODBC data source.
                  However, with the UTF-8 encoding option on, Japanese characters are not retrieved correctly.

                   

                  So, to make it work correctly:
                  1. Use WinJapanese encoding and neither constants nor JOINs in SQL.
                  2. Use UTF-8 encodng and do not use Japanese characters.

                   

                  Isn't it a bug of FileMaker ODBC driver?


                  • 6. Re: FMSA11 FileMaker ODBC returns nulls for text fields
                    IwaoAVE!
                    By specifying maximum number of characters in the field's validation option, I could retrieve Japanese data correctly (with auto-detect enabled in the advanced language setting).

                    The workaround was provided in the following bug report.
                    http://forum-en.filemaker.com/t5/Report-a-bug/FMSA11-can-t-write-results-from-ODBC-query-from-asp-pages/td-p/59557
                    • 7. Re: FMSA11 FileMaker ODBC returns nulls for text fields
                      ranja

                           Thank you for the information, Ave san.
                           But I have specified the maximum number of characters for all the text type fields already.
                           Moreover, an SQL like
                           SELECT 'MyString' AS TextData FROM ...
                           could not specifty the maximum number of characters...

                           I know I can address this issue if I modify my previous sample code to use
                           CHAR m_FullName[41];
                           instead of
                           WCHAR m_FullName[41];
                           and the UTF-8 encoding option of FileMaker ODBC driver.  In this case, the string data obtained in m_FullName is UTF-8 encoded text, so I can conver it to the wide (Unicode) string through the ordinal procedure.
                           I would do that if I need to write a new program, but today I don't use OLE DB anymore.  The issue is about my existing codes and the compatibility is my concern.
                           Anyway, I really thank you for your help.