Query Builder and querying CMS database directly

[Former Member]

Hi all,

when I enter the following SQL in the query builder

SELECT SI_ID, SI_NAME FROM CI_INFOOBJECTS WHERE SI_KIND = 'CrystalReport' the results are returned in a jsp page nicely formatted.

when I fired the same query to the CMS database, I get an error "table does not exists". Does anybody know why?

[amrsalem1983 · Accepted solution]

you may need to put the user name in front of the table name

user.CI_INFOOBJECTS

and also

CI_INFOOBJECTS is not like this in the database

it has the version beside it

cms_infoobjects6 for Xi 3.0

i dont know it for XI r2

good luck

Amr

[Former Member · Accepted solution]

I am using XIR2. The tables in my CMS db all begins with 'CMS_' ( for eg CMS_INFOOBJECTS5' ,'CMS_VERSIONINFO' )

I am very curious how come the query used in the query builder is so different?

[BasicTek · Accepted solution]

The CMS DB stores most of it's data in binary (1's 0's). When you access query builder it works off the infostore (a sort of memory representation of the CMS DB + SDK.The SDK interprets the data into the real valuers that you see in the QB results.

So it's really not possiblt to send the same query through the SDK (query builder) and the CMS DB (binary). If you were trying to do this to report off the DB tables directly it won't work I'm afraid. If that's your end goal the direction you would need to follow is developing a custom app (java/.net) that performs the conversion 1st then query.

Regards,

Tim