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.