Insert and return new record ID

Products, Plugins & FM hosting/cloud services MBS Plugins
1

There is a little thing we can easily do in FileMaker when creating new records. We can get the ID of the new record. But if we insert via SQL, we can't get it. Usually we workaround by using UUIDs and then put in a new UUID for the record into the insert command. This way we know in advance which ID the new record will have.

Now in PostgreSQL you can use INSERT command with RETURNING option to return the new record ID. As we can use our SQL functions to connect to PostgreSQL databases, we can use that feature here.

We skip the connection part and directly create an INSERT command to create a new record in table Persons. We pass values by name for the three fields and set them individually with SQL.SetParamAsText function. Then we run the SQL.Execute function to perform the insert command. If that succeeds, we can request the record with SQL.FetchNext function and the request the value with SQL.GetFieldValue function.

Set Variable [ $Command ; Value: MBS("SQL.NewCommand"; $Connection;
   "INSERT INTO Persons (FirstName, LastName, City) VALUES (:FirstName, :LastName, :City) RETURNING personid") ]
Set Variable [ $result ; Value: MBS("SQL.SetParamAsText"; $Command; "FirstName"; "John") ]
Set Variable [ $result ; Value: MBS("SQL.SetParamAsText"; $Command; "LastName"; "Meyer") ]
Set Variable [ $result ; Value: MBS("SQL.SetParamAsText"; $Command; "City"; "Boston") ]
Set Variable [ $result ; Value: MBS("SQL.Execute"; $Command) ]
If [ MBS("IsError") ]
	Show Custom Dialog [ "Failed to insert." ; $result ]
Else
	Set Variable [ $result ; Value: MBS("SQL.FetchNext"; $Command) ]
	Set Variable [ $NewID ; Value: MBS("SQL.GetFieldValue"; $Command; "personid") ]
	Show Custom Dialog [ "Created Record" ; $NewID ]
End If

By using RETURNING keyword in the query, we ask PostgreSQL to return us values from the new record. We could list multiple fields there or just a star to return the whole record back. That may gives us any calculated field back, that the server calculated.

If you need to do similar with MySQL or MariaDB, you can use SQL.MySQL.InsertID function to get the last insert ID after you made a record. For SQLite you can use SQL.SQLite3.LastInsertRowID function. And with Oracle you can use RETURNING just like PostgreSQL.

Have fun with SQL functions in MBS FileMaker Plugin to connect to other databases and exchange data in your scripts.