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 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 :
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:
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.
<file src="dsn.dsn" targetdir="$INSTALL_PATH"/> <parsable type="shell" targetfile="$INSTALL_PATH/whateveryoulike.dsn"/>
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:
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
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.
Here is what you will need:
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!
JDBCGeneral.java: (of course you need the compiled .class !!! but I'm showing the source code)
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 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