Power to Build

Home » tools

Category Archives: tools

Tools: WindirStat – Diskspace Statistics Viewer/cleaner

I ran into a problem at work this morning – my hard disk was almost full, yet again. Couldn’t do any work as the machine was crawling. I have been backing up and cleaning up, but with so many different things I do (parsing huge log files is one culprit), I just cannot keep up. Over the last 3 years, I have been working on project after project, I have too many “important” files sitting on my disk – logs, tests, versions of programs and of course screenshots and documents.

Today, I really wanted to find those and archive them and cleanup my disk. But, where do I start and where exactly are those big files hiding? If you have used Windows Explorer and Search that comes with it, you will see what I mean. So, I went on a mission to find a better tool! While researching this, I stumbled upon a nice blog about cleaning up on Windows 7.

From there, I got onto the tool WindirStat! Great tool. I see a lot of programs every day, this one stands out. Program is very nicely written and is visually appealing. It scans the drive(s) and lists out the folders and files, sorta like what Windows Explorer does. But, it also visualizes, which is where it’s strength is (looks like scandisk, but this shows files).

That visualization really helps to get to extreme corners of your hard disk easily and find those unnecessary files that may be hiding there. I was able to find some GB’s of Windows memory dump files that even Windows disk cleaner didn’t find. This should have been part of Windows!

2016-02-02 17_16_10-C_ - WinDirStat

There, I just included a screenshot of the tool in action, so you can see what I am talking about. For starters you get a full list of folders with sizes in the top grid, nicely sorted with the biggest offender on top. Those colored cells/blocks in the bottom grid are your files and folders, perfectly color-coded by file type shown on the top right. You can click these file types to “identify” them in the bottom maze. You can click your way through that maze to find those files that you want to get to. Wow!

As a programmer, I am even more intrigued by this utility, as it puts TreeMaps to a great (ca)use! If you are interested, see here for a full overload on TreeMaps!

If you are in a similar situation and in need of a tool to cleanup your hard disk, this is definitely one of the must have. And if you are looking for similar tools on Linux, If you are looking for tools on Linux, apparently KDirStat has been superceded by K4DirStat and QDirStat. And on Unix you have du. Happy (file) hunting! But, before you toss those files, please make sure to back up.

Troubleshooting ODBC (Kofax)

A friend in our desktop support team is trying to install Kofax software on a Windows 2008 machine. He is using Kofax capture 10.2. Apparently, our earlier version (6.x) is working fine in production. I really didn’t know what Kofax was before. I only went to help him with the database connection issue. I often try to learn something new, by helping others with something I know.

From what I understood, the software, called Kofax Capture (previously Ascent Capture), is used to batch scan images and by OCR or even looking up in a database, it can classify the batches automatically. Here is a snippet from their own description:

Kofax Capture™ automates and accelerates business processes by capturing all types of paper and electronic documents and forms, transforming them into accurate and actionable information, and delivering it all into core business applications, processes and workflows.

Kofax with Oracle (through ODBC)

My friend was using the database look up process to be able to identify each batch. Apparently, a document with a barcode is scanned along with the batch of documents. The Bar code contains one of the keys in our database. This is what the process uses to look up the meta data in the database. To do this, he was setting up Kofax to connect to our Oracle database to pick up some meta data from our table to be attached to the image file he picked up. The software is interesting in that, you can attach a query to a document set and that will attach the corresponding meta data automatically. And all these are scriptable.

He had first set up with Oracle 11g driver. This seemed to have failed initially.  When he talked to Kofax, they had suggested to downgrade to 10.2. and he installed Oracle client 10.2.3. This broke it completely, in the sense, even the ODBC datasource setup for Oracle kept giving unsupported method error. I looked at this setup a few days ago and thought there was some incompatibility with Oracle drivers on Windows 2008. In any case, I felt we had to make it to work with the latest version of Oracle client (our Server is Oracle 11g). So, I asked him to re-install the latest 11g client.

I came back again with some preparation and tools. We tried the new combination (Kofax Capture 10.2 with Oracle 11.2 driver). It kept failing – Interactive Query in the Kofax GUI client (called index.exe) against Oracle kept failing silently while the script seemed to succeed intermittently.

I really wanted to see the SQLs he was sending to the database. I didn’t know if there was any tool to capture the SQLs from ODBC. I resorted to Oracle v$ tables on the database side. I was able to see see his session and the SQLs. This was comforting. At least we know, it was actually connecting to the database. Using other tools (like sqlplus), I am now certain that Oracle client was working fine. Now, it was time to debug the ODBC/Oracle bridge.

Windows ODBC Setup

