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?
Does anyone know how to get repeating field info to come thru via ODBC?
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.
I have not yet heard anything on this issue yet. If you do find out the answer elsewhere, please post here, thanks! :)
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 FROM tablename") I'm not familiar with the rsInfo() function you are using.
Yes as I stated in my post the SQL statement looks like this:
SELECT repField, repField 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.
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.
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 AS rep1, repField 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,