Publishing Resources as Web-Services in CIS

CIS Publishing: Publishing is the only one way to expose the CIS Developed resources to external clients. There are basically two ways to publish resources
1. Databases-- External Clients access them as JDBC, ODBC and also as ODATA
2. Web-Services-- External Clients access them as REST or WSDL URL's
For the Web-Services they can be exposed as XML as well as JSON.

In this article lets see how to publish resources as a web-service.

After you have installed Cisco Information Server, there is an examples folder in this following PATH: /shared/examples
This folder contains pre-created resources.

There is a SQL Script in the examples folder "LookupProduct":
PATH: /shared/examples/LookupProduct
This SQL Script accepts öne input INTEGER parameter "desiredProduct" and returns ProductName, ProductID and ProductDescription for the matching record in the products table.

Lets publish this as a web-service.

1. Right-Click on the SQL Script "LookupProduct" and Select Publish.

2.  After clicking Publish a new window will pop-up.

3. Select the Web Services Folder and Select Add Composite Web Service

4. Provide a name for the Composite Web Service and Click OK

5. Provide a name for the Published Web-Service and click OK.

6. Now you can see that the folder has been created and the procedure has been published.

This procedure has been published and is now available for access through external clients.

7. Open the created Web-Service and in the SOAP tab there are 4 WSDL URL's and in the REST Tab there are 4 End-Point URL's for end user consumption.

WSDL URL's:

REST URL's:

Since the default Security Policy is /policy/security/system/Http-Basic-Authentication.xml
the http links will work so if you change the security policy to
/policy/security/system/Https-Basic-Authentication.xml then the https links will work.

The next step is to test the published web-services through SOAPUI. Shall post about testing published resources in future.

Connecting to CIS Published Resources through DBVisualizer

In this article lets see how to connect to CIS published resources through JDBC Tool DBVisualizer.

1. Download the latest version of DBVisualizer from here. Download the version according to your operating system and if you have Java installed on your machine download the file which says without Java VM otherwise download the file with Java VM.

2. After you download the installation is straightforward so I am not going to post the steps to Install.

3. Post installation, open DBVisualizer and in the Tools Menu Select Driver Manager

4. It will open up the Driver Manager window.

5. Click Create a new driver as shown in the picture above and enter the details for
Name: give any meaningful name like CIS62 or CIS

6. Click on the Folder icon to browse and open the csjdbc.jar file.

The csjdbc.jar file is located on the machine where you installed the CIS Server.
Location: C:\Program Files\Composite Software\CIS 6.2.0\apps\jdbc\lib if you installed on a windows machine. If you do not have access to the file contact your system administrator.

7. After you configure the driver you are ready to make connections to the CIS Server published resources.

8. Right click on connections and select Create a New Connection.

9. Click the Use Wizard.

10. In the New Connection Wizard give a name to your connection.

11. In the Select Database Driver Screen Select the previously configured CIS Driver.

12. After you select the appropriate driver click Next and it will take you to the screen below.

In the Database URL Field enter the URL:
URL FORMAT: jdbc:compositesw:dbapi@<hostname>:<jdbc_port>?domain=<domain_ name>&dataSource=<datasource_name>

Sample URL: jdbc:compositesw:dbapi@localhost:9401?domain=composite&dataSource=CompositeTraining

Enter the Userid and Password and Hit Finish.

13. Now you can connect, view and query the published resources in the Data Source you have specified in the Database URL.

Handling Exceptions (Run Time Errors) in SQL Scripts

This article would demonstrate how to handle Exceptions in CIS.

There are four attributes of an exception in the Exception handler.
1. CURRENT_EXCEPTION.NAME – VARCHAR(255) – Exception’s Name
2. CURRENT_EXCEPTION.MESSAGE – VARCHAR(255) – Value Defined for the current exception – Can be NULL
3. CURRENT_EXCEPTION.ID – INTEGER – An Integer that is the exception ID
4. CURRENT_EXCEPTION.TRACE – VARCHAR(32768) – The Exception stack trace (Detailed Error Message)

Additionally Exceptions can be declared to be handled by the SQL Script itself.

Eg:

PROCEDURE v_testExc (IN x INTEGER, OUT result VARCHAR(32768))
     BEGIN
     DECLARE illegal_arg_ex EXCEPTION; --declaring an exception
             IF x < 0 THEN
                RAISE illegal_arg_ex; --raising an exception when x is less than 0
             END IF;
             CALL PRINT(CAST(x/0 AS VARCHAR)); --Raise a run time error
             SET result = 'SUCCESS';
     EXCEPTION
             WHEN illegal_arg_ex THEN
             SET result = 'FAILURE - x value cannot be less than 0';
     ELSE
             IF CURRENT_EXCEPTION.MESSAGE IS NOT NULL THEN
                SET result = CURRENT_EXCEPTION.MESSAGE;
             ELSE
                SET result = CURRENT_EXCEPTION.NAME;
             END IF;
