So wise so young

http://shakespeare.mit.edu/richardiii/full.html

GLOUCESTER
[Aside] So wise so young, they say, do never live long.

My next Eclipse plugin, DB2 and PL/SQL

Introduction
I created my next Eclipse plugin. Full source code is available here. This time a little bit more complicated one. It allows extracting and working with PL/SQL packages (specification and body) and deploy them again. It is not a mistake - PL/SQL (SQL Oracle dialect) packages can be created and run against DB2 by virtue of Oracle Compatible Mode in DB2.
Of course - it is rather a lightweight version.
My purpose was also to practice some additional features of Eclipse plugin I have not used so far: project nature, new project wizard,  project properties, and integration with Data Tools Platform component.
Project nature
Eclipse project nature  does not mean anything special. It allows to distinguish one project from another and to define some actions or properties specific to some project. For instance : "Extract PL/SQL objects" actions (look below) makes sense for a project with nature "Database Object" only but does not make any sense for any other project.
Defining project nature is very simple. Just add an entry in plugin.xml file.

<extension
         id="databaseobject.category.projectNatureId"
         point="org.eclipse.core.resources.natures">
      <runtime>
         <run
               class="databaseobject.nature.ProjectNature">
         </run>
      </runtime>
</extension>

and supporting class (with empty content in this case).
New project wizard
Having project nature defined I created New Wizard Project action. It creates Database Objects project and applied project nature to it.

 <extension
         id="databaseobject.category.wizards"
         name="Custom Wizard"
         point="org.eclipse.ui.newWizards">
         <category
            id="databaseobject.category.wizards"
            name="DatabaseObject">
      </category>
        <wizard
            category="databaseobject.category.wizards"
            class="databaseobject.wizards.NewProjectWizard"
            id="databseobject.wizard.new.custom"
            name="Database Objects project">
      </wizard>
      </extension>

Wizard class - available here.

Project properties
Next step is to add some specific properties to the project (identified by project nature).

<extension
      point="org.eclipse.ui.propertyPages">
   <page
         class="databaseobject.properties.DatabaseObjectPropertyPage"
         id="databaseobject.properties.DatabaseObjectPropertyPage"
         name="Database Objects properties">
  <filter name="nature"
     value="databaseobject.category.projectNatureId"/>
   </page>
</extension>

By means of project nature this property will be visible only for this type of project and absent in all other projects.
Supporting class is  defined here. This property is very simple and allows assign DB2 database connection (look above) to the project.

Integration with Eclipse Data Tools Platform
Every Java developer knows very well how to set up JDBC connection to any database (jar files, driver, URL etc). But Eclipse plugin developer does not need reinvent this wheel again, it is done already. The only effort is to make usage of goodies provided by Data Tools Platform.

Unfortunately - integration with Data Tools Platform is not easy task and requires tedious browsing through doc and sample.  But after being connected we can run SQL statements by using standard JDBC methods and tools.
Several useful pieces of code can be found here.
Extract existing PL/SQL packages from DB2 database
First step to work with PL/SQL packages is to extract them from DB2 database into our Database Object project.
Enumerating and retrieving PL/SQL packages is performed by several DB2 specific SQL statements.
Source code

     /** Statement enumerating all PL/SQL packages. */
        private static final String SELECTPACKAGESTATEMEN = "SELECT MODULESCHEMA, MODULENAME FROM SYSCAT.MODULES WHERE MODULETYPE = 'P' ORDER BY MODULESCHEMA"; //$NON-NLS-1$
        /** Statement retrieving package specification. */
        private static final String SELECTPACKAGESPEC = "SELECT TEXT FROM DBA_SOURCE WHERE SCHEMA = ? AND TYPE = 'PACKAGE' AND NAME = ?"; //$NON-NLS-1$
        /** Statement retrieving package body. */
        private static final String SELECTPACKAGEBODY = "SELECT TEXT FROM DBA_SOURCE WHERE SCHEMA = ? AND TYPE = 'PACKAGE BODY' AND NAME = ?"; //$NON-NLS-1$

To display and select which packages are to be extracted I reused Feature Tree created in my previous Eclipse project.

After double clicking on any PL/SQL package name the content of this package is displayed.

Project with some PL/SQL packages extracted
After extracting required packages our project contains package specification and body as a plain text file and by using Eclipse text editor it is possible to modify and develop them.

Deploying PL/SQL packages into DB2 database
Of course - also opposite operation is possible. After modifying package content we can put it again into DB2.

Deploying package into DB2 is done by standard JDBC Statement.execute method. Source code :

// get jdbc connection
                        Connection jdbc = PluginUtil.getConnection(iP);
                        // read and prepare script as string
                        String fileName = PluginUtil.getFileName(sel);
                        FileReader r = new FileReader(new File(fileName));
                        String sql = "";
                        int ch;
                        while ((ch = r.read()) != -1) {
                                sql += (char) ch;
                        }

                        // run sql statement (deploy PL/SQL package here)
                        Statement stmt = jdbc.createStatement();
                        stmt.execute(sql);

Nothing specific is necessary to accomplish it.
Conclusion, future.
Of course, this plugin is rather a skeleton and is unlikely to be used in any production environment. But can be used as a starting point for future development.

• Add "New package" action. Creates empty package specification and body
• Deploy action also available from editor
• Deploy group of packages at the same time
• "Run" action. Allows defining and executing SQL statement which calls some package method for testing purpose.
• Better integration with Data Tools Platform, use SQL editor available there. Unfortunately Data Tools Platform package is not enabled for PL/SQL packages in DB2 and it is not possible to deploy them.
• Make usage of "SQL Results" view provided with Data Tools Platform for keeping tracks of all database activities (extracting and deploying).

IBM Data Studio

One can ask if it makes any sense to develop this skeleton if IBM Data Sudio is available for free and contains all that functionality and much more. IBM Data Studio is a very good choice but I found some problems there.

• It is not possible to deploy package specification only (without package body).
• Package content is persisted as XML document and there is no easy method to extract it into plain text file (only by copy and paste).
• Because of that also Eclipse "Compare" text does not work as expected (compares XML files).

But on the other hand IBM Data Studio is still evolving tool (version 3.1 was released on November 2011) and may be the next version will contain a solution to the problems described above.