Best Practices with use of ExecuteSQLQueryReq

[ehrenko]

Had a question concerning the use of SQL query if an existing API call will give you the desired information.

Specific example would be to find a phone in a cluster.

One could use getPhone with desired device name but this would result in an exception if no phone was found which you could handle accordingly.

One could use a sql select statement sent via the ExecuteSQLQueryReq\Res to find if the phone existed which would result in 0 rows returned if no phone found.

What I am trying to find clear direction or documentation on is which is the preferred method? Is one method worse that the other?  Is there a technical reason why one is better?

I thought I had read at some point that the use of SQL queries should be limited and to use an existing API call if one exists that will produce the same desired result\information.

Thank You

[dstaudt]

The main consideration is in regards to backwards compatibility.

'Thick' AXL requests (such as getPhone), are tested and supported to work across multiple versions (minimum two major versions).  'Thin' AXL (using AXL to submit executeSqlQuery/executeSqlUpdate requests) depends on the underlying UCM database schema tables and fields, which are NOT tested/supported for backward compatibility.  So, theoretically, an application that uses only thick AXL requests should have better backward compatibility over time.  Thick AXL also provides more validation, data-integrity checking, and error handling, so operations will tend to be safer.

In reality, the basic table/field structure of UCM has remained quite stable over time, with the vast majority of changes being added tables/fields rather than removals, so backward compatibility is not bad.  Thin AXL via SQL can also be highly efficient for certain specific queries or operations that aren't well-modeled by the thick AXL requests.

[ehrenko]

Anyone else have an opinion on this topic?  Its sparked a bit of a debate in house.

Technically you could do everything with SQL statements so why use the thick AXL requests at all?

[dhook]

Apart from dstaudt's exceptional answer I'd say that data binding in your client is a quite good reason to go with thick AXL. Just consume the wsdl or the xsd and you can build nice objects that map directly to the thich AXL requests and responses.

We mainly use thick AXL - it gives us code that is maintainable and easy to read. A SQL query that joins several tables tends to be hard to read, and it also demands more knowledge about the underlying database structure - which, as dstaudt says, might change over time. Rewriting SQL is generally more tedious than adding/changing a few fields in the thick AXL request.

We do use quite a lot of SQL as well, but only when we find a considerable performance increase in doing so. Remember, back in the days the back-end database in CallManager was MS SQL which was changed to Informix when it was changed to an appliance. Who knows how long Cisco and IBM is going to be friends...? Given the work that Cisco has put in to creating the thick AXL request the need for direct SQL is less and less required. Not saying that it's wrong but in my opinion it should be kept at a minimum.

All the best!

Dan