6 Replies Latest reply on Apr 16, 2011 3:50 PM by PaulCowper

    ODBC driver error

    pmarceaujr

      Summary

      ODBC driver error

      Description of the issue

      FileMaker ODBC is not returning results as expected.  For example, when using the OLE DB driver to connect to FileMaker, the IsFieldUpdateable flag returns NO for all fields, FieldLengths come back as 1,000,000 characters, etc...Our connection connects to ODBC using ADO.  Snippet from http://en.wikipedia.org/wiki/ActiveX_Data_Objects: Microsoft's ActiveX Data Objects (ADO) is a set of Component Object Model (COM) objects for accessing data sources. A part of MDAC, it provides a layer between programming languages and OLE DB (a means of accessing data stores, whether they be databases or otherwise, in a uniform manner). ADO allows a developer to write programs that access data without knowing how the database is implemented. You must be aware of your database for connection only. No knowledge of SQL is required to access a database when using ADO, although one can use ADO to execute SQL commands.

        • 1. Re: ODBC driver error
          TSGal

          pmarceaujr:

           

          Thank you for your post.

           

          What version of FileMaker Pro are you using?  Did you update your version of FileMaker Pro and still have the old FileMaker ODBC driver installed?  What commands are being sent to FileMaker's ODBC driver?  Any other information you can provide may be helpful.

           

          TSGal

          FileMaker, Inc.

          • 2. Re: ODBC driver error
            kapitaen_1

            Hi,

            is there a clear primary key that can be used by the ado object? If not, you have to tell the data adapter what to use as primary key.

            Take a closer look to the third note-area in this article.

            If ado object have a useable primary key, your is updateable field shloud have true (ehm, yes, if access privileges say they are writable)

             

            The fact that field lengths are said to be 1.000.000.000 bytes long comes from filemaker text fields that have no real length limit.

             

            greetings from germany

            Chris

            • 3. Re: ODBC driver error
              zanlok

              I'd like to back up what's being said here with another voice and similar results.

               

              Basically, imo, ADODB support is broken in FMSA11, for whatever reason. With 10 years of VB experience, and plenty of more recent C# experience, I believe the test script below should be a most basic test to exercise any ODBC interface. FMSA11 results? FAIL: miserably, without error, and, for me, zero technical support searchable on the FM website. Given that I am about to embark on a large-ish development project that ties into a very robust existing FM solution, and that this was my first dev experience with FileMaker, I must honestly say I wasn't well pleased. I beat my head against this for at least a week before deciding that my lack of knowledge concerning FMSA11specification or configurations was definitely not to blame.

               

              Avoiding complete doom and gloom, I'll also post that the workaround I've settled with is to use the .NET OdbcConnection / OdbcCommand / OdbcDataReader combo via C#. With some very large caveats, this is somewhat viable against simple tables; however, at a minimum, it would have been nice if Google or the FM searches could have returned some result regarding ADODB when I initially tested this (around 20010-04-01, ftr.) At better: it would be nice if the ODBC Integration Guide straight up said the ADODB support is broken. At best: code samples would be available, especially for Windows developers - even something as basic as WScript should be easy for you guys to assemble. Ammend that last - at BEST: fix the issue!

               

              (Unfairly addressing the issue this thread isn't about, regarding the "very large caveats" mentioned above with the .NET OdbcConnection workaround, I still remain in a world of hurt over proper ODBC support for real-world tables with unstored calculations, high numbers of fields, and other [frustrating?] FM uniquenesses with relationships/scripts/layouts/etc. Alas, if I ever take the time to post reproducible code for those issues, it will definitely be another day not in the near future.)

               

              In the sample: excuse the longish lines when reading, I left them that way for a simpler copy/paste experience.

               

               

              '=========================================================================
              ' Purpose: Test of ODBC on FileMaker Server Advanced 11, using WSH and ADODB via cscript.
              ' Result: Numerical fields contain data, but all others appear to be uselessly nulled.
              ' Note0: You must adjust the SERVER_NAME constant according to your environment.
              ' Note1: Accesses the sample database after enabling ODBC on an otherwise vanilla FMSA11.
              ' Note2: For testing ease, a DSN-less string; after installing ODBC driver on a Windows client.
              ' Note3: Error handling? Nah, it would uselessly complicate the point of this demo code.
              ' Usage: From a Windows cmd prompt, in the path of the script, execute: cscript <scriptfile.vbs>
              ' Reproducibility: Client OS = physical XPSP2 and XPSP3, and a clean VMware vm with XPSP2.
              ' Sidebar: [zanlok] FMSA11 may be okay for RAD prototyping, but isn't yet enterprise-ready?

                  ''' constants
                  Option Explicit
                  Const SCRIPT_NAME = "FM-ODBC-ADODB_test"
                  Const MAX_ROWS = 3
                  Const MAX_FIELDS = 10
                  Const SERVER_NAME = "FMSA11"  '!!! Note: change this to your FMSA installation
                  
                  ''' don't even proceed unless we're in cscript
                  If Not WScript.FullName = WScript.Path & "\cscript.exe" Then
                      WScript.Echo "[" & SCRIPT_NAME & "] NOTICE: please run using ""cscript <filename>"" to avoid massive msgbox spam !"
                      WScript.Quit
                  End If

                  ''' variables
                  Dim oDB, oRS
                  Dim intLoop, intField

                  ''' connect and retrieve
                  Set oDB = CreateObject("ADODB.Connection")
                  oDB.Open "Driver={FileMaker ODBC};Server=" & SERVER_NAME & ";Database=FMServer_Sample;", "admin", ""
                  Set oRS = oDB.Execute("SELECT * FROM ""ENGLISH_NATURE""")
                  
                  ''' iterate over some of the recordset
                  intLoop = 0
                  While Not oRS.EOF And intLoop < MAX_ROWS
                      intLoop = intLoop + 1

                      ''' dump some data about each record
                      WScript.Echo "Record # " & intLoop
                      For intField = 0 To oRS.Fields.Count - 1
                          WScript.Echo vbTab & intField & ": " & oRS.Fields(intField).name & " = " & oRS.Fields(intField).value
                          ''' don't show too much data
                          If intField > (MAX_FIELDS - 1) And (oRS.Fields.Count - 1) > MAX_FIELDS Then    
                              WScript.Echo vbTab & "(data listing abridged to " & MAX_FIELDS & " fields)"
                              Exit For
                          End If
                      Next
                      oRS.MoveNext
                  Wend

                  ''' so they know when something was abridged
                  If Not oRS.EOF Then
                      WScript.Echo "Record display abridged to " & MAX_ROWS & " rows."
                      oRS.Close
                  Else
                      WScript.Echo "Displayed data from all " & intLoop & " records found."
                  End If
                  
                  ''' cleanup
                  Set oRS = Nothing
                  oDB.Close
                  Set oDB = Nothing

                  ''' all done
                  WScript.Echo vbCrLf & "[" & SCRIPT_NAME & "] ...test complete" & vbCrLf

              '=========================================================================EOF

               

               


































              • 4. Re: ODBC driver error
                zanlok: I was able to run your example and experienced the same behavior. However, I was able to resolve this by setting the "Maximum number of characters" Validation option for Text fields. For some reason, the new ODBC driver needs this option set. Were there any other pertinent issues with using ADODB in FMP11?
                • 5. Re: ODBC driver error
                  zanlok

                  My example used the sample database and a very rigid set of instructions. If you were able to get it working, please describe in more detail the changes you made. Such as: to which text fields, and to what value for the maximum number of characters?

                   

                  I'm somewhat willing to re-try this scenario, but I will definitely stand by saying it absolutely SHOULD work out of the box, with the sample I posted above. As in, without going in and modifying what basically amounts what I gather is a specialized value for every single text field in the database. For connecting with a larger existing solution, that is simply unacceptable. In my case, I would seriously need to manually change thousands upon thousands of text fields in hundreds of tables in hundreds of files on multiple data servers. And, really, even if it weren't that daunting, it just shouldn't be necessary. That's something the driver needs to address, adding a restriction such as the one

                   

                  Besides, like I said, the .NET / System.Data.Odbc / OdbcDataReader does not have this problem (against the same sample data), so I'm not really willing to call this a resolution, regardless. It just plain shouldn't be necessary to go in and instrument a bunch of things to get ODBC to work, when it's touted (and sold for extra dollars) as a working solution... it should "just work".

                   

                  p.s. Yes, I've other "pertinent" issues with ODBC access into more complex FM tables, some already brought up, and others not, but will reserve those discussions for other threads.

                  • 6. Re: ODBC driver error
                    PaulCowper

                    @zanlok

                    I agree it should just work, or at the very least instructions should be posted on how to make it work.

                    I've spent some time struggling with this issue.  I tried setting the  "Maximum number of characters" Validation option for Text fields but it didn't work for me.  Eventually I managed to retrieve text fields by:

                    1. Install the latest ODBC driver v11.3.76;

                    2. Set the attribut UseLongVarchar=Yes (in DSN or connection string); and

                    3. Set the ADODB.Connection.CursorLocation = adUseClient;

                    The "Maximum number of characters" Validation option is still set on the test fields; I'm not sure if it's still required - it certainly didn't work on it's own (for me).

                    This does result in read-only datasets, but text fields are no longer blank.