Filemaker repeating fields and ODBC

So I'm transferring an old filemaker database to MySQL and some repeating fields are causing me some problems. I've read that the ODBC standard support those fields, only when their types is "Text" and that each repetition is concatenated with a certain delimiter (see page 47 (PDF)). However, I just can't reproduce this. All I get is the first repetition.

If I export the database to the .csv format, the fields are correctly concatenated, so I'm not completely stuck, but if po开发者_如何学JAVAssible, I'd like to be able to obtain the same result with the ODBC connection. Thanks!

---

With JDBC and Filemaker 12 I can access the repeating field using brackets as it was table beginning with index 1. It should be the same in ODBC. Of course I recommend to normalize but it can help to know there is other options.

---

In my experience the documentation about repeating fields is a lie. :)

If you can get it to work, please, please post an answer. But I imagine you'll have to do the workaround using the csv export.

---

My recommendation regarding this would be to normalize the repeating fields to a separate table within FileMaker and then perform the transfer of the data. You can create a related table in FileMaker and then use a script to populate the table with the repeating field values. Let me know if you need assistance writing such a script.

---

I want to provide details about @Signix answer above. I was able to fetch repeating fields from JDBC but it's tricky. At page 30 of FileMaker "ODBC and JDBC Guide", it states:

Note FileMaker repeating fields are supported like arrays.

Example

INSERT INTO mytable(repField[3]) VALUES (‘this is rep 3’)
SELECT repField[1], repField[2] FROM mytable

This is the only documentation! So in theory you could use this query:

ResultSet resultSet = fmStatement.executeQuery("SELECT id, repField[1], repField[2] FROM mytable");

But the tricky part is getting the results. The only way seems to use the column index.

System.out.println(resultSet.getString("repField[1]")); // fails, throws FMSQLException
System.out.println(resultSet.getString("repField[2]")); // fails, throws FMSQLException
System.out.println(resultSet.getString("repField")); // returns repField[1]
System.out.println(resultSet.getString(2)); // returns repField[1]
System.out.println(resultSet.getString(3)); // returns repField[2]

I think the reason is because fields are being named without their bracket parts.

System.out.println(resultSet.getMetaData().getColumnName(1)); // returns "id"
System.out.println(resultSet.getMetaData().getColumnName(2)); // returns "repField"
System.out.println(resultSet.getMetaData().getColumnName(3)); // returns "repField"

So when using resultSet.getString("repField") it returns the first column value with that name. It's stupid but it works.