db-adapters: same-batch MSSQL identity fallback, Oracle CURRVAL, and $identitySelect six-way dedup

[bpamiri]

Split out of the identity-retrieval-race fix (finding "Top 10 #10" / DA3 / DA8 / DA9 of the internal multi-agent framework review, 2026-06-09). That PR makes both adapters prefer the driver-supplied generated key, which fixes the race/trigger hazard on Lucee and Adobe CF. Three pieces of deliberately deferred scope remain:

1. SQL Server: same-batch retrieval for the no-generated-key fallback

vendor/wheels/databaseAdapters/MicrosoftSQLServer/MicrosoftSQLServerModel.cfc — when the engine surfaces no result.generatedKey (currently BoxLang), the adapter falls back to a standalone SELECT @@IDENTITY, which is session-scoped and can return a trigger-generated identity from another table. A standalone SELECT SCOPE_IDENTITY() is not a fix: it executes in its own batch (a batch is a scope per MS docs) and returns NULL over JDBC. Proper fix options:

• run the retrieval in the insert's own batch (e.g. INSERT ...; SELECT SCOPE_IDENTITY() AS lastId as one statement), or
• use OUTPUT INSERTED.<pk>, or
• drop the fallback entirely once BoxLang surfaces JDBC generated keys in its query result struct.

2. Oracle: replace the MAX(ROWID) last-resort with CURRVAL

vendor/wheels/databaseAdapters/Oracle/OracleModel.cfc — the same no-generated-key fallback still resolves the new PK via WHERE ROWID = (SELECT MAX(ROWID) FROM <table>). ROWID encodes physical storage location, not insertion order, so this races under concurrent inserts and can assign another session's key. Replace with CURRVAL on the backing identity sequence (session-scoped, race-free), or remove once BoxLang surfaces generated keys.

3. DA3: dedupe the six divergent $identitySelect implementations

$identitySelect is copy-pasted across six adapters with divergent engine workarounds:

• vendor/wheels/databaseAdapters/Base.cfc
• MicrosoftSQLServer/MicrosoftSQLServerModel.cfc
• PostgreSQL/PostgreSQLModel.cfc
• Oracle/OracleModel.cfc
• SQLite/SQLiteModel.cfc
• CockroachDB/CockroachDBModel.cfc

The BoxLang ReplaceList column-parse workaround and the missing-closing-paren guard each exist in only a subset (the race-fix PR copied the ReplaceList workaround into MSSQL/Oracle, widening the duplication). Extract a Base $parseInsertColumnList template method plus a small per-engine $lastIdLookup hook so future engine fixes land once.

[bpamiri]

Partial fix (driver generated-keys preference) landed in PR #2908; the three items above are the deliberately deferred remainder.

[wheels-bot]

Wheels Bot — Triage

Classified as bug.

Suspected layer

model — see .ai/wheels/ model/ORM patterns and .ai/wheels/cross-engine-compatibility.md. The affected code lives under vendor/wheels/databaseAdapters/ (model-layer ORM identity retrieval).

Fix sketch

Three deferred items, all in the database-adapter family:

1. MSSQL fallback correctness — vendor/wheels/databaseAdapters/MicrosoftSQLServer/MicrosoftSQLServerModel.cfc: replace the standalone SELECT @@IDENTITY fallback (session-scoped — can return a trigger-generated identity from another table) with same-batch retrieval (INSERT ...; SELECT SCOPE_IDENTITY() AS lastId as one statement) or OUTPUT INSERTED.<pk>. Standalone SELECT SCOPE_IDENTITY() is not viable (batch-scoped → NULL over JDBC).
2. Oracle race fix — vendor/wheels/databaseAdapters/Oracle/OracleModel.cfc: replace the WHERE ROWID = (SELECT MAX(ROWID) FROM <table>) last-resort lookup (races under concurrent inserts since ROWID encodes physical storage, not insertion order) with CURRVAL on the backing identity sequence (session-scoped, race-free).
3. $identitySelect dedup — six copy-pasted implementations across Base.cfc, MicrosoftSQLServer/, PostgreSQL/, Oracle/, SQLite/, CockroachDB/ model adapters. The BoxLang ReplaceList column-parse workaround and missing-closing-paren guard exist in only a subset. Extract a Base $parseInsertColumnList template method plus a per-engine $lastIdLookup hook so future engine fixes land once.

Items 1 and 2 are correctness/race bugs; item 3 is the refactor that prevents the next divergence. They are independent enough to land separately but share the same touchpoints.

Confidence: medium

The fix shapes are sketched concretely and the affected files are named, but cross-engine concerns force the auto-downgrade rule (BoxLang vs Lucee vs Adobe CF behavior around JDBC result.generatedKey is the original reason these fallbacks exist, and MSSQL/Oracle each require engine-specific SQL choices). Item 1 also has a design trade-off (same-batch SELECT vs OUTPUT INSERTED.<pk> vs dropping the fallback once BoxLang surfaces generated keys) that propose-fix will need to pick.

<!-- wheels-bot:triage:2906 -->
<!-- wheels-bot:triage-class:bug -->
<!-- wheels-bot:triage-confidence:medium -->

[wheels-bot]

Wheels Bot — Fix on hold for human review

The proposed fix touches a sensitive area (vendor/wheels/databaseAdapters/ — identity retrieval with engine-divergent JDBC behavior) and the bot's safety net requires a human in the loop before any code is written.

The triage already flagged this: the fallback path exists because BoxLang doesn't surface result.generatedKey the way Lucee/Adobe CF do, and the fix shape for each item carries a real design trade-off that shouldn't be picked unilaterally:

• Item 1 (MSSQL same-batch fallback) — three viable shapes: same-batch INSERT ...; SELECT SCOPE_IDENTITY() AS lastId as one statement, OUTPUT INSERTED.<pk> on the INSERT, or dropping the fallback entirely once BoxLang surfaces JDBC generated keys. Each has different test-matrix exposure (MSSQL versions × CFML engines × jTDS-vs-Microsoft driver) and different blast radii if it regresses.
• Item 2 (Oracle CURRVAL) — needs sequence-name discovery from the identity column (e.g. via USER_TAB_IDENTITY_COLS / ALL_TAB_IDENTITY_COLS) before seq.CURRVAL can be emitted. The discovery path itself has engine-version sensitivity (Oracle 12c+ identity columns vs older sequence-backed PKs) and the resulting code couples the adapter to a specific Oracle catalog view.
• Item 3 (\$identitySelect six-way dedup) — a refactor that moves cross-engine behavior into a Base template method + per-engine \$lastIdLookup hook. Worth doing, but only after items 1 and 2 settle on a shape, since the hook signature depends on what the per-engine implementations need.

The current MSSQL fallback already encodes a BoxLang-vs-other branch (!len(query.lastId) → retry with SCOPE_IDENTITY()), confirming cross-CFML-engine sensitivity in this code path. A bot-authored change here would be hard to validate without running the full Lucee/Adobe/BoxLang × MSSQL/Oracle test matrix and reading the JDBC driver behavior, both of which want a human reviewer in the loop.

Recommendation: a maintainer picks the shape for items 1 and 2 (a one-line decision per item), then this can be unblocked or a follow-up issue can carry the dedup refactor.