BOXI - How can I query the CMS_INFOOBJECTS table for the OBJNAME? 1

[Seabrook7]

Hi everyone,
I just upgraded my application from CE9 to BOXI.

In CE9 I was quering aps_infoobjects for the report ID, by using the name of the report like(converting a raw to a varchar2):

SELECT ObjectID FROM APS_InfoObjects WHERE TypeID=2 AND utl_raw.cast_to_varchar2(objname)like 'Manpower Summary%'

This query doesn't seem to work with the BOXI cms_infoobjects table. I get the error:

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
ORA-06512: at line 1

I'm assuming that BOXI isn't storing the objname in the same format as CE9 (as a raw). Anyone have any suggestions?

Thanks in Advance.

 

[mdwyer]

Don't know about the storage in XI, but I sure appreciate seeing the query for 9. No one on this forum has indicated any knowledge of straight SQL queries against the APS database. Anything else you can share about it would be useful. Just modifying your query to

SELECT Typeid, ObjectID, utl_raw.cast_to_varchar2(objname) FROM APS_InfoObjects2

is like turning on a light in a dark room.

 

[Seabrook7]

it's no problem. that query did take a while to figure out, though. glad someone else is getting some use out of it. i don't do any other straight queries on the ce database, so I don't have much more to add.

if anyone has any info on querying the new (BOXI) cms_infoobjects table, i'd appreciate it. Or even if you could identify what datatype it's storing the info as. The objname is 'Manpower Summary' and a straight query brings back:

B*DHFV2L!A{NRBB*LZ!<O

 

[mdwyer]

Oh, I see what you are doing - with TypeID and ObjectID, which are just NUMBER datatypes. But still a nice use of the UTL_RAW package to convert the RAW ObjName. If you don't have a tool to display the column datatypes, can you run this query:

SELECT Column_Name, Data_Type FROM All_Tab_Columns WHERE Table_Name LIKE 'CMS_INFOOBJECTS%'

(I used the LIKE operator because it appears that there is sometimes a digit appended to the info-objects table name.)

 

[KingfisherINC]

The column is Raw binary in Oracle (Binary Variable in SQL Server) but you know that from your query, no?

The problem, I think, is that you're casting it ok but this field is now encrypted (as far as I can tell). This does not surprise me given the Audit options in XI.

You'll have to use the SDK APIs.

Kingfisher [CECP]

 

[Seabrook7]

Kingfisher - thanks for the tip.

I figured it was encrypted but the error I got when casting from raw to varchar2 made me think it wasn't a RAW anymore.

But you're probably right. Maybe it is still a RAW, but I need to decrypt it now, also.

I'll check the API's.

 

[digitaldallas]

You can use another query without casting:

Select * from CI_INFOOBJECTS
where SI_NAME= 'reportname'
and SI_PROGID = 'CrystalEnterprise.Report'
and SI_INSTANCE=0

I use this to get an InfoObject to which I then caste as a ReportClientDocument. It works in CE9 & CE10 and I will try once I get CRSXI.