AXL-DB API Problem in Visual Studio 2005

[jness]

I'm trying to get a list of all phones per the Cisco Documentation and have the web reference to CallManager 5.1(2)-1000 setup and can successfully query the RisPort service and get a SelectCmDevice query to run and return data, but we have over 200 phones so first I have to use ExecuteCCMSQLStatement to get a full list of all the phones. I have used SOAPMonitor on the CallManager server and can see that the query from the documentation that I am using (Select name from device where tkclass = 1) is running and returning data, but when I run my program from Visual Studio it gets an error trying to read the response (I'm not doing anything with the response for debugging purposes and it is still hitting this exception):

System.InvalidOperationException was unhandled by user code

Message="There is an error in XML document (1, 608)."

Source="System.Xml"

StackTrace:

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle)

at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)

at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

at CM5RIS.cm1.RISService.ExecuteCCMSQLStatement(String ExecuteSQLInputData, ColumnType[] GetColumns) in C:\VS2K5Prj\CM5RIS\CM5RIS\Web References\cm1\Reference.vb:line 184

at CM5RIS._Default.Page_Load(Object sender, EventArgs e) in C:\VS2K5Prj\CM5RIS\CM5RIS\Default.aspx.vb:line 29

at System.Web.UI.Control.OnLoad(EventArgs e)

at System.Web.UI.Control.LoadRecursive()

at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

The inner exception it is hitting is this:

{"Cannot assign object of type System.Xml.XmlNode[] to an object of type System.String."}

That position in the XML document is between the </Name> and <Value> separators of the first <item> in the array that is returned.

Any help would be greatly appreciated.

[srg]

I ran into this same error with CM 6.0. After digging around the files that were generated by adding the web reference in Visual Studio, I found that the Name and Value properties of the ColumnValueType objects were strings. I used the SOAP monitor and noticed that in the SOAP response those two fields were coming back as ColumnNType and ColumnVType.

Since there was no definition for those two types in the generated code files, they were coming in as XMLNode objects. This is not the best solution, but I ended up changing the Name and Value properties on the ColumnValueType class to objects rather than strings. Now when the data comes back those two fields are cast from object to XMLNodes and I can get at the data.

The obvious problem with this approach is that if I update my service reference, I have to get back into the reference.cs file and make my edits again.

[jness]

Can you post the changes you made to the reference file and the portion of handling the data you are getting out from this?

[srg · Accepted solution]

I haven't quite finished up the code for processing the response yet, but here is what I changed in the Reference.cs file:

public partial class ColumnValueType{

private object nameField;

private object valueField;

public object Name{

get {return this.nameField;}

set {return this.valueField;}

}

public object Value{

get {return this.valueField;

set {return this.nameField;

}

}

Those fields and properties had previously been strings. I should have the processing code done this afternoon and will post it then.

[jness]

I made those changes (except my reference file is in VB) and for both Name and Value all I seem to get is "System.Xml.XmlNode[]" (although the array seems to have the correct number of objects and I don't get the Xml Serializer error I was getting).

[srg]

This isn't going to work as well as I had thought.

That is correct that the Name and Value fields come back as XmlNode arrays. Each XmlNode array contains two XmlNode objects (or one if you received a null value from the db). One object contains the atrributes and one contains the value. I had planned on going into the ColumnValueType Value field and pulling the text of the 2nd XmlNode object.

Now here is the problem:

For every column you select you get a ColumnValueType object. Rather than being contained in some type of "Row" parent object, each column sits in the root of your results. For example, in the query I am using I am asking for the AlertingName and DnOrPattern columns of the NumPlan table. My results come back like this (simplified):

AlertingName

John Doe

DnOrPattern

1234

...

With the data in this format, I can't just do a foreach loop and iterate over the ColumnValueType objects.

[jness]

Thanks to your assistance with the type of object I was able to get the data out. Here is the code I'm using (Visual Basic) to just get one field (name):

Dim cColType As New cm1.ColumnType

Dim strGetColumns(0) As cm1.ColumnType

cColType.Name = "name"

strGetColumns(0) = cColType

Try

' NET client is buggy, just run it a second time

oRIS.ExecuteCCMSQLStatement("Select name from device where tkclass = 1", strGetColumns)

Catch ex As Exception

oResult = oRIS.ExecuteCCMSQLStatement("Select * from device where tkclass = 1", strGetColumns)

Dim i

For i = 0 To UBound(oResult)

Dim oXmlNode() As System.Xml.XmlNode

oXmlNode = oResult(i).Value()

Response.Write (oXmlNode(1).InnerText)

Next

End Try

[srg]

This feels like a bit of a hack that could probably be done better, but I figured out a way to get the data out when you have multiple columns being returned:

int numRows = exSqlResult.Length

for (int i = 0; i < numRows; i++)

{

if(((XmlNode[])result[i].Value).Length == 2)

{

string alName = ((XmlNode[])result[i].Value)[1].Value;

i++;

string dn = ((XmlNode[])result[i].Value)[1].Value;

i++;

string department = ((XmlNode[])result[i].Value)[1].Value;

retVal.Add(new DirectoryNumber(alName,dn,department);

}

else

{

i++;

i++;

}

}

The ultimate return value for this particular method is a list of DirectoryNumber objects. Also, the if statement in the beginning of the for loop makes sure only those directory numbers that belong to a call pickup group are returned.