executeSQLUpdate - devicenumplanmap

cdnadmin · ‎01-25-2014

This document was generated from CDN thread

Created by: Martin Sloan on 27-02-2012 05:10:39 PM
Hello,

I'm trying to add lines to a phone using executeSQLUpdate and seem to be running into an issue. I'm resorting to the sql method since the updatePhone method has some unexpected behavior (it erases all other lines on a phone when I add a line, yikes!). Here's my message and the error response:

Message:
<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<executeSQLUpdate xmlns="AXLAPI.wsdl">
<sql xsi:type="xsd:string">insert into devicenumplanmap (fkdevice,fknumplan,numplanindex,label,labelascii,display,displayascii,e164mask,logmissedcalls) values ("e31bbc7d-10ae-90ab-948a-cc89842cee39","ec6d081e-31d9-968a-97c9-9d0f149393bf","2","123456","123456","test entry","test entry","2025551212","t")</sql></executeSQLUpdate>
</soap:Body>
</soap:Envelope>

Error:
Missing key in referenced table for referential constraint (informix.fk_devicenumplanmap_fknumplan)

If I copy/paste the SQL message into the CUCM CLI (adding run sql, of course) it works.

Can anyone spot what I might be missing on this one? Maybe an API version thing?

Thanks!

Marty

Subject: RE: executeSQLUpdate - devicenumplanmap
Replied by: Christopher O'Neil on 14-03-2012 09:03:36 PM
Hi, did you ever find a solution to this?

I am having the same issue!

Thanks,

Chris.

Hello,

I'm trying to add lines to a phone using executeSQLUpdate and seem to be running into an issue. I'm resorting to the sql method since the updatePhone method has some unexpected behavior (it erases all other lines on a phone when I add a line, yikes!). Here's my message and the error response:

Message:
<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<executeSQLUpdate xmlns="AXLAPI.wsdl">
<sql xsi:type="xsd:string">insert into devicenumplanmap (fkdevice,fknumplan,numplanindex,label,labelascii,display,displayascii,e164mask,logmissedcalls) values ("e31bbc7d-10ae-90ab-948a-cc89842cee39","ec6d081e-31d9-968a-97c9-9d0f149393bf","2","123456","123456","test entry","test entry","2025551212","t")</sql></executeSQLUpdate>
</soap:Body>
</soap:Envelope>

Error:
Missing key in referenced table for referential constraint (informix.fk_devicenumplanmap_fknumplan)

If I copy/paste the SQL message into the CUCM CLI (adding run sql, of course) it works.

Can anyone spot what I might be missing on this one? Maybe an API version thing?

Thanks!

Marty

Subject: RE: executeSQLUpdate - devicenumplanmap
Replied by: Manas Varma on 15-03-2012 09:55:14 AM
Hi Martin/Chris,

The update phone does have that kind of behaviour, what is needed is that you save the current line config into an object array, add a new line into the object array and then push that array to the updatePhoneReqLines object . Please feel free to ask if you have any queries.

Thanks
Manas Varma

Subject: RE: executeSQLUpdate - devicenumplanmap
Replied by: Christopher O'Neil on 15-03-2012 07:31:27 PM
Hi Guys,

I thought I would post back with a solution to this.

Martin, the issue we were having (in my case) was do to the manually formed SOAP packet. WHen making an insert statement like this all the values are case sensitive. I was trying to enter key values ("AA3BDSB...") when my database held the values ("aa3bdsb").

This is what the error is referring to - it cannot actually find the foreign key that you are specifying. It is looking for a PKID in the numplan database of "ec6d081e-31d9-968a-97c9-9d0f149393bf" but cannot find it for some reason. Try changing your cases to upper?

Alternatively, here is the proper way to implement updatePhone to add a line.

1public string createLine(string sepIn, string extIn, string label, string mask, string index)
2       {
3
4          StringBuilder s = new StringBuilder();
5          GetPhoneReq deviceReq = new GetPhoneReq();
6          deviceReq.ItemElementName = ItemChoiceType8.phoneName;
7          deviceReq.Item = sepIn;
8          GetPhoneRes deviceRes = new GetPhoneRes();
9          deviceRes = axlapiService.getPhone(deviceReq);
10
11          XLine newLine = new XLine();
12
13          XNPDirectoryNumber existingNumber = new XNPDirectoryNumber();
14          existingNumber.pattern = extIn;
15          newLine.Item = existingNumber;
16
17          newLine.asciiLabel = label;
18          newLine.display = label;
19          newLine.displayASCII = label;
20          newLine.e164Mask = mask;
21          newLine.index = index;
22
23          object[] newLines = new object[[email protected]() + 1];
24          int i = 0;
25          foreach (XLine line in [email protected])
26          {
27             newLines.SetValue(line, i);
28             i++;
29          }
30          newLines.SetValue(newLine, i);
31
32
33          UpdatePhoneReq updateReq = new UpdatePhoneReq();
34          UpdatePhoneReqLines reqLines = new UpdatePhoneReqLines();
35          reqLines.Items = newLines;
36          updateReq.lines = reqLines;
37          updateReq.ItemElementName = ItemChoiceType7.name;
38          updateReq.Item = sepIn;
39          StandardResponse response = axlapiService.updatePhone(updateReq);
40          return response.ToString();
41       }