This document describes how to, in limited circumstances, force the Cisco Information Server (CIS) Structured Query Language (SQL) parser to accept and push certain SQL functions to a datasource that supports it natively.
There are no specific requirements for this document.
The information in this document is based on the CIS Versions v6.1 and v6.2.
The information in this document was created from the devices in a specific lab environment. All of the devices used in this document started with a cleared (default) configuration. If your network is live, make sure that you understand the potential impact of any command.
Sometimes in a CIS procedure or view, you might want to use SQL syntax that is supported by a third-party datasource vendor but is not supported by the CIS SQL script language.
Generally, you are restricted to the CIS SQL support when you submit SQL statements against a datasource. However, in cases where the CIS does support an SQL function that matches your need but is simply represented by the datasource vendor with a different keyword, such as SUBSTR versus SUBSTRING, attempts can be made to emulate the support.
Expand the SQL Syntax in CIS
There are two steps that must be completed in order to expand the SQL syntax in the CIS, which are described in the next sections.
Create a New Custom Function
In order to emulate the supportability of a new keyword, you must create a new custom function in Composite that allows you to interpret the new keyword in your query during parsing. For SUBSTR, this is completed with the creation of a stored procedure in the SQL Script that takes zero or more scalar parameters as inputs, and has exactly one scalar output parameter.
After the procedure is created, navigate to the Administration > Custom Functions menu and enable the procedure as a custom function. Here is an example:
PROCEDURE SUBSTR(IN str VARCHAR, IN offset INTEGER, IN len INTEGER, OUT newstr VARCHAR)
SET newstr = SUBSTRING(str, offset, len);END
Modify the Capabilities
In order to modify the capabilities, navigate to the appropriate (latest revision) datasource capabilities file and add a mapping for that source. Here is an example for the new SUBSTR function:
SUBSTR(~string,~whole_number): SUBSTR($1, $2)
SUBSTR(~string,~whole_number,~whole_number): SUBSTR($1, $2, $3)
Restart the server in order to reload the modified capabilities file. Your SQL references to SUBSTR should now be accepted.