topic Some basic AXL sql questions in Management

Some basic AXL sql questions

Keith Abbott — Thu, 26 Dec 2019 19:33:56 GMT

Good Day,

I am a complete beginner with AXL, Java, and Eclipse IDE.

I have been trying to begin learning in these areas by reviewing what information I can find online, but am struggling!

Ive been able to do some basic stuff like getphone to pull phone device information, and set a phone device description ( as a basic step) using UpdatePhoneReq. But I just havent had any luck trying to run SQL queries via axl.

There seems to be a couple different methods for accomplishing an SQL query.

One appears to be by simply defining the query in a string, then using 'ExecuteSQLQueryReq' in a manner similiar to the code below:

String sSQLQuery = "SELECT * FROM numplan"; //V2
ExecuteSQLQueryReq axlParams = new ExecuteSQLQueryReq();
axlParams.setSql(sSQLQuery);

try{
 ExecuteSQLQueryRes getSQLResponse = axlPort.executeSQLQuery(axlParams);
 List<Object> returnList = new ArrayList<Object>(); //added to orig
 returnList = getSQLResponse.getReturn().getRow();

 for (Object object : returnList) {
  System.out.println(object.toString() + "\n");
 }

 } catch (AXLError e ) {
  System.out.println("An AXLError occurred:" + e.getFaultInfo());
 }
}

I can get this method to run, but no matter how I try to enumerate what should be the returned values, I just get Row: null; entries in the output (it does contain the correct number of rows tho...) (I should also mention that when I pull the axl logs from call manager(10.5 btw), the query looks properly formed and no errors are shown. I dont see any response information in the log but I dont know that I am supposed to)

The other method appears to be by constructing the soap structures manually like this:

	<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/8.5">
	   <soapenv:Header/>
	   <soapenv:Body>
	      <ns:executeSQLQuery sequence="1">
	         <sql>select * from processnode</sql>
	      </ns:executeSQLQuery>
	   </soapenv:Body>
	</soapenv:Envelope>

For that style, I was never able to get past compiler muster: I couldnt get it to like the soap tag structures.

So my questions are:

Why are there 2 different methods, when it seams like the ExecuteSQLQueryRes method would be more straight-forward?

Is the direct SOAP method supported via Java?

Why cant I see anything but nulls using the ExecuteSQLQueryRes method - even tho I have gone to command line on the pub, executed the same simple query, and see data populated?

Is there a preferred method between these 2? if so, why?

Should I be trying to do this via Java? I chose that language because I found a few demos and videos that used Java and I wanted to try to keep the already steep learning curve down as much as possible. If that answer is dependent upon the application, my immediate end goal is to create a form-driven api that would allow service-desk staff do basic phone builds in a simplified 'supercopy' type fashion, while limiting their access to just that function.

Are there any good, basic, resources that could help me slog through the basics? I done a lot of search-engine searches and I haven't been able to find much that steps me through a lot of this in a helpful fashion. Many of my directed searches wind up at dead links (a lot of jiveon's). Others are too far from applicability, or I cant get them to run at all. Part of the problem is probably because I dont have the luxury, time-wise, of first knowing, or learning java, then eclipse, then axl, so I can focus on 1 thing at a time - I have to dive in with a solution-based approach and learn as I go.

I'd appreciate your thoughts and any direction you can provide!

PS, something else I noticed is that there seems to be 2 methods of attempting to display returned information: just iterating through - as I tried in the first example listed - and one that uses getElementsByTagName like below:

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
(some other code to connect and perform query)
Element returnValue = Element)returnList.get(1);//
	     Field test = Doc.class.getField(phoneName);
	      Object dNumber = returnValue.getElementsByTagName("phoneName").item(0).getFirstChild().getTextContent();

but I can't get it to recognize getElementsByTagName, and I cant find any clear help on doing so. Can that method be used with Java? Is there a basic tutorial somewhere on setting it up and getting it working?

thx again

Re: Some basic AXL sql questions

Keith Abbott — Fri, 27 Dec 2019 21:13:02 GMT

Hi All,

