Secrets Of FireDac: Last Inserted Auto Generated Id And Enterprise Connectors

A common pattern when inserting data into a database table which has an auto increment field set on it is to use an SQL query to select the last inserted ID of the newly created record. Once you use your TFDQuery to run your INSERT query you can run this second query to get the newly inserted auto increment ID. Different databases have different SQL queries to accomplish this. MySQL for example has a SELECT query function that you can run called LAST_INSERT_ID() (example: SELECT LAST_INSERT_ID() ). You can run this select directly or use the built in FireDAC feature.

FireDAC offers a function which you can call from your TFDConnection component called GetLastAutoGenValue. It will return the last inserted ID with support for a number of different databases including Oracle, InterBase/Firebird, MySQL, MSSQL, and some others as well. It uses whatever unique SQL query for each different database to return the last inserted ID. The following is from the DocWiki.

The GetLastAutoGenValue method returns the last autogenerated value. The meaning and result depend on the DBMS, as described in the following table.

The Enterprise Connectors powered by CData don't seem to support the GetLastAutoGenValue function in the version I am running but they do have their own SQL query that can be run to accomplish the task. The SELECT query is called SCOPE_IDENTITY() (example: SELECT SCOPE_IDENTITY() ). I used the SCOPE_IDENTITY() function successfully with the CData Salesforce Enterprise Connector. SCOPE_IDENTITY() isn't mentioned much in the CData help files for FireDAC but they do show it in the SELECT syntax of the help file.

//
//
SELECT {
  [ TOP <numeric_literal> | DISTINCT ]
  {
    *
    | {
        <expression> [ [ AS ] <column_reference> ]
        | { <table_name> | <correlation_name> } .*
      } [ , ... ]
  }
  [ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
  {
    FROM <table_reference> [ [ AS ] <identifier> ]
  } [ , ... ]
  [ [ 
      INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }
    ] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]
  ] [ ... ]
  [ WHERE <search_condition> ]
  [ GROUP BY <column_reference> [ , ... ]
  [ HAVING <search_condition> ]
  [ UNION [ ALL ] <select_statement> ]
  [
    ORDER BY
    <column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
  ]
  [
    LIMIT <expression>
    [
      { OFFSET | , }
      <expression>
    ]
  ]
} | SCOPE_IDENTITY() 

Find out more about the Embarcadero Enterprise Connectors which you can use to access all kinds of cloud APIs using standard SQL.