Finding an Oracle Foreign Key by its ID to understand which tables are referenced by it

It’s quite common to receive errors when trying to executed SQL insert and delete because some of the changes we’re executing, cause some Oracle foreign keys to be violated.

I don’t like that the error message given by Oracle is not very clear about what’s exactly the cause of the problem, it just says something like this:

Error report:
SQL Error: ORA-02292: integrity constraint (MYDB.FKBA1C59B51590B46E) violated - child record found
02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
*Cause:    attempted to delete a parent key value that had a foreign
*Action:   delete dependencies first then parent or disable constraint.

In this message, I just know that the violated key name is FKBA1C59B51590B46E, in this case I usually need to write some more SQL to update (or delete) also the table where there are some dependencies.

Of course it’s useful to know which is that table. The command to execute is very simple, and I hardly understand why Oracle doesn’t show directly the table name where the foreign key is violated.

To know more about that cryptic FKBA1C59B51590B46E, just execute this:

select * from ALL_CONSTRAINTS where constraint_name = 'FKBA1C59B51590B46E';

Importing an Excel spreadsheet in MySQL to quickly manipulate its data

I’ve been asked to compute some data and statistics from an Excel spreadsheet containing an huge phonebook

The operations I need wasn’t very complicated, like to find and remove duplicate rows and so on, but I didn’t find any quick way to achieve those simple tasks using just Excel (or OpenOffice and LibreOffice).

Since I’m good in SQL, I decided to move this data in a MySQL database, then I wondered what’s the simplest way to obtain this result.

Excel Data Structure

My excel data is on three simple columns:

  • First Name
  • Last Name
  • Phone Number

I need to export my 20,000 rows in a .csv file.

Export XLS Data in a CSV

Using Microsoft Excel

From Excel, go in “Save As” then pick the option “Other Formats”, and from the combo box, choose Comma Delimited  CSV.

Microsoft Excel  by default creates values separated by a Comma, and the single column is not enclosed by any special char.

Using OpenOffice or LibreOffice

In OpenOffice, choose Save As and then CSV, using the default options the .csv file will have values separated by semicolon and enclosed by double quotes.

Create the MySQL table to import the CSV

It’s time to create the basic data structure in MySQL that will be able to host the data we exported from Excel. So the simple task is to generate a table with the same number of columns (and type) that will be associated to the Excel columns.

create table phonebook (first_name varchar(100), last_name varchar(100), phone_number varchar(100))

And now, the last step, importing the CSV in MySQL

Import the CSV (generated from an XLS) into MySQL table

Mysql offers a useful command for the operation of importing the CSV in a table, the command is LOAD DATA LOCAL INFILE.

And now the code in the case you exported the CSV from OpenOffice and the rows have the following structure:


The code to load the data is:

load data local infile ‘phonebook.csv’ into table phonebook fields terminated by ‘,’ enclosed by ‘”‘ lines terminated by ‘\n’ (first_name, last_name, phone_number);

If you exported using Microsoft Office the rows have the following structure:


The code to load the data is:

load data local infile 'phonebook.csv' into table phonebook fields terminated by ';' enclosed by '' lines terminated by '\n' (first_name, last_name, phone_number);

Ajax Dynamic Content with Struts2, JQuery and JSON plugin