END

In the above script we are declaring an exception illegal_arg_ex and raising it only when x value is less than 0. We are also raising a run time error by dividing the value of x by 0 which is not allowed.

In the Exception block we set the value of result as ‘FAILURE – x value cannot be less than 0′ when the illegal_arg_ex is raised also we capture all the other exceptions that can be raised during exception of the SQL Script by using the ELSE clause in the EXCEPTION block.

We set the value of result to CURRENT_EXCEPTION.MESSAGE if it is NOT NULL or CURRENT_EXCEPTION.NAME if the CURRENT_EXCEPTION.MESSAGE is NULL. Hence we capture all the exceptions in the SQL Script.

If you run the SQL SCRIPT with a value of a negative number
result = ‘FAILURE – x value cannot be less than 0′

If you enter any positive number
result = ‘A system exception has occurred. Unable to compute divide function for ’1 / 0′ since the denominator cannot be 0 [func-3956000]‘

If you comment out the code CALL PRINT(CAST(x/0 AS VARCHAR)); you will be able to execute the SQL SCRIPT without any exceptions and the value of result = ‘SUCCESS’

Connecting to CIS Published Resources through SQuirreL Client

CISCO INFORMATION SERVER provides many ways to access the published resources. Lets see how to query the published resources through the SQuirreL Client using the JDBC Drivers provided by CIS.

1. Download the SQuirreL Client latest snapshot from here. Make sure your computer is running Java. (1.6 and above version)

2. Open the cmd window and Run as Administrator

3. In cmd prompt browse to the file system directory containing the downloaded SQuirreL Client jar file
Eg:  D:\>cd Users\RAJ\Desktop

4. Execute the downloaded jar file using the following command:
java -jar squirrel-sql-<version>-install.jar
Eg: D:\Users\RAJ\Desktop>java -jar squirrel-sql-snapshot-20140429_1211-standard.jar

5. It will open up the Installation of SQuirreL SQL Client.

6. Follow the instructions and install the SQuirreL SQL Client.

7. Also while installation you can select the option to create shortcuts in the All Programs Folder.

8. Click Start Menu->All Programs->SQuirreL SQL Client->SQuirreL SQL Client

9. Obtain the CIS jdbc drivers (csjdbc.jar) from the following location:
<CIS HOME DIRECTORY>\apps\jdbc\lib
Eg: D:\Program Files\Composite Software\CIS 6.2.0\apps\jdbc\lib

10. Now we have to configure the SQuirreL SQL Client with the CIS jdbc drivers

11. Click on Create a New Driver Tab in the left corner as shown below:

12. After you click Create a New Driver a new Tab (Add Driver) will open up:
Enter the following information: You can edit it as required:
Name: CIS_DRIVER
Example URL: jdbc:compositesw:dbapi@:localhost:9401?domain=composite&dataSource=examples
Click the Extra Class path Tab:
and Hit Add:
When you hit Add a new pop-up will show up where you will have to point to the CIS JDBC drivers.
Click on List Drivers to pull the Class Name:
Click OK.

13. Now your CIS Drivers are ready and you can create Connections/Aliases in the SQuirreL SQL Client  to connect to the published resources in CIS. Everytime you create a new alias make sure you change the connection URL or else you will be connecting to the same datasource everytime.

Configure CIS (CISCO INFORMATION SERVER) to send E-Mails

This article would demonstrate how to configure SMTP settings in CIS.

Before you change the E-Mail configuration settings in CIS, you will have to install an SMTP server to send E-Mails. Fortunately there are many online services to create SMTP servers for us.

Follow this LINK to register for a free SMTP server account. After you register you will receive the SMTP information in your registered E-Mail.

This is the information you will have after registering for an SMTP Service.

SMTP Server: pro.turbo-smtp.com
SMTP Authentication: Enabled
SMTP Username: your turboSMTP Username (the email address you have registered with your turboSMTP account)
SMTP Password: your turboSMTP Password (you will receive your password in your E-Mail)
SMTP Port: 25 or 587 or 2525

After you setup your SMTP Server you will have to update a few config settings in CIS to configure E-Mails.

1. LOGIN to CIS with a user having ACCESS_TOOLS, READ_ALL_CONFIG and MODIFY_ALL_CONFIG rights. 

2. Click on Configuration on the Administration Tab

3. It will open up the configuration window.

