Finding an Oracle Foreign Key by its ID to understand which tables are referenced by it
Uncategorized August 24th, 2011
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
dependency.
*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';
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:
“Mario”,”Rossi”,”+390123456789″
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:
Mario;Rossi;+390123456789
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
Ajax, Java EE, JQuery, Struts2 July 12th, 2009
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 http://jsonplugin.googlecode.com) 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-types> <result-type name="json" class="com.googlecode.jsonplugin.JSONResult"/> </result-types> ... </package>
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
jsonresult-type have to extend the packagejson-defaultand not, as usual, thestruts-defaultpackage - in the packages where
jsonresult-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.jaradded 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> </result> </action> ... </package>
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);
items.add(itemMap);
}
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) {
$.getJSON(
/test/giveMeJsonData.action ,
{
cartId: cartId
},
function(json) {
$('#cartId').html(json.shoppingCartId);
$('#cartCreation').html(json.datetime);
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>”;
$('#cartItems').html(itemsHtml);
}
);
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.
Watch P2P Television on Linux with Sopcast and VLC player
Fun, Linux / Unix April 1st, 2009
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://your.favouritetv.com:3912/6002 3908 8908 > /dev/null
Obviously replace the URL sop://your.favouritetv.com:3912/6002 with the URL of the television you want to watch (on MyP2P.eu 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
Apache, Google, SEO April 1st, 2009
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 http://www.youroldsite.com/section/ to the new location http://www.yournewsite.com/section/.
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. http://www.youroldsite.com/section/page.html have to be placed in http://www.yournewsite.com/section/page.html.
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/(.*)$ http://www.yournewsite.com/section/$1
Obviously if the new site has section moved in othersection the .htaccess should look like this:
RedirectMatch 301 ^/section/(.*)$ http://www.yournewsite.com/othersection/$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 www.youroldsite.com|127.0.0.1|:80... connected. HTTP request sent, awaiting response... 301 Moved Permanently Location: http://www.yournewsite.com/section/ [following] --2009-03-24 20:04:59-- http://www.yournewsite.com/section/ Resolving www.yournewsite.com... 127.0.0.1 Connecting to www.yournewsite.com|127.0.0.1|: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
Tomcat March 12th, 2009
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:
conf/ logs/ temp/ webapps/ work/
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.
SSH Tunnelling to Remote Servers, and with Local Address Binding
Networking, System Administration February 28th, 2009
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 5306:remoteserver.com:3306 remoteuser@remoteserver.com
In this case, the local port 5306 is forwarded (with ssh tunnelling) to remoteserver.com, 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
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 8666:ircserver.org:6666 remoteuser@remoteserver.com
In this case the local port 8666 is mapped on the port 6666 of the IRC server ircserver.org, 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
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 192.168.1.1, the other clients on the local network should connect to 192.168.1.1:8666 to reach the remote ircserver.org 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: 127.0.0.1 and 192.168.1.1, it’s useful to open the tunnel binding it on 192.168.1.1. In this way other clients on the LAN can use the tunnel. This is the syntax:
ssh -L 192.168.1.1:8666:ircserver.org:6666 remoteuser@remoteserver.com

SSH Tunnelling with Local Address Binding
Java EE Load Balancing with Tomcat and Apache
Apache, Java EE, Tomcat February 16th, 2009
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 www.yoursite.com and the path where the Tomcat servers may be reached is /javaee.
In few words, an URL like http://www.yoursite.com/home.html is mapped on the file /path/to/your/site/home.html.
An URL like http://www.yoursite.com/javaee/hello.jsp 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
ServerName www.yoursite.com
DocumentRoot /path/to/your/site/
<Directory /path/to/your/site/>
Options MultiViews
AllowOverride All
Order allow,deny
allow from all
</Directory>
ErrorLog /var/log/yoursite-error.log
LogLevel warn
CustomLog /var/log/yoursite-access.log combined
<Proxy balancer://tomcatservers>
BalancerMember ajp://tomcatserver.yoursite.com:8009 route=tomcatA retry=60
BalancerMember ajp://tomcatserver.yoursite.com:8010 route=tomcatB retry=60
BalancerMember ajp://tomcatserver.yoursite.com:8011 route=tomcatC retry=60
</Proxy>
<Location /javaee>
Allow From All
ProxyPass balancer://tomcatservers/javaee stickysession=JSESSIONID nofailover=off
</Location>
</VirtualHost>
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 tomcatserver.yoursite.com 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 http://www.yoursite.com/javaee/hello.jsp 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
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.