This guide explains how to create a simple web application that dynamically populates a page through AJAX, using both Struts2 and the JSON features of JQuery.
First of all it’s required the Json Plugin (available at that should be placed in the /WEB-INF/lib/ directory (where obviously are placed all the Struts2 jar as explained in other tutorials of this site).

The plugin adds (through its struts-plugin.xml) a new result type defined this way:

<package name="json-default" extends="struts-default">
  <result-type name="json" class="com.googlecode.jsonplugin.JSONResult"/>

Since it’s defined in the json-default package, in order to use that result inside custom action mappings, there are two choices:

  • the packages containing actions with json result-type have to extend the package json-default and not, as usual, the struts-default package
  • in the packages where json result-type is used, it’s to possible to add the previous <result-types>...</result-types> lines that simply refers to a class contained in the json plugin .jar added to the application.

It’s now possible to define action mappings using json as result type, like the following one:

<package name="testPackage" extends="json-default" namespace="/test">
 <action name="giveMeJsonData" class="testAction" method="giveMe">
  <result type="json">
   <param name="root">jsonData</param>

The above definition states that the url /test/giveMeJsonData.action will cause the execution of the method public String giveMe() defined inside the class testAction (in this case it’s a Spring managed bean, but it can be even a qualified name of a Struts2 action class, obviously extending ActionSupport class).

The result of that action (with a SUCCESS result code) is the json data structure stored in the jsonData property of the action class, and so available through its getter getJsonData().

An example of the behavior for giveMe() method:

public String giveMe() {
 jsonData = new LinkedHashMap<String, Object>();
 jsonData.put("shoppingCartId", getCartId());
 jsonData.put("datetime", new Date());
 Set<Map<String, Object>> items = new HashSet<Map<String, Object>>();
 for (Item item : businessMethod.findItemsForCart(getCartId())) {
  HashMap<String, Object> itemMap = new HashMap<String, Object>();
  itemMap.put("id", item.getId());
  itemMap.put("quantity", item.getQuantity());
  itemMap.put("price", item.getPrice);
 jsonData.put("items", items);
 return SUCCESS;

The final step is to use JQuery to call (on a specific event) through AJAX the URL where the action has been defined, and obviously to use the returned data to dynamically populate the page HTML.

function testingJsonAndAjax(cartId) {
  /test/giveMeJsonData.action ,
   cartId: cartId
  function(json) {
   itemsHtml = "<table>";
   for (i in json.items) {
    itemsHtml += “<tr>”;
    itemsHtml += “<td>” + json.items[i].id + “</td>”;
    itemsHtml += “<td>” + json.items[i].quantity + “</td>”;
    itemsHtml += “<td>” + json.items[i].price + “</td>”;
    itemsHtml += “</tr>”;
   itemsHtml += “</table>”;
 return false;

A sample HTML would look like this

Cart 32233 <a href=”#” onclick="return testingJsonAndAjax(32233)>Refresh</a> <br />
Cart 82382 <a href=”#” onclick="return testingJsonAndAjax(82382)>Refresh</a> <br />

<div id=”cartId”>JQuery will replace this text with the Cart Id returned by the json action</div>
<div id=”cartCreation”>JQuery will replace this text with the Cart creation date returned by the json action</div>
<div id=”cartItems”>Jquery wil replace this text with a HTML table containg all the items of the selected cart</div>

The id will be used by the JQuery selector to determine in which of them the data returned by the json action will be written in.

I hope this tutorial has been useful for a simple introduction to AJAX and JSON using JQuery and Struts2.

EDIT 1: on the Struts User Mailing List, Wes Wannemacher suggested that it would be better to directly put the item object inside the root object returned through JSON.

This is absolutely right and would lead to cleaner code. But I didn’t used that technique for a security reason, i.e. if the Item object is a JPA entity, it may contain some properties that is better not to show to the end users. In the case of a User entity, it would be no good to return in the json data its hashed password.

So I created that ugly workaround, defining some HashMaps and putting there just the specific properties I wish to return in the Json result (and maybe this will save too some HTTP traffic 🙂 )

EDIT 2: on the Struts User Mailing List, Nils-Helge Garli Hegvik suggested that it’s even possible to use the “includeProperties” or “excludeProperties” parameters (as described here) in the result configuration to simply return some objects and the JSON plugin will do the trick of filtering just the specific properties to show.

in the packages where “json” result-type is used, it’s to possibile to add the previous &lt;result-types&gt;…&lt;/result-types&gt; lines that simply refers to a class contained in the .jar added to the application (contained in the json-plugin jar).

Hibernate, hashCode, equals and Eclipse

Java programmers use often Eclipse to write their code, and it’s common to rely on the Eclipse features to quickly generate the hashCode() and equals(Object obj) methods of the beans.

Working on a Hibernate based application, I often noticed unexpected issues, and after some in depth debugging I’ve found that probably some of these problems were caused by the Eclipse automatic generation of equals method.

In my opinion, this is mostly caused by the fact that Hibernate generates on top of our entity bean classes other proxy classes that overrides the getters, and other methods in order to provide the lazy loading of data.

I.e.: When Hibernate loads from the DB a Movie bean, it will have, for example, an associated list of MovieActor objects. Obviously Hibernate will not fetch all the data related to every single actor, but anyway it will generate the stub for the MovieActor objects.

The programmer would be tempted to use code like this:

Movie aMovie = getEntityManager.find(...);
MovieActor aMovieActor = getEntityManager.find(...);
if (aMovie.getActors().contains(aMovieActor)) {
	System.out.println(aMovieActor.getActor().getName() + " acted in the movie " + aMovie.getTitle());

I noticed that contains method often returns false even if that actor really acted in that movie!
After some long debugging sessions, my final idea is that the problem is how Eclipse automatically generated the equals method of MovieActor class:

	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (!(obj instanceof MovieActor))
			return false;
		MovieActor other = (MovieActor) obj;
		if (id == null) {
			if ( != null)
				return false;
		} else if (!id.equals(
			return false;
		return true;

In this code id is an @EmbeddedId composed by the Actor and the Movie associations. What I believe that causes problems is:!

This is because probably the Id of the other object hasn’t been loaded yet, and its fetching will happen on the calling of getId() of the proxy class built on MovieActor.

So, I changed the equals method this way:

	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (!(obj instanceof MovieActor))
			return false;
		MovieActor other = (MovieActor) obj;
		if (id == null) {
			if (other.getId() != null)
				return false;
		} else if (!id.equals(other.getId()))
			return false;
		return true;

And after this little change, contains began to work correctly!

Another very important thing is to check use ‘instanceof’ to compare types otherwise some ugly code would be generated.
Some checks like if (getClass() != obj.getClass()) would miserably fail when hibernate proxy classes are used.

Watch P2P Television on Linux with Sopcast and VLC player

On Windows I was used to watching on-line television with the software Sopcast. On linux I had to spend about an hour to setup everything to correctly view P2P Tv, so I believe that this post may be useful to other people.

First of all, find and download the file sopcast-3.0.1-1.pkg.tar.gz or a more recent version if available. Then unzip it on your disk and change directory going into its bin folder

tar xfvz sopcast-3.0.1-1.pkg.tar.gz
cd sopcast-3.0.1-1.pkg/usr/bin/

It’s now time to run the service that will connect to a p2p television stream, and will stream that video on your PC.

./sp-sc-auth sop:// 3908 8908 > /dev/null

Obviously replace the URL sop:// with the URL of the television you want to watch (on there are some good channel listings) Be careful to pick an URL for Sopcast (there are many other kind of p2p softwares such as TvAnts and so on that uses different protocols).

Now there is a background service on your system that streams that TV on the chosen port 8908

The final step is to run VLC Media Player to view that stream on your monitor. This is the command:

vlc http://localhost:8908/tv.asf

Enjoy the show 🙂

Change your site domain or path keeping your Google PageRank and position

When a website moves from an URL to another, the main issue for the webmaster is try to keep a good position on the Google search results (or other search engines).
Time ago I applied the SEO technique described in this article and I obtained good results.

The goal, in this example, is to move the site located in to the new location

The trick to map everything to the new location (without specifying every single URL) and especially to tell correctly to Google to consider that the site has moved (as described in Google FAQ) is to use the HTTP response code 301 that causes a Permanent Redirect.

Obviously everything inside the section directory must keep exactly its previous relative path. I.e. have to be placed in
It’s even possible, using regular expression, to map relative paths that have some differences, but a “logic” between them should exist.

A .htaccess file should be placed inside the document root or in the section subdirectory of the old website where the site is moving out
The content of .htaccess should look like this:

RedirectMatch 301 ^/section/(.*)$$1

Obviously if the new site has section moved in othersection the .htaccess should look like this:

RedirectMatch 301 ^/section/(.*)$$1

And if the relocation has happened within the same domain:

RedirectMatch 301 ^/section/(.*)$ /othersection/$1

A test made with wget (a GNU tool):

Connecting to||:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: [following]
--2009-03-24 20:04:59--
Connecting to||:80... connected.
HTTP request sent, awaiting response... 200 OK

I applied the first configuration to one of my websites that was in the first position on Google for some query strings, because unluckily I had to move it on a new domain name.

The result has been very good. After 7 days I placed the Redirect 301, my old URL (that was the first position of Google) has been replaced by the new one. Still in the first position, and it’s still there!

Creating Tomcat6 Windows Services

Running the default Windows Tomcat installer a Tomcat server named Tomcat6 will be created on the Windows machine.

In the case the tomcat zipped installation file has been used, or if during the installation wizard the option for creating a Windows service hasn’t been checked, or finally, if it is required to have multiple Tomcat services running on the server, the Tomcat features to create new services should be used.

Obviously it’s required to have Java installed, in this case, it’s used the JDK.

The following batch script will install a tomcat service named YourTomcat related to a CATALINA_BASE located in c:\YourDir\YourTomcat.

set JAVA_HOME="c:\Program Files\Java\jdk1.6.0_12\"
set TOMCAT_HOME="c:\Program Files\Apache Software Foundation\Tomcat 6.0"
set CATALINA_BASE="c:\YourDir\YourTomcat"
call service.bat install YourTomcat

JAVA_HOME and TOMCAT_HOME are obviously the places where Tomcat and Java are installed.

What’s the CATALINA_BASE? In few words, from the same Tomcat installation (located in this example in c:\Program Files\Apache Software Foundation\Tomcat 6.0, the TOMCAT_HOME) it’s possible to run more than one server instances. Each Tomcat instance has its own deployed web applications, its own logs, its own configuration and so on.

The CATALINA_BASE needs to have those directories structure:


In conf/ it’s possible to copy the content of the conf/ directory of the Tomcat installation, and obviously these files have to be customized, depending on what is required for that specific Tomcat service, i.e. the HTTP, AJP and SHUTDOWN ports, and so on.

In logs/ each tomcat will write its custom Catalina logs or the web application specific logs (i.e. handled by Log4J).

In temp/ Tomcat will obviously place temporary files.

In webapps/ the web applications .war will be placed (and exploded).

in work/ the compiled .jsp will be placed.

Solving Tomcat OutOfMemoryError: Heap space and PermGen space

It’s quite common to run In memory problems when running some big Java EE application on a Tomcat server.
Some of the most commmon errors are like the following ones.

This is about a full Heap space:

SEVERE: Servlet.service() for servlet jsp threw exception
java.lang.OutOfMemoryError: Java heap space

This other is about the PermGen space that’s a memory area, where compiled classes (and JSPs) are kept, and this error might happen often if the running web application have many .java and .jsp.

MemoryError: PermGen space
java.lang.OutOfMemoryError: PermGen space

To increase the memory available to Tomcat, about heap and permgen the correct options are the following ones.

This sets the max heap available to Tomcat at 1Gb of memory:

--JvmMx 1024

This sets the max permgen available to Tomcat at 256Mb of memory:


To change the Tomcat memory settings (when Tomcat is installed on Windows as system service), it’s required to use the command-line tool tomcat6. The next command changes the memory settings for the Tomcat service named Tomcat6

tomcat6 //US//Tomcat6 --JvmMx 1024 ++JvmOptions="-XX:MaxPermSize=256m"

The label //US//Tomcat6 has the meaning of Updating Server parameters for the service named Tomcat6.
Obviously this command should be executed from the directory C:\Program Files\Apache Software Foundation\Tomcat 6.0\bin or from wherever is the bin directory of your Tomcat installation. Or to make things simple, that directoy should be added to your PATH environment variable.

It’s even possible to update memory settings from a GUI frontend, or to view what happened after running the command line tool. Running the following command:

tomcat6w //ES//Tomcat6

a window will open showing all the parameters about the windows service Tomcat6.

It’s possible to see in this image that, after running the previous command, for setting higher memory limits, in the sections Maximum memory pool and at the end of the Java Options the new memory limits are set.

Tomcat Memory Settings on Windows

Tomcat Memory Settings on Windows

SSH Tunnelling to Remote Servers, and with Local Address Binding

It’s often required to open different kind of connections to a server where there is available just a SSH account (or where only the port 22 is open).
Using ssh tunneling it’s easy to to access any port on the server, or even to connect to any other servers reachable from the server where the SSH account is available.

To access directly (i.e. with MySQL Query Browser) a MySQL service on the remote server, where the access to the port 3306 is denied, the trick is to open a SSH tunnel to the remote server, mapping an arbitrary local port the the remote port 3306. In the following example the local port 5306 is used:

ssh -L

In this case, the local port 5306 is forwarded (with ssh tunnelling) to, that attaches the tunnel on its port 3306.
When the tunnel is open, it’s only required to setup MySQL Query Browser to connect on localhost:5306 and the connection will be magically forwarded to the remote server on its port 3306.

Simple ssh tunnelling of a MySQL Connection

Simple ssh tunnelling of a MySQL Connection

It’s even possible to set the remote side of the tunnel to be mapped not on the remote server itself, but on a different host.
For example, if the local computer is not allowed to access IRC servers, an idea could be to use a remote server where a SSH account is available to tunnel the IRC connections.

Here is an example:

ssh -L

In this case the local port 8666 is mapped on the port 6666 of the IRC server, so the local IRC client (i.e. mIRC) should be simply setup to connect on localhost on the port 8666.

SSH Tunnelling to a Different Remote Host

SSH Tunnelling to a Different Remote Host

Finally, other people in the local network might desire to use the tunnel to the remote server (in this example it’s a IRC server). If the client that opened the SSH tunnel has the IP address, the other clients on the local network should connect to to reach the remote on the port 6666.

In this last case, it’s important to make sure that the tunnel binds to the correct local IP address.
If the local client has 2 addresses: and, it’s useful to open the tunnel binding it on In this way other clients on the LAN can use the tunnel. This is the syntax:

ssh -L
SSH Tunnelling with Local Address Binding

SSH Tunnelling with Local Address Binding

Java EE Load Balancing with Tomcat and Apache

This tutorial explains how to configure an Apache HTTPD server to map a specific path on a series of load-balanced Apache Tomcat.
The first step is to define the Virtual Host in the Apache configuration files.
In this case the root directory (on file system) of the site is located in /path/to/your/site/, the name of the site is and the path where the Tomcat servers may be reached is /javaee.

In few words, an URL like is mapped on the file /path/to/your/site/home.html.

An URL like is mapped to the hello.jsp file contained in javaee.war application deployed on all the Tomcat servers defined in the load balanced cluster.

The configuration of the Apache virtual host:

<VirtualHost *>
	ServerAdmin webmaster@localhost
	DocumentRoot /path/to/your/site/
	<Directory /path/to/your/site/>
		Options MultiViews
		AllowOverride All
		Order allow,deny
		allow from all

	ErrorLog /var/log/yoursite-error.log

	LogLevel warn

	CustomLog /var/log/yoursite-access.log combined

    <Proxy balancer://tomcatservers>
	BalancerMember ajp:// route=tomcatA retry=60
        BalancerMember ajp:// route=tomcatB retry=60
	BalancerMember ajp:// route=tomcatC retry=60

    <Location /javaee>
	Allow From All
        ProxyPass balancer://tomcatservers/javaee stickysession=JSESSIONID nofailover=off


The most important settings are Proxy and Location.
In Proxy it’s defined a load balancer made with 3 tomcat servers and an URL is assigned to the balancer, in this case balancer://tomcatservers.

The balancer has three members, everyone with its own URL based on the ajp protocol. In this case Apache will connect to the Tomcat servers on their AJP connectors (an alternative would be to use their HTTP connectors).

The Tomcat servers run on the hostname and each of them opens its own AJP connector on a different port: the first on 8009 (the default one), the second on 8010, the third on 8011 (obviously if they run on the same hostname/IP they must bind to different ports).

Each Tomcat is identified by a route name: tomcatA, tomcatB and tomcatC. The importance of it will be explained later.

In the Location section, a specific path /javaee of the virtual host is mapped on the previously defined balancer balancer://tomcatservers/javaee. So when someone asks for the virtual host will request that JSP to a randomly chosen Tomcat in the balancer members.

What’s the stickysession attribute? It’s a very useful configuration parameter used in conjunction with the route attributes, defined before.

As probably every Java EE (or Web) developer should know, while browsing on a server, it keeps trace of some data about the browsing session in a server-side HttpSession object. For example an ecommerce web application needs to store somewhere the information about the shopping cart of non registered users.

How the server can associate the remote session data with the specific navigation session? This is done through a cookie (or via a GET parameter in the URL) that gives to the server the session ID value.

In Java EE applications, the cookie name to identify the sessions is JSESSIONID.

This is closely related to the management of the load balancing between the Tomcat servers.

If Apache picked randomly one of the Tomcat to handle a single request and if the next request from the same user/browser was forwarded by the balancer to another Tomcat in that battery, things wouldn’t work correctly.

Each Tomcat doesn’t know anything of the existence of other Tomcat in that balancer configuration and especially a single Tomcat server cannot access the information of http sessions handled by another Tomcat.

In few words, when a Tomcat is chosen to handle the first request from a user/browser, it’s absolutely required that, to keep valid session data, the same Tomcat must be used to handle the following requests coming from that browser/user.

If not, on each request, the session data would be lost and simple tasks, such as building a shopping cart would result impossible.

So, it’s required to tell to Apache what is the session cookie name: JSESSIONID and which is the identifier of the routes to each single tomcat Server: tomcatA, tomcatB, tomcatC.

In this way, Apache will append to the end of the cookie value the information about the route to the specific Tomcat.

Java EE Tomcat Load Balancing

Java EE Tomcat Load Balancing

Finally, the last thing to set-up Apache, is obviously to add to it the modules required by the previous configuration:

  • proxy
  • proxy_balancer
  • proxy_ajp

About Tomcat configuration, there are just few changes to apply to the default configuration, in the Engine section it’s required to add the jvmRoute attribute.

<Engine name="Catalina" defaultHost="localhost" jvmRoute="tomcatA">

This is related to the route parameter defined in the Apache load balancer. So, tomcatA should be set in the configuration of the first Tomcat server, tomcatB in the second and tomcatC in the third one.

Then, the port where AJP connector listens, has to be set accordingly to the apache configuration, so 8009, 8010, 8011 respectively on the first, second and third Tomcat.

The following is the the configuration of the AJP connector:

<Connector port="8009" protocol="AJP/1.3" redirectPort="8443" />

It’s not directly related to the setup of the load-balancer, but since they run on the same host, each Tomcat should have its own set of ports.

To prevent any conflict you should change the following settings on the second and third servers: Server port="8005" and Connector port="8080".

I hope this tutorial has given a complete overview about every step required to setup Apache and Tomcat to create a simple load-balanced cluster.