topic Re: Get PKID of inserted row in Management

Get PKID of inserted row

Gordon Ross — Thu, 03 Apr 2014 08:16:14 GMT

When inserting a row via AXL SQL, is there a way to get the PKID of the newly inserted row?

Thanks,

GTG

Re: Get PKID of inserted row

Geevarghese Cheria — Tue, 08 Apr 2014 12:42:43 GMT

Hi GTG,

Here is a snip of SQL to get the PKID of a phone.

select pkid from device where name='SEP000000111111'

This will return the PKID, or UUID, of the phone with MAC address 000000111111

Download yourself a copy of the data dictionary for whichever version of Communications Manager you are working with.

If you have access to the COmmunications Manager console directly or through an SSH client you can run sql commands and see the returned values from there before writing them into your code.

In the command line the query would look something like this:

run sql select PKID from device where name = "SEP000000111111"

Reference - Finding UUID for CUCM Objects

Thanks and Regards,

Geevarghese

Re: Get PKID of inserted row

Gordon Ross — Tue, 08 Apr 2014 13:06:02 GMT

I know how to do that, and I do have the CallManager Data Dictionary by my side - all 1,000 pages of it.

In some databases, you can do a "INSERT INTO table (columns) VALUES (....) RETURNING pkid" and the insert command would give you the pkid of the just inserted row.

I was hoping to avoid the round-trip of issuing an INSERT, then having to do a separate SELECT.

GTG

Re: Get PKID of inserted row

tinghche — Wed, 09 Apr 2014 20:50:10 GMT

Take a look at this sample SQL query, you should be able to get the pkid using newid() function.

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">

<SOAP-ENV:Body>

<axlapi:executeSQLUpdate sequence="1" xmlns:axlapi="http://www.cisco.com/AXL/API/7.0" xmlns:axl="http://www.cisco.com/AXL/7.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.cisco.com/AXL/API/7.0 axlsoap.xsd">

<sql>

insert into device (fkPhoneTemplate,fkDevicePool,tkclass, tkpreemption, tkdeviceprofile, tkmodel, tkdeviceprotocol, tkproduct, description, tkstatus_mlppindicationstatus, name, pkid) values ('Standard 7941 SCCP','default',1, 2, 2, 115, 0, 115, '', 0, 'Cisco 7941', newid())

</sql>

</axlapi:executeSQLUpdate>

</SOAP-ENV:Body>

</SOAP-ENV:Envelope>

Regards,

Howard