topic Finally got this working. in Unified Communications Infrastructure

cuc sql query on mutliple db

n.bratsikas — Tue, 19 Mar 2019 19:03:44 GMT

Hi,

I'm trying to query on mutliple db in Unity, but looks like 'run cuc dbquery' does not allow it.

admin:run cuc dbquery select map.description, u.alias from unitydirdb.tbl_mailboxmap map,unitymbxdb1.tbl_mailbox m,unitydirdb.tbl_globaluser u where m.mailboxobjectid=map.mailboxid and u.objectid=map.userobjectid and map.description != u.alias

The value of 'database_name' is not valid; see help or syntax for valid names
USAGE: run cuc dbquery database_name sql_statement [page]
Command failed

Does anybody know how to achieve this?

Nick

Hi Nick,

Anoop Krishnan — Tue, 31 Jan 2017 07:03:27 GMT

Hi Nick,

Please find the details below

database_name specifies the database that sql_statement operates on. Database names are case sensitive. Connection databases include:

–unitydirdb—contains the directory and configuration data

–unitydyndb—contains dynamic data used internally by Connection

–unitymbxdb1—contains the data about the current voice messages, including pointers to the audio files that are stored in the file system

–unityrptdb—contains audit log data

You could have a try using these names

Regards,

Anoop

Hi Anoop,

n.bratsikas — Tue, 31 Jan 2017 11:27:02 GMT

Hi Anoop,

I'm well aware you need to provide a db name, but as my query joins tables from 2 databases, you can't just put one as database_name. Not working with 2 either.

with one db defined:

admin:run cuc dbquery unitydirdb select map.description, u.alias from unitydirdb.tbl_mailboxmap map,unitymbxdb1.tbl_mailbox m,unitydirdb.tbl_globaluser u where m.mailboxobjectid=map.mailboxid and u.objectid=map.userobjectid and map.description != u.alias

The specified table (unitydirdb.tbl_mailboxmap) is not in the database.

with 2 db defined:

admin:run cuc dbquery unitydirdb,unitymbxdb1 select map.description, u.alias from unitydirdb.tbl_mailboxmap map,unitymbxdb1.tbl_mailbox m,unitydirdb.tbl_globaluser u where m.mailboxobjectid=map.mailboxid and u.objectid=map.userobjectid and map.description != u.alias

The value of 'database_name' is not valid; see help or syntax for valid names
USAGE: run cuc dbquery database_name sql_statement [page]
Command failed

Nick

Finally got this working.

n.bratsikas — Mon, 06 Feb 2017 10:13:50 GMT

Finally got this working. Here's the correct syntax:

admin:run cuc dbquery unitydirdb select alias from unitydirdb:tbl_globaluser,unitymbxdb1:tbl_mailbox,unitydirdb:tbl_mailboxmap where mailboxobjectid=mailboxid and objectid=userobjectid and description != alias

Nick