4. Browse to the following configuration properties
Composite Server -> Configuration -> E-Mail

5. Enter the following in each of the configuration parameters:

From Address: Any Valid E-Mail address
SMTP Authentication Required: True (Select True Check Box)
SMTP Authentication User Name: Enter your turboSMTP Username (the email address you have registered with your turboSMTP account)
SMTP Authentication Password:  Enter your turboSMTP Password (you will receive your password in your E-Mail)
SMTP Host Name: pro.turbo-smtp.com
SMTP Port: 587

6. Now your CIS server is configured to send E-Mails. There is a CIS in-built procedure SendEMail
PATH: /lib/util/SendEMail or /lib/resource/SendResultsInEMail which can now be used to send E-Mails from within CIS.

How to connect to a WSDL Data Source in CIS

In this article you will learn how to Add/Query a WSDL Data-Source. Follow this LINK to create a WSDL in Oracle 11g.

The newly created WSDL is:
http://localhost:8080/orawsv/TEST/GET_DESCRIPTION?wsdl

Let's see how to add a WSDL data source and get data from the REST data source.

1. LOGIN into Composite Studio with a user who has ACCESS_TOOLS Right.

2. Right-Click on a folder and from the drop-down list select 'New Data Source'

3. It opens up a pop-up window where you select the Data Source adapter. Select the WSDL adapter and click Next.

 4. After you click Next you will have to provide the following information for the new Data Source:
Data Source Name -  Provide any name you like
URL - http://localhost:8080/orawsv/TEST/GET_DESCRIPTION?wsdl
Login - test
Password - test
Hit - Create and Introspect

5. After you hit Create and Introspect a pop-up will open where you can select all the Services to Introspect
Click on GET_DESCRIPTIONservice and click Next and Introspect Task Status Report will open. Click Finish.

6. Now the web service operation is ready to be queried and can be used in Composite Views/SQL Scripts and in XSLT Transformations.

How to connect to a REST Data Source in CIS

In this article I am going to show you how to connect and query a REST Data Source. To begin with I have created a REST web service on Oracle 11g XE (Express Edition). Follow this LINK to create a REST web service in Oracle 11g.

After you have created the REST web service you would end up with REST-Style URL's like this

REST URL:
1. http://test:test@localhost:8080/xml_demo/rest-ws/emp
2. http://test:test@localhost:8080/xml_demo/rest-ws/emp/7900

The first URL consists of three parts
1.1 USERNAME:PASSWORD - test/test
1.2 BASE URL - http://localhost:8080
1.3 OPERATION URL -  xml_demo/rest-ws/emp

The second URL consists of four parts
2.1 USERNAME:PASSWORD - test/test
2.2 BASE URL - http://localhost:8080
2.3 OPERATION URL -  xml_demo/rest-ws/emp
2.4 URL_PARAMETERS - this parameter is an employee id and this value can be substituted at run time.

Let's see how to add a REST data source and get data from the REST data source.

1. LOGIN into Composite Studio with a user who has ACCESS_TOOLS Right.

2. Right-Click on a folder and from the drop-down list select 'New Data Source'

3. It opens up a pop-up window where you select the Data Source adapter. Select the REST adapter and click Next.

4. After you click Next you will have to provide the following information for the new Data Source:
Data Source Name -  Provide any name you like
Base URL -  http://localhost:8080
Login -  test
Password - test
 Click on Operations '+' tab

After you click on '+' another pop-up will appear where you enter the operation name (each Base URL can have any number of operations)

Enter the Operation URL:
For the first operation enter: xml_demo/rest-ws/emp and
For the second operation enter: xml_demo/rest-ws/emp/{emp_id} where emp_id is an input parameter

For the Second URL after you enter the operations URL hit Parse and it will update the URL Parameters tab.

Click the Create & Close tab which creates the Data Source and two operations.

 
These two operations can now be used to create views and can also be used in SQL Scripts (procedures).

COMPOSITE Modeling and Publishing

Composite Modeling and Publishing is a three step process
1. Introspect
2. Model
3. Publish

1. Introspect
Introspection is a process where you examine a Data Source and select the required resources for modeling. Composite supports a wide variety of Data Sources

• Relational Data Sources
• File Data Sources 
• WSDL/REST Data Sources 
• Application Data Sources 

2. Model
Modeling is a process where you Create, Design and Edit the introspected resources. After the resources have been modeled, performance tuning (caching and join ordering) can be done on the modeled resources.

3. Publish
Publishing is a process where you make the modeled resources available to client applications (end-users). The published resources are available through ODBC, JDBC and SOAP (Web-Services).

NOTE: Composite ODBC, JDBC drivers are installed during the composite studio/server installation.