Where is the data saved, from the Datatable-items, in ECM eRoom-database?

Question

I am trying to retrieve data out of the ECM eRoom Database (which isn't documented, as I know of).

I have an eRoom with an custom "Database", some Fields.

When I query the objects table I find the "Database" row
select * from[dbo].[Objects] where internalId = 1234567

and the Rows for the entries
select top 10 * from[dbo].[Objects] where parentInternalId = 1234567

but I don't find any field with the values of the entries, only an Column with NonSearchableProperties., that is only full with Hex Data.

My Question(s),

how could i retrieve the values?
Is it possible to retrieve them with mere SQL?
What is the simplest way?

score 2 · Accepted Answer · edited May 23 '17 at 12:30

This is not the silver bullet, but it is okay for my usecase

After long goolging and alot of test scripts, i found some answers, but probably due to the fact the the system is soon end-of-life and that the documentation is not easy to read, here are my finding.

Is it possible to retrieve them with mere SQL?
As far as I could find out, no! (please correct me If I'm wrong)

how could i retrieve the values?
With the eRoom API(on the Server there are some Sample programms to query the data/objects <installation-path>\eRoom Server\Toolkit\Samples, with c++, vb, vbscript, ... all a bit to much overhead), or with the eRoom XML Query Language(exql) over soap calls.

What is the simplest way?
After alot of tests, searching in forums and many tests with soap ui. I found out that queries with exql seem to be the simplest way to retrieve Data, if you understand the structure.
Here some ressource that were helpful:

(very) Basic info of exql from the manufacturer https://eroom.asce.org/eRoomHelp/en/XML_Help/Introduction.htm
(disclaimer: I didn't find it helpful, but it shows at least some basics)
short 9 page Developer guide https://developer-content.emc.com/developer/downloads/eRoomXMLCapabilitiesUseCaseProgramDashboard.pdf (the last example on page 8, helped me understand how to setup the query, with alot of fantasy)

But for this to work, don't forget, to activate Allow XML queries and commands from external applications in the Site Settings

screenshot Sitesettings

TIP 1:

you always can go go deeper you just need to know the right xml-element under. <Database>, <Cells> and <DBCell> can help you go deeper

TIP 2:

don't query to much data since this query likely run into timeouts

Update 1:
Just to save time for anyone who is looking, this "query" returns all rows (properties) for a Database(s) created in an eRoom Root.

(don't forget to set facility and room in the Url ex. http://server/eroomxml/facilities/TEST/Rooms/TestRoom, although it could be set in the query)

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:er="http://www.eroom.com/eRoomXML/2003/700">
   <soapenv:Header/>
   <soapenv:Body>
      <er:ExecuteXMLCommand>
         <er:eRoomXML>
            <er:command er:select="HomePage/Items">
                <er:getproperties>
                    <er:Item>
                        <Database>
                            <Rows>
                                <Item>
                                    <Cells>
                                        <DBCell>
                                            <Content>
                                            </Content>
                                        </DBCell>
                                    </Cells>
                                </Item>
                            </Rows>
                        </Database>
                   </er:Item>
                </er:getproperties>
             </er:command>
         </er:eRoomXML>
      </er:ExecuteXMLCommand>
   </soapenv:Body>
</soapenv:Envelope>

Where is the data saved, from the Datatable-items, in ECM eRoom-database?

1 Answers1

This is not the silver bullet, but it is okay for my usecase

TIP 1:

TIP 2: