We need you!

Icon

The IzPack documentation needs work, and you are invited to edit it!

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 5.3

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 setup:

  • The system source
  • The file source

Basically, the system source writes in the 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 :

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

Therefore you can realize straightforwardly that by changing the UID and PWD you will make your connection point to any schemas 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 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:

Code Block
SET DSN=filedsn=$INSTALL_PATH\whateveryoulike.dsn

A very nice trick is to put in this batch the UID and the PWD 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 usefull when you have on the same DB many schema and you want the same application server to access both of them without reinstalling the whole thing !

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

c. Discussion

Install.xml:

<file src="dsn.dsn" targetdir="$INSTALL_PATH"/> <parsable type="shell" targetfile="$INSTALL_PATH/whateveryoulike.dsn"/>

UserInputSpec.xml:

Code Block
<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:

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

whateveryoulike.dsn:

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

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

Pretty simple !

What about SQL Server or other db you would say ? Well, there's many ways to do it, a simple would be to have a skeleton for 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:

Code Block
[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 DB 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. Work around for pack and process dependence And Execution of Java Classes that runs SQL/PLSQL

a. Problem

I've encountered in many cases the need to have a relation between job being executed with the processpanel and a pack. Since IzPack doesn't provide yet 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 Stuffs, 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

Inside those folders you can have any number of SQL scripts. The scripts can be written in this way for example: 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 on the client side 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:

Code Block
<?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:

Code Block
<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:

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

Launchsql.bat:

Code Block
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)

Code Block
/**
 * 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 copy the files, the userinput ask for the DB connections, the process.xml launch 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 semi-colon separated). This folder contains 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