Monday, August 11, 2014

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.
PROCEDURE v_testExc (IN x INTEGER, OUT result VARCHAR(32768))
     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';
             WHEN illegal_arg_ex THEN
             SET result = 'FAILURE - x value cannot be less than 0';
                SET result = CURRENT_EXCEPTION.MESSAGE;
                SET result = CURRENT_EXCEPTION.NAME;
             END IF;
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’