search

foxysoft / idm-connector-bobj

SAP Identity Management connector for SAP BusinessObjects BI Platform
Apache License 2.0
5 stars 2 forks source link

SQL exception in pass "Set modify triggers from package constant" in SAP BOBJ 4.2 - Initial Load #13

Closed boskamp closed 5 years ago

boskamp commented 5 years ago

When executing the job "SAP BOBJ 4.2 - Initial Load", an SQL exception occurs. This has been reported for MS SQL Server and Sybase ASE. On Sybase, the exact error message is:

Exception executing --SQL file: fx_bobj_load_set_triggers.sqlselect attr_id ,attrname from mxi_attributes where attrname in ( 'MXREF_MX_GROUP','MXMEMBER_MX_GROUP','DISPLAYNAME','DESCRIPTION','MX_MAIL_PRIMARY','MX_DISABLED','MX_ENCRYPTED_PASSWORD' ) and is_id=FUNCTION.fx_IDSID()$$
java.lang.Throwable: '$' is an invalid money value.

In the job log file, the following stack trace occurs:

Error code 151 SQL state:ZZZZZ(!) - com.sybase.jdbc4.jdbc.SybSQLException: '$' is an invalid money value.

'$' is an invalid money value.

        com.sybase.jdbc4.tds.Tds.processEed(Tds.java:4084)
        com.sybase.jdbc4.tds.Tds.nextResult(Tds.java:3174)
        com.sybase.jdbc4.jdbc.ResultGetter.nextResult(ResultGetter.java:78)
        com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:289)
        com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:271)
        com.sybase.jdbc4.jdbc.SybStatement.executeLoop(SybStatement.java:2593)
        com.sybase.jdbc4.jdbc.SybStatement.execute(SybStatement.java:2580)
        com.sybase.jdbc4.jdbc.SybStatement.execute(SybStatement.java:1292)
        com.sap.idm.ic.DBStatement.executeInternal(DBStatement.java:818)
        com.sap.idm.ic.DBStatement.execute(DBStatement.java:857)
        com.sap.idm.ic.JDBCHandler.fetchEntries(JDBCHandler.java:1683)
        com.sap.idm.ic.DSERunTime.runToPass(DSERunTime.java:2377)
        com.sap.idm.ic.DSERunTime.runPass(DSERunTime.java:3137)
        com.sap.idm.ic.DSERunTime.runJob(DSERunTime.java:1264)
        com.sap.idm.ic.DSERunTime.loopJob(DSERunTime.java:667)
        com.sap.idm.ic.DSERunTime.doCommand(DSERunTime.java:1888)
        com.sap.idm.ic.DSERunTime.doCommand(DSERunTime.java:333)
        com.sap.idm.ic.DSERunTime.run(DSERunTime.java:3526)
        com.sap.idm.ic.DSERunTime.main(DSERunTime.java:3449)
boskamp commented 5 years ago

It seems that this is a problem in the SAP IDM package import code. The predicate 

is_id=$FUNCTION.fx_IDSID()$$

which is contained in the source SQL query of this pass is consistently corrupted by the package import and becomes

is_id=FUNCTION.fx_IDSID()$$

after import. Note that the leading dollar sign before FUNCTION is missing. This seems to be a product issue which I cannot fix directly.

WORKAROUND To side-step this problem, I've changed the predicate into

and is_id in ( $FUNCTION.fx_IDSID()$$ )

Because the return value of fx_IDSID is always a scalar, operator IN and operator = are equivalent in this case.