CookBooks

1. How To create an ODBC connection with IzPack (by Fabrice Mirabile)

a. Problem

ODBC can be used as a layer between app servers and databases. It is quite convienent to setup an ODBC connection at installation time when the installer can actually retrieve all info needed for such setup. How can we achieve that? And for which OS ?

b. Solution

After looking at many solutions, I found one that is very convenient in the sense that it applies to both Windows and UNIX environment. In fact, the Windows ODBC Manager applet offers two type of setups:

The system source
The file source

The system source writes in the Windows registry and unfortunately does something else that I couldn't figure out. However, the file source is very similar to ODBC.ini under UNIX. In ODBC.ini, you can define all connections into this file. For Windows it's a bit different as you will have as many files as connections. But even though, there's a trick!

A fileDSN (the name given to this type of connection) for a connection to an Oracle database will look like this :

[ODBC]
DRIVER=Oracle in OraHome92
UID=%{UID}
PWD=%{PWD}
DBQ=%{DBName}
SERVER=%{DBName}

Therefore by changing the UID and PWD, you can make your connection point to any schema that you want.

In my company's software, we use ODBC to make the connection between the application and the database. Therefore, we use a batch file to launch the server with a bunch of parameters. One of them is the ODBC DSN. This one, using fileDSN, should be defined as follows:

SET DSN=filedsn=$INSTALL_PATH\whateveryoulike.dsn

A very nice trick is to put the UID and the PWD in this batch file so that it's not needed in the file directly and therefore you make the installer create different batch loaders for different schemas ! That's very useful when you have many schemas in the same DB and you want the same application server to access any of them without reinstalling the whole thing !

In the following discussion, I'll show you an example of how to prepare the installer for creating a file at the root of the installation path which will permit the application to connect to an Oracle DataBase.

c. Discussion

Install.xml:

UserInputSpec.xml:

<userInput>
    <panel order="0">
            <field type="staticText" align="left" txt="Server Only:
Enter the settings for the ODBC Connection (DSN) environment variable:"
id="staticText3.text"/>
            <field type="divider" align="center"/>
            <field type="text" variable="UID">
                    <description align="left" txt=""
id="description2.text"/>
                    <spec txt="-> Enter UID:" id="text.label"
size="15" set=""/>
            </field>
            <field type="divider" align="center"/>
            <field type="password" variable="PWD">
                    <description align="left" txt=""
id="description3.text"/>
                    <spec>
                    <pwd txt="-> Type the password for the
connection:" id="pwd.label" size="10" set=""/>
                            <pwd txt="-> Retype the password for the
connection:" id="pwd.label2" size="10" set=""/>
                    </spec>
                    <validator
class="com.izforge.sample.PWDValidator" txt="Both versions of the
password must match" id="error.label"/>
                    <processor
class="com.izforge.sample.PWDEncryptor"/>
            </field>
            <field type="space" align="center"/>
            <field type="divider" align="center"/>
            <field type="space" align="center"/>
            <field type="text" variable="DBName">
                    <description align="left" txt=""
id="description4.text"/>
                    <spec txt="-> Enter the name of the Database:"
id="text.label" size="15" set=""/>
            </field>
            <field type="text" variable="DBPortNo">
                    <description align="left" txt="-> Enter the port
number for the database connection" id="description5.text"/>
                    <spec txt="(usually 1521 for oracle and 1433 for
MS SQL Server)" id="text.label" size="15" set=""/>
            </field>
    </panel>
</userInput>

BatchLoader.bat:

SET DSN=filedsn=$INSTALL_PATH\whateveryoulike.dsn;UID=$UID;PWD=$PWD
start $INSTALL_PATH\yourpath\yourapp

whateveryoulike.dsn:

[ODBC]
DRIVER=Oracle in OraHome92
DBQ=%{DBName}
SERVER=%{DBName}

Now during the installation the user will be prompted for the parameters (UID, PWD...) and the file will be parsed.

Pretty simple !

What about SQL Server or another db? Well, there are many ways to do it, a simple one would be to have a skeleton for each kind of db and then during the userinput ask for the database type (DB2, SQLSERVER,ORACLE...) and switch to the corresponding file before parsing.

