AXL INSERT INTO on CUCM 12.5 table - Get new PKID

[neblaz]

I want to do an INSERT INTO on a CUCM table.

There are two SQL requests:
- ExecuteSQLQueryReq

- ExecuteSQLUpdateReq

I need the PKID of that insert.

How to do that? Which of the two Req to use?

[neblaz]

It looks like an INSERT INTO has to be done with ExecuteSQLUpdateReq.

I found this example:
https://community.cisco.com/t5/management/get-pkid-of-inserted-row/td-p/3429510

Which shows an example to use "newId()" function.
But what kind of function is "newId()"?

But when I try so with AXL, adding "newId()" to my INSERT INTO statment:

String sqlStmtUpdateTemplate =
"""
INSERT INTO telecastersubscribedservice (
pkid,
fkdevice,
serviceurl,
servicename,
fktelecasterservice
)
VALUES (newId(), '%s', '%s', '%s', '%s')
""";

Replacing the %s with String values, and executing it with ExecuteSQLUpdateReq, results in:

com.cisco.axlapiservice.AXLError: Failure
AXLError-Code: -1

[npetrele]

Try newid() instead of newId().

[neblaz]

newid() would be the correct method.

But, it doesn't give back the PKID.
In fact I can remove the newid() entirely in the INSERT INTO statement, as according to the Data Dictionary the default value for the PKID will be generated with newid().

INSERT INTO telecastersubscribedservice (
pkid,
fkdevice,
serviceurl,
servicename,
fktelecasterservice,
secureserviceurl )
VALUES (
newid(),
'...fkdevice lower-case without { and }...',
'http://...?param1=...',
'MyTestService',
'...fktelecasterservice lower-case without { and }...',
'https://...?param1=..'
) 

I can insert that into telecastersubscribedservice.

ExecuteSQLUpdateRes executeSQLUpdateRes = null;
try {
    executeSQLUpdateRes = axlService.getAxlPort().executeSQLUpdate(executeSQLUpdateReq);
} catch (AXLError axlError) {
...
}

return executeSQLUpdateRes.getReturn().getRowsUpdated();

The response ExecuteSQLUpdateRes of the ExecuteSQLUpdateReq returns the rows updated, which in case of one INSERT INTO result in 1.

I don't get the PKID ot the insert back.

[npetrele]

I'm not sure why you would expect to get the PKID back from the insert. It's a SQL operation, and that operation doesn't return a PKID value.

If you want to get the PKID, then you should create a SQLQuery to get it.  Perhaps something like Select pkid from telecastersubscribedservice where servicename = "MyTestService".

[neblaz]

In theory your right, after the INSERT INTO simply query the tuple.

But as far as I can see, the primary key is PKID.

That means, that there could be possibly many tuples where only the PKID differs.

After the INSERT INTO I could get back more then the just inserted tuple.

[npetrele]

You're dealing with the limitations of using SQL. That's just the way SQL works.

Perhaps you should, instead, use AXL API updatePhone, which includes this section in the XML:

<services>
<!--Zero or more repetitions:-->
<service>
<telecasterServiceName uuid="?">?</telecasterServiceName>
<name>?</name>
<!--Optional:-->
<url>?</url>
<!--Optional:-->
<urlButtonIndex>0</urlButtonIndex>
<!--Optional:-->
<urlLabel>?</urlLabel>
<!--Optional:-->
<serviceNameAscii>?</serviceNameAscii>
</service>
</services>

Then you can use getPhone for that particular phone. If you tell getPhone to return <services>, you'll see the UUID of the telecaster service, which is the same as the PKID.