This document was generated from CDN thread
Created by: GEERT DEWINTER on 04-05-2010 10:18:11 AM
Hi,
I would like to know if there exist an SQL command to find the column names that are defined for a field.
If you do a "SELECT * FROM DEVICE;" that also the column names are displayed.
Thanks a lot in advance.
Geert
Subject: RE: SQL to find the names of the fields
Replied by: David Staudt on 04-05-2010 01:37:27 PM
The return of executeSQLQuery does have the field names embedded in the XML:
<axl:executeSQLQueryResponse sequence="1" xmlns:axl="http://www.cisco.com/AXL/API/7.0" xmlns:xsi="http://www.cisco.com/AXL/API/7.0">
<return>
<row>
<pkid>672e5328-403f-4aa9-a969-e2483426b682</pkid>
<name>MTP_2</name>
<description>MTP_DS-CM7</description>
<tkmodel>110</tkmodel>
<tkdeviceprotocol>6</tkdeviceprotocol>
...
If you want to manually check, from the UCM CLI you can execute SQL directly - the result includes field names as a header:
admin:run sql select first 2 pkid,name from device
pkid name
=========================== =====
672e5328-403f-4aa9-a969-e2483426b682 MTP_2
34186aed-859f-4ee9-98a8-d3df66258778 CFB_2
If you need to programmatically check the schema of a table, see the 'typetable' and 'typefield' tables in the database:
<ns:executeSQLQuery sequence="1">
<sql>select fieldname,fieldtype from typetableinfo,typefieldinfo where typetableinfo.enum=typefieldinfo.tktableinfo and tablename='Device'</sql>
</ns:executeSQLQuery>
---------------------------
<axl:executeSQLQueryResponse sequence="1" xmlns:axl="http://www.cisco.com/AXL/API/7.0" xmlns:xsi="http://www.cisco.com/AXL/API/7.0">
<return>
<row>
<fieldname>pkid</fieldname>
<fieldtype>GUID</fieldtype>
</row>
<row>
<fieldname>Name</fieldname>
<fieldtype>string</fieldtype>
</row>
<row>
<fieldname>Description</fieldname>
<fieldtype>string</fieldtype>
</row>