Let's imagine you choose SQL Server in the userinputpanel, then instead of copying whateveryoulike.dsn, you can copy whateveryoulikeforMS.dsn which looks like this:

[ODBC]
DRIVER=SQL Server
WSID=%{DBName}
APP=Microsoft Open Database Connectivity
SERVER=%{DBName}

In our installer, we create four packs, one for each DataBase. These packs copy the corresponding file and parse them. Again, pretty simple !

Another remark, is that in this way, if you choose more than one pack, you could setup more than one connection at once on different DBs as long as UID and PWD are the same. If not you'll need a little trick...

I hope this helps and if anyone has a question, don't hesitate to contact me via `http://developer.berlios.de/sendmessage.php?touser=12462` or post into the user/devel list.

Done by Fabrice Mirabile on 20th of april 2005

2. Workaround for pack and process dependence And Execution of Java Classes that runs SQL/PLSQL

a. Problem

in many cases, there is a need to have a relation between job being executed with the processpanel and a pack. Since IzPack doesn't yet provide such feature I worked out something that does the job.

I'll explain it using an example on how to execute a Java class that runs SQL statements.

b. Solution

Here is what you will need:

UserInputSpec.xml
Install.xml
ProcessPanel.Spec.xml

Which are at the root of the installation folder.

Then you could have a folder with the SQL files, let's call it update. So in update you'll have:

JDBCGeneral.class, I use JDBC to make a DataBase connection
launchsql.bat, which runs the class with all kind of arguments
ojdbc14.jar, oracle JDBC drivers
mssqlserver.jar, msutil.jar and msbase.jar, SQL server drivers (You could have also drivers for other DB such as DB2 or Sybase)
Two folders for the SQL scripts:
- sqlsms, for SQL Server scripts
- sqlsoracle, for oracle scripts

In these folders you can have any number of SQL scripts. For example, the scripts could include: delete from task_category; insert into task_category values('LoadSource','Data Source Loading','source_loader_task.bat');

Once you have this tree of files prepared, you need to setup each file. The idea is that the install should copy the SQL scripts depending on the pack(s) chosen, plus the class and the batch file and then run the batch using the processpanel job. Therefore only the scripts for a specific pack would be run and there is the dependence we're looking for!

c .Discussion

Install.xml:

<?xml version="1.0" encoding="iso-8859-1" standalone="yes" ?>
<installation version="1.0">
....
  <resources>
     <res id="ProcessPanel.Spec.xml"
     src="ProcessPanel.Spec.xml"/>
    <res id="userInputSpec.xml" src="UserInputSpec.xml"/>
....
  </resources>
  <panels>
  .....
    <panel classname="UserInputPanel"/>
    <panel classname="ProcessPanel"/>
  .....
  </panels>
  <packs>
