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
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