CDM cache warming fails on SQL Server — @vocabTableQualifier parameter not substituted (parameter name mismatch in CDMCacheService)

[smicke92]

CDM results cache warming fails for all SQL Server CDM sources with the following error:

PreparedStatementCallback; bad SQL grammar [...
join @vocabTableQualifier.concept c on cc.concept_id = c.concept_id
...]; SQL state [S0002]; error code [137];
Must declare the scalar variable "@vocabTableQualifier".

The @vocabTableQualifier placeholder is never substituted before the SQL is sent to SQL Server, causing the query to fail. This affects all SQL Server CDM sources regardless of version (observed in 2.14.1 and 2.15.1).

Root cause

There is a parameter name mismatch between CDMCacheService.java and the SQL template files.

In CDMCacheService.java, the PreparedStatementRenderer is constructed with the table parameter named "vocabularyTableQualifier":
String[] tables = {"resultTableQualifier", "vocabularyTableQualifier"};

This causes SqlRender to substitute @vocabularyTableQualifier in the SQL. However, the SQL template files (getBatchConceptRecordCount.sql, getConceptRecordCount.sql, and their person-count variants) use @vocabTableQualifier — a different name — so it is never substituted.

Expected behavior

The vocabulary schema name is substituted into the SQL before execution, e.g.:
join OMOP_2025_11_21_HBB_85_DD_18.concept c on cc.concept_id = c.concept_id

Fix

Either:

Rename @vocabTableQualifier → @vocabularyTableQualifier in the 4 affected SQL template files, or
Change "vocabularyTableQualifier" → "vocabTableQualifier" in CDMCacheService.java
Environment

WebAPI versions: 2.14.0, 2.15.1
CDM database: SQL Server
WebAPI database: PostgreSQL

[chrisknoll]

Crazy that we haevn't seen this ! Thank you for the report, this should be a straightforward fix. I think we'd go with the changing the CDMCacheService.java to pass the correct param vs. chaning the 4 SQL templates.

[chrisknoll]

Actually after doing some research, i only find @vocabTableQualifier in one sql, the other uses vocabulary, so I think the fix is to fix the template to use the proper placeholder to make it consistent.

Which 4 other files were you referencing?

[smicke92]

Thank you ! Your'e correct, it was only in 1 place. A bit of search results mistype so i got 4 results with matching patterns, but actually it's only 1.

[chrisknoll]

Ok PR created, we can probably do a simple hotfix for 2.15.2 that includes this. Shouldn't be an issue.