To connect to any database (or file) using ODBC, you need to setup Data Sources. To do this, you can go into Control Panel -> Administrative Tools -> ODBC Data Sources (32-bit*) option. It shows you several tabs, including File DSN, User/Machine level Data sources, Available drivers etc. One of the options is to enable trace. This comes in handy immensely while trying to troubleshoot the ODBC driver/connection. Note that the trace file is for all ODBC data sources, so make sure you are using onlyone data source at a time, to be able to debug properly.


2014-05-26 21_03_22-ODBC Data Source Administrator (32-bit)

“User DSN” refers to datasources that are available only to the current user. “Machine DSN” means it’s visible to all users logging into this machine. File DSN is a file that contains the settings saved in a file. This comes in handy to use the same setup on different machines. Drivers tab contains list of the database drivers available on this machine. There is also an option to trace entire machine. Again use with caution, if there are multiple users logged on to the machine.

Way back, ODBC settings used to be stored in a couple of ini files – ODBC.ini and ODBCinst.ini. These are now stored in registry in the following location:


This contains a list of drivers and their settings. HKCU contains all the datasources for the currently logged in user where as, HKLM contains the settings for the entire machine. This link shows this information for a Sybase driver.

ODBCINST contains information about the drivers installed on this machine/user.

*Our operating system was Windows 2008 32-bit. This made things a bit easier. If this was a 64-bit OS, things will be slightly different. The program that’s tied to the Control Panel item is typically odbcad32.exe. This is actually the 64-bit version of the software.  If you are trying to get to the 32-bit ODBC administrator on a 64-bit machine, you will need to run the program in SysWOW64. You cannot mix the bitness. If your database client is 32-bit, then you will have to use 32-bit ODBC admin and 64-bit admin, otherwise. See here for more information. The drivers tab will only list drivers with the same bitness as the admin software. See this Microsoft note for more info.


Troubleshooting ODBC driver

First, my goal was to find out how compatible was the Oracle driver with the new ODBC drivers on Windows 2008. This way, I can find out if the driver would even work for the current setup. One of the tools I found earlier, called ConfTool from Microsoft (?), which shows the conformance/compatibility of a certain database driver to ODBC levels. Unfortunately, this only tests ODBC Levels 1 and 2.

The tool showed, while Oracle driver seems to support most of the functions, there were some features and attributes that were failing.
Next, I really wanted to see if the data source used by Kofax was working. To do this, I used was SQLRun from SQLEdit.This is a command line utility that lets you test any SQL against a specific ODBC Data Source. This is a real cool tool. I set it up, so it will use the ODBC data source we setup earlier for use with Kofax. I also used the query coming from Kofax that I captured in the database above. The goal was to see the result of issuing this query worked OK through the data source outside of Kofax. I had already made sure this query worked in Oracle SQL*Plus. Now, I am trying the same query through ODBC. This will guarantee that the ODBC to Oracle connection is OK.


You can setup all the required parameters for a test run in a file called SQLRun.ini. These parameters can also passed on commandline. SQLRun has a lot commandline options.

Here is what SQLRun.ini looks like for my test:

Statement=Select * from latax.sysparm where rownum < 2;

When it completes, if all goes well, the record(s) fetched will be placed in Out.txt file. Errors.txt file contains any errors.

I turned on ODBC Trace just before this step. I turned it off right after, so the trace file now contains all the ODBC calls generated for transaction in SQLRun. Since this worked fine, I will keep this trace as my baseline to compare against.

Troubleshooting Kofax ODBC/Oracle connection

After confirming that the ODBC setup worked fine outside of Kofax, I returned to verifying the connection from Kofax. I turned on a fresh ODBC Trace with a new trace file. Then I asked my friend to run Kofax query in the Index tool.

At the end of  this test, I have 2 trace files for the same SQL run through ODBC connection to Oracle – one from command line and another through Kofax. I diffed the 2 files using Winmerge. There was the difference I was looking for.

The Kofax software was sending several additional ODBC calls. One of them was to SQLSetStmtAttr function to set SQL_ATTR_QUERY_TIMEOUT to 0 (see here for more on this). This seemed to cause the query to fail immediately. Now, why they would set the timeout to 0, I don’t know.

I wanted to increase the timeout value. I couldn’t figure out if Kofax has a setting to change this. I tried the next best thing – can this feature be disabled? I looked around and found Enable Timeout Checkbox on the data source details in ODBC Admin screen. I unchecked this and ran Kofax again with new ODBC Trace restarted. Bingo!! That seemed to have done the trick!!!! The screen got populated this time!!

