1. How To create an ODBC connection with IzPack (by Fabrice Mirabile)
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 ?
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 :
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:
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.
<file src="dsn.dsn" targetdir="$INSTALL_PATH"/> <parsable type="shell" targetfile="$INSTALL_PATH/whateveryoulike.dsn"/>
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:
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
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.
Here is what you will need:
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!
JDBCGeneral.java: (of course you need the compiled .class !!! but I'm showing the source code)
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 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