AXL executeSQLQuery Error

Version 1
    This document was generated from CDN thread

    Created by: JOHN SHEAHAN on 02-02-2010 08:54:51 PM
    The following code directly from the Cisco online documentation is throwing an error:

    <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"><font face="Courier" size="1"><font face="Courier" size="1">

    <SOAP-ENV:Body>
    <axlapi:executeSQLQuery sequence="1"
    xmlns:axlapi="http://www.cisco.com/AXL/API/1.0"
    xmlns:axl="http://www.cisco.com/AXL/API/1.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.cisco.com/AXL/API/1.0 axlsoap.xsd">
    <sql>SELECT * from numplan</sql>
    </axlapi:executeSQLQuery>
    </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>
     
    The error is:
     
    'axl:code' => '5001',
    'axl:message' => 'Element type "axlapi:executeSQLQuery" must be followed by either attribute specifications, ">" or "/>".',
    'xmlns:axl' => 'http://www.cisco.com/AXL/API/6.1'
    Can anyone help point me in the right direction?
     
    thanks
     
    john

    </font></font><font face="Courier" size="1">
     
    </font>
     

    Subject: RE: AXL executeSQLQuery Error
    Replied by: David Staudt on 03-02-2010 02:23:47 AM
    Looks good on the surface...perhaps there is a problem with whitespace or hidden characters?  It would be a good idea to use the RTMT tool to look at the AXL logs, perhaps you may spot something.  Attach here if you need help.

    Subject: RE: AXL executeSQLQuery Error
    Replied by: JOHN SHEAHAN on 03-02-2010 02:51:55 AM
    I read somewhere that this error could be because I'm not setting the Content-Length in my packet.
    I'm using LWP and PERL to send the POST, does anyone know how to set Content-Length with LWP?
     
    Thanks
     
    John

    Subject: RE: AXL executeSQLQuery Error
    Replied by: Dan-Anders Hook on 03-02-2010 09:39:00 PM
    Hi,
     
    Not the answer to your actual question, but an alternative using SOAP::Lite... TIMTOWTDI, you know ;-) The happy face is of course a colon and a D.
     
    #!/usr/bin/perl
    use warnings;
    use strict;
    use SOAP::Lite;
    my $cucmip = "192.168.0.10";
    my $axl_port = "8443";
    my $user = "cisco";
    my $password = "cisco";
    BEGIN {
      sub SOAP::Transport::HTTP::Client::get_basic_credentials {
        return ($user => $password)
      };
    }
    my $cm = new SOAP::Lite
        encodingStyle => '',
        proxy => "https://$cucmip:$axl_port/axl/" ;
    #axl request
    my $res =  $cm->executeSQLQuery(SOAP:ata->name("sql" => "SELECT * FROM numplan"));
    unless ($res->fault) {
     for my $row ($res->valueof('//executeSQLQueryResponse/return/row')) {
      #print "$_, ${$row}{$_}\n" foreach keys %$row;
      print "${$row}{$_}," foreach keys %$row;
      print "\n";
     }
    }
    else {
            print join ', ',
            $res->faultcode,
            $res->faultstring;
    }
     
    Cheers,
     
    //Dan

    Subject: RE: AXL executeSQLQuery Error
    Replied by: JOHN SHEAHAN on 03-02-2010 09:57:09 PM
    Thanks alot Dan.
     
    I'm currently getting errors installing SOAP::Lite so I'm going to have to debug and fix this before I can continue with testing the code you provided.
     
    I really appreciate the help.
     
    John

    Subject: RE: AXL executeSQLQuery Error
    Replied by: Dan-Anders Hook on 03-02-2010 11:49:20 PM
    Hi,
     
    OK, I understand. I think SOAP::Lite is quite easy to use. I wrote a LWP-version as well, I got the same error as you at first but when I removed some of the namespace info in the XML it worked better :-) It's not the first time the samples from Cisco is broken...
     
    use LWP::UserAgent;
    use Data:umper;
    use XML::Simple;
    my $cucmip = "192.168.0.10";
    my $axl_port = "8443";
    my $user = "cisco";
    my $password = "cisco";
    my $url = "https://$cucmip:$axl_port/axl/";
    my $soap = <<AXL;
    <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
     <SOAP-ENV:Body>
      <executeSQLQuery>
       <sql>SELECT * FROM numplan</sql>
      </executeSQLQuery>
     </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>
    AXL
    # Create ua
    my $ua = LWP::UserAgent->new;
    push @{ $ua->requests_redirectable }, 'POST';
    # Create request
    my $req = HTTP::Request->new(POST => $url);
    $req->content_type('text/xml');
    $req->header('Accept' => 'text/*');
    $req->content($soap);
    $req->authorization_basic($user,$password);
    # Send request
    my $res = $ua->request($req);
    #print $res->content;
    # Check response
    if ($res->{_rc} == 200) {
     # Request successful
     $xml = new XML::Simple();
     $data = $xml->XMLin($res->content,KeyAttr => "return" );
     print Dumper($data);
    }
     
    Cheers,
     
    //Dan

    Subject: RE: AXL executeSQLQuery Error
    Replied by: JOHN SHEAHAN on 04-02-2010 12:20:43 AM
    that worked nicely Dan...thanks again.
     
    For my last question, would you mind telling me the names of the tables that I can query besides numplan?
     
    john

    Subject: RE: AXL executeSQLQuery Error
    Replied by: David Staudt on 04-02-2010 02:42:21 AM
    If you haven't seen it, the Data Dictionary documents all the tables and fields in the UCM database: http://developer.cisco.com/web/axl/docs.  All of these are accessible via <executeSQLQuery>

    Subject: RE: AXL executeSQLQuery Error
    Replied by: JOHN SHEAHAN on 04-02-2010 12:24:52 PM
    thanks David.
     
    I notice that the script you provided only returns a single row of data from the DB, is there an easy way to change it so I get all the data when I do a "select *" statement?
     
    thanks again.
     
    john

    Subject: RE: AXL executeSQLQuery Error
    Replied by: JOHN SHEAHAN on 04-02-2010 02:52:03 PM
    I figured it out...I just needed to adjust my select statement, here's an example that returns several records:
     
    <sql>SELECT firstname,lastname,userid,manager,telephonenumber FROM Enduser WHERE department = 'Finance'</sql>
     
    Thanks for all the help.
     
    John

    Subject: RE: AXL executeSQLQuery Error
    Replied by: JOHN SHEAHAN on 04-02-2010 05:39:46 PM
    Although my SELECT statements seem to work when I specify a WHERE statement, I am still concerned that I can't pull a list of, let's say "all end users" with a statement like:
     
    SELECT * from Endusers
     
    and have it return more than one record.
     
    I am trying to figure out what is stopping the query at one record and the proper code to allow it to return all records?
     
    thanks
     
    john