MySQL tip - logging results in a file

While using a mySQL command line client has it ever happened that you fire a query and the result runs into pages scrolling your entire command line. Obviously you need some better way to view the results. probably redirecting result to a file would help.

Naah! traditional redirection operator does not work. Instead use this mysql> tee c:/result.txt

Thats it......
After this command everything you do on your client window will be logged into the file c:/result.txt

Its for your command line session obviously. For the next session, repeat the activity.

Disclaimer: Anything can be found on google. I just jot it down here again because this acts my repository. And yeah! I have a better memory if I write down things ;)

Oracle and null values

I have a table say Employee with the following structure
Employee ( id number, name varchar2(50) )

If i try inserting (1,'')into this above table guess what will be the name of the employee with id 1? Yeah its null. I thought that was weird, since i meant a 0-length string and not null. Alright, so i surrendered to the fact that Oracle would store a null every time I added a zero length string.

But that's not done. Now assume, my table has the following values
Id - Name
1 - null
2 - Anita
3 - null
4 - Beena
5 - Neha

What would this query return -
Select * from Employee where name not in('Anita', 'Neha');

If you thought it would return me 3 rows, you are wrong (as was I). it just returns one record -
4 Beena
Thats because when you fire a not in clause it will not consider null. So if you thought that null != Anita/Neha you are partially right but not completely

You have to explicitly change the above query as -

Select * from Employee where name not in('Anita', 'Neha') or name is null;

Morally of the story: Deal with null value columns carefully. A small mistake will be really difficult to debug

Modifying the right click in Win XP

Folks, this is no big deal and I am sure most of you already know about it but I always seem to forget the registry entry needed and waste time in googling it. Hence jotting it down at this place.

1) To add the command prompt entry to your right click Open Explorer -> Tools(menu) -> Option -> FileTypes (tab) -> Folder Click on the Advanced button. In the dialog box , click on the New Button. In the new dialog box that pops up
Action: Command Prompt (any title)
Application used to perform action : cmd.exe
Click all the Ok buttons and your work is done. Now everytime you right click on any folder, you will see the Command Prompt option which you can use to directly go to the command prompt.

Note: Instead of choosing ‘Folder’(Open Explorer -> Tools(menu) -> Option -> FileTypes (tab)-> Folder) if you choose ‘File Folder’ you are in for some trouble. Everytime now you double click a folder, instead of opening it , it will open the command prompt. Now to solve this problem read point 2) below

2) To change the default action of a right click Many times after you add/remove new actions to a right click it may so happen that because of some mistake your default folder behaviour is changed. In my case every time I double clicked any folder it would open the command prompt (see note in point 1)). This is because some mistake of mine had affected the default behaviour of the folder double click. Set the HKEY_CLASSES_ROOT\Directory\shell to none and this problem should go away.

3) To remove the options added by various software’s when you install them. E.g. Winzip, Winmerge, etc You have to delete them from - HKEY_CLASSES_ROOT\*\shellex\ContextMenuHandlers

Regular Experssions and Java

What happens when you do the following –
Example 0)
String str = “someString”;
str.replaceAll(“*”,”#”);


The above snippet would replace all ‘*’ characters in the String str by ‘#’, simple, isn’t it? Well, if it’s so straight why is it here ;)

The replaceAll() API in String class has the following syntax – replaceAll(String regex, String replacement)

Yeah, I hope the term regex caught your attention. The first String is treated as a regular expression by Java and as a result the above snippet gives you a lovely error -

Exception in thread "main" java.util.regex.PatternSyntaxException: Dangling meta character '*' near index 0

Lovely, because it uses the word dangling ;)
If you know regular expressions even a little the character ‘*’ has a special meaning – it means zero/more times. Let’s take some examples so that this is clear

Example 1)
String str = "the Blue Umbrella is bllue in collor";
System.out.println(str.replaceAll("ll*","?"));

Output: the B?ue Umbre?a is b?ue in co?or
Explanation: the regular expression ll* searches for strings {l, ll, lll,….} etc and the code replaces them with ?
Note: simple l* will search for {emptyString, l, ll, lll, ….}

Example 2)
String str = "the Blue Umbrella is blue in color";
System.out.println(str.replaceAll("rella.*","???"));
Output = the Blue Umb???
Explanation: ‘.’(dot) means any character, so .* would mean any character zero/more times. Hence everything from rella… gets replaced with ???


So in Example 0) to replace * with # you have to tell Java that do not take * in the regular expression term but as it is; so do a str.replaceAll(“\*”,”#”)….just escape your *….thats all!!!!

Java Object References

Someone was writing a parser and while unit testing he got bugged because the two lists that he was creating using the same objects was getting modified without he explicitly doing so. After having a look at the code it turned out to be a simple problem. In java, you deal with object references as against objects. So if you have

Object o1 = new Object(); Object o2 = o1; Object o3 = o1; You have a single object but 3 references. //heres a snippet of what my colleague was actually trying to do. He complained that list1 and list2 were getting modified on their own…Really??? Have a look

import java.util.ArrayList; class Emp { private int code; public Emp(int c) { code = c; } public void setCode(int c) { code = c; } public String toString() { return ""+code; }

} public class References { public static void main(String[] args) { ArrayList list1 = new ArrayList(); ArrayList list2 = new ArrayList(); Emp e1 = new Emp(100); Emp e2 = new Emp(200); Emp e3 = new Emp(300); list1.add(e1); list1.add(e2); list1.add(e3); list2.add(e1); list2.add(e2); list2.add(e3); System.out.println(list1); System.out.println(list2); e2.setCode(900); System.out.println("--------"); System.out.println(list1);; System.out.println(list2); } }

p.s. Sorry about teh formatting....this stupid doesnt maintain the format that i write in and i am too lazy to use html tags and google docs is not working....so just pick the snippet and paste in a decent browser. it will work ;)

Reading a file using PL/SQL

Alright if the title sounds too infant to match your level I would suggest you skip going ahead because it is indeed not something fantastic. I am into converting some data and storing it as CLOB and the amateur that I am it needed me to do a lot of things before I could get it right. And to begin I had to first write a procedure to read data from a file. And that is what this blog contains.
Its got nothing fancy and a simple google would yield the needed info. Then the use of this blog? If thats what you were thinking....Folks googling , you will faind the info distributed. I have tried my best to put everything at one place.

I am assuming an oracle database on a windows machine;

The compact algorithm is -

Step1: Entry in your init.ora file -> utl_file_dir = dirPath

Step2: Create virtual directory mapping for oracle which points to the actual physical directory where the files are stored

Step3: Grant appropriate permissions on the virtual directory.

Step4: Create the file you would like to read thru PL/SQL, on the physical location

Step5: Write a procedure which would use the “utl_file” package of Oracle to read/write to a file

To get the details of this algo please click here

Generics

Java 1.5 introduced this thing and its nice. Something that helps you to get rid of all ClassCastExceptions. Also it allows you to create better and elegant systems. I have just started off with this and liked it....The negative aspect of generics (if any) havent yet been spotted and i would really like ur comments if u know of anything.
Everything about genrics is here