Implementation Notes

Not every DBMS is the same; that's a big part of why this project was written! Below are notes on specific DBMS implementations / limitations.

PostgreSQL
  • PostgreSQL does not have the concept of a "last insert ID" the way other databases do. Calling LastIdentity() on a PostgreSQL instance will result in a InvalidOperationException.

MySQL
  • MySQL does not have sequences. The implementation of Sequence() for MySQL does a "MAX(column) + 1" on the given table and column name (specified "columnname|tablename" as the argument to Sequence()).
  • LastIdentity() is a wrapper around the MySQL last_insert_id() function.

SQL Server
  • The implementation of Sequence() for SQL Server gets the current identity value of a column, incremented by 1. Note that identity values may have an increment specified other than 1; this implementation does not look at that. (If you know how to get this value, drop me a line; this implementation reflects my current knowledge of SQL Server identity fields.)
  • LastIdentity() returns the current SCOPE_IDENTITY.

SQLite
  • SQLite (the actual database) uses MAX(pk) + 1 for it's INTEGER PRIMARY KEY defined fields. So, the implementation of Sequence() for SQLite does the same thing, for a given table and column name.
  • LastIdentity() is a wrapper around the SQLite last_insert_rowid() function.

ODBC
  • ODBC uses the same MAX(column) + 1 logic as MySQL and SQLite.
  • As ODBC connections could connect to any database, the syntax for a proper LastIdentity() call is unknown. If you wish to use this call with an ODBC connection, you will need to provide logic that defines a query named database.identity.odbc to handle the vendor's implementation. If this query is provided, the implementation will run it.

Last edited Nov 2, 2011 at 3:45 PM by danielsummers, version 2

Comments

No comments yet.