......
    <pack name="16-12-04" preselected="no" required="no"
    os="windows">
      <description>jar and SQL</description>
      <singlefile src="updates\sqlsms\sql161204.txt"
      target="$INSTALL_PATH\updates\sqlsms\sql161204.txt"/>
      <singlefile src="updates\sqlsoracle\sql161204.txt"
      target="$INSTALL_PATH\updates\sqlsoracle\sql161204.txt"/>
      <file src="uninstaller.bat"
      targetdir="$INSTALL_PATH\Uninstaller"/>
      <file src="uninstall.ico"
      targetdir="$INSTALL_PATH\Uninstaller"/>
      <singlefile src="updates\msbase.jar"
      target="$INSTALL_PATH\updates\msbase.jar"/>
      <singlefile src="updates\mssqlserver.jar"
      target="$INSTALL_PATH\updates\mssqlserver.jar"/>
      <singlefile src="updates\msutil.jar"
      target="$INSTALL_PATH\updates\msutil.jar"/>
      <singlefile src="updates\ojdbc14.jar"
      target="$INSTALL_PATH\updates\ojdbc14.jar"/>
      <singlefile src="updates\jdbcgeneral.class"
      target="$INSTALL_PATH\updates\JDBCGeneral.class"/>
      <singlefile src="updates\class\axiom_lang.jar"
      target="$INSTALL_PATH\class2\axiom_lang.jar"/>
      <singlefile src="updates\class\axiom_lib.jar"
      target="$INSTALL_PATH\class2\axiom_lib.jar"/>
      <singlefile src="updates\launchsql.bat"
      target="$INSTALL_PATH\updates\launchsql.bat"/>
      <parsable
      targetfile="$INSTALL_PATH\updates\launchsql.bat"/>
      <parsable
      targetfile="$INSTALL_PATH\Uninstaller\uninstaller.bat"/>
    </pack>

    <pack name="17-12-04" preselected="no" required="no"
    os="windows">
      <description>jar and SQL</description>
      <singlefile src="updates\sqlsms\sql171204.txt"
      target="$INSTALL_PATH\updates\sqlsms\sql171204.txt"/>
      <singlefile src="updates\sqlsoracle\sql171204.txt"
      target="$INSTALL_PATH\updates\sqlsoracle\sql171204.txt"/>
      <file src="uninstaller.bat"
      targetdir="$INSTALL_PATH\Uninstaller"/>
      <file src="uninstall.ico"
      targetdir="$INSTALL_PATH\Uninstaller"/>
      <singlefile src="updates\msbase.jar"
      target="$INSTALL_PATH\updates\msbase.jar"/>
      <singlefile src="updates\mssqlserver.jar"
      target="$INSTALL_PATH\updates\mssqlserver.jar"/>
      <singlefile src="updates\msutil.jar"
      target="$INSTALL_PATH\updates\msutil.jar"/>
      <singlefile src="updates\ojdbc14.jar"
      target="$INSTALL_PATH\updates\ojdbc14.jar"/>
      <singlefile src="updates\jdbcgeneral.class"
      target="$INSTALL_PATH\updates\JDBCGeneral.class"/>
      <singlefile src="updates\launchsql.bat"
      target="$INSTALL_PATH\updates\launchsql.bat"/>
      <singlefile src="updates\class\axiom_lang.jar"
      target="$INSTALL_PATH\class2\axiom_lang.jar"/>
      <singlefile src="updates\class\axiom_lib.jar"
      target="$INSTALL_PATH\class2\axiom_lib.jar"/>
      <parsable
      targetfile="$INSTALL_PATH\updates\launchsql.bat"/>
      <parsable
      targetfile="$INSTALL_PATH\Uninstaller\uninstaller.bat"/>
    </pack>
.....
  </packs>
</installation>

UserInputSpec.xml:

<userInput>
  <panel order="0">
    <field type="title" align="Left" txt="Database Connection
    Parameters" bold="true" size="1" id="DBParam"/>
    <field type="staticText" align="left" txt="The following
    information are needed for making the connection with the database."
    id="staticText1.text"/>
    <field type="staticText" align="left" txt="Careful   These
    fields are case sensitive !" id="staticText2.text"/>
    <field type="divider" align="center"/>
    <field type="divider" align="center"/>
    <field type="combo" variable="SQLServerType">
      <spec txt="Select the type of DataBase you're using    "
      id="SqlServerType.spec">
      <choice processor="" txt="Not Needed !"
      id="SQLServerType.notneeded" value="None" set="true"/>
      <choice processor="" txt="Oracle"
      id="SQLServerType.Oracle" value="Oracle"/>
      <choice processor="" txt="Microsoft SQL Server"
      id="SQLServerType.MS" value="SQLServer"/>
      </spec>
    </field>
    <field type="text" variable="ServerNameTextInput">
      <description align="left" txt="" id="description1.text"/>
      <spec txt="Enter server name  " id="text.label" size="15"
      set="localhost"/>
    </field>
    <field type="divider" align="center"/>
    <field type="text" variable="PortNbTextInput">
      <description align="left" txt="-> Enter the port number
      for the database connection" id="description5.text"/>
      <spec txt="(usually 1521 for oracle and 1433 for MS SQL
      Server)" id="text.label" size="15" set="1433"/>
    </field>
    <field type="divider" align="center"/>
    <field type="text" variable="DBNameTextInput">
      <description align="left" txt="" id="description3.text"/>
      <spec txt="Enter Database name  " id="text.label"
      size="15" set="axiom"/>
    </field>
    <field type="divider" align="center"/>
    <field type="text" variable="UserNameTextInput">
      <description align="left" txt="" id="description4.text"/>
      <spec txt="Enter Schema/User name for the Database  "
      id="text.label" size="15" set="axiom"/>
    </field>
    <field type="text" variable="UserPwdTextInput">
      <description align="left" txt="" id="description5.text"/>
      <spec txt="Enter Schema/User name password for the
      Database  " id="text.label" size="15" set="okta007"/>
    </field>
  </panel>
