Filemaker repeating fields and ODBC

Question

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 possible, I'd like to be able to obtain the same result with the ODBC connection. Thanks!

score 3 · Answer 1 · edited Nov 15 '12 at 21:59

3

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.

edited Nov 15 '12 at 21:59

ThiefMaster

310,957
84
592
636

answered Nov 15 '12 at 21:31

Signix

51
5

score 1 · Answer 2 · answered Jan 24 '10 at 14:11

1

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.

answered Jan 24 '10 at 14:11

DisplacedAussie

4,578
1
27
21

2

Yeah well in the end, after some search on diverse forum, I came to the conclusion that those repeating fields are the work of the devil and should therefor be banned from every single FileMaker database which currently exists. So yeah, I had to use the workaround :/ – Afrosimon Jan 26 '10 at 01:02
1

In my experience, whenever there's a doubt, assume the FMXDBC docs are wrong in part or en toto. And, yes, most of the fancier FM features are pure evil. Always test test test to see how the monster really behaves. Workarounds are the name of the game in the special hell of FM integration. – zanlok Jun 24 '11 at 17:03

score 0 · Answer 3 · answered Jan 26 '10 at 07:04

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.

score 0 · Answer 4 · answered Aug 23 '19 at 21:01

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.

Filemaker repeating fields and ODBC

4 Answers4