#1 2010-04-21 11:08:30

**_vitand_**

Database structure info needed

I will need to run the queries to directly access the database. Can anybody point me to any documentations related to database structure, tables relations, fields descriptions, probably some queries examples.
Thanks

**_mrock66_**

Re: Database structure info needed

Only views are described in Adobe documentation, everything else is done/sorted out on your own :(

**_tlchurch_**

Re: Database structure info needed

Vitand,

In my experience, if you know what info you're looking for and can't figure it out on your own, Adobe support can usually give you the SQL query you're looking for.

And/or post your area of inquiry here and we'll see if anyone can help.

Tables I use most:
- PPS_ENUM_DATA_HOSTS - servers in your cluster
- PPS_SCOS - meetings, content, folders
- PPS_PRINCIPALS - user information
- PPS_TRANSCRIPTS - user activity

Anything other than SELECT statements, I would definitely have approved by Adobe before running - otherwise they may not support the change for you.

**_doug.morgan_**

Re: Database structure info needed

Vitand,

What are you looking for specifically?

**_Biffa_**

Re: Database structure info needed

I'd like to run a query to update all the First name fields in the PPS_PRINCIPALS database specifically to add some text at the front of each name

e.g.

Before: Name = John Smith
After: Name = # John Smith

Last edited by **_Biffa_** (2010-04-29 12:33:14)

**_connectguy_**

Re: Database structure info needed

Contact Adobe support.  We will help you do this.
Note, it is a one time thing.  Running queries directly against the DB is usually not supported.

**_cypride_**

Re: Database structure info needed

Does anyone know what tables/fields the course duration comes from in the curriculum reports?

I am writing a Crystal report and trying to find the 'hours to complete' a course.  In the reports section of Connect Pro, by item, by user, there is a field called 'Time Taken (hh:mm:ss)' that displays the time spent in this course.  I cannot find a view or table that I can calculate this data from.

(p.s. I am new to this site; I hope it is ok to add to this post)

Last edited by **_cypride_** (2010-05-12 15:02:31)

**_mrock66_**

Re: Database structure info needed

I don't believe it exists unless you are on SP1, then you should look for AICC_TIME

**_cypride_**

Re: Database structure info needed

Which table?  Is that PPS_TRANSCRIPTS.TIME_AICC?

If so, is that in seconds?  I have a value of 115000 for one of my records, but if it is seconds, then it is longer than the time spent for this course and user.

Last edited by **_cypride_** (2010-05-12 15:50:34)

**_connectguy_**

Re: Database structure info needed

cypride,

It is in ms. 
Please keep in mind that querying the DB directly is not advisable, nor supported by Adobe. 
Adobe Connect exposes API calls (Web Services) to get this information progromatically.  The underlying DB schema of Connect changes from release to release.  The API's are tested, but Adobe cannot support all the custom queries that could break. 

Brad

Last edited by **_connectguy_** (2010-05-13 11:55:46)

**_rayonkerion_**

Re: Database structure info needed

i write database connectivity code but there is an exception raised unable to connect with database
please give me write information about connection string

**_lenadionex_**

Re: Database structure info needed

Similar to the previous poster, I am also exploring the option of directly querying the database and would appreciate any basic information as to how to connect and execute the query (once I have the table names I can write the SQL query.

Thanks!

**_drilos_**

Re: Database structure info needed

Hi all, I've been looking your posts and them give several ideas and more questions.

We need to create a detailed report from our users. We have 5 studen programs. The report must to have the member group, complete name, and the certificate number or the status en each program.

Before read your comments I understood that I can

**_hansmartin_**

Re: Database structure info needed

you should do is contact adobe
rpp madrasah aliyah
kanker servik

**_phyoe2004_**

Re: Database structure info needed

Vitand,

In my experience, if you know what info you're looking for and can't figure it out on your own, Adobe support can usually give you the SQL query you're looking for.

And/or post your area of inquiry here and we'll see if anyone can help.

Tables I use most:
- PPS_ENUM_DATA_HOSTS - servers in your cluster
- PPS_SCOS - meetings, content, folders
- PPS_PRINCIPALS - user information
- PPS_TRANSCRIPTS - user activity

Anything other than SELECT statements, I would definitely have approved by Adobe before running - otherwise they may not support the change for you.

Dear tlchurch,

Is it possible to create new user by manually insert into the PPS_PRINCIPALS? Is there any other way to insert without using Adobe connect pro?

**_Purnima_**

Re: Database structure info needed

Adding users directly to DB is not the most preferred way. Though yes you can do so.

You can also use XML apis to do so, which is much safer way.

If you have long list of users to add, you can add them in one go by import user functionality in Administration panel.

**_phyoe2004_**

Re: Database structure info needed

Dear Purnima,

Thanks for your suggestion.

**_phyoe2004_**

Re: Database structure info needed

What is the table name to retrieve the Group List?