I am by no means an sql expert, but does the following return anything? I believe it should check for a null character…at least in oracle. If anyone knows more, feel free to chime in!
Select * from rxmenuaction where name like '%\0%' or displayname like '%\0%'
or description like '%\0%'
Oh, I would check something similar on the following tables: rxmenuactionparam, rxmenuactionproperties, rxmenuvisibility (as those appear to have allowed string values).
I was able to figure out the problem. Initially I thought that the invalid null character was coming from XML file generated by sys_psxCms/QueryActions.xml. Within this XML file, there are different URL’s which result in more XML files. And it turned out that http://server-address:9992/Rhythmyx/sys_psxCms/VisibilityContexts.xml?sys_actionid=ACTIONID was the problem. After digging a little more, I realized that the data for this XML files is being queried from a view PSX_MENUVISIBILITY_VIEW. Here is how the view is created:
CREATE VIEW "RMX" ."PSX_MENUVISIBILITY_VIEW" ("ACTIONID" , "VISIBILITYCONTEXT" , "VALUE" , "DESCRIPTION" ) AS
select * from RMX.RXMENUVISIBILITY where VISIBILITYCONTEXT <> '2' union select a.ACTIONID, '2'VISIBILITYCONTEXT, rtrim( char( C.COMMUNITYID)) VALUE , [B]'from acls' DESCRIPTION[/B] from RMX.RXMENUACTION a, RMX.RXCOMMUNITY C where (select count (*) from RMX.PSX_COMMUNITY_PERMISSION_VIEW pwhere p .OBJECTTYPE = 107 and p .OBJECTID = a.ACTIONID and (C .COMMUNITYID = COMMUNITYID) and p .PERMISSION = 40) = 0 and (select count (*) from RMX.PSX_ACLS ACL where OBJECTTYPE = 107 and OBJECTID = a.ACTIONID) <> 0;
To fix the problem we changed ‘from acls’ DESCRIPTION to cast ((‘from acls’ ) as varchar (255)) DESCRIPTION and that solved the problem.
CREATE VIEW "RMX" ."PSX_MENUVISIBILITY_VIEW" ("ACTIONID" , "VISIBILITYCONTEXT" , "VALUE" , "DESCRIPTION" ) AS
select * from RMX.RXMENUVISIBILITY where VISIBILITYCONTEXT <> '2' union select a.ACTIONID, '2'VISIBILITYCONTEXT, rtrim( char( C.COMMUNITYID)) VALUE , [B]cast (('from acls' ) as varchar (255))[/B] DESCRIPTION from RMX.RXMENUACTION a, RMX.RXCOMMUNITY C where (select count (*) from RMX.PSX_COMMUNITY_PERMISSION_VIEW pwhere p .OBJECTTYPE = 107 and p .OBJECTID = a.ACTIONID and (C .COMMUNITYID = COMMUNITYID) and p .PERMISSION = 40) = 0 and (select count (*) from RMX.PSX_ACLS ACL where OBJECTTYPE = 107 and OBJECTID = a.ACTIONID) <> 0;
Thanks for all your help!
Thank you for sharing your solution.
Were you able to get much assistance from Tech Support? I realize that changing the database can cause TS to stop supporting your environment :\
Good job on locating this. I have put this into a bug report CMS-317 and will make sure this gets into the next release so it is officially supported.
Stephen,
Do you know if there is an active list of all the open bug reports? There is the Product Defects forum, but it isn’t active.