#1 2009-10-15 15:09:13

**_leandro.ald_**

Questions in the database

Hello Guys

I live in Brazil and we greatly Adobe Connect Pro meeting, I menage the program but do not have much knowledge in database.

I need to do a "select" or "view" to bring me: start date of the meeting, the date of termination of the meeting, all participants of each meeting along with the link of the meeting. That alone helps me a lot

Note: sorry for the mistakes of writing, because I used online translator

**_quinn_**

Re: Questions in the database

Hi Leandro, 

You could use the XML APIs to get all this information for a specific meeting.  Here's the list of APIs that you need to use:

report-meeting-sessions
report-meeting-attendance
report-meeting-summary

I personally think that it is more straightforward to deal with the XML APIs instead of writing queries against the db.

**_leandro.ald_**

Re: Questions in the database

Hi Leandro, 

You could use the XML APIs to get all this information for a specific meeting.  Here's the list of APIs that you need to use:

report-meeting-sessions
report-meeting-attendance
report-meeting-summary

I personally think that it is more straightforward to deal with the XML APIs instead of writing queries against the db.

Thank you for answering
The problem of API (XML) and I do not have any knowledge ... I have not idea how to do.

excuse the errors, but what I'm using online translator

hugs and thank you

**_quinn_**

Re: Questions in the database

This blog entry gives a brief introduction to a few of the most widely used meeting report XML APIs:

    * meeting-report-sessions
    * meeting-report-attendance
    * meeting-report-summary

These are the same web service calls that are used to generate the reports underneath the meeting

**_leandro.ald_**

Re: Questions in the database

this was the conclusion I reached

SELECT     TOP (100) PERCENT MAX(dbo.PPS_SCOS.URL_PATH) AS URL_PATH, MAX(dbo.PPS_ASSETS.DATE_CREATED) AS DATE_CREATED,
                      MAX(dbo.PPS_ASSETS.DATE_END) AS DATE_END, COUNT(DISTINCT dbo.PPS_TRANSCRIPTS.SESSION_ID) AS QTD_SESSION_ID
FROM         dbo.PPS_ASSETS INNER JOIN
                      dbo.PPS_TRANSCRIPTS ON dbo.PPS_ASSETS.ASSET_ID = dbo.PPS_TRANSCRIPTS.ASSET_ID INNER JOIN
                      dbo.PPS_SCOS ON dbo.PPS_TRANSCRIPTS.SCO_ID = dbo.PPS_SCOS.SCO_ID
WHERE     (dbo.PPS_ASSETS.DATE_CREATED BETWEEN CONVERT(DATETIME, '2010-02-01 02:00:00', 102) AND CONVERT(DATETIME, '2010-03-01 01:59:00',
                      102)) AND (NOT (dbo.PPS_ASSETS.DATE_END IS NULL))
GROUP BY dbo.PPS_ASSETS.ASSET_ID, dbo.PPS_ASSETS.DATE_CREATED
ORDER BY dbo.PPS_ASSETS.DATE_CREATED

Now I have another problem: I need to do a query that shows me the registered users in their respective rooms.

can someone help me?

**_quinn_**

Re: Questions in the database

Hi Leandro,

I would recommend against calling the database directly since the db schema may change from release to release.  It is not guaranteed to be backwards compatible like the XML APIs are.   

Here's the API that you can call to get a list of registered users for a meeting room:

https://admin.adobe.acrobat.com/api/xml … =mini-host

This returns the following XML payload:

<results>
<status code="ok"/>
<permissions>
<principal principal-id="612130066" is-primary="false" type="user" has-children="false" permission-id="host" training-group-id="">
<name>Quinn Wong</name>
<login>qwong@adobe.com</login>
</principal>
<principal principal-id="612901954" is-primary="false" type="user" has-children="false" permission-id="view" training-group-id="">
<name>Test User2</name>
<login>testuser2@adobe.com</login>
</principal>
<principal principal-id="612948450" is-primary="false" type="user" has-children="false" permission-id="mini-host" training-group-id="">
<name>Test User</name>
<login>testuser1@adobe.com</login>
</principal>
</permissions>
</results>

If you really do want a database query, here it is:

SELECT
    P.PRINCIPAL_ID,
    P.LOGIN,
    A.ACL_ID AS MEETING_ID,
    (CASE A.PERMISSION_ID
        WHEN 2 THEN 'participant'
        WHEN 10 THEN 'host'
        WHEN 22 THEN 'presenter'
        END) AS PERMISSIONS
FROM
    PPS_ACL_ENTRIES A, PPS_PRINCIPALS P
WHERE
    A.PRINCIPAL_ID = P.PRINCIPAL_ID AND
    A.ACL_ID = 35360

Thanks,
Quinn