One of my clients wants to sync their local (Filemaker 10) and web (mySQL 5) databases. That means creating new user accounts on the web from scripts on an existing local FM database. I'm using an ODBC driver to load the mySQL data into filemaker. Here's the issue:
I INSERT rows to the users table just fine using filemaker's Execute SQL() command but I can't for the life of me find out how to get the unique id that was created in mySQL when this row was made.
- last_insert_id() is the usual way I'd go about it but I don't see any way to get data returned from the query back into FM.
- max(field names) is another approach but there could be any variable number of fields in the database, and this function appears to operate on currently selected records rather than the whole table... or does it support multiple rows somehow?
- I might be able to do a workaround if I can refresh a shadow table via scripts... is there any way to refresh the local version of the mySQL table immediately after INSERTing the new records?
Thanks in advance,