</userInput>

ProcessPanel.Spec.xml:

<processing>
  <job name="Executing the Needed Queries">
    <os family="windows" />
    <executefile name="$INSTALL_PATH\updates\launchsql.bat"/>
  </job>
</processing>

Launchsql.bat:

echo "Execution of SQL Queries \n"
java -classpath $INSTALL_PATH\updates\msutil.jar;$INSTALL_PATH\update
s\msbase.jar;$INSTALL_PATH\updates\mssqlserver.jar;$INSTALL_PATH\updates\
ojdbc14.jar;$INSTALL_PATH\updates\
JDBCGeneral
$INSTALL_PATH\updates\
$ServerNameTextInput
$PortNbTextInput
$DBNameTextInput
$UserNameTextInput
$UserPwdTextInput
$SQLServerType

JDBCGeneral.java: (of course you need the compiled .class !!! but I'm showing the source code)

/**
 * Parses a .sql file and runs them depending on DB settings
 * through jdbc.
 *
 * @author  Fabrice Mirabile
 * @version 2.0
*/


import java.*;
import java.sql.*;
import java.io.*;
import java.util.*;
import java.io.File;


public class JDBCGeneral{
    private java.sql.Connection  con = null;
    private final String selectMethod = "cursor";

     // Constructor
     public JDBCGeneral(){}

     public static void main(String[] argv) throws Exception
    {
      String folderpath = "";

      final String folderpathbase = argv[0];
      final String serverName= argv[1];
      final String portNumber = argv[2];
      final String databaseName= argv[3];
      final String userName = argv[4];
      final String password = argv[5];
      final String SQLServerType = argv[6];

      if (SQLServerType.equals("SQLServer")) {
        final String url = "jdbc:microsoft:sqlserver://";
        folderpath = folderpathbase.concat("sqlsms");
        String classforname =
        "com.microsoft.jdbc.sqlserver.SQLServerDriver";
        JDBCGeneral myDbTest = new JDBCGeneral();
        //myDbTest.displayDbPropertiesMS(classforname,url,ser
        verName,portNumber, databaseName, userName, password);
        String[] files =
        myDbTest.getfilenamesMS(classforname,url,folderpath, serverName,
        portNumber, databaseName, userName, password);
      }
      else if (SQLServerType.equals("Oracle")) {
        final String url = "jdbc:oracle:thin:@";
        folderpath = folderpathbase.concat("sqlsoracle");
        //test if there's no need for SQL Execution,
        //check if null value is returned from file selection
        on selecte path
       String classforname =
       "oracle.jdbc.driver.OracleDriver";
       JDBCGeneral myDbTest = new JDBCGeneral();
       //myDbTest.displayDbPropertiesOracle(classforname,url,s
       erverName,portNumber, databaseName, userName, password);
       String[] files =
       myDbTest.getfilenamesOracle(classforname,url,folderpath,
       serverName, portNumber, databaseName, userName, password);
      }

    }

     public void displayDbPropertiesOracle(String classforname,
     String url,String serverName, String portNumber, String
     databaseName, String userName, String password){
          java.sql.DatabaseMetaData dm = null;
          java.sql.ResultSet rs = null;
          try{
               con=
               this.getConnectionOracle(classforname,url,serverName,
               portNumber, databaseName, userName, password);
               if(con!=null){
                    dm = con.getMetaData();
                    System.out.println("Driver
                    Information");
                    System.out.println("\tDriver
                    Name: "+ dm.getDriverName());
                    System.out.println("\tDriver
                    Version: "+ dm.getDriverVersion ());
                    System.out.println("\nDatabase Information ");
                    System.out.println("\tDatabase Name: "+
                    dm.getDatabaseProductName());
                    System.out.println("\tDatabase Version: "+
                    dm.getDatabaseProductVersion());
                    System.out.println("Avalilable Catalogs ");
                    rs = dm.getCatalogs();
                    while(rs.next()){
                    System.out.println("\tcatalog: "+
                         rs.getString(1));
                    }
                    rs.close();
                    rs = null;
                    closeConnection();
               }else System.out.println("Error: No
               active Connection");
          }catch(Exception e){
               e.printStackTrace();
          }
          dm=null;
     }

     public void displayDbPropertiesMS(String classforname,
     String url,String serverName, String portNumber, String
     databaseName, String userName, String password){
          java.sql.DatabaseMetaData dm = null;
          java.sql.ResultSet rs = null;
          try{
               con=
               this.getConnectionMS(classforname,url,serverName,
               portNumber, databaseName, userName, password);
               if(con!=null){
                    dm = con.getMetaData();
                    System.out.println("Driver
                    Information");
                    System.out.println("\tDriver
                    Name: "+ dm.getDriverName());
                    System.out.println("\tDriver
                    Version: "+ dm.getDriverVersion ());
                    System.out.println("\nDatabase Information ");
                    System.out.println("\tDatabase Name: "+
                    dm.getDatabaseProductName());
                    System.out.println("\tDatabase Version: "+
                    dm.getDatabaseProductVersion());
                    System.out.println("Avalilable Catalogs ");
                    rs = dm.getCatalogs();
                    while(rs.next()){
                    System.out.println("\tcatalog: "+
                         rs.getString(1));
                    }
                    rs.close();
                    rs = null;
                    closeConnection();
               }else System.out.println("Error: No
               active Connection");
          }catch(Exception e){
               e.printStackTrace();
          }
          dm=null;
     }

     private java.sql.Connection getConnectionMS(String
     classforname, String url,String serverName, String portNumber,
     String databaseName, String userName, String password){
          try{
               Class.forName(classforname);
               con = java.sql.DriverManager.getConnect
               ion(getConnectionUrlMS(url,serverName, portNumber,
               databaseName),userName,password);
               if(con!=null)
               System.out.println("Connection Successful!");
          }catch(Exception e){
               e.printStackTrace();
               System.out.println("Error Trace in
               getConnection() : " + e.getMessage());
         }
          return con;
      }

     private String getConnectionUrlMS(String url,String
     serverName, String portNumber, String databaseName){
          return url+serverName+":"+portNumber+";databaseNa
          me="+databaseName+";selectMethod="+selectMethod+";";
     }

     private java.sql.Connection getConnectionOracle(String
     classforname, String url,String serverName, String portNumber,
     String databaseName, String userName, String password){
          try{
               Class.forName(classforname);
               con = java.sql.DriverManager.getConnect
               ion(getConnectionUrlOracle(url,serverName, portNumber,
               databaseName),userName,password);
               if(con!=null)
               System.out.println("Oracle Connection Successful!");
          }catch(Exception e){
               e.printStackTrace();
               System.out.println("Error Trace in
               getConnectionOracle() : " + e.getMessage());
         }
          return con;
      }

     private String getConnectionUrlOracle(String url,String
     serverName, String portNumber, String databaseName){
          return
          url+serverName+":"+portNumber+":"+databaseName;
     }

     private String[] getfilenamesOracle(String classforname,
     String url, String folderpath, String serverName, String portNumber,
     String databaseName, String userName, String password) throws
     FileNotFoundException,IOException{
         String newfolderpath = folderpath + "\\";
         File toto = new File(newfolderpath);
         String [] thelist = toto.list();
         for (int j=0; j<thelist.length; ++j)
         {
                 //System.out.println("file n?" + j
                 + " = " +  thelist[j] + "\n");
                 String[] StatementsSQL =
                 SQLFileInput(newfolderpath + thelist[j]);
                 RunSQLOracle(StatementsSQL,classforname,url,serverName,
                 portNumber, databaseName, userName, password);
         }
         return thelist;
     }

     private String[] getfilenamesMS(String classforname, String
     url, String folderpath, String serverName, String portNumber, String
     databaseName, String userName, String password) throws
     FileNotFoundException,IOException{
         String newfolderpath = folderpath + "\\";
         File toto = new File(newfolderpath);
         String [] thelist = toto.list();
         for (int j=0; j<thelist.length; ++j)
        {
              //System.out.println("file n?" + j + " =
              " +  thelist[j] + "\n");
              String[] StatementsSQL =
              SQLFileInput(newfolderpath + thelist[j]);
              RunSQLMS(StatementsSQL,classforname,url,serverName,
              portNumber, databaseName, userName, password);
        }
         return thelist;
     }

     /*
     public String[] addToArray(String[] array, String s)
     {
       String[] ans = new String[array.length+1];
       System.arraycopy(array, 0, ans, 0, array.length);
       ans[ans.length - 1] = s;
       return ans;
     }
    */

     public String[] SQLFileInput(String sqlinput) throws
     FileNotFoundException,IOException {
        BufferedReader br = new BufferedReader(new
        FileReader(sqlinput));
        List lines = new ArrayList();
        int i = 0;
        int h = 0;
        String thisLine;
        String[] SQLStatements = new String[1000];

        while ((thisLine = br.readLine()) != null)
        {
           //System.out.println(thisLine);
           SQLStatements[h] = thisLine;
            h++;
        }

        /*for(String line = br.readLine();line != null;line =
        br.readLine()) {
            // split by semi-colon
            InsertRows = line.split(";");
            i++;
        }

        for (int j=0; j<SQLStatements.length; ++j)
        {
            if (SQLStatements[j] != null)
                System.out.println("query n?" + j + "
                = " +  SQLStatements[j]);
        }*/

        return SQLStatements;
    }

     public void RunSQLOracle(String[] StatementsSQL, String
     classforname, String url, String serverName, String portNumber,
     String databaseName, String userName, String password){
         try {
            con=
            this.getConnectionOracle(classforname,url,serverName,
            portNumber, databaseName, userName, password);
            Statement stAddUser = con.createStatement();

            for (int i=0; i<StatementsSQL.length; ++i)
            {
                 if (StatementsSQL[i] != null)
                 {
                     System.out.print(StatementsSQL[i] + "...");
                     int rowsAffected =
                     stAddUser.executeUpdate(StatementsSQL[i]);
                     if (rowsAffected == 1)
                     System.out.println("OK");
                  }
            }
            closeConnection();
         }
         catch(SQLException e) {
          e.printStackTrace();
          System.out.println("\nError Trace in
          RunSQLOracle(): " + e.getMessage());
         }
     }

     public void RunSQLMS(String[] StatementsSQL, String
     classforname, String url, String serverName, String portNumber,
     String databaseName, String userName, String password){
         try {
            con= this.getConnectionMS(classforname,
            url,serverName, portNumber, databaseName, userName,
            password);
            Statement stAddUser = con.createStatement();

            for (int i=0; i<StatementsSQL.length; ++i)
            {
                if (StatementsSQL[i] != null)
                 {
                    System.out.print(StatementsSQL[i] + "...");
                    int rowsAffected =
                    stAddUser.executeUpdate(StatementsSQL[i]);
                    if (rowsAffected == 1)
                        System.out.println("OK");
                 }
            }
            closeConnection();
         }
         catch(SQLException e) {
          e.printStackTrace();
          System.out.println("\nError Trace in RunSQLMS():
          " + e.getMessage());
         }
     }

     private void closeConnection(){
          try{
               if(con!=null)
                    con.close();
               con=null;
          }catch(Exception e){
               e.printStackTrace();
          }
     }
}

To sum up:

The install.xml copies the files, the userinput asks for the DB connections, the process.xml launchs the class which takes as arguments the following entries:

a folder that will contain the sql files (each file is a sequence of sql queries separated by semi-colons ). This folder contains a subfolder for each type of DB
the server name of the machine hosting the DB
the port number of the connection (1433 for sql server and 1521 for oracle for example)
name of the DB
username
username password
type of DB (oracle, sqlserver...) in order to execute the sql inside the corresponding sub-folder

Once again, i hope you'll find this useful and if anyone has a question, don't hesitate to contact me via `http://developer.berlios.de/sendmessage.php?touser=12462` or post into the user/devel list.

Done by Fabrice Mirabile on 20th of april 2005