-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: 6.5.0
-
Component/s: Module - Audit, Module - Repository JDBC
-
Labels:
IDM 6.5.
Using MSSQL as repository, and repo as handler for audit queries.
Querying the recon audit with "queryFilter=... and /message eq ..." fails because in the DB schema for MSSQL, "message" is defined as ntext.
E.g.
REST GET of
/openidm/audit/recon?_queryFilter=/reconId eq "65e155de-c8f3-41ad-b674-b3d96256660f-57366" and /message eq "Policy validation failed" and /status pr and /status eq "FAILURE"&aggregateFields=TIMESTAMP=/timestamp,VALUE=/message,VALUE=/status,VALUE=/messageDetail,VALUE=/sourceObjectId
Response is:
{
"code": 500,
"reason": "Internal Server Error",
"message": "DB reported failure executing query."
}
openidm log:
[162] Feb 18, 2019 6:14:43.277 PM org.forgerock.openidm.repo.jdbc.impl.query.TableQueries query
FEIN: DB reported failure executing query org.forgerock.openidm.repo.jdbc.impl.query.TracedPreparedStatement@751323cc with params: {_pageSize=2147483647, _queryFilter=(/reconId eq "65e155de-c8f3-41ad-b674-b3d96256660f-57366" and /message eq "Policy validation failed" and /status pr and /status eq "FAILURE"), _pagedResultsCookie={}, aggregateFields=TIMESTAMP=/timestamp,VALUE=/message,VALUE=/status,VALUE=/messageDetail,VALUE=/sourceObjectId, _queryExpression=null, _pagedResultsOffset=0, _resource=audit/recon, _queryId=null, _sortKeys=[]} error code: 402 sqlstate: S0001 message: The data types ntext and nvarchar are incompatible in the equal to operator.
com.microsoft.sqlserver.jdbc.SQLServerException: The data types ntext and nvarchar are incompatible in the equal to operator.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1635)
...
WORKAROUND:
The query works if the "co" operator is used instead of "eq".
(queryFilter=/message co ...)
- is related to
-
-
- Closed
-
- relates to
-
OPENIDM-13053 Document upgrade strategy for MSSQL conversion from NTEXT to NVARCHAR(MAX)
-
- Closed
-