topic Null SQL Statement error from executeSQLQuery in Management

Null SQL Statement error from executeSQLQuery

lborojimll — Mon, 05 Dec 2016 17:12:23 GMT

We've just upgraded our CUCM to 11.5.1.SU1 and I've been asked by the telecoms manager to update some AXL scripts that I wrote some years ago. These have been working fine under the previous 8.6 version of CUCM, but now fail. Lots of them use executeSQLQuery AXL calls, and these now seem to be failing. For example:

<?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><executeSQLQuery xmlns="http://www.cisco.com/AXL/API/11.5"><sql xsi:type="xsd:string">select NumPlan.pkid, NumPlan.dnorpattern, NumPlan.description from NumPlan where NumPlan.tkPatternUsage = 7</sql></executeSQLQuery></soap:Body></soap:Envelope>

now dies with the error:

<?xml version='1.0' encoding='utf-8'?><soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><soapenv:Fault><faultcode>soapenv:Client</faultcode><faultstring>Null SQL statement</faultstring><detail><axlError><axlcode>-79726</axlcode><axlmessage>Null SQL statement</axlmessage><request>executeSQLQuery</request></axlError></detail></soapenv:Fault></soapenv:Body></soapenv:Envelope>

As you can see, there's definitely a "sql" element inside the executeSQLQuery, with the correct xsi:type. So why does the server think there isn't? The error code -79726 looks to be coming from the Informix database (it has the same error message text as well according to an IBM site), so looks like something is amiss between the AXL request and the database that's causing the SQL to go missing.

Any ideas?

Re: Null SQL Statement error from executeSQLQuery

dstaudt — Mon, 05 Dec 2016 17:59:15 GMT

It looks like the namespace declaration for the AXL namespace is a bit off...perhaps the XML handlers are more picky about this in later CUCM versions. Two forms which worked for me:

As generated by soapUI:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/11.5">
  <soapenv:Header/>
  <soapenv:Body>
      <ns:executeSQLQuery sequence="?">
        <sql>select NumPlan.pkid, NumPlan.dnorpattern, NumPlan.description from NumPlan where NumPlan.tkPatternUsage = 7</sql>
      </ns:executeSQLQuery>
  </soapenv:Body>
</soapenv:Envelope>

Via manually tweaking your example:

<?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>
    <ns:executeSQLQuery xmlns:ns="http://www.cisco.com/AXL/API/11.5">
      <sql xsi:type="xsd:string">select NumPlan.pkid, NumPlan.dnorpattern, NumPlan.description from NumPlan where NumPlan.tkPatternUsage = 7</sql>
    </ns:executeSQLQuery>
  </soap:Body>
</soap:Envelope>

Re: Null SQL Statement error from executeSQLQuery

lborojimll — Tue, 06 Dec 2016 09:54:46 GMT

Thanks - that's really helpful. Looks like I'll need to hit SOAP::Lite a few times to get it to match up with what SOAPUI is generating.