Our application is migrating on azure, earlier it was on CMS. So database need to migrated from oracle to azure sql db.
Out of the scripts provided by camunda, which scripts are suitable for azure sql db.
mssql OR mysql OR any other.
I tried with mysql and oracle, the script didn’t execute.
For mssql, the application loaded but its giving error while login.
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The data types numeric and int are incompatible in the ‘&’ operator.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265) ~[mssql-jdbc-9.4.1.jre8.jar!/:?]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1662) ~[mssql-jdbc-9.4.1.jre8.jar!/:?]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:615) ~[mssql-jdbc-9.4.1.jre8.jar!/:?]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:537) ~[mssql-jdbc-9.4.1.jre8.jar!/:?]
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7417) ~[mssql-jdbc-9.4.1.jre8.jar!/:?]
at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.lambda$executeSelectList$1(DbSqlSession.java:112) ~[camunda-engine-7.18.0.jar!/:7.18.0]
at org.camunda.bpm.engine.impl.util.ExceptionUtil.doWithExceptionWrapper(ExceptionUtil.java:351) ~[camunda-engine-7.18.0.jar!/:7.18.0]
… 106 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The data types numeric and int are incompatible in the ‘&’ operator.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265) ~[mssql-jdbc-9.4.1.jre8.jar!/:?]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1662) ~[mssql-jdbc-9.4.1.jre8.jar!/:?]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:615) ~[mssql-jdbc-9.4.1.jre8.jar!/:?]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:537) ~[mssql-jdbc-9.4.1.jre8.jar!/:?]
Query that is failing to execute -
SELECT CASE
WHEN EXISTS (SELECT A.resource_id_
FROM ncto_owner.act_ru_authorization A
WHERE A.type_ = 1
AND A.user_id_ = ?
AND A.perms_ &? = ?
AND A.resource_type_ = ?
AND A.resource_id_ = ?) THEN 1
WHEN EXISTS (SELECT id_
FROM ncto_owner.act_ru_authorization A
WHERE A.type_ = 1
AND A.user_id_ = ?
AND A.perms_ &? = ?
AND A.resource_type_ = ?
AND A.resource_id_ = ‘') THEN 1
ELSE (SELECT CASE
WHEN EXISTS (SELECT A.resource_id_
FROM ncto_owner.act_ru_authorization A
WHERE A.type_ = 1
AND A.group_id_ IN ( ? )
AND A.perms_ &? = ?
AND A.resource_type_ = ?
AND A.resource_id_ = ?) THEN 1
WHEN EXISTS (SELECT id_
FROM ncto_owner.act_ru_authorization A
WHERE A.type_ = 1
AND A.group_id_ IN ( ? )
AND A.perms_ &? = ?
AND A.resource_type_ = ?
AND A.resource_id_ = '’) THEN 1
ELSE (SELECT CASE
WHEN EXISTS (SELECT A.resource_id_
FROM
ncto_owner.act_ru_authorization A
WHERE A.type_ = 0
AND A.user_id_ = ‘’
AND A.perms_ &? = ?
AND A.resource_type_
= ?
AND A.resource_id_ =
?) THEN
1
WHEN EXISTS (SELECT id_
FROM
ncto_owner.act_ru_authorization A
WHERE A.type_ = 0
AND A.user_id_ = '’
AND A.perms_ &? = ?
AND A.resource_type_
= ?
AND A.resource_id_ =
‘*’)
THEN 1
ELSE NULL
END)
END)
END