Search code examples
sql-serveremceroom

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

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.
eroom database icon

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

screenshot database table result

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

screenshot database table result

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.

screenshot database table result

My Question(s),

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

  • 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:

    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>