8 Replies Latest reply on Aug 23, 2010 7:43 PM by IwaoAVE!

    ODBC and Repeating Fields FMSA11

    jcrossfield

      Title

      ODBC and Repeating Fields FMSA11

      Your post

      Hi All,

      I am using Filemaker Server Adv 11 - connecting to my database via the web using ODBC and ASP.


      I am trying to get repeating field values from my database to display on my website using ASP.


      I see in the FM11 ODBC guide that:

      "Note FileMaker repeating fields are supported like arrays.

      Examples:SELECT repField[1], repField[2] FROM mytable"


      Unfortunately there is no detail on how to write the values to screen. I assumed it would be like other fields, so I tried this (but with my real field names of course):


      <%= rsInfo("repField[1]") %>  <%= rsInfo("repField[2]") %>


      However this is the response

      "Item cannot be found in the collection corresponding to the requested name or ordinal."


      When I change it to:

      <%= rsInfo("repField") %>


      I just get the data from the 2nd repetition field only.

       

      How do I get it to write each value from each repetition?

       

      Thanks!

        • 1. Re: ODBC and Repeating Fields FMSA11
          jcrossfield

          Hi, I was wondering if anyone had any luck getting repeating fields from their FileMaker 11 db thru ODBC to display on the web? I can only get one value from one repetition to display, but I need all of the repeating values to show - any ideas?

          • 2. Re: ODBC and Repeating Fields FMSA11
            jcrossfield

            Does anyone know how to get repeating field info to come thru via ODBC?

            • 3. Re: ODBC and Repeating Fields FMSA11
              AlexanderMorano

              Is there a resolution for this?

              I have written a driver for SQLObject and SQlAlchemy to use FM as a backend. Wors great except for repeating fields, which, accoriding to the documentation, says FM11 supports this via the new ODBC driver.

              The reality is this is untrue. The values do not come through on the driver beyond the first.

              Cheers.

              • 4. Re: ODBC and Repeating Fields FMSA11
                jcrossfield

                Hi Alexander,

                I have not yet heard anything on this issue yet. If you do find out the answer elsewhere, please post here, thanks! :)

                JCrossfield

                • 5. Re: ODBC and Repeating Fields FMSA11

                  jcrossfield:

                  Can you provide a fuller sample of your code, or at least the part where you specify the SQL statement? The repeating field name should appear in this statement as any other field name would but with the repitition specified in brackets afterward. (e.g. "SELECT fieldname[1] FROM tablename") I'm not familiar with the rsInfo() function you are using.

                  TSuki
                  FileMaker, Inc.

                  • 6. Re: ODBC and Repeating Fields FMSA11
                    jcrossfield

                    Yes as I stated in my post the SQL statement looks like this:

                    SELECT repField[1], repField[2] FROM mytable

                    It's setup just as you describe in your message, but that does NOT work with the new ODBC driver with FMSA 11. If you try it with two repetitions, like in my query, you'll see it only returns the value of the 2nd repetition.

                    • 7. Re: ODBC and Repeating Fields FMSA11
                      AlexanderMorano

                      Just wanted to add my 0.02.

                       

                      Not everyone uses 100% SQL to transact. IOW, I have two ORMs I have drivers written for: SQLObject and SQLAlchemy.  These ORMs layers prefer to do the SQL themselves, especially in the case of getting a valid schema.

                      I can, if I want, do raw SQL, but why?  Your index proposal via [] is not valid in the world of the standard DBAPI scheme.

                      I understand you guys hand wrote your own driver for ODBC. I also am fulyl aware repeating fields are a non-standard field in teh DBAPI spec.  This is why I'd recommend you toss those values out as a comma or tab sperated string and let the driver layer re-translate it back and forth, instead of requiring special SQL syntax which are outside the designed standards.

                      Cheers.

                      • 8. Re: ODBC and Repeating Fields FMSA11
                        IwaoAVE!

                        Although I have no experience with ASP nor ADO, here are a few things you can try.

                        Use column alias in the query.

                        SELECT repField[1] AS rep1, repField[2] AS rep2 FROM mytable
                        
                        

                        and use the alias to retrieve the field values.

                         <%= rsInfo("rep1") %> <%= rsInfo("rep2") %>
                        
                        

                        Use number to specify field.

                         <%= rsInfo(1) %> <%= rsInfo(2) %>
                        
                        

                        I'm totally unsure about the second solution, but the first one should work.

                        Hope this helps,
                        Iwao