When I diffed the new trace file with the one from the previous run, I noticed this time, it did try to set the Query Timeout to which the Oracle driver returned a -1, saying it’s an unsupported optional feature. From then on, Kofax seemed to have stopped setting the timeout setting before issuing a  query!!!! After we did this, the query passed each time, we tried. (Oracle driver didn’t seem to support the function anyway. But, according to this page, it can be used to avoid infinite wait time to resolve locks).

When we did the same exercise with the scripted (automated) run he had in Kofax, it seemed to go through fine. The Trace output more or less matched my trace file from running the same query in SQLRun.

I really don’t know how the Kofax script was different from running the query interactively in the GUI tool. Whatever it was, the tool was creating a problem and we found a solution. But, I know, this is just a workaround. What if a query really needed timeout? I’ve asked my friend to check with Kofax as to why the discrepancy. That’s for another day 🙂

PBGrep – A simple Search Tool for PowerBuilder Code – in Perl

I wrote this script a few years ago. This comes in handy if you have a lot of PowerBuilder export files and would like to search for specific patterns, sort of like grep does. To use this all your PowerBuilder code must be exported to files (.SR? files). (We always keep the source files).

The simplest usage is to provide a pattern using -p option and directory to search in with -d option.

For e.g.,

To look for all the PB source files that have “dropdownlistbox” in them, I use:

pbgrep.pl -p “dropdownlistbox” -d z:

This will result in the below listing. Each entry shows, Source File Name, Line #, Event or function name with the line # within the function/event and finally the line with the pattern we are looking for.

type ddlb_printlayout from dropdownlistbox within w_adhoc_results

type ddlb_resulttype from dropdownlistbox within tabpage_resultdetails

type ddlb_serverselection from dropdownlistbox within w_login

type ddlb_printqueue from dropdownlistbox within w_search_correspondenc

type ddlb_serverselection from dropdownlistbox within w_login

I could use the -FCSV format, to generate an Excel file. All the entries above for each file, will be collapsed into a single row.

Here is the complete usage from the script:

usage: $0 [-hv] [-d dir] [-p pattern] [-F TAB|CSV|REPORT|LIST*] [-o<filename>|-oa]

-h : this (help) message
-d dir : Directory to search in
-p <pattern>: Pattern to search

-F <format>: Output can be TAB or CSV delimited or LIST or Report (default)
LIST outputs object name and function name where pattern was found.

-o <filename>: Optional, if missing, the output will be sent screen (STDOUT)
-oa : Optional, when present, pattern is used for name of the file (with special chars removed).

example: $0 -v -d Z:\LA -p <pattern>
If <pattern> has spaces or is a regular expression, enclose it in double quotes

Script: http://pastebin.com/kszxR1ar

Here is the full Perl script:

# pbgrep.pl - Grep like utility for a PoweBuilder Application
# Usage: pbgrep.pl ....

# Author: Sam Varadarajan
# Date: 12/01/2009
# Purpose: Script to grep PB export files to find patterns; Each find will show the function name and line #s.

# Modification History:
# Copyright, Sam Varadarajan, 2009 - 2014

# Globals
use vars qw/ %opt /;

my $ptrn = $opt{'p'};
my $dir = '';
my $verbos = 0;
$verbos = 1 if $opt{'v'};
my $file_name = '';

if ($opt{'o'})
	$file_name = $opt{'o'};

	#oa for automatic file name
	if (!$file_name || $file_name eq "a")
		$file_name = "search_".$ptrn.".txt" ;
	$file_name =~ s/ /_/g;
	$file_name =~ s/[^a-zA-Z0-9_.\-]*//g; #derive the name from the pattern
