1 Reply Latest reply on Jun 22, 2009 9:16 PM by kapitaen_1

    last_insert_id()

    DarkGamer

      Title

      last_insert_id()

      Post

      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,

        • 1. Re: last_insert_id()
          kapitaen_1
            

          using microsoft sql server i had a similar problem. i changed the way of using sqlcommand to using sql server as an external data source via odbc. the id field had to be marked as "is identity" on the sql server side, and all worked as i wanted it.

           

          as soon as i entered a new record on the filemaker side, clicked somewhere into the layout to submit the record to the external data source, the id number that has been generated by sql server appeared on the filemaker side.

           

          greetings from germany

          chris

           

          ps.: when changing your file description on the mysql side, dont forget to refresh your external data source inside filemaker