Saturday, November 20, 2010

Importing MS Access database into Oracle 10g or MySQL

While working on a data mining project I came across an issue in which I had to dump all the data from an MS Access mdb file into Oracle and MySQL database tables. Actually, to test your implementations of various data mining algorithms you need huge real life data sets, and they are mostly available on the internet in CSV formats or MS Access mdb files. One great place to download such data sets is the Machine Learning repository of University of California. Here is the link:

http://archive.ics.uci.edu/ml/

Now lets get back to the point of this post. Yesterday I got an mdb file and I needed to put all the data into my Oracle 10g XE installation for developing and testing an implementation of CTU-Tree data mining algorithm. The table had 1 lakh records. The first column was the ID, for serial number or transaction ID and next 19 were labelled as A to S. It was a transactional dataset. I would like to point that here transactional doesn't mean the ACID property following database, but actual data pertaining to transactions in supermarkets.
I personally hate MS Access as a database and I am a big fan of Oracle (although they're doing nasty things with Android now a days :P).
First attempt
I fiddled with some options in MS Access and exported the whole data set into XML format. For that, in the left column just right click the table name and see the Export menu option. Then I went to application express in 10g XE and tried the Load XML data option. Unfortunately it didn't work. Probably Oracle's Load XML Data couldn't parse Access's exported XML well.
Second Attempt
Next, I tried to export the Access file into TXT format delimited by commas. The export was successful except with an issue that I got warnings because some of the cells in the Access DB were null. I tried Load Text Data, in Oracle.This time Oracle's importer crashed, dunno why. I tried the same thing again but in vain. The application didn't crash but it showed me the status as
Succeeded: 0
Failed: 100000
Third attempt with MySQL
I thought, let me dump all of it to MySQL as MySQL is more flexible in many trivial things. The text export had one lakh lines like these two:


1,"25","52","164","240","274","328","368","448","538","561","630","687","730","775","825","834","0","0","0","100"
2,"39","120","124","205","401","581","704","814","825","834","0","0","0","0","0","0","0","0","0","0"

I created a table in a MySQL database with 20 columns all of type INT. Then I used this command in the mysql client command line utility:

LOAD DATA INFILE 'G:\TRANSACTIONS.TXT' INTO TABLE transactions;

I pressed the enter and within a second the output came. But it showed some warning. I ignored and straight away launched PHPMyAdmin to have a glance at the data. Alas! Only the first column had data! Rest all were set to NULL! Then I noticed the double quotes enclosed values in the rest 19 columns except the first. In the text editor(I use jEdit) I did a find and replace for " and tried to replace it with nothing. But it crashed. Same thing happened with Notepad++. This was due to the high volume of data.
I straight away went to MS Access and in the export wizard I removed the TEXT QUALIFIER as " and set it to {none} in the third dialog of the wizard. This time all the data in the export was without double quotes. I ran the MySQL's LOAD DATA command as written earlier, again and still I got warnings.
Then I modified the command a bit as follows to indicate the comma delimitation:
LOAD DATA INFILE 'G:\TRANSACTIONS.TXT' INTO TABLE transactions FIELDS TERMINATED BY ','
and executed it. Voila! The first success! It all went well and within a second, 1 lakh entries were inside MySQL. And this time without any errors and warnings.
Attempt 4: Oracle
Now after a fortune with MySQL I had to get the data anyhow into Oracle. I created a table in Oracle with the requisite number of columns all with NUMBER data type. Then I wrote a PHP program to automate the task, and in 1 minutes 47 seconds it did the job! Here is the program:


<?php
ini_set("max_execution_time",1200);
// Connect to the Oracle 10g XE database
$conn = oci_connect('user', 'pass', '127.0.0.1/XE');

if (!$conn){
   $e = oci_error();
   trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$blah=file_get_contents("TRANSACTIONS.TXT");
$blah1=explode("\n",$blah);
$i=0;
while($i<count($blah1)){
   $blah2="INSERT INTO transactions VALUES(".$blah1[$i].")";
   //use the below line instead of above if you need to dump a file that has field values enclosed with double quotes
   //$blah2="INSERT INTO transactions VALUES(".str_replace('"','',$blah1[$i]).")";
   //echo $blah2;
   $stid = oci_parse($conn, $blah2);
   oci_execute($stid);
   $i++;
}
echo "Done!";
?>
The latest update is that I successfully dumped the file that I had dumped in MySQL(i.e. the one without double quotes enclosed field values), into Oracle using the built in Load Text Data technique. The errors earlier were due to the double quotes which made the data as string but the column types were NUMBERs in the table.

Sunday, November 7, 2010

Android 2.1 on X10 Mini

I recently got Android 2.1 up and running on my X10 Mini. Although it isn't yet available for Indian X10 Mini owners but through the instructions at XDA-Developers forums, I became one lucky Indian to have the Eclair on my phone!
If you are still running the old 1.6 firmware, you can upgrade to 2.1 easily. There are two techniques. One is to directly flash a firmware into your phone, and other is to make SE Update Service believe that your phone belongs to a place where 2.1 has already been rolled out and then SE Update Service application will put new firmware onto your phone.

The first method

The second method

For the first method to work, you must have Java(at least JRE) installed in your system. To check, just open a command prompt and type java and press enter. If you get some large output, that means java is installed and working. If you get "Java is not recognized as a.........." that means Java isn't installed. Go here and get Java for your OS and install it.

In the second method, you need a rooted phone. Rooting a phone can be done by two techniques. I'll tell you the second one because thats the easiest and even non-engineerig background people will be able to do it without ease! You just need to install an application called UniversalAndRoot on your phone, launch it and tap Root!!!! Thats all!!! After the rooting, you need to install RootExplorer on your phone. Then overwrite the build.prop file in your phone's system directory with the one downloaded from here . After that turn off your phone and turn it on. You will get an error that some application has crashed. Now again turn it off. Launch SE Update Service on your computer and follow the instructions. Your computer must be connected to a high speed internet. In the step when it instructs you to release the back key, don't release it. You will see the New Software Available prompt, release the back button and install the software.

Whats new in the new firmware?


The social phonebook
This is my favourite feature in the new firmware. Your contacts' profile photos from their facebook profiles are displayed when they call you or when you call them. Also, you can see their twitter and facebook status updates right in the contacts. To link any of your phone contacts with his/her facebook/twitter profile go here otherwise if the contact has same first name and last name on facebook, his/her profile will be automatically linked to your phonebook's contact entry.

Double tap to zoom
In the browser now you can zoom to a specific part of the webpage by double tapping. Also its nice when you double tap and the text columns adjust automatically to make reading easier. You can download Dolphin Browser HD from the Android Market to have an still better experience. Dolphin Browser HD has a plugins API and several plugins have been developed for it. For example web2pdf plugin can convert whole webpage to a pdf file.

Live wallpapers
Now you can have animated wallpapers on your phone. I installed the Aquarium live wallpaper and it is very beautiful. There are several more good live wallpapers available for free download from the market to spice up your phone.

Video recording with continuous autofocus
The new firmware makes your phone capable of VGA@30fps video recording with continuous autofocus, which is a big plus.

Skype
Skype is available for Android 2.1 but it isn't supported on X10 Mini. I installed it but it used to crash whenever I tapped any contact's name to call.

Minor changes in interface
There are several minor changes in the interface. The blue ambience has gone deeper blue. The lock screen has changed. The call receive/reject screen slider has changed. It looked confusing for me at first when I got the first call. I was confused which way should I drag the slider to receive the call. Finally I went with my intuition and dragged the slider to the right to receive the call. Dragging to the left rejects the call.
The settings menu entries have got icons beside them. The wakeup key has changed. The middle key should now be used to wake the phone up, instead of any of the three keys.

Album issue
The album has gone damn slow at initial launch. You need to wait patiently till all the thumbnails are generated. Anyways, after loading the thumbnails it regains its elegance and is fast as ever.

I have discovered some more great apps that you should try:
PowerAmp: The best music player for Android
RockPlayer: Play all video formats on your phone
TapaTalk: It can let you browse and post in any TapaTalk enabled forum.
iReader: It can view chm files(the compiled help manuals of windows)
Call Recorder: You can record any calls
Android Notifier: Get notified on your pc about any incoming calls or SMSes when your phone isn't in your direct sight.
goChat: Facebook chat on your phone
Recordoid: Record voices
Wyse Pocket Cloud: A VNC client. You can control your Windows or Linux or even Mac computer from your phone directly as if you are using your PC, on your phone
Camera Zoom FX: Its a good replacement for the inbuilt camera

For downloading more and more apps visit this page To be able to download anything you must be registered there. You should also register and keep visiting xda-developers forums x10 mini zone

Wednesday, November 3, 2010

Grid & Cluster computing

Both grid computing and cluster computing are high performance computing techniques that are interchangeably confused between each other by people not involved in any one or both of these. There are some very fundamental differences between these which I'm going to enlist below:

Salient characteristics of grids
Grids are collections of heterogeneous systems. The underlying OS, as well as hardware may vary among the computers participating in the grids. The computers that are part of a grid use their spare computational power to perform the tasks assigned to the grid. They are very loosely coupled and inherently distributed in nature may be over LAN, MAN or even WAN. In a grid infrastructure, every computer behaves as a single entity and each entity has its own resource manager that manages the computing resources. Grids are of two types: computational grids and data grids. The former are used for computationally intensive operations and the latter are used for large scale data base management systems like Oracle 10g/11g.

Salient characteristics of clusters
Clusters are collections of homogeneous systems. The operating system as well as hardware in the participating computers of a cluster are same. The computers in the cluster use their full computational power to solve a problem together, contrasting to the spare computational power usage of grid entities. In a cluster all nodes behave like a single system and there is a single centralized resource manager.

Hope, this clears the confusion between grids and clusters. Both of these technologies are trending research topics for scholars.