else {
	$file_name = '-'; #stdout

open FILE, ">$file_name"; 

#my $bare = 0;
# see below $bare = 1 if $opt{'b'};

my $format = "REPORT"; # default Report
$format = uc($opt{'F'}) if $opt{'F'};

my $delim = "\t";
if ($format eq "CSV")
	$delim = ",";
elsif ($format eq "TAB")
	$delim = "\t" ;
elsif ($format eq "LIST")
	$delim = "\t" ;

usage() if (!$ptrn);

print $ptrn."\n";

$dir = $opt{'d'} if $opt{'d'};
if (!$dir)
	$dir = 'Z:\\';

if ($ptrn)
		process_dir($dir, \&process_file, $ptrn);

#end of main

sub process_dir {
    my ($path, $code_ref, $ptrn) = @_;
    my ($DH, $file);
	#debug print $path;
    if (-d $path) {
        unless (opendir $DH, $path) {
            warn "Couldn't open $path.\n$!\nSkipping!";
        while ($file = readdir $DH) {
				#DEBUG print "processing $file\n";
            next if $file eq '.' || $file eq '..';
            process_dir("$path/$file", $code_ref, $ptrn);
	elsif (-f $path && (grep /\.sr[u|w|f]/, $path))
#DEBUG		print $path." ".$ptrn."\n";
		$code_ref->($path, $ptrn);

sub process_file()
	my $path = @_[0];
	my $ptrn = @_[1];
	my $proto_done = 0;
	my $curr_func = "";
	my @words = ();
	my $end_proto = 0;
	my $func_line_nbr = 0;
	my $file_line_nbr = 0;
	my @lines = ();
	my $in_proto = 0;
	my $cnt = 0;

	open (SRFILE, $path)
    or die "$0: Cannot open $path: $!\n";
	#DEBUG 	print "debug*** ".$path." ".$ptrn."\n";

	@lines = ();

	# print "processing $path\n";

	# this assumes every file will have forward prototypes
	while (<SRFILE>) {
		next unless ($_ =~ /forward prototypes/);
		$proto_found = 1;
	if ($proto_found)
		while (<SRFILE>) {
			next unless ($_ =~ /end prototypes/);

	LOOP1: while (<SRFILE>)
		s/\t+/   /g;
		s/^[ ]*//g;

		@words = split(/\s+/);

#		if (((/public/i || /protected/i || /private/i) &&  /function/i || /subroutine/i) || (/event/i))
# SV, 3/17, a line with 	inv_log.Event ue_closelogfile() caused it to think it was inside an event. Added ^ to event check.
# REDTAG, may be we will have to treat them separately and also, what if there was a function statement or event that
# was commented out?

		if (((@words[0] =~ /public/i || @words[0] =~ /protected/i || @words[0] =~ /private/i) &&
				(@words[1] =~ /function/i || @words[1] =~ /subroutine/i)) ||
			(@words[0] =~ /^event/i   && (@words[1] !~ /Post/i && @words[1] !~ /Trigger/i)))
			if (@words[0] =~ /^event/i)
				$curr_func = @words[1];
			else {
				$curr_func = @words[3];
#			print "found func header@words[3]\n";
			$func_line_nbr = 0;
			next LOOP1;
		if ($_ =~ /^[ |\t]*end function/i || $_ =~ /^[ |\t]*end event/i)
			if ($cnt && ($format eq "LIST"))
					$path =~ s/sr.[ ]*$//g;
					print FILE $path.$curr_func."\n";
			} else {
				if (@lines)
					my $count = scalar(@lines);
					foreach (my $i=0; $i < $count; $i++)
						my $li = $lines[$i];
						#DEBUG print "*****".{$lines[i]}."\n";
						if ($format eq "REPORT")
							if ($ptrn eq $curr_func)
								print FILE "<<Function defition>>\n";
							print FILE $path."(".$li->{'FILE_LINE'}."):\n   ".$curr_func."(".$li->{'FUNC_LINE'}."):\n      ".$li->{'LINE'}."\n\n";
						else {
							print FILE $path.$delim.$curr_func.$delim.$li->{'FILE_LINE'}.$delim.$li->{'FUNC_LINE'}.$delim.$li->{'LINE'}."\n";
					#VERBOSE print "****# of items found = ".scalar(@lines)."\n\n";
				else {
					# VERBOSE print $path."\t".$curr_func." No match found*******\n\n";
			$curr_func = "";
			$end_proto = 0;
			$func_line_nbr = 0;
			@lines = ();
			$cnt = 0;
			next LOOP1;
		# all other lines
		if ($_ =~ /$ptrn/ig) {
			my $line_info = {};
			#if (!$bare)
			if ($format ne "LIST")
				# bare format - list of functions (obj.func)
				$line_info->{'FILE_LINE'} = $file_line_nbr;
				$line_info->{'FUNC_LINE'} = $func_line_nbr;
				$line_info->{'LINE'} = $_;
			push(@lines, $line_info);
		next LOOP1;

# Command line options processing
sub init()
	use Getopt::Std;
	my $opt_string = 'hvbd:p:F:o:';
	getopts( "$opt_string", \%opt ) or usage();
#	use Getopt::Long;
#	GetOptions("h"=>\$opt{'h'},
#		"v"=>\$opt{'v'},
#		"b"=>\$opt{'b'},
#		"optional:o"=>\$opt{'o'},
#		"d"=> \$opt{'d'},
#		"p"=> \$opt{'p'},
#		"F"=> \$opt{'F'}
#	); 	

	usage() if $opt{'h'};

# Message about this program and how to use it
sub usage()
  print STDERR << "EOF";

	This program does...

	usage: $0 [-hv] [-d dir] [-p pattern] [-F TAB|CSV|REPORT|LIST*] [-o<filename>|-oa]

	-h        	: 	this (help) message
	-d dir    	: 	Directory to search in
	-p <pattern>: 	Pattern to search

	-F <format>: 	Output can be TAB or CSV delimited or LIST or Report (default)
						LIST outputs object name and function name where pattern was found.

	-o <filename>: Optional, if missing, the output will be sent screen (STDOUT)
	-oa 			:	Optional, when present, pattern is used for name of the file (with special chars removed).

	example: $0 -v -d Z:\LA -p <pattern>
	If <pattern> has spaces or is a regular expression, enclose it in double quotes

PowerBuilder DB Trace – SQL Extraction Utility

If you ever have to debug database scripts in PowerBuilder, the DB Trace option comes in handy. As I mentioned in my earlier post on Enabling DB Trace, you just need to prefix the DBMS name with Trace. For e.g., if your application works with Oracle, you will have say,


A typical DB Trace file gets created in C:\Windows and contains a lot of noise. Some of these can be changed, but I was trying to get all the SQLs while the user was doing data entry – sort of reverse engineering the SQLs. The file grew really large and was not easy to extract SQLs manually.

Here is an excerpt with 2 SQLs from the original dbtrace.log:

/*                 10/08/2010  16:17                 */
(ad2ca34): LOGIN: (90.315 MS / 90.315 MS)
(ad2ca34): CONNECT TO TRA O10:
(ad2ca34): LOGID=latax
(ad2ca34): DBPARM=UseContextObject=’Yes’,Disablebind=1,CacheName=’DEV2-ITS’ (0.000 MS / 90.315 MS)
(ad2ca34): PREPARE: (0.000 MS / 90.315 MS)
(ad2ca34): PREPARE:
(ad2ca34): GET AFFECTED ROWS: (0.003 MS / 90.441 MS)
(ad2ca34): ^ 0 Rows Affected
(ad2ca34): GET NUMBER OF COLUMNS SELECTED: (7.282 MS / 97.723 MS)
(ad2ca34): ^ 1 Columns
(ad2ca34): DESCRIBE SELECT: (0.000 MS / 97.723 MS)
(ad2ca34): 1 Bind Columns
(ad2ca34): DECIMAL Length=80
(ad2ca34): EXECUTE: (0.000 MS / 97.723 MS)
(ad2ca34): BIND SELECT OUTPUT BUFFER (PowerScript): (0.014 MS / 97.737 MS)
(ad2ca34): 1 Bind Columns
(ad2ca34): INT Length=80
(ad2ca34): FETCH NEXT: (1.186 MS / 98.923 MS)
(ad2ca34): GET AFFECTED ROWS: (0.002 MS / 98.925 MS)
(ad2ca34): ^ 1 Rows Affected
(ad2ca34): FETCH NEXT: (0.024 MS / 98.949 MS)
(ad2ca34): *** DBI_FETCHEND *** (rc 100)
(ad2ca34): CANCEL: (0.001 MS / 98.950 MS)
(ad2ca34): PREPARE:
(ad2ca34): GET AFFECTED ROWS: (0.002 MS / 99.061 MS)
(ad2ca34): ^ 1 Rows Affected
(ad2ca34): GET NUMBER OF COLUMNS SELECTED: (2.139 MS / 101.200 MS)
(ad2ca34): ^ 1 Columns
(ad2ca34): DESCRIBE SELECT: (0.000 MS / 101.200 MS)
(ad2ca34): 1 Bind Columns
(ad2ca34): CHAR Length=302
(ad2ca34): EXECUTE: (0.000 MS / 101.200 MS)
(ad2ca34): BIND SELECT OUTPUT BUFFER (PowerScript): (0.020 MS / 101.220 MS)
(ad2ca34): 1 Bind Columns
(ad2ca34): CHAR Length=302
(ad2ca34): FETCH NEXT: (1.218 MS / 102.438 MS)
(ad2ca34): GET AFFECTED ROWS: (0.002 MS / 102.440 MS)
(ad2ca34): ^ 1 Rows Affected
(ad2ca34): FETCH NEXT: (0.039 MS / 102.479 MS)
(ad2ca34): *** DBI_FETCHEND *** (rc 100)
(ad2ca34): CANCEL: (0.000 MS / 102.479 MS)
(ad2ca34): DISCONNECT: (0.031 MS / 102.510 MS)
(ad2ca34): SHUTDOWN DATABASE INTERFACE: (0.000 MS / 102.510 MS)
/*                 10/08/2010  16:17                 */

In comes PERL to the rescue. Attached script in pastebin is the one I wrote to do the extract.
I wrote this script in 2010. When I ran the script against the dbtrace, it extracts the SQLs from it, as shown below.
— 10/08/2010  16:17
To run it, you simply pass in the trace file and reroute the output to a file.
pb_sqls.pl -f dbtrace.1008.log > dbtrace.1008.sql
Years later


Perl Version:


Python Version:


HW: Salvage a smoked hard disk

I admit. I have old PCs hanging around. I know it’s hoarding, but you may need something from it, some day! Like the one with Windows 2000. I recently decided to get rid of it. But, I wanted to move the programs and data from the hard disk to a new one, so I can use it in a machine. The drive was an old Maxtor 80GB hard drive. It had old Windows 2000, so I didn’t bother with the OS files. I just wanted to copy the documents and other files (may be even some programs I wrote) to my new computer. This drive used the old PATA technology.

I normally get a external hard drive enclosure, so I could attach it as an external USB drive and copy files from it. This time I got a little adventurous (cheap?) and went for a SATA/PATA hard drive adapter, something like this. Essentially, it’s open ended cable with SATA or PATA pins available to plugin the hard drive. There is no safeguard compared to a hard disk enclosure.

I got my PATA drive hooked up to my computer via USB using this adapter. Everything was going well. Except, it is not hot-swappable – meaning you can’t plug and unplug while the thing was on. I did that and the next thing I know, the inevitable happened! I smelled smoke and surely, the drive was fried.

I thought it was gone. I researched a bit about data recovery and all. Sounded so expensive. Then I landed on this site called PCB Solutions.  According to this site, apparently when you smell smoke on a hard drive, chances are it’s only the PCB circuit that sits on tops that’s fried, not the disk or the platter. So, in theory, if you can replace this PCB board, you can salvage any drive. Really? I’ve worked with SCSI hard drives for a while, when I worked for a SCSI software company in the past. Never knew those things came off like that.

2013-12-22 13_56_01-2013-12-22 12.59.19

It’s the L-shaped circuit board (in green with chips) that comes off the top of the drive.

The site offers really good service. First the identifying/matching service. Lets you enter a few codes/numbers and matches the PCB needed for your drive. Once you pay and order it, it arrived within a week or so. The package comes with a kit – a small screw driver included. So, anyone can remove the old plate and replace it with the new one. Voila!! That worked!

It was that simple. I’ve done some hardware stuff, but I can’t claim to be an expert in that area. I was able to do this easily. Great service, great support. I highly recommend this for anyone at a loss of their precious hard drive. This service can save you 100’s if not thousands. You just need some patience and some caution. (Like don’t touch the circuit part, not to zap it by accident etc).

Source Code Control


I always think, we programmers are like writers and artists. We create software that could be powerful and beautiful. There is no lack of abstraction in our work either!writer

There is one difference, though. When an Author writes a book, she could revise her work so many times at her desk, but only the final outcome becomes public. Not so with our work! A programmer comes up with several revisions of his/her program before it goes to production. Even so, there may be a bug or something was missed that he/she has to revise it again.

A software could also be “backed out” to a prior version. (Even software giant like Microsoft does this once in a while. Remember when people rejected Windows Vista, their solution was to go back to XP, the prior version of Windows! I myself went back to Gnome desktop on my Ubuntu 12.04 desktop, because of some issues with Unity desktop). And of course, someone might point out that something worked in an earlier version, that you will have to dig up the sources for that version, find out what changed and try to re-incorporate that code back into the new version!! I can’t imagine an artist having to go through this type of revising of his/her painting! (I agree a book may have editions, but it’s always move forward for them.)

So, if you are a software developer you need to worry about keeping track of the history of the program. Not just the history, but all the files that made up a particular version! And to do this we resort to so many ingenious(?) ways. How many times, have you seen files with .bak, .sav, .org etc extensions on a developer’s machines? (guilty!). Some may even organize their files in dated directories, so they can go back to a specific date (guilty as well). But when your program is made up of so many files (gone are the days when you wrote all your code in one file!), such rudimentary methods of keeping track of files won’t work! Even more so, if you are working (collaborating) with other developers.

Source Code Control or Version Control

Wouldn’t it be better if a Software can track these information at the source level, so it’s easy to pull up any version of the software sources and rebuild it? Such a software tool exists for over 40+ years and is known variously as Source Code Control Software (SCCS), Version Control System (VCS), Revision Control System (RCS) and even Software Control Management (SCM). Dozens of tools have been developed with these acronyms in their names. Some of the most popular ones over the years are SCCS, RCS, PVCS, Perforce, Visual Source Safe (VSS), Mercury etc. And in the open source arena, we have had CVS, SVN and Git. Please refer to the wiki site for comparison of many more tools available on the market. The difference is not only in the names, but in techniques and technology involved. Newer tools support concurrency better.

The least a VCS can do is to keep track of history of files and thus offers reversibility. With various versions saved in the repository, it is only natural that we require the VCS to provide a good compare (diff) utility or at least the capability of using external diff tools.  Any type of files can be saved in the repository including binary files. Though, the diff utility is typically geared for text files.

More traditional VCS offers a centralized repository. SCCS, PVCS etc belong to this category. More modern ones offer more distributed approach. With developers from around the world working on open source projects, distributed repositories more sense. And the other characteristics that go with the territory is the concurrency. Again, modern VCS have more and more concurrent support built in. For e.g., CVS refers to Concurrent Version System.

Further Reading

Please refer to the Eric Sink’s website (his book is available in PDF format on his site) for detailed discussion on the SCCS tools. This blogger recaps the history of Source Code control in a funny way! And the wiki page like always delivers a nice introduction to this topic. Eric Raymond’s paper explains various generations of Version Control Systems nicely.


Oracle: Forward Slash in SQL*Plus – Take 2

Take 2?
I posted about Forward slash in Oracle SQL before. I even went back and updated it a few times based on the traffic. Believe it or not, I still get a lot of traffic to this blog site solely based on this one little topic!! So, I thought I would spend a little more time on this.

WordPress has a nice feature in the Stats page that shows the Search engine terms that drove the user to my page. This is what some enters in say, Google and clicks Search. Here are some sample terms on Slash:

sqlplus forward slash
sql forward slash
forward slash in sql
oracle forward slash
oracle sql forward slash
slash in sql
sql slash
commit vs forward slash
oracle pl/sql semi-colon or forward slash
oracle slash before commit
oracle sql commit slash
forward slash in ddl file

and so many variations of these. One thing that caught my attention (in bold) was the confusion user(s) had with commit and Forward slash. They really don’t have anything in common, but I guess Oracle behavior in different scenarios could lead one to that. So, I thought of explaining these one more time.

Then Vendor said: Let there be a Database and a (client) Tool

Let’s start from the beginning. A database system is typically run on a remote server. To work with it, the vendors – like Oracle, Sybase etc- provide a client tool. Oracle has always come with SQL*Plus as a client tool. This is a simple command line tool, yet powerful. People use it for both interactive and batch use. Oracle also came up with “better” tools eventually, like iSQL*Plus, SQL Developer etc. Similarly, Sybase has Interactive SQL. Apart from these tools, there are plenty of tools available from outside vendors. Some prominent ones for Oracle are Toad, SQLTools, PLSQL Developer etc. The modern tools are typically GUI based and provide plenty of options for interactive usage. Irrespective of that, SQL*Plus is still going strong. Several DBAs prefer this tool to flashy new GUI tools, so whichever tool you use, you may want to make sure your SQL will run SQL*Plus.

Why those Punctuation?
In a GUI tool, you can type up a SQL and click on a button (or press a key), the tool will get the SQL executed (send it to Oracle server, get the results etc). If there are multiple SQLs, you just highlight one and click and go. Now, SQL*Plus is a command line tool. It’s text based and often used in both interactive and batch modes. You couldn’t really click a button or press a key to execute SQLs. Especially, if it’s running SQLs from a file. Then you need a marker to show the end of each SQL, right? That, my friend, is the semi-colon – the statement terminator. Sort of like the period at the end of an English statement. Semi-colon is mentioned as the statement terminator in the SQL Standards books also.

Unfortunately, SQL Standards came much later. Database vendors already came up with their way of telling their tool to execute the SQLs. For e.g., client tools for Sybase and Microsoft SQL Server use go command. That makes sense. Type in a SQL. Key in go and it goes! (SQL is sent to server to be executed etc). Oracle (SQL*Plus) had a similar command – RUN!! So, if you are running SQLs in a file, you would have a SQL followed by RUN and then the next SQL followed by RUN and so on. This is good. But then Oracle decided to give us the short cuts. Like Forward Slash (“/”) for RUN1. (Those early days, every byte counted. Why type 3 chars for RUN and why store so many of them in a file? Slash will do it for cheaper price!!).

1. Actually RUN = Load & Run and the shortcut for it is “R” while “/” is to just “Run” what’s already loaded

What does Forward Slash mean?

So, there you have it. Forward Slash executes a SQL before it. If it was left like that, things probably would have been simpler. Oracle also decided to support the statement terminator, semi-colon. (Sybase and other databases did not require this earlier). Now, you have a semi-colon that could get the SQL executed and a Slash that could do the same thing. They are not the same, but served the same purpose of indicating end of a SQL statement in SQL*Plus!! This started the trouble, especially when people started mixing them. Imagine what happens when you have a semi-colon and a forward slash?

Then came PL/SQL. Now, PL/SQL is a programming language in Oracle which can have several embedded SQLs, often separated by you guessed it – semi-colon – the statement terminators. Now you can have a bunch of PL/SQL block in a file, how do you distinguish each block? Don’t you need something like a PL/SQL statement terminator? Well, semi-colon is already taken. So, Oracle came up with the ingenious way of reusing a character that already had a purpose – you guessed it, the Forward Slash!!!!! It was meant to be “RUN” the SQL, right? Now, it also has the purpose to terminate PL/SQL. (It could also mean RUN the PL/SQL). That paved the way to all the troubles we face in mixing these up.

Semi-colons and Forward Slashes

That mixture combined with SQL*Plus’s own quirks made it worse. In SQL*Plus, you can type a (SQL like) text and end it with semi-colon, it will pass it on to the database. You type a SQL followed by slash (“/”), it will be sent to the server as well. What happens when you have a semi-colon and slash? It gets executed twice. What happens when you have 2 semicolons? You might get an error on the second semi-colon. This is because of the way SQL*Plus works. As soon as you run a SQL, SQL*Plus stores it away in a buffer. Next time you type “/”, it re-executes what’s already stored in the buffer. Type “/” again, it runs it again. Until you type up a new SQL. You can smell trouble, right? People run into trouble because they don’t understand the nuances or simply because they mistyped.

Remember, “/” is a command. So, it has to be on a line by itself. Semi-colon on the other hand, has to be the last character on a line. If there is anything else after that, even a comment (–) or another semi-colon will cause an error!!

Image 595

Anything after semi-colon generates an error.

Image 596

Double semi-colon does too.

DML, DDL etc
When I mentioned SQL above, I meant any type of SQL. SELECT, INSERT, UPDATE, DELETE, CREATE TABLE etc. First 4 deal with data in tables. SELECT is a query. INSERT, UPDATE, DELETE make up the DML. There are whole bunch of SQL statements like CREATE TABLE that actually define the structure of a database object (here TABLE). These are called Database Definition Language (DDL). What we saw above applies to both DMLs and DDLs. You can use semi-colon to end DML statements as well as DDL statements. You can use Forward Slash with DML and DDL statements.

To confuse you a bit, general convention in Oracle world is to use Forward Slash to end DDL statements. This would make sense if you look at the syntax for creating Stored Procedures, Functions etc. These are PL/SQL objects. Remember, I said PL/SQL needs to be ended in Forward Slash? Now that we decided to (have to) use Forward Slash for DDL for Stored programs, why not use it for all DDLs? So, there you have it. This is how we arrived at Semi-colon (“;”) to end DML statements and Forward Slash (“/”) for DDLs.

How did COMMIT enter this discussion about Forward Slash?

Phew! We got through that fine. Now, where does commit come into the picture and why confusion about it? Typically, Oracle has Auto-commit option turned off by default. You will have to issue an explicit COMMIT to commit a transaction. (This helps us to have bunch of related DMLs inside a transaction).

Here again there are some short cuts and quirks in the tool that made it more confusing. If you didn’t add an explicit COMMIT and simply exit SQL*Plus, the tool will issue the COMMIT for you! And the DDLs do not require explicit COMMIT, as a DDL always issues implicit commits (See here) before and after the actual DDL statement. So, then with DDL it’s automatically committed and DDLs typically end with Forward Slash (“/”). Now, I can imagine why the user searched for a connection between Commit and Slash. He/She is probably running a script that has “/” at the end. The user probably ran the script and when SQL*Plus exited, it probably committed. Are you seeing the connection now? Really, there is no connection between “/” and COMMIT. They just look to be related because of quirks and twists in the tool and the specs.

What was all the blabbering about?

In summary, Forward slash is like go in SQL Server, but use with caution, as it’s not only the RUN command, but could be indirectly construed as a statement terminator as well. Forward Slash (“/”) does not commit. SQL*Plus may silently COMMIT your transaction when you exit. But, always add COMMIT and ROLLBACK explicitly.

And Remember, all this is specifically applicable to SQL*Plus. But, since it has become a de facto standard for running SQLs from a file, you make sure to follow these standards to avoid surprises.

And that mumbo-jumbo applies only to Oracle SQL or PL/SQL. Forward slash doesn’t have this type of special meaning in other databases!!!!