For a little further enlightenment, I downloaded SoapUI and from there was able to use 'executeSQLQuery' to run exactly the same query I have been attempting in my java code - So the problem that started all of the questions doesnt relate to:

The server Im running against - used the same one

the credentials - same

the sql query - same

and RTMT AXL log shows exactly what it did on Java attempts.

So AXL calls appear they should work, and shouldnt require manually building SOAP requests

and there must be a problem with the way I am capturing and iterating he return data.

But the basic questions still remain:

Can Java use the manually constructed SOAP SQL requests?

Is there an advantage to using those over 'ExecuteSQLQuery'?

Is Java a good choice over, say C++ or PHP - especially when looking for ease-of-learning?

Add to that the more specific questions regarding the problem with my code. I am capturing the return (at least I think I am) in getSQLResponse:

ExecuteSQLQueryRes getSQLResponse = axlPort.executeSQLQuery(axlParams);

then I think Im dumping each row into array returnList:

List<Object> returnList = new ArrayList<Object>();
returnList = getSQLResponse.getReturn().getRow();

Then I think Im iterating through that, converting it to string, and dumping to console with:

for (Object object : returnList) {
     System.out.println(object.toString() + "\n");
}

though Ive also tried:

for (int i = 0; i < returnList.size(); i++) {
	System.out.println(returnList.get(i));
}

Im sure making some basic mistake - does anyone see what it is?

thanks

Re: Some basic AXL sql questions

thedd — Mon, 06 Jan 2020 08:33:28 GMT

I don't know it you read the documentation and know how AXL is working.

If you call ExecuteSQLQueryReq there will be a SOAP Requst processed by the AXL Api.

If you create a SOAP yourself it is the same.

As Java can handle SOAP Requests and Responses it should work fine.

Re: Some basic AXL sql questions

thedd — Mon, 06 Jan 2020 08:39:14 GMT

Regardind your qestion:
Is Java a good choice over, say C++ or PHP - especially when looking for ease-of-learning?

Well that depends on your coding experience.

Did you ever use Java, C++, C#, php, perl, python?

Are you familiar with any of these languages?

If not you could nearly start with any of these.

In my opinion coding with eclipse is harder and more complex then other IDEs.

As far as i see not AXL is your main problem it it SOAP Handling within the chosen language.

If you want to test you SQL you can just do it on the CUCM CLI by using the command run sql and your query.

Then you will see if there is anything returned.

Re: Some basic AXL sql questions

Keith Abbott — Mon, 06 Jan 2020 13:30:52 GMT

Thanks Thedd, I do not have experience in any of those languages - mostly VB and some older languages. Because of the difficulty in finding help with the Java/AXL combo, Ive just switched to Python: I found someone familiar with that pairing, that can provide some assistance. I started with NetBeans IDE, and Eclipse seems easier than does NetBeans - tho it may just be because started gaining some familiarity with IDEs. I did test the SQL query with pub command line, and that is fine. And the AXL log shows that its being delivered to the AXL service correctly - I think the SQL is being correctly executed - I think the issue is in the way Im trying to grab and process the response. thanks

Re: Some basic AXL sql questions

dstaudt — Mon, 06 Jan 2020 15:39:20 GMT

There are a couple of sample AXL+Java apps in this repo that might be helpful: https://developer.cisco.com/codeexchange/github/repo/CiscoDevNet/axl-java-samples

In DevNet we tend to use AXL+Python with Zeep for building samples and demos - if you don't have a strong language preference, you might lean that way..

Re: Some basic AXL sql questions

thedd — Thu, 06 Feb 2020 11:07:55 GMT

did you solve your issue?

Re: Some basic AXL sql questions

Keith Abbott — Thu, 06 Feb 2020 13:38:55 GMT

Hi Thedd, I have not yet resolved my issue - unfortunately other demands have kept me away from the project but I hope to resume soon - I have switched to python and zeep, as was suggested by dstaut and a good guy from our vendor staff. It seems to be a little easier to work with than was Java. But I dont know how it will work out overall yet. I just kept hitting roadblocks with the java returns. Hopefully I can start back on the project tonight or tomorrow - at which juncture Im sure you'll hear me whining! thx