Enjoy The Life .......

Write data into Excel file using Apache POI

2012-01-08T21:06:00.000+05:30

IN my previous post, I posted code for reading excel file using Apache POI library. In this post I'll cover, how can we write data into excel. Here is the code:


package com.qa.test;

import java.io.FileOutputStream;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class SpreadSheetWriter {
	
	 /** This method writes data to new excel file **/
    public static void writeDataToExcelFile(List> data, String fileName) 
    {
    	try{
	    	HSSFWorkbook myWorkBook = new HSSFWorkbook();
	        HSSFSheet mySheet = myWorkBook.createSheet();
	        HSSFRow myRow = null;
	        HSSFCell myCell = null;
	        
	        //Create header row
	        createHeaderRow(mySheet);
	        
	        int rowNum = 1;
	        Iterator> iter = data.iterator();
	        while(iter.hasNext())
	        {
	        	myRow = mySheet.createRow(rowNum++);
	        	int cellNum = 0;
	        	
				List key = iter.next();
				for(String values: key)
				{
					myCell = myRow.createCell(cellNum++);
					myCell.setCellValue(values);
				}
			}
	
	        FileOutputStream out = new FileOutputStream(fileName);
            myWorkBook.write(out);
            out.close();
        }catch(Exception e){ 
        	e.printStackTrace();
        }         
   }
    
 }

Read Excel file using apache POI library

2012-01-08T18:33:00.001+05:30

If you want to read Excel file in Java, Apache POI is the library which supports to read/write microsoft documents. Here is the code to read an Excel file.


import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;

public class ReadExcel {
 
 public static void main(String[] args){
  
  try{
   InputStream myxls = new FileInputStream("C:\\exceltest.xls");
   HSSFWorkbook wb     = new HSSFWorkbook(myxls);
   
   HSSFSheet sheet = wb.getSheetAt(0); 
   
   Iterator rows = sheet.rowIterator();
   
   System.out.println("Total Rows :: " + sheet.getLastRowNum());
   
   //Iterate over each row
   while (rows.hasNext()) 
   {
    HSSFRow row = (HSSFRow) rows.next();
    if(checkIfRowEmpty(row)) 
     continue;
    
    int minColIndex = row.getFirstCellNum();
    int maxColIndex = row.getLastCellNum();
    
    for(int colIndex = minColIndex; colIndex <= maxColIndex; colIndex++) 
    {
     HSSFCell cell = row.getCell(colIndex);
     
     // POI can't recognize empty cells of Excel, so we should create new cells with a blank value
     if(cell == null) 
     {
        cell = row.createCell(colIndex);
           cell.setCellValue(" ");
     }    
     String value = "";
     
     switch(cell.getCellType())
     {
      case HSSFCell.CELL_TYPE_NUMERIC:
       value = Double.toString(cell.getNumericCellValue());
       break;
      case HSSFCell.CELL_TYPE_BOOLEAN:
       value = Boolean.toString(cell.getBooleanCellValue());
       break;
      case HSSFCell.CELL_TYPE_BLANK:
       value = "";
       break;
      case HSSFCell.CELL_TYPE_ERROR:
       value = "";
       break;
      case HSSFCell.CELL_TYPE_FORMULA:
       value = "";
       break;
      default:
       value = cell.getStringCellValue();
       break;
     }
     System.out.print(value + "|");
    }
    System.out.println();
   }
  }
  catch(Exception e){
   e.printStackTrace();
  }
 }
 
 public static boolean checkIfRowEmpty(HSSFRow row) 
 {
  int minColumIndex = row.getFirstCellNum();
  int maxColumnIndex = row.getLastCellNum();
  
  boolean isRowEmpty = true;
  for(int columIndex = minColumIndex; columIndex <= maxColumnIndex; columIndex++) 
  {
   HSSFCell cell = row.getCell(columIndex);
   if(cell == null || cell.toString().trim().isEmpty() || cell.toString().length() == 0){
    
   }
   else{
    isRowEmpty = false;
    return isRowEmpty;
   }
  }
  return isRowEmpty;
 }
}

Running multiple version of firefox

2011-12-14T17:51:00.001+05:30

If you want to run multiple versions of firefox on same machine, follow below steps:

1. Close your all running instances of firefox
2. Download older versions of firefox from http://www.oldversion.com/Mozilla-Firefox.html and do a custom install, install them on a specific version directory. For example I installed them as below:
C:\Program Files\Mozilla Firefox_6.0.2
C:\Program Files\Mozilla Firefox_7.0.1
3. Create profiles for every version by following steps:
    A. Goto Run command (or press WINDOWS+R)
    B. C:\Program Files\Mozilla Firefox_6.0.2 -P
    C. It will open a profile window, Create a profile name with 'Firefox6.0.2'
4. Repeat step 3 for all firefox versions, create unique profile for each version
5. To run a specific version of firefox use below command:
<path to firefox executable> -no-remote -P <profile name>

C:\Program Files\Mozilla Firefox_6.0.2\firefox.exe -no-remote -P Firefox6.0.2
C:\Program Files\Mozilla Firefox_7.0.1\firefox.exe -no-remote -P Firefox7.0.1

Selenium: Taking screenshot of a webpage

2011-12-14T15:28:00.001+05:30

I had a requirement where I had to take screenshots of different URLs on different browsers. Instead of taking it manually everytime, thought of taking it programatically using Selenium. Here is the program which takes the screenshot of a webpage using selenium:

ScreeshotTest.java



import com.thoughtworks.selenium.DefaultSelenium;
import com.thoughtworks.selenium.Selenium;
import junit.framework.TestCase;

public class ScreeshotTest extends TestCase{
    private Selenium browser;
    private static int seleniumServerPort = 2001;
    private static SeleniumServerControl seleniumServerControl = null;
    private final static String URL = "http://www.vishnuagrawal.blogspot.com";

    public void setUp() { 
        seleniumServerControl = SeleniumServerControl.getInstance();
        seleniumServerControl.startSeleniumServer(seleniumServerPort);
        browser = new DefaultSelenium("localhost", seleniumServerPort, "*firefox", URL); 
    }

    public void tearDown() { 
        browser.stop(); 
        seleniumServerControl.stopSeleniumServer();
    }

    public void testFlashApp() throws Exception{
        browser.start();
        browser.setTimeout("120000");
        browser.open(URL);
        browser.captureEntirePageScreenshot("D:\\ss.png", "");
    }
}

SeleniumServerControl.java


import org.openqa.selenium.server.RemoteControlConfiguration;
import org.openqa.selenium.server.SeleniumServer;

public class SeleniumServerControl {

  private static final SeleniumServerControl instance = new SeleniumServerControl();
  private SeleniumServer server = null;

  public static SeleniumServerControl getInstance() {
    return instance;
  }

  protected SeleniumServerControl() {
  }

  public void startSeleniumServer() {
    startSeleniumServer(RemoteControlConfiguration.DEFAULT_PORT);
  }

  public void startSeleniumServer(int port) {
    if (server == null) {
      try {
        RemoteControlConfiguration settings = new RemoteControlConfiguration();
        //File f = new File("/home/user/.mozilla/firefox/default");
        //settings.setFirefoxProfileTemplate(f);
        //settings.setReuseBrowserSessions(true);
        settings.setSingleWindow(true);
        settings.setPort(port);
        server = new SeleniumServer(settings);
        System.out.println(" selenium server " + server.toString());
      } 
      catch (Exception e) {
        System.err.println("Could not create Selenium Server because of: "  + e.getMessage());
        e.printStackTrace();
      }
    }
    try {
      server.start();
    } 
    catch (Exception e) {
      System.err.println("Could not start Selenium Server because of: " + e.getMessage());
      e.printStackTrace();
    }
  }

  public void stopSeleniumServer() {
    if (server != null) {
      try {
        server.stop();
        server = null;
      } 
      catch (Exception e) {
        System.err.println("Could not stop Selenium Server because of: " + e.getMessage());
        e.printStackTrace();
      }
    }
  }
}

Windows: remove initial characters from file names

2011-11-12T09:22:00.001+05:30

In my continuation of last post, if you do not have a specific pattern to delete, but you know the number of characters to delete, you can use following script. Following script will remove initial 5 characters from the file name. (you can change 5 to any number, ofcourse)


@ECHO ON
FOR %%F IN ("*.mp3") DO CALL :process "%%F"
GOTO :EOF

:process
SET oldname=%1
SET "newname=%~nx1"
SET "newname=%newname:~5%"
RENAME %oldname% "%newname%"

Windows: remove prefixes from file names

2011-11-12T08:52:00.001+05:30

Whenever I download any film songs from internet, the file name have a specific prefix (basically the website name, from where I downloaded the songs). To keep the file names shorter, I wish to remove the prefixes from all files. Doing this task manually consumes so much time, so here is the script to do it at one go.

File name is in format "[Site.name] Film Name - Song Number - Song Title.mp3". Save the following contents in a file (let say rename.bat) and run this file in windows command prompt. It will change all file names to "Film Name - Song Number - Song Title.mp3"


#################################
::rename.bat
#################################
@ECHO OFF
FOR %%F IN ("[*] *") DO CALL :renamer "%%F"
GOTO :EOF

:renamer
SET oldname=%1
SET "newname=%~nx1"
SET "newname=%newname:*] =%"
RENAME %oldname% "%newname%"

Shell Script: Find number of elements in an array

2011-09-27T11:26:00.000+05:30

shell (bash) provides one-dimensional array variables. Any variable may be used as an array and there is no maximum limit on the size of an array. Below is the way to find length of array in shell script.


#!/bin/bash
SERVERS=(SERVER1 SERVER2 SERVER3 SERVER4)
# set length of an array
arrayLength=${#SERVERS[*]}
# or
#arrayLength=${#SERVERS[@]}

#Traverse each entry
for (( i=0; i<${arrayLength}; i++ ));
do
  echo ${SERVERS[$i]}
done

My first AdSense paycheck

2011-09-06T10:42:00.000+05:30

Yesterday I got my first AdSense paycheck. It feels good to get first paycheck from Google AdSense. A reward for sharing your knowledge to the world :)

Excel to testlink results upload

2011-08-11T20:43:00.004+05:30

In my previous post I described about a utility which uploads my tests into Testlink. Once my tests are uploaded, I need to execute them, right.. ? But executing tests one by one from Testlink UI is quite slow process and somehow degrades productivity of the QA person. (as compare to when we use excel sheet for test cases tracking and mark Pass/Fail in the excel sheet itself, of which my team is habitual).
To overcome this, I wrote another utility which uploads tests results from excel into Testlink and your lead/manager can access reports from testlink directly. Excel should have following columns:

||Project Name||Test Suite Title||Test Case Title||Test Case Summary||Pre conditions||Step Actions||Expected Results||Priority||Keywords||TestPlan Name||Build Name||Comments||testlink login name||

1. Test Status may have values (PASS, FAIL, BLOCKED). Any other value will update test status as 'Not Run'.
2. Test Plan name should exactly match with as in Testlink
3. Build name should exactly match with as in Testlink
4. Comments can be used to specify bug number
5. User name should be testlink login name

So the whole process of uploading tests and their results goes as below:
1. QA creates tests in above mentioned column format (Project Name, suite name, status, testplan name, qa build name, comments, user name can left blank for now).
2. Create a test suite in Testlink
3. Update testlink project name and test suite name in above excel.
4. Upload excel from php of my previous post
5. Create a test plan (e.g. Release X.0) and QA Build (eg. QA1) in testlink
6. Once you execute your tests, update the excel with test status, testplan name, qa build name, comments , user name (testlink login name)
7. Upload the excel from php of my this post (see below)
8. That’s it, now anyone can access test reports from testlink UI.


<?php
// Test CVS
// Vishnu Agrawal

ini_set("display_errors", "1");
error_reporting(E_ALL ^ E_NOTICE);
date_default_timezone_set('America/Los_Angeles');

if($_FILES['xls_file']['tmp_name'])
{ 
    move_uploaded_file($_FILES['xls_file']['tmp_name'],'uploads/'.$_FILES['xls_file']['name']);
    $xls_file = 'uploads/'.$_FILES['xls_file']['name'];
    
    include('../config_db.inc.php');
    
    mysql_connect(DB_HOST,DB_USER,DB_PASS);
    mysql_select_db(DB_NAME);
    
    require_once 'Excel/reader.php';
    
    
    // ExcelFile($filename, $encoding);
    $data = new Spreadsheet_Excel_Reader();
    
    
    // Set output Encoding.
    $data->setOutputEncoding('CP1251');
    
    /***
    * if you want you can change 'iconv' to mb_convert_encoding:
    * $data->setUTFEncoder('mb');
    *
    **/
    
    /***
    * By default rows & cols indeces start with 1
    * For change initial index use:
    * $data->setRowColOffset(0);
    *
    **/
    
    
    
    /***
    *  Some function for formatting output.
    * $data->setDefaultFormat('%.2f');
    * setDefaultFormat - set format for columns with unknown formatting
    *
    * $data->setColumnFormat(4, '%.3f');
    * setColumnFormat - set format for column (apply only to number fields)
    *
    **/
    
    $data->read($xls_file);
    
    /***
    
    
     $data->sheets[0]['numRows'] - count rows
     $data->sheets[0]['numCols'] - count columns
     $data->sheets[0]['cells'][$i][$j] - data from $i-row $j-column
    
     $data->sheets[0]['cellsInfo'][$i][$j] - extended info about cell
        
        $data->sheets[0]['cellsInfo'][$i][$j]['type'] = "date" | "number" | "unknown"
            if 'type' == "unknown" - use 'raw' value, because  cell contain value with format '0.00';
        $data->sheets[0]['cellsInfo'][$i][$j]['raw'] = value if cell without format 
        $data->sheets[0]['cellsInfo'][$i][$j]['colspan'] 
        $data->sheets[0]['cellsInfo'][$i][$j]['rowspan'] 
    **/
    
    $values = '';
    
    for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
        $values[] = $data->sheets[0]['cells'][$i];
    }
    $rownum = 0;
    $test_update = 0;
    $test_update_existing = 0;
    $test_update_fail = 0;
    
    foreach ($values as $colnum)
    {    
        $rownum++;
        if($rownum > 1)
        {
            $excel_project_name = trim($colnum[1]);
            $excel_suite_name = trim($colnum[2]);
            $excel_tc_title = trim($colnum[3]);
            $excel_tc_summary = trim($colnum[4]);
            $excel_pre_condition = trim($colnum[5]);
            $excel_tc_step = trim($colnum[6]);
            $excel_tc_result = trim($colnum[7]);
            $excel_tc_priority = trim($colnum[8]);
            $excel_tc_keywords = trim($colnum[9]);
            $excel_tc_test_result = trim($colnum[10]);
            $excel_tc_testplan = trim($colnum[11]);
            $excel_tc_build = trim($colnum[12]);
            $excel_tc_comment = trim($colnum[13]);
            $excel_tc_username = trim($colnum[14]);
            
            if (!$excel_project_name) {
                if($excel_suite_name && $excel_tc_title) {
                    echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: Testlink project name is not Specified</font>\");                    
                    $test_update_fail++;
                }                
                continue;
            }
            if (!$excel_suite_name) {
                echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: Testlink Suite name is not Specified</font>\");
                $test_update_fail++;
                continue;
            }
            if (!$excel_tc_title) {
                echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: Testlink tese case title is not Specified</font>\");
                $test_update_fail++;
                continue;
            }
            if (!$excel_tc_testplan) {
                echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: Testlink test plan name is not Specified</font>\");
                $test_update_fail++;
                continue;
            }
            if (!$excel_tc_build) {
                echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: Testlink test build name is not Specified</font>\");
                $test_update_fail++;
                continue;
            }
            
            // Get Project id - Start
            $query_project = mysql_query("SELECT testprojects.id, nodes_hierarchy.name FROM testprojects, nodes_hierarchy WHERE testprojects.id = nodes_hierarchy.id AND nodes_hierarchy.name='$excel_project_name'");
            if(mysql_num_rows($query_project) != 1) {
                echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . mysql_num_rows($query_project) . " test project found with name <b>$excel_project_name</b></font>\");
                $test_update_fail++;
                continue;
            }
            $res_project = mysql_fetch_assoc($query_project);
            $project_id = $res_project["id"];
            //echo " Project id is " . $project_id . "\" ;
            // Get Project id - End
            
            // Get testplan id - Start
            $query_testplan = mysql_query("SELECT testplans.*, NH.name FROM testplans testplans, nodes_hierarchy NH WHERE testplans.id=NH.id AND NH.name = '$excel_tc_testplan' AND NH.parent_id=$project_id");
            if(mysql_num_rows($query_testplan) != 1) {
                echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . mysql_num_rows($query_testplan) . " test project found with name <b>$excel_tc_testplan</b></font>\");
                $test_update_fail++;
                continue;
            }
            $res_testplan = mysql_fetch_assoc($query_testplan);
            $testplan_id = $res_testplan["id"];
            //echo " testplan id is " . $testplan_id . "\" ;
            // Get testplan id - End
            
            // Get build id - Start
            $query_build = mysql_query("SELECT builds.id, builds.name, builds.notes FROM builds builds WHERE builds.testplan_id = $testplan_id  AND builds.name= '$excel_tc_build' AND builds.active = 1 AND builds.is_open = 1");
            if(mysql_num_rows($query_build) != 1) {
                echo "<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . mysql_num_rows($query_build) . " build found with name <b>$excel_tc_build</b>. Either build name do not exists or build is not active/open</font>\";
                $test_update_fail++;
                continue;
            }
            $res_build = mysql_fetch_assoc($query_build);
            $build_id = $res_build["id"];
            //echo " build id is " . $build_id . "\" ;
            // Get build id - End
            
            // Get tester id - Start
            $query_tester = mysql_query("SELECT * from users  where login = '$excel_tc_username'");
            if(mysql_num_rows($query_tester) != 1) {
                echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . mysql_num_rows($query_tester) ." Users with name <b>$excel_tc_username</b></font>\");
                $test_update_fail++;
                continue;
            }
            $res_tester = mysql_fetch_assoc($query_tester);
            $tester_id = $res_tester["id"];
            //echo " tester id is " . $tester_id . "\" ;
            // Get tester id - End
            
            // Get test case status - Start
            if (strcasecmp($excel_tc_test_result, 'PASS') == 0) {
                $test_status = 'p';
            }
            elseif(strcasecmp($excel_tc_test_result, 'FAIL') == 0) {
                $test_status = 'f';
            }
            elseif(strcasecmp($excel_tc_test_result, 'BLOCKED') == 0) {
                $test_status = 'b';
            }
            else {
                echo ("<font color=#ff0000>Incorrect test result <b>$excel_tc_test_result</b> in row $rownum</font>\");
                $test_status = 'n';
            }
            // Get test case status - End
            
            // Get test Suite id - Start
            $query_testsuitenames = mysql_query("SELECT TS.*, NH.name, NH.parent_id FROM testsuites TS JOIN nodes_hierarchy NH ON NH.id = TS.id WHERE NH.name = '$excel_suite_name'");
            
            $count = 0;
            $test_suite_id = 0;
            while($suites = mysql_fetch_assoc($query_testsuitenames)) {
                $query_get_project = mysql_query("SELECT _id FROM (SELECT @r AS _id, (SELECT @r := parent_id FROM nodes_hierarchy WHERE id = _id) AS parent FROM ( SELECT @r := $suites[id] ) vars, nodes_hierarchy h WHERE @r IS NOT NULL) AS t WHERE t.parent IS NULL");
                $res_get_project = mysql_fetch_assoc($query_get_project);
                if(count($res_get_project) == 1) {
                    if($res_get_project[_id] == $project_id) {
                        $count++;
                        $test_suite_id = $suites[id];
                    }
                }
            }
            
            if($count != 1 && $test_suite_id ==0) {
                echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . $count . " suite name found with name <b>$excel_suite_name</b> </font>\");
                $test_update_fail++;
                continue;
            }            
            //echo " suite id is " . $test_suite_id . "\" ;
            // Get test Suite id - End
            
            //Get test case details - Start
            $query_testcase = mysql_query("SELECT DISTINCT NH_TCASE.id,NH_TCASE.name,NH_TCASE_PARENT.id AS parent_id, NH_TCASE_PARENT.name AS tsuite_name, TCV.version, TCV.tc_external_id, TCV.id AS tcversion_id FROM nodes_hierarchy NH_TCASE, nodes_hierarchy NH_TCASE_PARENT, nodes_hierarchy NH_TCVERSIONS, tcversions TCV  WHERE NH_TCASE.node_type_id = 3 AND NH_TCASE.name = '$excel_tc_title' AND TCV.id=NH_TCVERSIONS.id AND NH_TCVERSIONS.parent_id=NH_TCASE.id AND NH_TCASE_PARENT.id=NH_TCASE.parent_id AND NH_TCASE_PARENT.id = $test_suite_id AND NH_TCASE_PARENT.node_type_id = 2  ORDER BY TCV.version desc LIMIT 1");
            if(mysql_num_rows($query_testcase) != 1) {
                echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . mysql_num_rows($query_testcase) . " test cases found with name <b>$excel_tc_title</b></font>\");
                $test_update_fail++;
                continue;
            }
            $res_testcase = mysql_fetch_assoc($query_testcase);
            $testcase_id = $res_testcase["id"];
            $testcase_version = $res_testcase["version"];
            $testcase_version_id = $res_testcase["tcversion_id"];
            //echo "Test case details " . $testcase_id . "<>" . $testcase_version . "<>" . $testcase_version_id . "\";
            //Get test case details - End
            
            $test_notes = $excel_tc_comment;
            
            $query_tcversion = mysql_query("SELECT * from testplan_tcversions where testplan_id = $testplan_id AND tcversion_id = $testcase_version_id");
            if (mysql_errno()) { 
                echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\When selecting data from testplan_tcversions\"; 
                $test_update_fail++;
                continue;
            }
                
            //Check if test case is already linked with test plan, we don't need to update the table again
            if(mysql_num_rows($query_tcversion) == 0) {    
                mysql_query("BEGIN");
                $objQuery1 = mysql_query("INSERT INTO testplan_tcversions(testplan_id, tcversion_id, author_id) VALUES($testplan_id, $testcase_version_id, $tester_id)");
                $case_id = mysql_insert_id();
                $objQuery2 = mysql_query("INSERT INTO user_assignments(feature_id, user_id, build_id, assigner_id) VALUES($case_id, $tester_id, $build_id, $tester_id)");
                
                if(($objQuery1) and ($objQuery2))  {  
                    //*** Commit Transaction ***//  
                    mysql_query("COMMIT");  
                }  
                else  {  
                    //*** RollBack Transaction ***//  
                    mysql_query("ROLLBACK");  
                    echo "ERROR :: Rollbacked transaction of updating user assignment for row $rownum ...";
                    $test_update_fail++;
                    continue;
                }
            }
            
            // Convert current Timestamp into a string, safe for MySql
            $currentTSExecution = date("Y-m-d H:i:s", time());
            
            if (strcasecmp($test_status, 'n') != 0) {    //update test status only if test status is not 'n'
                $query_executions = mysql_query("SELECT * from executions WHERE build_id = $build_id AND testplan_id = $testplan_id AND tcversion_id = $testcase_version_id AND tcversion_number = $testcase_version");
                if (mysql_errno()) { 
                    echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\When selecting data from executions\"; 
                    $test_update_fail++;
                    continue;
                }
                
                //Check if test case is not already updated in executions, then we need to add a new entry otherwise update existing entry
                if(mysql_num_rows($query_executions) == 0) {            
                    mysql_query("INSERT INTO executions(build_id, tester_id, execution_ts, status, testplan_id, tcversion_id, tcversion_number, notes) VALUES($build_id, $tester_id, '$currentTSExecution', '$test_status', $testplan_id, $testcase_version_id, $testcase_version, '$test_notes')");
                    if (mysql_errno()) { 
                        echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\When insertng data into executions\"; 
                        $test_update_fail++;
                        continue;
                    }        
                }
                elseif(mysql_num_rows($query_executions) == 1) {
                    mysql_query("UPDATE executions SET tester_id = $tester_id, execution_ts = '$currentTSExecution', status = '$test_status', notes = '$test_notes' WHERE build_id = $build_id AND testplan_id = $testplan_id AND tcversion_id = $testcase_version_id AND tcversion_number = $testcase_version");
                    if (mysql_errno()) { 
                        echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\When updating data into executions\"; 
                        $test_update_fail++;
                        continue;
                    }
                    $test_update_existing++;
                    $test_update--;
                }
                else {
                    echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . mysql_num_rows($query_executions) . " execution status found ...</font>\");
                    $test_update_fail++;
                    continue;
                }
            }
            $test_update++;
        }        
    }    
    echo "<font color=#0000ff>Tests updated (New): \$test_update</b> </font>\";
    echo "<font color=#0000ff>Tests updated (Existing): <b>$test_update_existing</b> </font>\";
    echo "<font color=#0000ff>Tests update Failed: <b>$test_update_fail</b> </font>\";
}
?>
<!DOCTYPE unspecified PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Testlink Testcase Results Uploader</title>
</head>
<body>
<form name="frm" action="" method="post" enctype="multipart/form-data">

<table>
<tr><td>Use this page to upload test results from Excel sheet to Testlink, Excel file should have following columns.</td></tr>
</table>

<table border="1">
<tr>
<th>Test Project Name from Testlink</th>
<th>Test Suite Name from Testlink</th>
<th>Test Case Title</th>
<th>Test Case Summary</th>
<th>Pre Conditions</th>
<th>Step Actions</th>
<th>Expected Results</th>
<th>Priority</th>
<th>Keywords</th>
<th>Test Status</th>
<th>Testplan Name from Testlink</th>
<th>Build Name from Testlink</th>
<th>Comments (can be used to specify Bug#)</th>
<th>Username (Testlink login name)</th>
</tr>
</table>

<p>

<table>
<tr>
<th align="left">NOTE:</th>
</tr>
<tr>
<td>
<ol>
<li>Tests should be in the first sheet of the excel file</li>
<li>First row will be ignored as it will have column headers</li>
<li>Test Project name should exactly match with Testlink project name in which you want to upload your tests. </li>
<li>Test Suite Title name should exactly match with Testlink test suite name in which you want to upload your tests. </li>
<li>Test Case Title is the title which will be used to list test in the testlink tree</li>
<li>Multiple keywords are supported (comma separated)</li>
<li>Priority column may have values Low/Medium/High. If no value (or wrong value) is specified, test will be assigned Medium priority.</li>
<li>Test Status may have values (PASS, FAIL, BLOCKED). Any other value will update test status as 'Not Run'.</li>
<li>Test Plan name should exactly match with as in Testlink</li>
<li>Build name should exactly match with as in Testlink</li>
<li>Comments can be used to specify bug number</li>
<li>User name should be testlink login name</li>
<li>If test case title already exists for the given suite in testlink, new test will not be added, existing will be updated</li>
</ol>
</td>
</tr>
</table>

<table>
<tr>
    <td><b>Testlink Test Result Uploader:</b></td>
</tr>
<tr>
    <td><input type="file" name="xls_file" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href="pub_dashboard.xls">Sample file format for testcase</a> </td>
</tr>
<tr>
    <td><input type="submit" name="submit" value="submit" /> </td>
</tr>
<tr>
    <td></td>
</tr>
</table>
</form>
</body>
</html>

Automatically login a ssh session using putty

2011-07-25T10:28:00.002+05:30

Putty is used to connect to remote server and for day to day tasks developer login into remote machine using putty. User have to input username/password for every login. This process can be automated to save some time.

1. Downlaod PuTTY

2. Download PuTTYgen

3. Run PuTTYgen

4. Select SSH-2 DSA as the "Type of Key" to generate

5. Click on generate button and move your mouse around so that randomness is generated

6. Click on “Save Private Key” and save it to your computer

7. Copy the entire content inside the box to your clipboard (this is your generated public key).

8. Login to your SSH server using putty (by using user name and password)

9. Create file ~/.ssh/authorized_keys and put your public key on a single line (from step 7)

10. Make this file readable (chmod +r )

11. Open PuTTY, navigate to Connection->Data and fill in the auto-login username

12. Navigate to Connection->SSH->Auth and under Private-key, browse to the file you had saved earlier on your computer. (from step 6)

You are done. Now whenever you login into your remote machine, it will login automatically.

Excel to Testlink upload

2011-07-12T19:47:00.010+05:30

Once you setup testlink, the first question comes in mind that how do I upload my existing test sheets into Excel. Tetslink 1.9.2 supports uploading excel sheets but before that you need to create few sample tests into testlink and then export it and then use the imported xsd to convert your excel sheet. For me, its quite cumbersome process and I wanted that we have some sort of upload functionality so that my QA team don't have to make much effort to upload their existing tests. I found a utility which is php based and supports excel upload by a simple web interface. the utility can be downloaded from http://code.google.com/p/testlink-1-9-xls-test-case-uploader/downloads/list

This utility was great but it was limited to few columns of excel but I needed additional columns plus some small modification in the existing column. With reference to their php, I modified it to support columns as per our requirement. Here is the modified version of their php.

This modified version of utility supports following columns

||Project Name||Test Suite Title||Test Case Title||Test Case Summary||Pre conditions||Step Actions||Expected Results||Priority||Keywords||TestPlan Name||Build Name||Comments||testlink login name||

Few Notes:

Tests should be in the first sheet of the excel file
First row will be ignored as it will have column headers
Test Project name should exactly match with Testlink project name in which you want to upload your tests.
Test Suite Title name should exactly match with Testlink test suite name in which you want to upload your tests.
Test Case Title is the title which will be used to list test in the testlink tree
Multiple keywords are supported (comma separated)
Priority column may have values Low/Medium/High. If no value (or wrong value) is specified, test will be assigned Medium priority.
Test Status, Test Plan, Build Name, Comments and User Name columns are ignored as they are used to update test results
If test case title already exists for the given suite in testlink, new test will not be added, existing will be updated
Utility has been tested on Testlink version 1.9.2/1.9.3
To Use this utility, you need to install original utility from above mentioned link and then replace their index.php with the below given php file


<?php
// Test CVS
// Vishnu Agrawal

ini_set("display_errors", "1");
error_reporting(E_ALL ^ E_NOTICE);
date_default_timezone_set('America/Los_Angeles');

if($_FILES['xls_file']['tmp_name'])
{ 
    move_uploaded_file($_FILES['xls_file']['tmp_name'],'uploads/'.$_FILES['xls_file']['name']);
    $xls_file = 'uploads/'.$_FILES['xls_file']['name'];
    
    include('../config_db.inc.php');
    
    mysql_connect(DB_HOST,DB_USER,DB_PASS);
    mysql_select_db(DB_NAME);
    
    require_once 'Excel/reader.php';
    
    
    // ExcelFile($filename, $encoding);
    $data = new Spreadsheet_Excel_Reader();
    
    
    // Set output Encoding.
    $data->setOutputEncoding('CP1251');
    
    /***
    * if you want you can change 'iconv' to mb_convert_encoding:
    * $data->setUTFEncoder('mb');
    *
    **/
    
    /***
    * By default rows & cols indeces start with 1
    * For change initial index use:
    * $data->setRowColOffset(0);
    *
    **/
    
    
    
    /***
    *  Some function for formatting output.
    * $data->setDefaultFormat('%.2f');
    * setDefaultFormat - set format for columns with unknown formatting
    *
    * $data->setColumnFormat(4, '%.3f');
    * setColumnFormat - set format for column (apply only to number fields)
    *
    **/
    
    $data->read($xls_file);
    
    /*
    
    
     $data->sheets[0]['numRows'] - count rows
     $data->sheets[0]['numCols'] - count columns
     $data->sheets[0]['cells'][$i][$j] - data from $i-row $j-column
    
     $data->sheets[0]['cellsInfo'][$i][$j] - extended info about cell
        
        $data->sheets[0]['cellsInfo'][$i][$j]['type'] = "date" | "number" | "unknown"
            if 'type' == "unknown" - use 'raw' value, because  cell contain value with format '0.00';
        $data->sheets[0]['cellsInfo'][$i][$j]['raw'] = value if cell without format 
        $data->sheets[0]['cellsInfo'][$i][$j]['colspan'] 
        $data->sheets[0]['cellsInfo'][$i][$j]['rowspan'] 
    */
    
    $values = '';
    
    for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
        $values[] = $data->sheets[0]['cells'][$i];
    }
    /*echo '<pre>';
    print_r($values);*/
    $rownum = 0;
    $case_count = 0;
    $test_new = 0;
    $test_update = 0;
    $test_add_fail = 0;
    
    foreach ($values as $colnum)
    {
        $rownum++;
        if($rownum > 1)
        {
            $excel_project_name = trim($colnum[1]);
            $excel_suite_name = trim($colnum[2]);
            $excel_tc_title = trim($colnum[3]);
            $excel_tc_summary = trim($colnum[4]);
            $excel_pre_condition = trim($colnum[5]);
            $excel_tc_step = trim($colnum[6]);
            $excel_tc_result = trim($colnum[7]);
            $excel_tc_priority = trim($colnum[8]);
            $excel_tc_keywords = trim($colnum[9]);
            
            if (!$excel_project_name) {
                if($excel_suite_name && $excel_tc_title) {
                    echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: Testlink project name is not Specified</font>\<br\>");                    
                    $test_add_fail++;
                }                
                continue;
            }
            if (!$excel_suite_name) {
                echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: Testlink Suite name is not Specified</font>\<br\>");
                $test_add_fail++;
                continue;
            }
            if (!$excel_tc_title) {
                echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: Testlink tese case title is not Specified</font>\<br\>");
                $test_add_fail++;
                continue;
            }
            
            // Get Project id - Start
            $query_project = mysql_query("SELECT testprojects.id, nodes_hierarchy.name FROM testprojects, nodes_hierarchy WHERE testprojects.id = nodes_hierarchy.id AND nodes_hierarchy.name='$excel_project_name'");
            if(mysql_num_rows($query_project) != 1) {
                echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . mysql_num_rows($query_project) . " test project found with name <b>$excel_project_name</b></font>\<br\>");
                $test_add_fail++;
                continue;
            }
            $res_project = mysql_fetch_assoc($query_project);
            $project_id = $res_project["id"];
            //echo " Project id is " . $project_id . "\<br\>" ;
            // Get Project id - End
        
            // Get test Suite id - Start
            $query_testsuitenames = mysql_query("SELECT TS.*, NH.name, NH.parent_id FROM testsuites TS JOIN nodes_hierarchy NH ON NH.id = TS.id WHERE NH.name = '$excel_suite_name'");
            
            $count = 0;
            $suite_id = 0;
            while($suites = mysql_fetch_assoc($query_testsuitenames)) {
                $query_get_project = mysql_query("SELECT _id FROM (SELECT @r AS _id, (SELECT @r := parent_id FROM nodes_hierarchy WHERE id = _id) AS parent FROM ( SELECT @r := $suites[id] ) vars, nodes_hierarchy h WHERE @r IS NOT NULL) AS t WHERE t.parent IS NULL");
                $res_get_project = mysql_fetch_assoc($query_get_project);
                if(count($res_get_project) == 1) {
                    if($res_get_project[_id] == $project_id) {
                        $count++;
                        $suite_id = $suites[id];
                    }
                }
            }
            
            if($count != 1 && $suite_id ==0) {
                echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . $count . " suite name found with name <b>$excel_suite_name</b> </font>\<br\>");
                $test_add_fail++;
                continue;
            }            
            //echo " suite id is " . $suite_id . "\<br\>" ;
            // Get test Suite id - End
            
            $priority_str = $excel_tc_priority;
            $priority_id = 2;
            if (strcasecmp($priority_str,"Low") == 0) $priority_id = 1;
            if (strcasecmp($priority_str,"High") == 0) $priority_id = 3;
                
            $query_test = mysql_query("SELECT DISTINCT NH_TCASE.id,NH_TCASE.name,NH_TCASE_PARENT.id AS parent_id, NH_TCASE_PARENT.name AS tsuite_name, TCV.version, TCV.tc_external_id, TCV.id AS tcversion_id FROM nodes_hierarchy NH_TCASE, nodes_hierarchy NH_TCASE_PARENT, nodes_hierarchy NH_TCVERSIONS, tcversions TCV  WHERE NH_TCASE.node_type_id = 3 AND NH_TCASE.name = '$excel_tc_title' AND TCV.id=NH_TCVERSIONS.id AND NH_TCVERSIONS.parent_id=NH_TCASE.id AND NH_TCASE_PARENT.id=NH_TCASE.parent_id AND NH_TCASE_PARENT.id = $suite_id AND NH_TCASE_PARENT.node_type_id = 2  ORDER BY TCV.version desc LIMIT 1");
            if (mysql_errno()) { 
                echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\<br\>When selecting suite id\<br\>"; 
                $test_add_fail++;
                continue;
            }
            $res_test = mysql_fetch_array($query_test);
            
            if(count($res_test)>1)    
            {
                //Test case already exists in the test suite, then we need to just update it
                $testcase_id = $res_test["id"];
                $testcase_version_id = $res_test["tcversion_id"];                
                
                mysql_query("UPDATE tcversions SET importance = $priority_id, summary = '".mysql_real_escape_string(str_replace("\n", "<p>", $excel_tc_summary))."', preconditions = '".mysql_real_escape_string(str_replace("\n", "<p>", $excel_pre_condition))."' WHERE id = $testcase_version_id");
                if (mysql_errno()) { 
                    echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\<br\>When UPDATE tcversions for row $rownum ...\<br\>"; 
                    $test_add_fail++;
                    continue;
                }
                
                if($excel_tc_step||$excel_tc_result)
                {
                    $query_test_step_parent = mysql_query("SELECT TCSTEPS.id, MAX(TCSTEPS.step_number) AS max_step FROM tcsteps TCSTEPS JOIN nodes_hierarchy NH_STEPS ON NH_STEPS.id = TCSTEPS.id WHERE NH_STEPS.parent_id=$testcase_version_id");
                    if (mysql_errno()) { 
                        echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\<br\>When select test case steps for row $rownum ...\<br\>";            
                    }
                    else {
                        $res_test_step_parent = mysql_fetch_array($query_test_step_parent);
                        mysql_query("UPDATE tcsteps SET actions = '".mysql_real_escape_string(str_replace("\n", "<p>", $excel_tc_step))."', expected_results = '".mysql_real_escape_string(str_replace("\n", "<p>", $excel_tc_result))."' WHERE id = $res_test_step_parent[id]");
                        if (mysql_errno()) { 
                            echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\<br\>When UPDATE test case steps for row $rownum ...\<br\>"; 
                        }
                    }
                }
                
                if($excel_tc_keywords)
                {
                    //First remove all existing keywords of this test and then add again
                    mysql_query("DELETE from testcase_keywords where testcase_id = $testcase_id");
                    $keywords = explode(',',mysql_real_escape_string($excel_tc_keywords));
                    foreach ($keywords as $keyword) {
                        $key = trim($keyword);
                        $query_keyword = mysql_query("SELECT id FROM keywords WHERE keyword='$key'");
                        $res_keyword = mysql_fetch_assoc($query_keyword);
                        $keyword_id = $res_keyword["id"];
                        if($keyword_id){
                            mysql_query("INSERT INTO testcase_keywords (testcase_id, keyword_id) VALUES ($testcase_id,$keyword_id)");
                        }
                    }
                }
                $test_update++;
            }
            else  { 
                //This is a new test and we need to add it in test suite
                //*** Start Transaction ***//  
                mysql_query("BEGIN"); 
                $objQuery1 = mysql_query("INSERT INTO nodes_hierarchy (parent_id,node_type_id,node_order,name) VALUES ($suite_id,3,100,'".mysql_real_escape_string($excel_tc_title)."')");
                $case_id = mysql_insert_id();
                $objQuery2 = mysql_query("INSERT INTO nodes_hierarchy (parent_id,node_type_id,node_order) VALUES ($case_id,4,0)"); 
                $case_des_id = mysql_insert_id();
                
                $objQuery3 = mysql_query("INSERT INTO tcversions (id,importance,summary,preconditions) VALUES ($case_des_id, $priority_id,'".mysql_real_escape_string(str_replace("\n", "<p>", $excel_tc_summary))."','".mysql_real_escape_string(str_replace("\n", "<p>", $excel_pre_condition))."')");
                
                if(($objQuery1) and ($objQuery2) and ($objQuery3))  {  
                    //*** Commit Transaction ***//  
                    mysql_query("COMMIT");  
                }  
                else  {  
                    //*** RollBack Transaction ***//  
                    mysql_query("ROLLBACK");  
                    echo "ERROR :: Rollbacked transaction of new tests for row $rownum ...";
                    $test_add_fail++;
                    continue;
                }  
                                                        
                if($excel_tc_step||$excel_tc_result)
                {
                    //*** Start Transaction ***//  
                    mysql_query("BEGIN");
                    $objQuery1 = mysql_query("INSERT INTO nodes_hierarchy (parent_id,node_type_id,node_order) VALUES ($case_des_id,9,0)");
                    $case_step_id = mysql_insert_id(); 
                    $objQuery2 = mysql_query("INSERT INTO tcsteps (id,actions,expected_results) VALUES ($case_step_id,'".mysql_real_escape_string(str_replace("\n", "<p>", $excel_tc_step))."','".mysql_real_escape_string(str_replace("\n", "<p>", $excel_tc_result))."')");
                    if(($objQuery1) and ($objQuery2))  
                    {  
                        //*** Commit Transaction ***//  
                        mysql_query("COMMIT");  
                    }  
                    else  
                    {  
                        //*** RollBack Transaction ***//  
                        mysql_query("ROLLBACK");  
                        echo "ERROR :: Rollbacked transaction of test step updation for row $rownum ...";                        
                    } 
                }
                
                if($excel_tc_keywords)
                {
                    $keywords = explode(',',mysql_real_escape_string($excel_tc_keywords));
                    foreach ($keywords as $keyword) {
                        $key = trim($keyword);
                        $query_keyword = mysql_query("SELECT id FROM keywords WHERE keyword='$key'");
                        $res_keyword = mysql_fetch_assoc($query_keyword);
                        $keyword_id = $res_keyword["id"];
                        if($keyword_id){
                            mysql_query("INSERT INTO testcase_keywords (testcase_id, keyword_id) VALUES ($case_id,$keyword_id)");
                        }
                    }
                }
            $test_new++;
            }
        }
    }    
    $case_count = $test_new + $test_update;    
    echo "<font color=#993300>Total <b>$case_count</b> test cases updated successfully.</font>\<br\>";
    echo "<font color=#0000ff>New tests : <b>$test_new</b></font>\<br\>";
    echo "<font color=#0000ff>Tests updated : <b>$test_update</b> </font>\<br\>";
    echo "<font color=#0000ff>Tests update failed: <b>$test_add_fail</b> </font>\<br\>";
}
?>
<!DOCTYPE unspecified PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Testlink XLS Uploader</title>
</head>
<body>
<form name="frm" action="" method="post" enctype="multipart/form-data">

<table>
<tr><td>Use this page to import test cases from Excel sheet to Testlink, Excel file should have following columns:</td></tr>
</table>

<table border="1">
<tr>
<th>Test Project Name from Testlink</th>
<th>Test Suite Name from Testlink</th>
<th>Test Case Title</th>
<th>Test Case Summary</th>
<th>Pre Conditions</th>
<th>Step Actions</th>
<th>Expected Results</th>
<th>Priority</th>
<th>Keywords</th>
<th>Test Status</th>
<th>Testplan Name from Testlink</th>
<th>Build Name from Testlink</th>
<th>Comments (can be used to specify Bug#)</th>
<th>Username (Testlink login name)</th>
</tr>
</table>

<p>

<table>
<tr>
<th align="left">NOTE:</th>
</tr>
<tr>
<td>
<ol>
<li>Tests should be in the first sheet of the excel file</li>
<li>First row will be ignored as it will have column headers</li>
<li>Test Project name should exactly match with Testlink project name in which you want to upload your tests. </li>
<li>Test Suite Title name should exactly match with Testlink test suite name in which you want to upload your tests. </li>
<li>Test Case Title is the title which will be used to list test in the testlink tree</li>
<li>Multiple keywords are supported (comma separated)</li>
<li>Priority column may have values Low/Medium/High. If no value (or wrong value) is specified, test will be assigned Medium priority.</li>
<li>Test Status, Test Plan, Build Name, Comments and User Name columns are ignored as they are used to update test results</li>
<li>If test case title already exists for the given suite in testlink, new test will not be added, existing will be updated</li>
</ol>
</td>
</tr>
</table>

<table>
<tr>
    <td><b>Testlink XLS Uploader:</b></td>
</tr>
<tr>
    <td><input type="file" name="xls_file" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href="pub_dashboard.xls">Sample file format for testcase</a> </td>
</tr>
<tr>
    <td><input type="submit" name="submit" value="submit" /> </td>
</tr>
<tr>
    <td></td>
</tr>
</table>
</form>
</body>
</html>

Setup Test case management tool - TestLink

2011-07-09T03:24:00.002+05:30

These days I am working on introducing TestLink as test case management tool. Tetslink have few software pre-requisites, here is the step by step guide to setup TestLink for your team.

1. Download and install Apache Web Server
2. Download and install PHP
3. Configure Apache
4. Download and Install MySQL server
5. Download and install TestLink
6. Configure TestLink

Detailed installation instruction for step 1-4 can be found @ http://webdevcodex.com/tutorial-installing-apache2-php5-mysql5-phpmyadmin3-windows-7-vista/

Download and unzip testlink in apache web directory and open http://localhost/Tetslink. If it is the first time installation it will open installation page. Follow the instructions and Testlnk is installed.

Once you open the testlink login page or login, you might get following error:

"PHP Warning: strftime(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function"

To resolve this issue, edit <php install dir>\php.ini and add "date.timezone = PRC"

Sometimes you also get warning message in UI, to eliminate those warning message from UI

make change in <Testlink install dir>\config.inc.php and change "$tlCfg->config_check_warning_mode = 'SILENT';"

Oracle: Join Example

2011-02-28T15:06:00.003+05:30

I have a table TEST in oracle which stores data for some users on monthly basis, I need to fetch the data in such a way that "all data for current month with the entry of their last month record for a particular column." Here is the table details and query:

CREATE TABLE DATETEST (ID NUMBER(4) NOT NULL, NAME VARCHAR2(10), BUDGET NUMBER(7), TDATE DATE);

INSERT INTO DATETEST VALUES (1, 'test1', 100, TO_DATE('01-FEB-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (2, 'test2', 200, TO_DATE('01-FEB-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (3, 'test3', 300, TO_DATE('01-FEB-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (4, 'test4', 200, TO_DATE('01-FEB-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (5, 'test5', 300, TO_DATE('01-FEB-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (1, 'test1', 200, TO_DATE('01-JAN-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (2, 'test2', 100, TO_DATE('01-JAN-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (3, 'test3', 400, TO_DATE('01-JAN-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (2, 'test2', 200, TO_DATE('01-DEC-2010', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (3, 'test3', 100, TO_DATE('01-DEC-2010', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (4, 'test4', 100, TO_DATE('01-DEC-2010', 'DD-MON-YYYY'));

select * from DATETEST;

# This Month's entry
select * from DATETEST where TDATE = ADD_MONTHS(trunc(sysdate,'MM'),0);

# Last Month's entry
select * from DATETEST where TDATE = ADD_MONTHS(trunc(sysdate,'MM'),-1);

# Common users for current and last month and their budgets for current and last months
select A.ID, NAME, A.BUDGET, B.BUDGET from DATETEST A,
(select ID, BUDGET from DATETEST where TDATE = ADD_MONTHS(trunc(sysdate,'MM'),-1)) B
where A.ID=B.ID and TDATE = ADD_MONTHS(trunc(sysdate,'MM'),0) ;

# Query which displays data for all users with current month entry and thier last month record
select A.ID, NAME, A.BUDGET, NVL (B.BUDGET, 0)
from DATETEST A left join
(select ID, BUDGET from DATETEST where TDATE = ADD_MONTHS(trunc(sysdate,'MM'),-1)) B
on A.ID=B.ID where TDATE = ADD_MONTHS(trunc(sysdate,'MM'),0) order by id asc;

sed: How to Escape Forward Slash

2011-02-22T12:28:00.003+05:30

As you may know, sed performs a search and replace with this command:

sed s/seacrh pattern/replacement pattern/g file list

forward slash ("/") is used as part of the regular expression to separate the command options and search text. What if your search/replace pattern itself includes forward slash character.

I wanted to replace /usr/local/bin to /usr/local/dev, I escaped forward slashes with backward slash and my sed command looked like this:

sed -i -e 's/\/usr\/local\/bin/\/usr\/local\/dev/g' testfile.txt

Unfortunately, sed didn’t work and gave me this error:

sed: -e expression #1, char 27: unknown option to `s'

Then On googling, I discovered an exciting thing about sed. In the regular expression, it’s not necessary to delimit the find and replace texts and search options with the forward slash ‘/’ character. We can use any character to delimit the expression.

So I changed my command a bit, used @ character to delimit the expression and then it worked.

sed -i -e s@\/usr\/local\/bin@\/usr\/local\/dev@g testfile.txt

use variable refeences in awk

2010-12-22T17:56:00.002+05:30

I need to compare 1st column of user.csv with value, it works well with following

value=2
awk -F':' '$1==$value {print}' user.csv

now I have a case where column number is also a variable for me, in that case, the solution would be:

value=2
column=1
awk -F':' ''\$$column==$value' {print}' filename

1. You need to put backslash (\) before first $
2. You need to put comparison in single quote

Retrieve web pages using perl

2010-09-27T15:20:00.004+05:30

Here is the sample script to fetch web pages using perl.

#!/usr/local/bin/perl -w
use strict;
use LWP 5.64;
use URI;

my $browser = LWP::UserAgent->new;
$browser->cookie_jar({});

my @browserHeaders = (
'User-Agent' => 'Mozilla/4.0; (compatible; MSIE 6.0; Windows NT 5.1; en-US)',
'Accept-Language' => 'en-US',
);

my $response;
$response = $browser->get("http://vishnuagrawal.blogspot.com",@browserHeaders);

if ($response->is_success) {
print $response->content;
} else {
print $response->error_as_HTML, "\n";
print $response->status_line, "\n";
}

print $response->code(), "\n";
print $response->message() , "\n";
print $response->header('content-type'), "\n";

my $url = URI->new("http://www.softwareqa.com");
$response = $browser->get($url,@browserHeaders);

print $response->content;

Find the day diff between two days

2010-09-24T10:31:00.007+05:30

Following is the shell script solution for getting number of days between two dates:

#/bin/sh
date1=`date +%s -d $1`
date2=`date +%s -d $2`
diff=`expr $date2 - $date1`
diff_days=`expr $diff / $ 60 '*' 60 '*' 24 $`
echo $diff_days

[vishnu@ shell]# ./daydiff.sh 2010-08-02 2010-09-15
44
[vishnu@ shell]#

Here is the perl solution for the same:

#!/bin/perl
use strict;
use warnings;
use Time::Local;

my $date1 = $ARGV[0];
my $date2 = $ARGV[1];

my @formatted_date1 = split(/-/, $date1);
my @formatted_date2 = split(/-/, $date2);

my $local_date1 = timelocal(0, 0, 0, $formatted_date1[2], $formatted_date1[1], $formatted_date1[0]);
my $local_date2 = timelocal(0, 0, 0, $formatted_date2[2], $formatted_date2[1], $formatted_date2[0]);

my $diffSeconds = $local_date2- $local_date1;
my $diffDays = $diffSeconds / (60 * 60 * 24);
print "Day diff :: $diffDays\n";

[vishnu@ shell]# ./daydiff.pl 2010-08-02 2010-09-15
Day diff :: 43
[vishnu@ shell]#

Here is modified version of above shell script, if you want to find out time difference in seconds

#!/bin/bash

d1=`date +%s -d "$1"`
d2=`date +%s -d "$2"`
((diff_sec=d2-d1))
echo "Diff Seconds : $diff_sec"

[vishnu@ shell]#./date.sh "2011-07-19 22:44:34" "2011-07-20 02:04:14"
Diff Seconds : 11980
[vishnu@ shell]#

Dump cpu and memory usage of a process

2010-09-08T10:56:00.004+05:30

Here is the automated shell script which can dump the memory and cpu usage of a process. Helpful in cases when you want to monitor a process for a long time.

#!/bin/bash

if [ "$#" -ne 1 ]; then
echo "Usage ... [$0 <pid>]"
exit
fi

pid=$1

# maxRunTime is used to control the script, so that it run only for specified number of seconds
maxRunTime=72000
# sleepTime is used to sleep for specified seconds, to take the memory dump again
sleepTime=120

logFile=${pid}_process.log
commandFile=${pid}_command.log

`ptree $pid >> $commandFile`;
`pargs $pid >> $commandFile`;

currentRunTime=0

while [ $currentRunTime -lt $maxRunTime ]
do
time=`date +%F::%X | tr -d '[\n]'`
line=`ps -eo pid,pcpu,vsz | grep $pid`;
echo "$time $line" >> $logFile
sleep $sleepTime
currentRunTime=`expr $currentRunTime + $sleepTime`
echo "Execution Time : ${currentRunTime} seconds [max ${maxRunTime}]"
done;

Flash Player debug version on New Google Chrome

2010-08-04T10:38:00.002+05:30

If you install the latest version of Google Chrome (mine is 5.0.375.125), flash player debug version do not work in it; though the same debug player is working fine in Firefox3. Reason is that Chrome now comes pre-installed with the Flash player which get used by default, despite whatever other version you might have installed. It always use its original version. To change this, follow below steps:

- Open Google Chrome
- Type about:plugins in the url, - It will show the list of all the plugins
- Click on the "Disable" link of "Shockwave Flash" plugin

It will not disable flash, but just the built in version. Now the chrome will use the system-wide flash player version (probably the debug version).

Unix: Get a file's name, extension and directory name

2010-02-24T08:13:00.003+05:30

file="/usr/local/bin/test.sh"

# get extension; everything after last '.'
ext=${file##*.}
# will return 'sh'

# everything after last '/'
basename=${file##*/}
# will return 'test.sh', similar to 'basename'

# everything before last '/'
basename=${file%/*}
# will return '/usr/local/bin', similar to 'dirname'

# back one directory
basename=${file%/*/*}
# will return '/usr/local'

Share files between local and remote computer using Remote Desktop Connection

2010-01-16T11:06:00.001+05:30

One of the most common tasks you'll need to do when using an Remote Desktop Connection is transfer a file from your local machine to the remote machine that you're logged into. During a Remote Desktop session, you can gain easy access to your local disk drives on the remote computer so that you can transfer files between these two systems in the same way that you copy files from a network share.

1. Click Start ->All Programs->Accessories->Remote Desktop Connection.
2. Click Options, and then click the Local Resources tab.
3. Click Disk Drives and then click Connect.
4. When you get logged into the remote box, open My Computer and you'll see that all the local drives will show up as mapped disk drives.
5. Now you can transfer your file as you do in local system.

Remove a file whose name begins with "-"

2010-01-14T23:09:00.002+05:30

Due to a bug in our product the log file was being created with the name "-server.log" (instead of "host-server.log"). Once the issue was fixed, i was trying to remove the file. Tried single quote/double quote/escape, but nothing worked. Finally after googling, found solution for it. Since the file name begins with the "-", all the unix commands treats the file name itself as a parameter to the command. To make it work put -- or ./ before the file name.

example:
rm -- -server.log
rm ./-server.log

Paragliding in Manali

2010-01-07T21:06:00.003+05:30

Last Christmas I went on vacation in Manali (Himachal) with my friends. One of the adventure we planned for the trip was Paragliding and we all were excited about it. By googling, we found out that Paragliding could be done in Solang Valley of Manali.

After reaching Manali, we enquired about the paragliding packages in Solang Valley. Most of the tour agents offered us 3 packages Short, Fly, Mediaum Fly and High fly. While roaming into Manali market, we noticed a tour agency which was offering paragliding in comaprtive cheaper rates. We talked to sales guy of that agency and he told us that they do offer pragliding in Naggar Village (which is 20 kms away from Manali). After having talk with him, we found it to be excited (though we were not sure, how would it be as we hven't heard of it before but we thought of giving it a try). Finally we gave him advance payment. He asked us to reach a particular destination (Heritage Hotel) which was 16 km from the Manali, from where their guys were supposed to pick us.

After half an hour we reached the Heritage hotel where one guy was waiting for us. We parked our car there and we sit in the Jeep which that guy was driving. REAL ADVENTURE starts from here.

Next 4 km was a mountain road with blind curves and had a road space only for a single Car/jeep. That guy started the Jeep and he was driving on that ride like he is running a ferrari on that road . We asked him that Jeep has Brake, you can apply them, he answered that if he would apply break then how would the jeep drive -:). Moreover, after half of our drive, we knew that the jeep do not have power steering. The experince in this 4 km drive was hilarious and full of adventure :). Finally we reached a point from where we have to trek to a top of mountain from where our glides were suppose to take off. Ohhh My GOD, that was again a surprise for us... we had to treck for around 2 kms. to reach at peak of the hill. No problem as we all like the trekking .. we started treking and reached on top in around 25 mins.

We were around 2500ft above from the ground and the view was fantastic from there. We had a rest there for some time as we were tired of the trekking. After some time the guys were ready with the glide and I was the first one to start. The pilot prepared me for the drive (helmet and glide dress which was tied with the glides), then Finally it was time to take off. We (me and my pilot) have to run for few distance and then jump from the hill.

"I ran slowly against the wind and suddenly, i felt an elevating force. In an instant, my feet were no longer touching the ground and i went into gentle decent.... The act of flying gives one sensations that are extraordinary and absolutely indescribable"

I was roaming in air around 2500 ft above from the ground. The surrounding hills and ground looks amazing from the top. After around 10-12 mins we landed in fields and this whole experience was amazing.

After a day, we went to Solang Valley for sight seeing. We saw the guys doing paragliding there. We didn't like it as the take off height was so short and the duration was only for couple of minutes. For us it was like swimming in a pond while we already swim in the sea :-)

Guys, if you want to do real paragliding, then you should go for this one.

Using variable with sed inside a shell script

2009-10-24T22:39:00.002+05:30

For using sed inside a shell script, The variable should be in "double quotes" and the command in 'single quotes'. Following is the example

#!/bin/bash

usage () {
echo "$0 -s < name> -r <replace name> -f <file name>"
exit
}

while getopts s:r:f: option
do
case "$option" in
s) search="$OPTARG";;
r) replace="$OPTARG";;
f) filename="$OPTARG";;
\?) usage
esac
done

sed -i 's/'"$search"'/'"$replace"'/g' "$filename"

https redirection handling in httpunit

2009-10-03T16:23:00.002+05:30

In httpunit when you are requesting a http page and if the page internally redirects to a https page, httpunit throws following error and response is not received:

sun.security.validator.ValidatorException: PKIX path validation failed: java.security.cert.CertPathValidatorException: timestamp check failed

To resolve this error, add following code in your httpunit program.

import javax.net.ssl.HostnameVerifier;
import javax.net.ssl.HttpsURLConnection;
import javax.net.ssl.SSLContext;
import javax.net.ssl.SSLSession;
import javax.net.ssl.TrustManager;
import javax.net.ssl.X509TrustManager;
import java.security.cert.*;

try{
SSLContext context = SSLContext.getInstance("SSLv3");
TrustManager[] trustManagerArray = { new NullX509TrustManager() };
context.init(null, trustManagerArray, null);
HttpsURLConnection.setDefaultSSLSocketFactory(context.getSocketFactory());
HttpsURLConnection.setDefaultHostnameVerifier(new NullHostnameVerifier());
}catch(Exception e) {
e.printStackTrace();
}

class NullX509TrustManager implements X509TrustManager {
/* Implements all methods, keeping them empty or returning null */
public X509Certificate[] getAcceptedIssuers() {
return null;
}

public void checkClientTrusted(X509Certificate[] chain, String authType) {

}

public void checkServerTrusted(X509Certificate[] chain, String authType) {

}
}

class NullHostnameVerifier implements HostnameVerifier {
public boolean verify(String hostname, SSLSession session) {
return true;
}
}

Getting thread dump for java application

2009-10-03T15:57:00.006+05:30

When an java application server freezes or becomes non-responsive, it is recommended to generate a thread dump for the application. A thread dump is a user-friendly snapshot of the threads and monitors in a Java Virtual Machine (JVM). A thread dump can range from fifty lines to thousands of lines of diagnostics depending on how complex your application is.

On UNIX platforms you can send a signal to a program by using the kill command. This is the quit signal, which is handled by the JVM. On Solaris you can use the command kill -QUIT process_id, where process_id is the process id of your Java program.

run the command kill -QUIT <pid> or kill -3 <pid> on shell which will cause the thread dump to be displayed your application console. Alternatively you can enter the key sequence <CTRL> \ in the window where the Java program was started. This is why it's critical to redirect standard output & standard error to a log file so it is captured when you need a thread dump.

On Windows platform, enter the key sequence <CTRL> <break> for the thread dump of your application.

Thread States :: The key used for the thread states in Thread dump is:
R ==> Running or runnable thread
S ==> Suspended thread
CW ==>Thread waiting on a condition variable
MW ==> Thread waiting on a monitor lock
MS ==> Thread suspended waiting on a monitor lock

Run sql query from command line

2009-07-20T20:16:00.002+05:30

We all know to run sql scripts (.sql files) from command prompt but sometimes we need to run a sql query from command prompt. It is sometimes necessary when we have to fetch a query result from a script (shell/perl) and process it. By following way, we can run a sql query from command prompt.

echo "select * from dual;" | sqlplus -S user/pwd\@sid

Send Email from perl script

2009-07-20T20:02:00.002+05:30

Sometimes we do not have enough privileges to install perl modules on the host system and we have to work with ideal installation of perl. In that case, simpler way to send emails using perl is to use sendmail command. Following function can be used to send emails in perl.

sub sendEmail
{
($from, $to, $subject, $message) = @_;
my $sendmail = '/usr/lib/sendmail';
open(MAIL, "|$sendmail -oi -t");
print MAIL "From: $from\n";
print MAIL "To: $to\n";
print MAIL "Subject: $subject\n\n";
print MAIL "$message\n";
close(MAIL);
}

above function can be used as below:

sendEmail("vishnu\@test.com", "vishnu\@mytest.com", "Test sendEmail.", "Testing sendEemail function.");

Recover Photos from SD card

2009-06-25T22:32:00.001+05:30

Recently my family went for a annual trip and accidentally removed the photos from my digital camera. They asked me if somehow we could recover those photos. As obvious, i did some googling and found out that there are many software available which can recover data from your SD card (most of them are paid and i was looking for any free alternative :) ). Finally i got this one PhotoRec (http://www.cgsecurity.org/wiki/PhotoRec). It do not require any installation, just download the software and start using it. (obviously you should have SD card reader in your system). Thanks to it, I could recover all photos.

While googling, i found other free software as well but didn't give them try, one of them was Mjm (http://www.mjm.co.uk/freephotorecoverysoftware.html)

Building brand value at workplace

2009-05-31T21:07:00.003+05:30

Recently read an article from Delhi Times Ascent. Very inspiring and true

There are often times when an employee may wonder, how do i stand out in a team? And experts say that by simply completing tasks assigned on time or working hard for longer hours, may not necessarily be the answer. They say it takes more than just one's work and job profile to be outstanding. This is where the role of crafting your own "USP"(Unique Selling Proposition) comes into the picture.

Crafting your USP:
According to experts, the first thing to do while brand-building is to identify the qualities and characteristics that give you an edge over the rest. Understand your strengths/abilities and use it towards your advantage at the workplace. Maintaining good relations with people whom you deal with will also add to your brand value. Self-branding is no easy task. It comprises of identifying your best personality traits and displaying it from time to time through enhanced performance and higher productivity. This will not only get you noticed but will also give you an edge over your colleagues.

Build your brand:
- Be a great team-mate and a supportive colleague.
- Be passionate about your job and make others know that you are excellent in your particular task/job.
- Finish the tasks assigned to you on time with exceptional quality.
- Be proactive and discuss your views on improving things with your senior officials.
- Bring in innovations into your project/tasks to make it more efficient.

Comparing Excel Files

2009-05-17T23:44:00.002+05:30

While testing any reporting application, one comes under situations when he/she have to compare two excel files. If the excel sheets are small, its easier to compare them manually but as long the sheets becomes large, you need some automated way to compare those.
Earlier, i used to export those files as csv and then comparing them using perl scripts but it was an overhead to exporting and then comparing so i thought of doing something within the excel sheets itself. After googling, i found that it can be done by using Excel Macros. I did try that, but found that you need to open those excel files in Microsoft Excel and then apply the macro, which again i didn't like as it require to open MS Excel. I was interested to have a program which i can run from command prompt. Finally i got my solution. I converted the above macro as a standalone vb script program and used it from command prompt.
The great benefit with this script is that i can take control of displaying the diff results (currently, i am highlighting the rows in the excel sheet itself.)

to run this program, open command prompt and run following command:

C:\> cscript /nologo ExcelCompare.vbs <ExcelFile1> <ExcelFile2>

Following is the program:

Publish at Scribd or explore others: Internet & Technolog Research excel vbscript

Run a Visual Basic Script Program from a command prompt

2009-05-17T12:04:00.005+05:30

A Visual Basic script program can be run from a DOS prompt. cscript command can be used for this purpose.

C:\>cscript <pathToVBScriptFile>

by default cscript command displays the Microsoft Header but this can be avoided by providing /nologo argument.

Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.

C:\>cscript /nologo <pathToVBScriptFile>

User can also take control to take input and display the output on the console. For that user had to use following options in the VB Script.

dim input
dim output
dim name

set output = wscript.stdout
set input = wscript.stdin

output.write "Enter Your Name : "
name = input.readline

output.writeline "Your Name is " & name

Compare two files line by line

2009-04-27T23:09:00.001+05:30

If your are looking for comparing two files(may be any CSV file) and wanted to find out which liens are common or unique to files. See below script:

Publish at Scribd or explore others: Internet & Technolog Research perl

Flex automation testing with QTP

2009-04-19T19:23:00.003+05:30

QTP can be used to test the Flex application. You need to perform following steps to setup recording a flex application with qtp.

Install QTP 9.2
Install Internet Explorer
Install flash player debug version for IE
Install Flex Builder 3
Install QTP plugin of flex from FLEX_BUILDER3/Installers/QTP_Plugin_Installer
Compile your flex application with automation libraries. It can be done in two ways; either use -include-libraries parameter in Flex Builder3 or use mxmlc ant task. This step is must otherwise QTP will not recognize the flex application
To record a test in QTP, you must compile the application’s SWF file and generate the HTML wrapper files. The main wrapper file defines the application as an object on the page and embeds it so that Flash Player is invoked when the page is requested. You cannot request the application’s SWF file directly. You can write the HTML wrapper yourself, or generate it with the compiler.
Start QTP
QTP wil show the all plugins on startup, select the flex plugin and continue
Start recording for your flex application.

Build FLEX application with automation libraries using Flex3 SDK and ANT

2009-04-19T18:02:00.013+05:30

To test Flex Application with any automated testing tool (like QTP), automation libraries should be included in the application SWF file to make the application testable. Flex Automation libraries are not part of the Flex SDK, these comes with Flex Builder 3. If you are using Flex SDK and ANT as build tool, you need to perform following steps to compile your application.

Copy flex automation libraries (automation.swc, automation_agent.swc, automation_dmv.swc, automation_flashflexkit.swc) from Flex Build 3 installation (/frameworks/libs) to your local directory (let say C:\flex-automation).
Copy automation tool library (qtp.swc in case if you are using QTP as testing tool) in C:\flex-automation
Copy flex agents (automation_agent_rb.swc, automation_rb.swc) in C:/flex-automation/locale/en_US. Flex agent facilitates communication between a flex application and an automation tool.
Copy FLEX_HOME/frameworks/flex-config.xml to flex-config-auto.xml and keep it in same directory (FLEX_HOME/frameworks)
Add following entries in include-libraries section of flex-config-auto.xml
<include-libraries>
<library>C:/flex-automation/automation.swc</library>
<library>C:/flex-automation/automation_agent.swc</library>
<library>C:/flex-automation/automation_dmv.swc</library>
<library>C:/flex-automation/automation_flashflexkit.swc</library>
<library>C:/flex-automation/qtp.swc</library>
</include-libraries>
In mxmlc task of ant build file specify load-config path as
<load-config filename="FLEX_HOME/frameworks/flex-config-auto.xml"/>
Add compiler-library-path in mxmlc task
<compiler.library-path dir="C:/flex-automation" append="true">
<include name="locale/en_US" />
</compiler.library-path>

so your mxmlc task would look like this:

<mxmlc file="${APP_ROOT}/Main.mxml" output="${build.dir}/main.swf">
<load-config filename="${FLEX_HOME}/frameworks/flex-config-auto.xml"/>
<source-path path-element="${FLEX_HOME}/frameworks"/>
<compiler.library-path dir="${FLEX_HOME}/frameworks" append="true">
<include name="libs" />
<include name="locale/en_US" />
</compiler.library-path>
<compiler.library-path dir="${FLEX_AUTOMATION_HOME}" append="true">
<include name="locale/en_US" />
</compiler.library-path>
</mxmlc>

mxmlc ANT task comes with flexbuilder3 and flex sdk. In flex builder, the location of task is : _FLEX_BUILDER_INSTALL_LOCATION_\sdks\3.0.0\ant\flexTasks.jar

To use this ant task in ant build file use ant taskdef task as below:
< taskdef resource="flexTasks.tasks" classpath="_PATH_TO_TASK_/flexTasks.jar"/>

JMeter: run tests using ANT

2009-04-15T23:53:00.008+05:30

Do you run your test cases in jmeter GUI, i really never like to run my tests in it. I always like to have command line options or preferably ANT. Following are the steps to run any JMeter test cases using ANT:

1. Install ANT and JMeter
2. Copy ant-jmeter.jar ant ANT_HOME/lib directory.
3. Create a common.xml file (as mentioned below) which can be referred by any Jmeter Test plan.
4. Create a build.xml for every test plan, which refers to common.xml
5. Use xslt task to create the html report of the test cases.

common.xml

<project name="jmeter-test-common" default="run" basedir=".">

<property name="jmeter.dir" value="C:/Jmeter"/>
<taskdef name="jmeter" classname="org.programmerplanet.ant.taskdefs.jmeter.JMeterTask"/>

<target name="run-test">

<echo message="${jmeter.dir}"/>
<jmeter jmeterhome="${jmeter.dir}"
testplan="${test.dir}/${jmeter.file}.jmx"
resultlog="${test.dir}/${jmeter.file}.jtl">
<property name="jmeter.save.saveservice.output_format" value="xml"/>
<property name="jmeter.save.saveservice.assertion_results" value="all"/>
<property name="request.threads" value="${request.threads}"/>
<property name="request.loop" value="${request.loop}"/>
<jvmarg value="-Duser.dir=${test.dir}"/>
</jmeter>

<echo message="Generating Report ..."/>

<xslt in="${test.dir}/${jmeter.file}.jtl"
out="${test.dir}/${jmeter.file}_summary.html"
style="${jmeter.dir}/extras/jmeter-results-report_21.xsl"/>

<xslt in="${test.dir}/${jmeter.file}.jtl"
out="${test.dir}/${jmeter.file}_detailed.html"
style="${jmeter.dir}/extras/jmeter-results-detail-report_21.xsl"/>
</target>
</project>

build.xml

<project name="my-test" default="run" basedir=".">

<import file="${ant.file.my-test}/common.xml"/>

<target name="run">
<antcall target="run-test">
<param name="jmeter.file" value="LOGIN"/>

<param name="test.dir" value="${ant.file}/.."/>
<param name="request.threads" value="5"/>
<param name="request.loop" value="1"/>
</antcall>
</target>

</project>

JMeter: Using Variables

2009-04-15T23:24:00.004+05:30

In JMeter value of user.dir varaible is set to jmeter/bin directory. Jmeter saves and loads the test script from user.dir, so by default it is jmeter/bin. But what if your test scripts are saved in directory other than jmeter/bin which is the case most of the time.
We can override the value of this varaible in jmeter/bin/jmeter.properties as mentioned below:
user.dir=C:/perftest/mytest
Now the jmeter wil look for test script in C:/perftest/mytest instead of its default location. We can also specify this varaibale as jvm arg; if we are running jmeter from command prompt. (-Duser.dir=C:/perftest/mytest)

Accessing user.dir value in Jmeter test plan
In Test Plan -> User Defined Variables add a new variable
Name: userdir
Value: ${__property(user.dir)}

Now this userdir varaible can be used in jmeter test plan as location of the test script (${userdir}).

Reading values from a file
${__CSVRead(${userdir}/env.txt,0)} -> It will read the first column of the first line from the env.txt file

__P
This is a property function which is intended for use with properties defined on the command line. Unlike the __property function, there is no option to save the value in a variable, and if no default value is supplied, it is assumed to be 1.
${__P(request.threads)} -> return the value of request.threads
${__P(request.loop)} -> return the value of request.loops
${__javaScript(${__P(request.threads)}*2)} -> multiply the value of request.threads by 2

Use JMeter to load test FLEX Applications

2009-04-15T23:00:00.005+05:30

Flex applications use a binary protocol called AMF (Action Message Format) that they use to remotely communicate with the server. The easiest way to use JMeter with a Flex application is to use HTTP proxy server.

- Create a New Test Plan
- Right Click on Test Plan, Add -> Thread Group
- Right Click on Thread Group, Add -> Config Element -> HTTP Request Defaults
- Select 'HTTP Request Defaults'
- Specify Server Name or IP, for eg. www.mysite.com
- Specify Port number, eg. 8888
- Specify protocol, i.e. http
- Specify Path, foe eg. /main/index.html
- Select Work Bench
- Right Click on Work bench, Add -> Non-Test Elements -> HTTP Proxy Server
- Select 'HTTP Proxy Server'
- Specify port number for proxy (default is 8080)
- Set Target Controller to 'Test Plan -> Thread Group'
- Set inlude URL patterns , .* to include all
- Set exclude URL patterns, for eg. , .*\.gif, .*\.png, .*\.css, .*\.js
- Open Internet Explorer
- Goto Tools -> Internet Options -> Connections -> LAN settings
- In Proxy Server panel, specify address as 'localhost' and port number (which is specified in
meter's HTTP proxy server, i.e. 8080)
- Select 'HTTP Proxy Server'
- Click on Start
- Open IE and open URL 'http://www.mysite.com:8888/main/index.html'
- As soon as you visit the URL jmeter starts the recording, and you can see all the requests in
METER
- For AMF requests (for Adobe FLEX), request is saved as file with extension '.binary' in the bin
irectory of jmeter
- Click on Stop button of 'HTTP Proxy Server' to stop the recording.

Test Plan is ready. You can modify it now according to your requirements.

Proxy Server setup document from Apache:
http://jakarta.apache.org/jmeter/usermanual/jmeter_proxy_step_by_step.pdf

PERL round function

2009-03-22T21:30:00.002+05:30

Following is the round function in PERL, which you can put into your script and actually call to round numbers instead of cutting them with ceil or cut.

In the following round function, you can specify the number to round and the number of digits to show after the decimal.

sub round {
 my $number = shift || 0;
 my $dec = 10 ** (shift || 0);
 return int( $dec * $number + .5 * ($number <=> 0)) / $dec;
}

$result = round(123.4567,3);

it would return 123.457

About url in firefox

2009-03-22T21:12:00.004+05:30

There are few special URLs which begins with about: that you can type into the firefox location bar:

about: => The same page as Help -> About
about:blank => A blank page
about:cache => Displays cache statistics and directory location
about:config => GUI for modifying user preferences
about:plugins => List all plugins
about:logo => Displays the firefox logo

There is an extension available for firefox to save advanced preferences. gui:config makes it easier to change preferences that can only be found in the “about:config”. This add on can be downloaded from https://addons.mozilla.org/en-US/firefox/addon/5523

TOAD: Tips and Tricks

2009-03-20T21:53:00.002+05:30

Tips for TOAD's sql editor

Tired of typing long column/table name while writing a sql query? Use Auto Complete feature of TOAD. Type a part of the table/column name and then press CTRL + . (period). TOAD will automatically identify the name of the table/column and fill in the rest of the word. If there is more than one option available then TOAD will list all the available options and you can select from there.
Forgot the queries which you ran in past? In TOAD's sql editor Press ALT + UP/DOWN key and it will show you the past queries (similar to SQL recall feature , F8)
You constantly making spell mistakes while writing queries? Right click in the editor and select “Editing Options”. On this new option screen click on the “Auto Replace” button in the bottom right hand corner. Here you will see a list of the common misspelled words our user’s experience. If there are words you constantly misspell, then you can add them to the list by using the “Add” button. So, now in the editor if you mistype the word “select” as “seelct” … it will automatically fix itself once you hit the spacebar.
Wanted to control number of rows in your grid? Use OCI array size option to tell toad how many rows you want retrieved at a time. If you want to only see first 100 rows of an table, set this option to 100.
Generating Schema Documentation- From the Database menu, select Report -> HTML Schema Doc Generator.

Perl : Get filename or directory name from an absolute path

2008-12-28T19:26:00.004+05:30

Use Perl's File module to get Filename or directory name from an absolute path. Below is an example of how to do this:

#!/usr/bin/perl
use File::Basename;

print getDirPath("/mnt/users/vishnu/blog/PerlPost.html");
print getFileNameWithoutExt("/mnt/users/vishnu/blog/PerlPost.html");
print getFileName("/mnt/users/vishnu/blog/PerlPost.html");

sub getDirPath() {
return dirname($_[0]);
}

sub getFileNameWithoutExt() {
my $file = basename($_[0]);
$file =~ s/\.[^.]*$//;
return $file;
}

sub getFileName() {
return basename($_[0]);
}

Output of above program would be:
/mnt/users/vishnu/blog
PerlPost
PerlPost.html

Perl error: Can't locate module in @INC

2008-12-20T21:49:00.003+05:30

If you get a Perl error message like Can't locate module.pm in @INC, this message means that the Perl module you're trying to include (like the module named module) can't be found in Perl's include path, which is represented by the variable named @INC.

Perl uses an @INC variable that contains the directories it will search in when you specify that you want to include a module, but you can't look at it like an environment variable. Although Perl doesn't have something like the CLASSPATH environment variable you can easily look at, you can print the @INC variable with a simple one-line command. The following Perl command shows the directories that Perl looks in when it tries to load modules:

perl -e 'print join("\n", @INC);'

Including a perl module your @INC include path

There are different ways by which you can add your perl module in @INC include path
1. Specify it in PERL5LIB environment variable
export PERL5LIB=path

2. Perl takes -I option where you can specify the additional directories where it should search for modules.
perl -I path myscript.pl

3. Modify your Perl program to find the module by adding below line near the top of Perl programs. This simple line of code tells your Perl program to add this directory to it's @INC search directory.
use lib "path"

Perl: FileDiff utility

2008-12-19T21:00:00.005+05:30

I have 2 csv files (pipe seperated), one column (basically an Id field) is common in both the files. I need to compare both the files on the basis of this common field and print the lines which has common Id, Lines with id which is only in file1 and vice-versa. I thought up for writing perl script for this task. See below script.

Publish at Scribd or explore others: perl Technology

Oracle: Binary operations

2008-11-06T19:40:00.003+05:30

Today i faced a situation when i need to do binary operations in Oracle Query. I had no idea how to do it. Obviously Googling worked as always. :-) :-) . Below was the problem:

I need to apply AND operation on a hexadecimal number as below but following query did not work.

select <num> & 0xf from dual;

I found that BITAND function will do this task, so i chnaged above query to as below and it worked.

select BITAND(<num>, 15)+0 from dual;
(here 15 is equivalent to 0xf, Note that BITAND returns a binary integer, so to use it in SQL you will need to convert it to a numeric.)

Similarly, Oracle has BITOR function for OR operation.

AARTI: Shree Google Maharaaj :-)

2008-11-06T11:16:00.002+05:30

Recently i got an email from one of my friends which had Aarti for Google. Though it is in Hindi but i thought it to share with you all. If you know Hindi, then just read it and enjoy .. :) :)

Group rows of a csv file with sum of column values

2008-10-22T10:33:00.004+05:30

You have heard about Group functionality in Microsoft Excel, which group the rows on a common value and does the sum of other field. Same thing can be done in unix using awk very easily. See below example:

[ ~]# cat test.csv
a,5
b,10
a,4
c,8
b,9
b,4
[ ~]# awk -F"," '{sum[$1] += $2 } END { for(n in sum)print n"=" sum[n] }' test.csv
a=9
b=23
c=8

Perl version of above script can be seen here:

AWK examples

2008-10-16T19:39:00.005+05:30

Find number of columns in a pipe seperated csv file
awk -F"|" '{print NF}' vishnu.csv |uniq

Find sum of column of a pipe seperated csv file
awk -F"|" '{sum += $col } END { print sum }' vishnu.csv
awk -F"|" '{sum += $col } END { print sprintf("%.2f",sum) }' vishnu.csv (print float value)

Replace tab character with a pipe symbol in a csv file
awk '{gsub("\t","|");print}' vishnu.csv

print the 5th and 7th columns of csv file if 5th and 7th column values are not same
awk -F"," '{if($5 != $7) {print $5","$7}}' test.csv

print the line of csv file if 5th and 7th column values are same
awk -F"," '{if($5 !== $7) {print}}' test.csv

Puzzle: Elevator problem

2008-10-14T21:00:00.001+05:30

Question: A man lives on the twelfth floor of an apartment building. Every morning he takes the elevator down to the lobby and leaves the building. In the evening, he gets into the elevator, and, if there is someone else in the elevator -- or if it was raining that day -- he goes back to his floor directly. Otherwise, he goes to the tenth floor and walks up two flights of stairs to his apartment.

Answer: The man is a dwarf. He can't reach the upper elevator buttons, but he can ask people to push them for him. He can also push them with his umbrella.

Puzzle: 100 doors in a row

2008-10-14T20:58:00.000+05:30

You have 100 doors in a row that are all initially closed. you make 100 passes by the doors starting with the first door every time. the first time through you visit every door and toggle the door (if the door is closed, you open it, if its open, you close it). the second time you only visit every 2nd door (door #2, #4, #6). the third time, every 3rd door (door #3, #6, #9), etc, until you only visit the 100th door.

for example, after the first pass every door is open. on the second pass you only visit the even doors (2,4,6,8...) so now the even doors are closed and the odd ones are opened. the third time through you will close door 3 (opened from the first pass), open door 6 (closed from the second pass), etc..

question: what state are the doors in after the last pass? which are open which are closed?

Solution: you can figure out that for any given door, say door #42, you will visit it for every divisor it has. so 42 has 1 & 42, 2 & 21, 3 & 14, 6 & 7. so on pass 1 i will open the door, pass 2 i will close it, pass 3 open, pass 6 close, pass 7 open, pass 14 close, pass 21 open, pass 42 close. for every pair of divisors the door will just end up back in its initial state. so you might think that every door will end up closed? well what about door #9. 9 has the divisors 1 & 9, 3 & 3. but 3 is repeated because 9 is a perfect square, so you will only visit door #9, on pass 1, 3, and 9... leaving it open at the end. only perfect square doors will be open at the end.

Puzzle: get exactly 2 liters with the 4 ltr and 7 ltr jars

2008-10-14T20:56:00.001+05:30

The Problem:

You've got two jars, one of them fits exactly 7 liters, the other one fits exactly 4 liters. How could you get exactly 2 liters with these two jars? You have unlimited supply of water and you are allowed to spoil some water.

The Solution:

7 0 fill the 7 ltr jar completely
3 4 fill the 4 ltr jar using the 7 ltr one completely
3 0 empty the 4 ltr jar
0 3 fill all water from 7 ltr jar to 4 ltr water
7 3 fill the 7 ltr jar completely
6 4 fill 4 ltr jar from 7 ltr one completely
6 0 empty the 4 ltr jar
2 4 fill 4 ltr jar from 7 ltr one completely
2 0 empty the 4 ltr jar

Now you have exactly 2 ltrs in 7 ltr jar

Solaris: colorize stuff

2008-10-13T21:12:00.004+05:30

If you want to colorize directory listing in solaris, just add the following alias in your .bashrc

alias ls='ls -h --color=auto'

If you want to colorize vim editor, add below in your .bashrc

export TERM=dtterm (for solaris)
export TERM=xterm (for linux)

Perl: Date formatting

2008-10-13T21:08:00.005+05:30

If you need to convert Date from YYYY-MM-DD format to MM/DD/YYYY format, use below perl code.

$str="2008-10-13";
$str =~s/^(\d{4})\-(\d{2})\-(\d{2})/$2\/$3\/$1/;
print $str;

will output:
10/13/2008

sed examples

2008-10-13T21:03:00.006+05:30

Delete a line from file which contain particular string (sed -e "//d" file1 > newfile)
print all lines except first line (sed -n '2,$p' vishnu.csv)
Convert YYYYMMDD to MM/DD/YYYY (echo "YYYYMMDD"|sed -n -e "s_$....$$..$$..$_\2/\3/\1_p")
Remove tab character from a file (sed -e \"s/[ <TAB>]*//g\" file > output)
Find and replace values in a file (sed -i 's/find1/replace1/g' fileName)
View only lines of a file that contains a particular string (sed -n '/string/p' fileName)
View particular row number (let say 3rd) of file (sed -n '3p;3q' fileName)
View range of row (let say 2 to 8) of file (sed -n '2,8p;8q' fileName)

Perl : Formatting a number to currency format

2008-10-10T22:12:00.002+05:30

sub formatCurrency {
my $number = sprintf "%.2f", shift @_;
# Add one comma each time through the do-nothing loop
1 while $number =~ s/^(-?\d+)(\d\d\d)/$1,$2/;
# Put the dollar sign in the right place
$number =~ s/^(-?)/$1\$/;
return $number;
}

How to access secure website (https) in HTTPUNIT

2008-09-20T21:27:00.004+05:30

If you are using httpunit to automate your website testing and your site is on secure domain (i.e. on https); httpunit would not be able to access your site unless you add below code in your tests(probably in setup() method)

//code to make test case run for HTTPS site com.sun.net.ssl.internal.www.protocol.https.HttpsURLConnectionOldImpl.setDefaultHostnameVerifier(new com.sun.net.ssl.HostnameVerifier()
{
public boolean verify(String urlHostname, String certHostname)
{
return true;
}
}
);

Change the case of file contents

2008-09-19T22:11:00.003+05:30

If you want to change case (either lower or upper case) of contents of a file use the tr command.

Change contents of filename to lower case
cat filename | tr "[:upper:]" "[:lower:]" > newfilename
or
cat filename | tr '[A-Z]' '[a-z]' > newfilename

Change contents of filename to upper case
cat filename | tr "[:lower:]" "[:upper:]" > newfilename
or
cat filename | tr '[a-z]' '[A-Z]' > newfilename

Determining the disk space used by Oracle schema

2008-09-09T12:41:00.002+05:30

Run below query to find out how much disk space is being used by an Oracle schema

Select SUM(bytes)/1024/1024/1024 Gb, tablespace_name FROM user_segments GROUP BY tablespace_name;

Memory usage by process

2008-09-07T20:15:00.003+05:30

Solaris pmap command provides the memory usage of a process. This command tells you how much of memory a particular process is using and how much of there particular threads/heap are using. It's a very useful command to debug cases where you have to find that who is consuming more memory in your process.

pmap <PID>

example$ pmap 102905
102905: sh
00010000 192K r-x-- /usr/bin/ksh
00040000 8K rwx-- /usr/bin/ksh
00042000 40K rwx-- [ heap ]
FF180000 664K r-x-- /usr/lib/libc.so.1
FF236000 24K rwx-- /usr/lib/libc.so.1
FF23C000 8K rwx-- /usr/lib/libc.so.1
FF250000 8K rwx-- [ anon ]
FF260000 16K r-x-- /usr/lib/en_US.ISO8859-1.so.2
FF272000 16K rwx-- /usr/lib/en_US.ISO8859-1.so.2
FF280000 560K r-x-- /usr/lib/libnsl.so.1
FF31C000 32K rwx-- /usr/lib/libnsl.so.1
FF324000 32K rwx-- /usr/lib/libnsl.so.1
FF340000 16K r-x-- /usr/lib/libc_psr.so.1
FF350000 16K r-x-- /usr/lib/libmp.so.2
FF364000 8K rwx-- /usr/lib/libmp.so.2
FF380000 40K r-x-- /usr/lib/libsocket.so.1
FF39A000 8K rwx-- /usr/lib/libsocket.so.1
FF3A0000 8K r-x-- /usr/lib/libdl.so.1
FF3B0000 8K rwx-- [ anon ]
FF3C0000 152K r-x-- /usr/lib/ld.so.1

Sorting a csv file on a particular field

2008-09-07T19:55:00.004+05:30

I have below csv file, which have around 5k lines in following format:

ABC,12345,test.com,56780,0.00,200.65,0.00,0.00,200.65
XYZ,54311,tset.com,69540,0.00,102.32,0.00,0.00,102.32
..
...

I wanted to sort this file on field6 so that i could extract my desired lines from the csv file. I did use below command which sorted my csv file on field no 6:

sort -n -r -t, +6 -7 vishnu.csv

-n Specifies numeric field
-r Reverse sorting
-t specifies seperator (here , is a seperator)
+6, -7 specifies column position

sorting the csv file if separator is tab
sort -t $'\t' -k5 -nr filename

List of tables in the Oracle schema

2008-09-07T01:20:00.002+05:30

Wanted to find out list of tables in the Oracle schema? We can use any of the following sql query; which will list all the tables in the specified schema of Oracle database.

SELECT * FROM cat;
SELECT TABLE_NAME FROM TABS;
SELECT * FROM user_objects WHERE object_type = 'TABLE';
SELECT TABLE_NAME FROM ALL_ALL_TABLES;

Execute a perl script from java Program

2008-08-14T17:09:00.004+05:30

Interested to execute a perl script from a java program.... uhmmmmm... see below java program (Sample.java)

import java.io.*;

public class Sample{

public static void main(String args[]) {

Process process;

try
{
process = Runtime.getRuntime().exec("perl testscript.pl");
process.waitFor();
if(process.exitValue() == 0)
{
System.out.println("Command Successful");
}
else
{
System.out.println("Command Failure");
}
}
catch(Exception e)
{
System.out.println("Exception: "+ e.toString());
}
}
}

javac Sample.java
java -cp . Sample

compare two sorted files in unix

2008-08-14T16:09:00.003+05:30

If you wanted to compare 2 sorted files then use comm command. comm command compares two sorted files line by line. This command reads the File1 and File2 as parameter and writes a three column output to standard output.

First Column: Lines that are only in File1
Second Column: Lines that are only in File2
Third Column: Lines that are in both File1 and File2

Syntax:

comm [-1] [-2] [-3 ] file1 file2

-1 Suppresses the display of the first column (lines in File1)
-2 Suppresses the display of the second column (lines in File2)
-3 Suppresses the display of the third column (lines both in File1 and File2)

Killing a defunct process

2008-08-14T16:04:00.003+05:30

A defunct (or "zombie") process is a process that isn't running, isn't eligible to run, and takes up no system resources. It's actually a process that has exited, but its parent has not called wait() in order to find out its exit status.

defunct processes can't be killed since they are already dead. To make them disappear you have to kill their parent process.

Find the parent process id of the defunct process and then kill that parent process:

ps -fe | grep defunctprocess | awk '{print $3}'

kill -9 parentprocessid

Reverse the contents of a file

2008-08-14T15:54:00.002+05:30

If you wanted to reverse the contents of a file, use below perl script:

#!/usr/bin/perl

$filename = "a.java";
open(INFILE, "<", $filename)
or die "Can't open $filename for reading: $!\n";
my(@content) = <INFILE>;
close(INFILE);
while (@content) {
my($line) = pop(@content);
chomp $line;
print reverse($line)."\n";
}

Reversing a file

2008-08-14T15:20:00.003+05:30

You have a file and you wanted to reverse it. How to do it? Guys...... its really very easy.. Use below commands:

tail -r filename
or
cat -n filename | sort -nr | cut -c8-

You can achieve the same by writing a perl script. See below perl script:

#!/usr/bin/perl

$filename = "a.java";
open(INFILE, "<", $filename)
or die "Can't open $filename for reading: $!\n";
my(@content) = <INFILE>;
close(INFILE);
while (@content) {
my($line) = pop(@content);
chomp $line;
print "$line \n";
}

Zephyr - A Test Management Tool

2008-07-22T23:15:00.002+05:30

Recently i got an email from Zephyr regarding their new product launch. Zephyr is basically a Test Management tool aimed at global SME, IT departments and Testing Vendors which helps the testing team to manage their test cases and provide test cases visibility at all levels using a central repository. I checked their product demo which looks cool.
http://www.getzephyr.com/

Software Testing Life Cycle

2008-07-20T09:25:00.001+05:30

Software Testing Life Cycle

From: sunny.deb, 8 months ago

Software Testing Life Cycle

view presentation (tags: cycle life testing software)

Software Testing Life Cycle

SlideShare Link

Make INSERT statement syntax from a csv file using perl

2008-07-14T16:34:00.004+05:30

I need to read a csv file and insert the data from csv to oracle. I created below perl script which will read the csv file line by line and will create INSERT statement accordingly.

#!/usr/bin/perl

$filename = "test.csv";
open(INFILE, "<", $filename)
or die "Can't open $filename for reading: $!\n";
my(@lines) = <INFILE>;
my($line1) = $lines[0];
my($line2) = $lines[1];

print "$line1 \n";
print "$line2 \n";

close(INFILE);

my($tablename) = "mytable";
my($createquery) = "Create table " . $tablename . "(";

my(@var1) = split("\t", $line1);
my(@var2) = split("\t", $line2);
my($i) = 0;
for $item1 (@var1){
chomp($item1);
$createquery = $createquery . $item1 . " ";
$data = $var2[$i++];
print "$item1\t : $data\n";
if(checkData($data) eq "int"){
$createquery = $createquery . "number(10,0), ";
}
elsif(checkData($data) eq "float"){
$createquery = $createquery . "number(10,2), ";
}
else{
$createquery = $createquery . "varchar2(50), ";
}
}
$createquery = substr($createquery,0, -2);
$createquery = $createquery . ");";
print "$createquery\n";

$i = 0;
for $dataline (@lines){
if ( $i == 0){
$i = 1;
next;
}
chomp($dataline);
my(@dataitem) = split("\t", $dataline);
my($insertquery) = "INSERT INTO " . $tablename . " VALUES (";
print "$dataitem\n";
for $items (@dataitem){
if( (checkData($items) eq "int") || (checkData($items) eq "float") ){
$insertquery = $insertquery . $items .",";
}
else{
$insertquery = $insertquery . "\"" . $items . "\"" .",";
}
}
$insertquery = substr($insertquery, 0, -1);
$insertquery = $insertquery . ")";
print "$insertquery\n";
}

sub checkData(){
if(@_[0] =~ /^-?\d+$/){
return "int";
}
elsif(@_[0] =~ /^-?(?:\d+(?:\.\d*)?|\.\d+)$/){
return "float";
}
else{
return "string";
}
}

Make CREATE TABLE syntax from a csv file using perl

2008-07-10T22:36:00.005+05:30

I have a csv file and i have to make the CREATE TABLE statement (which later will be used to create an Oracle table) by reading first and second line of the csv file. First line in the csv file is column headers and the second line is actual data. By reading the first line, i will create column name and by reading the second line, i'll decide the data type for the column.
Below is the perl script for the same: (i am assuming that data in the csv file is tab seperated)

#!/usr/bin/perl

$filename = "test.csv";
open(INFILE, "<", $filename) or die "Can't open $filename for reading: $!\n";
@lines = <INFILE>;
$line1 = $lines[0];
$line2 = $lines[1];

print "$line1 \n";
print "$line2 \n";

close(INFILE);

$tablename = "mytable";
$query = "Create table " . $tablename . "(";

@var1 = split("\t", $line1);
@var2 = split("\t", $line2);
$i = 0;
for $item1 (@var1){
chomp($item1);
$query = $query . $item1 . " ";
$data = $var2[$i++];
print "$item1\t : $data\n";
if($data =~ /^-?\d+$/){
$query = $query . "number(10,0), ";
}
elsif ($data =~ /^-?(?:\d+(?:\.\d*)?|\.\d+)$/){
$query = $query . "number(10,2), ";
}
else{
$query = $query . "varchar2(50), ";
}
}
$query = substr($query,0, -2);
$query = $query . ");";
print "$query\n";

Read data from a csv file using perl (when the column count is not known)

2008-07-08T18:31:00.002+05:30

I need to read a csv file, and process each and every column data (i do not know the column count before processing the file)

#!/usr/local/bin/perl

if (scalar(@ARGV) < 1)
{
print "Please Specify the file name to be read \n";
exit
}

$file = $ARGV[0];

open FILE, "< $file" or die "Can't open test.csv : $!";
while(<FILE>){
chop;
my(@columns)= split("\t");
my($colnum)=$#columns;
# print "Number of Column in this row is : $colnum\n";
foreach $col(@columns){
print "$col \t";
}
print "\n";
}
close FILE;

Read data from a csv file using perl (when the column count is known)

2008-07-08T18:27:00.003+05:30

I need to read a csv file, and process each and every column data (i know the column count before processing the file)

#!/usr/local/bin/perl

open FILE, "< test.csv" or die "Can't open test.csv : $!";
while(){
chomp;
($col1,$col2,$col3,$col4) = split ("\t");
print "$col1 \t";
print "$col2 \t";
print "$col3 \t";
print "$col4 \t";
print "\n";
}
close FILE;

Check datatype of a argument in perl

2008-07-08T18:23:00.004+05:30

I need to pick few lines from a particular file and check the data type of values of a particular column. Following is the perl script for the same:

#!/usr/bin/perl

$filename = "test.csv";
open(INFILE, "<", $filename) or die "Can't open $filename for reading: $!\n";
@lines = <INFILE>;
$line1 = $lines[0];
$line2 = $lines[1];

print "$line1 \n";
print "$line2 \n";

close(INFILE);

@var1 = split("\t", $line2);
for $item (@var1){
if($item =~ /\D/){
print "$item\t : Has Non Digits\n";
}
elsif ($item =~ /^\d+$/){
print "$item\t : Whole Number\n";
}
elsif ($item =~ /^-?\d+$/){
print "$item\t : Integer\n";
}
elsif ($item =~ /^[+-]?\d+$/){
print "$item\t : +/- Integer\n";
}
elsif ($item =~ /^-?\d+\.?\d*$/){
print "$item\t : Real Number\n";
}
elsif ($item =~ /^-?(?:\d+(?:\.\d*)?|\.\d+)$/){
print "$item\t : Float Number\n";
}
else{
print "$item\t : NAN\n";
}
}

Print particular line/s from a file using perl

2008-07-08T18:19:00.000+05:30

Method 1:

#!/usr/bin/perl

$filename = "test.csv";
open(INFILE, "<", $filename)
or die "Can't open $filename for reading: $!\n";

$line_number = 1;
while () {
$line = $_;
last if $. == $line_number;
}
if ($. != $line_number) {
die "Didn't find line $line_number in $filename\n";
}
print;

close(INFILE);

Method 2:

#!/usr/bin/perl

$filename = "test.csv";
open(INFILE, "<", $filename)
or die "Can't open $filename for reading: $!\n";
@lines = ;
$line1 = $lines[0];
$line2 = $lines[1];

print "$line1 \n";
print "$line2 \n";

close(INFILE);

Set Terminal Title

2008-06-27T21:37:00.002+05:30

If you want the Terminal window title to be set automatically to the remote zone you have logged into, add this to your .bashrc file on the remote machine.

# Terminal settings
PS1="[\u@\h \W]# "

if [ -n "$PS1" ]; then
echo -n -e "\033]0;`hostname`\007"
fi

TNSNAMES.ORA file

2008-06-14T13:53:00.004+05:30

TNSNAMES.ORA is a configuration file that defines databases addresses for establishing connections to the database. It resides in ORACLE HOME\NETWORK\ADMIN directory. Below is the sample entry in TNSNAMES.ORA

<addressname> =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>))
)
(CONNECT_DATA =
(SERVICE_NAME = <sid>)
)
)

Read data from a file in perl

2008-06-11T16:50:00.000+05:30

Below is the sample perl script, if you want to read some data from a file and pass it to as a argument in your perl script.

In my case, I have some object ids in a file and for each id I have to run oocheck command.

#!/usr/local/bin/perl

open FILE, "testoids" or die $!;

while ()

{

chomp $_;

systemCmd("oocheck -id $_");

}

close(FILE);

sub systemCmd {

my($cmd) = @_;

print("$cmd\n");

(system($cmd) == 0) || die ("cmd $cmd failed");

}

Linux time command

2008-06-06T19:51:00.002+05:30

The time command runs the specified program command with the given arguments. When command finishes, time writes a message to standard output giving timing statistics about this program run.

See below example:

[vishnu@solaris /mnt/usr/vishnu] time myProgram arg1

real 24m10.951s

user 6m2.390s

sys 0m15.705s

[vishnu@solaris /mnt/usr/vishnu]

Above command outputs the time taken by myProgram.

· real - Elapsed time from beginning to end of program

· user - time used by the program itself and any library subroutines it calls

· sys- time used by the system calls invoked by the program (directly or indirectly)

Forgot to mention subject in mail ???

2008-05-15T18:47:00.000+05:30

Forgot to mention subject, while writing an official mail ??

Yes.... It's a concern for all.... A mail without a subject brings a bad impression on us.

To avoid this just follow the simple steps mentioned below and see the result

Steps:
1. Open your outlook

2. Press Alt+F11. This opens the Visual Basic editor and then Press Ctrl+R which in turn open Project-Project 1 (left side)

3. On the Left Pane, one can see "Microsoft Outlook Objects" or "Project1", expand this. Now one can see the "This Outlook Session".

4. Double click on "ThisOutLookSession". It will open up a code pane on the right hand side.

5. Copy and Paste the following code in the right pane. (Code Pane) and save it

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
Dim strSubject As String
strSubject = Item.Subject
If Len(Trim(strSubject)) = 0 Then
Prompt$ = "Subject is Empty. Are you sure you want to send the Mail?"
If MsgBox(Prompt$, vbYesNo + vbQuestion + vbMsgBoxSetForeground, "Check for Subject") = vbNo Then
Cancel = True
End If
End If
End Sub

6. Now whenever u try to send a mail without subject, which will raise a pop-up to remind you.

List only Directory/Files in ls command

2008-04-23T10:48:00.002+05:30

ls -l (include both directories and files)

ls -l | grep -v ^d (include only files in listing)

ls -l | grep ^d (include only directories in listing)

Read latest file in vi editor

2008-04-22T11:53:00.005+05:30

In my daily routine on solaris box, i have to read the latest file in vi. For this i have to run below 2 commands:

ls -ltr
vi filename

I get the latest file name with ls -ltr command (last file in the list) and then vi to open this file.

I was sick of using 2 commands for this simple operation, so i thought of using a single command using combination of commands; following are the commands which I used for this operation:

vi `ls -ltr|tail -1|awk -F" " '{print $9}'` (includes directory listing)
vi `ls -ltr|grep -v ^d|tail -1|awk -F" " '{print $9}'` (ignores directory listing)
or
vi `ls -ltr|tail -1|cut -d":" -f2|cut -d" " -f2` (includes directory listing)
vi `ls -ltr|grep -v ^d|tail -1|cut -d":" -f2|cut -d" " -f2` (ignores directory listing)

Location of firefox cookies ?

2008-04-03T16:48:00.003+05:30

All the firefox cookies are stored in cookies.txt at C:\Documents and Settings\Windows login/user name\Application Data\Mozilla\Firefox\Profiles\profile folder

Testplan plugin in Confluence WIKI

2008-03-31T11:08:00.003+05:30

Recently in our organization, we started to use Confluence WIKI for documentation purpose. This wiki has a plugin called TestPlan. I liked this plugin very much as it allows us to maintain our test cases and get the test case execution status.
Below is the screenshot of sample testplan:

Below is the wiki markup for above sample:
{testplan:Release includeJiraLinks=false useFailMacro=false}
^Feature1
//This is comment
Test1
Test2
^Feature2
//This is comment
Test3
Test4
{testplan}

vi Examples

2008-03-26T20:48:00.006+05:30

s/pat/text/ substitute 1^st match of pat with text

s/pat/text/g substitute every match of pat with text

s/pat/text/n substitute the n^th occurrence of pat with text

:%!sort or :1,$!sort sort current file

:5,10s/foo/bar/2 change the second occurrence of foo with bar on lines 5-10

:map g 1G map g to really run 1G

3J Join next 2 lines to current one

3,9m$ move lines 3 through 9 to the end of the file

ab w/o without when w/o is typed change to without

:?foo?,/bar/d delete from the reverse match of foo until the next match of bar

:g/{/,/}/< shift all lines between, and including, a "{" and a "}" left

:$-4,$d delete last five lines of buffer

:%s/^$.*$ $.*$$/\2 \1/ swap everything before and after the first space

d'' delete from current position to line of last jump

Perl matches Perl

^Perl matches Perl at beginning of line

Perl$ matches Perl at end of line

^Perl$ matches Perl as the only word on line

^$ matches the empty line

^..*$ matches a line with at least one character

.* matches any string of characters including none

^[ ^I]*$ as above but line can also contain spaces and/or tabs(^I)

[pP]erl matches perl or Perl

[aA][nN] matches an, aN, An, AN

p[aeiou]g second letter is a vowel

i[^aeiou]g second letter is not a vowel

p.g second letter is anything

^....$ matches a line with exactly four characters

^\. matches any line beginning with a dot

^\.[0-9a-z] same with a lowercase letter or digit following

^[^\.] matches any line that does not begin with a dot

;$ matches a line ending with a semicolon

figs* matches fig, figs, figss, figsss etc.

[a-z][a-z]* matches one or more lowercase letters

[a-zA-Z] matches any character

[^0-9a-zA-Z] matches any symbol (not a letter or number)

\&ltthe matches the, theater, then

the\> matches the, breathe

\&ltthe/> matches the

:%s/\<./\u&/g turn the first letter of all words to uppercase

:%s/\<[a-z][!-~]*\>/\u&/g as above

:%s/\<[a-z]/\u&/g as above

:%s/.*/\L&/ turn entire file to lowercase

:%s/<[^>]*>//g remove strings from file that start with a less than sign and end with a greater than sign (html tags)

:1,$s/1$/0/g Replace 1 to 0 from each line of the file, if last character of the line is 1

:1,$s/^1/0/g Replace 1 to 0 from each line of the file, if first character of the line is 1

see the differences between two branches of CVS

2008-03-11T12:59:00.003+05:30

To see all of the changes between two branches

1. checkout the source from HEAD/or branch, CD into it
2. run command cvs -q diff --brief -r brach_to_compare | grep Index

vi environment variables

2008-03-10T21:35:00.003+05:30

set
you can customize your environment with this command by typing set var=value, this will set the specified var to value for a scalar variable. For boolean varaibles, use set var to set and set novar to unset. You can see which variables are set by just typing the set by its itself. You can see a list of all variables by typing set all. some environment variables are specific to the ex editor and some are specific to the vi editor.

boolean variables:

`autoindent(ai)`	begin editing next line at same level of indent-ion as this one.
`autowrite(aw)`	write current buffer before leaving
`exrc(ex)`	tells vi/ex if it should read the .exrc file in the current directory(this can be a security risk).
`errorbells`	editor sends a beep to the terminal when an incorrect
`flash`	inverse the screen on an error instead of producing a bell
`ignorecase(ic)`	ignore case of characters in searches.
`lisp`	enter lisp mode
`list`	place a $ at the end of each line and a ^I on each tab.
`magic`	allow ., [, and * to be interpreted as special characters in RE's.
`modelines`	execute the first and last 5 lines of the file if of the form: `ex:command:` or `vi:command:`
`number(nu)`	number lines in left margin
`showmatch(sm)`	when closing a paren., brace or bracket; move the visual cursor to opening item to check scope
`showmode(smd)`	show type of insert mode
`wrapscan(ws)`	when searching and at bottom of file, continue searching from the top

Scalar variables:

`directory`	the location of the temporary directory used by vi
`paragraphs(para)`	macros to signify the beginning of a paragraph
`report`	vi will notify you if you change more lines than the value of `report`
`sections(sect)`	macros to signify the beginning of a section
`shell`	The shell to use when executing the command `:sh` or `:!`
`shiftwidth(sw)`	number of spaces to to insert on a shift operation
`showmatch(sm)`	show the match of ) and } when typed
`tabstop`	the length, in characters, of a tabstop
`term`	holds the name of the terminal type being used
`wrapmargin(wm)`	split lines at the column which is equal to the value of `wrapmargin`

avoid error "Not owner of .exrc or .exrc is group or world writable" in vi

2008-03-10T21:31:00.002+05:30

set this environment variable

export EXINIT="set noexrc"

vi Editor Commands

2008-03-08T22:18:00.028+05:30

The VI editor is a screen-based editor used by many Unix users. The VI editor has powerful features to aid programmers, but many beginning users avoid using VI because the different features overwhelm them.

General startup
        To use vi: vi
        To exit vi and save changes: ZZ or :wq
        To exit vi without saving changes: :q!
        To enter vi command mode: [esc]
        Read the original file back in so that you can start over.: :e!


Counts
        A number preceding any vi command tells vi to repeat that command that many times.

Deleting
        x       delete character under cursor
        X       delete character before cursor
        dd      delete line under cursor
        dw      delete word under cursor
        db      delete word before cursor
        D       delete to the end of line from the current cursor position
        d^      delete from current cursor position to the beginning of the line
        d$      delete from current cursor position to the end of the line

Copy/Paste Text
        yy     copies line
        P      paste copied data before cursor
        p      paste copied data after cursor

Find Commands
        ?       finds a word going backwards
        /       finds a word going forwards
        f       finds a character on the line under the cursor going forward
        F       finds a character on the line under the cursor going backwards
        t       find a character on the current line going forward and stop one character before it
        T       find a character on the current line going backward and stop one character before it
        ;       repeat last f, F, t, T
        ,       repeat last f, F, t, T going backwards
        n       repeat last search given by ‘/’ or ‘?’
        N       repeat last search given by ‘/’ or ‘?’ going backwards

Miscellaneous Commands
        .       repeat last command
        u       undoes last command issued
        U       undoes all commands on one line
        xp      deletes first character and inserts after second (swap)
        J       join current line with the next line
        ^G      display current line number
        %       if at one parenthesis, will jump to its mate
        mx      mark current line with character x
        'x      find line marked with character x
        ^^      Go back to last file

READING/WRITING FILES
        :r filename    Copies filename after cursor in file currently editing
        :n      Start editing next file in list
        :rew    rewind file list, start editing 1st file on argument list
        :w      Saves the current file without quitting

MOVING
        :#      Move to line #
        :$      Move to last line of file


Character/Line Formatting
        ~      Switch the case of the character under cursor
        <      Shifts the line upto where to the left by one shiftwidth
        <<     Shifts the current line to the left, and can be specified with a count
        >      Shifts the line upto where to the right by one shiftwidth
        >>     Shifts the current line to the right, and can be specified with a count
        J      Join the current line with the next one.

SHELL ESCAPE
        :!'CMD'      Executes CMD as a shell command
        :!sh         Fork shell in vi; hit ctrl-d to go back to vi

INSERTING
        r      replace character under cursor with next character typed
        R      keep replacing character until [esc] is hit
        i      insert before cursor
        I      insert from the beginning of line
        a      append after cursor
        A      append at end of line
        o      open line below cursor and enter append mode
        O      open line above cursor and enter append mode
        c      change until . "cc" changes the current line
        C      change to the end of line from the current cursor position
        s      substitute one character under the cursor and go into insert mode
        S      change an entire line


Screen Movement
        G      move to the last line in the file
        xG     move to line x
        z+     move current line to top of screen
        z      move current line to the middle of screen
        z-     move current line to the bottom of screen
        ^F     move forward one screen
        ^B     move backward one line
        ^D     move forward one half screen
        ^U     move backward one half screen
        ^R     redraw screen (does not work with vt100 type terminals)
        ^L     redraw screen (does not work with Televideo terminals)

Cursor Movement
        h      move left
        j      move down
        k      move up
        l      move right
        [return]    move to the beginning of next line
        $      last column on the current line
        0      move cursor to the first column on the current line
        ^      move cursor to the first nonblank column on the current line
        w      move to the beginning of the previous word or punctuation
        W      move past the next space
        b      move to the beginning of the previous word or punctuation mark
        B      move to the beginning of the previous word, ignores punctuation
        e      end of next word or punctuation mark
        E      end of next word, ignoring punctuation
        H      move cursor to the top of the screen
        M      move cursor to the middle of the screen
        L      move cursor to the bottom of the screen
        ^H     move cursor one space to the left
        ^J     move cursor down one line in same column
        ^M     move to the first character on the next line
        ^N     move cursor down one line in same column
        ^P     move cursor up one line in same column
        %      move cursor to the matching parenthesis or brace
        (      move cursor to the beginning of a sentence
        )      move cursor to the beginning of the next sentence
        {      move cursor to the preceding paragraph
        }      move cursor to the next paragraph
        |      move cursor to the column specified by the count
        +      move cursor to the first non-whitespace character in the next line
        -      move cursor to the first non-whitespace character in the previous line
        _      move cursor to the first non-whitespace character in the current line

Finding bugs in the application

2008-01-09T12:09:00.000+05:30

A QA engineer always thinks of a bug in the application and always try to find them. Finding bugs in any application is really a challenging work and if you find bugs, it gives you the satisfaction of your job. Below are few tips by which you can find bugs in the application:

1. Understand the application/module in depth before starting the testing
2. Prepare good functional test cases before starting the testing.
3. Create all possible type of test data before testing.
4. Test the system in different environments.
5. When you tired or bored by testing same module or application, do MONKEY TESTING, really it works. I tried it many times and got good bugs :)
6. Try some standard test cases for which you found the bugs in some different application.
7. Test the application with dedication and passion.
8. Think alike of different users while testing the application.

Doing QA is like being a goalkeeper. You can make a hundred brilliant saves but the only shot that people remember is the one that gets past you.

Read properties from ant build file using perl

2007-12-29T23:21:00.000+05:30

I want to read some properties from my ant build file in my perl script. I wrote a sample perl script for it. below is the script:

Check all arguments of a running process

2007-12-27T15:31:00.000+05:30

Many Java processes have long command lines, and as as a regular user, I may wish to determine that my java processes are running with the correct arguments or not.
unfortunately with the ps (/usr/bin/ps) command, users can only see the first 80 characters of command line, rest are ignored. So the user can not see the all command line arguments.

Solution:
1. Use pargs argument (ex. ps -eaf | pargs -a <pid>)
or
2. Use /usr/ucb/ps command ( /usr/ucb/ps -awwx | grep <pid>)

What version of Flash do you have?

2007-12-26T13:47:00.001+05:30

Visit following link to display the currently installed version of the Flash player.

www.adobe.com/products/flash/about

http://kb.adobe.com/selfservice/viewContent.do?externalId=tn_15507

Kill java processes from perl script

2007-12-24T22:12:00.000+05:30

Requirement:- If multiple java processes are running on your system and you want to kill only few specific from them.
If we do it manually then we have to perform below two steps:

1. jps (it will return all the java processes running on the system) in below format:
<pid>     <process name>
<pid>     <process name>
<pid>     <process name>
<pid>     <process name>
...

in my case i got the below output from above command:

1234 Server1
1334 Server2
1454 Server3
1264 Server4
1238 Server5
1244 Server6

2. now i have the process id of all the java processes so i can kill them using kill command, (let say if i have to kill only Server1, Server5 , then)

kill -9 1234
kill -9 1238

By using perl script, i can write below simple script which will do my above task:

foreach $_ (`jps`) {
my($pid,$pname) = /(\S+)\s+(.*)/;
if($pname eq "Server1" | $pname eq "Server5" ) {
print("Killing Process : $pname ($pid) \n");
system("kill -9 $pid");
}
}

Searching tips in Google

2007-12-17T18:53:00.001+05:30

1. Explicit Phrase: Lets say you are looking for content about internet marketing. Instead of just typing internet marketing into the Google search box, you will likely be better off searching explicitly for the phrase. To do this, simply enclose the search phrase within double quotes.

Example: “internet marketing”

2. Exclude Words: Lets say you want to search for content about internet marketing, but you want to exclude any results that contain the term advertising. To do this, simply use the “-“ sign in front of the word you want to exclude.

Example Search: internet marketing -advertising

3. Site Specific Search: Often, you want to search a specific website for content that matches a certain phrase. Even if the site doesn’t support a built-in search feature, you can use Google to search the site for your term. Simply use the “site:somesite.com” modifier.

Example: “internet marketing” site:www.smallbusinesshub.com

4. Similar Words and Synonyms: Let’s say you want to include a word in your search, but want to include results that contain similar words or synonyms. To do this, use the “~” in front of the word.

Example: “internet marketing” ~professional

5. Specific Document Types: If you’re looking to find results that are of a specific type, you can use the modifier “filetype:”. For example, you might want to find only PowerPoint presentations related to internet marketing.

Example: “internet marketing” filetype:ppt

6. This OR That: By default, when you do a search, Google will include all the terms specified in the search. If you are looking for any one of one or more terms to match, then you can use the OR operator. (Note: The OR has to be capitalized).

Example: internet marketing OR advertising

7. Phone Listing: Let’s say someone calls you on your mobile number and you don’t know how it is. If all you have is a phone number, you can look it up on Google using the phonebook feature.

Example: phonebook:617-555-1212 (note: the provided number does not work – you’ll have to use a real number to get any results).

8. Area Code Lookup: If all you need to do is to look-up the area code for a phone number, just enter the 3-digit area code and Google will tell you where it’s from.

Example: 617

9. Numeric Ranges: This is a rarely used, but highly useful tip. Let’s say you want to find results that contain any of a range of numbers. You can do this by using the X..Y modifier (in case this is hard to read, what’s between the X and Y are two periods. This type of search is useful for years (as shown below), prices or anywhere where you want to provide a series of numbers.

Example: president 1940..1950

10. Stock (Ticker Symbol): Just enter a valid ticker symbol as your search term and Google will give you the current financials and a quick thumb-nail chart for the stock.

Example: GOOG

11. Calculator: The next time you need to do a quick calculation, instead of bringing up the Calculator applet, you can just type your expression in to Google.

Example: 48512 * 1.02

12. Conversion: The next time you need to do a quick calculation on the temperature, you can just type your expression in to Google.

Example: 72F in C

The conversion feature works for a whole host of things: "100cm in inches", "100us gallons in uk gallons", "100bar in psi", "100 GBP in USD", etc.

13. Word Definitions: If you need to quickly look up the definition of a word or phrase, simply use the “define:” command.

Example: define:plethora

14. Searching for URLs containing certain words. Use the "inurl:word" modifier.

Example site:i-hack.org inurl:psp

Find Nth Max salary of employee

2007-11-20T17:24:00.001+05:30

I have following table in the DB

Create table Employee
(
Eid int,
Name varchar(10),
Salary money
)

insert few values in the table:

Insert into Employee values (1,\'harry\',3500)
Insert into Employee values (2,\'jack\',2500)
Insert into Employee values (3,\'john\',2500)
Insert into Employee values (4,\'xavier\',5500)
Insert into Employee values (5,\'steven\',7500)
Insert into Employee values (6,\'susana\',2400)

A simple query that can find the employee with the maximum salary, would be:

Select * from Employee where salary = (Select max(Salary) from Employee)

[The SQL Engine evaluates the inner most query and then moves to the next level (outer query). So, in the above example inner query i.e. Select max(Salary) from Employee is evaluated first. This query will return a value of 7500 (based on the sample data shown as above). This value is substituted in the outer query and it is evaluated as: ]

Select * from Employee where salary = (7500)
Returns:

Eid Name Salary
5 steven 7500

If the same syntax is applied to find out the 2nd or 3rd or 4th level of salary, the query would become bit complex to understand. See the example below:

Select * from Employee where salary =
(Select max(Salary) from Employee where salary
< (Select max(Salary) from Employee where
Salary < (Select max(Salary) from Employee where
Salary < …………………………………………… N

The above query would go on and on, depending on the level of salary that is to be determined. As mentioned earlier, the SQL Engine evaluates the inner most query first and moves the next outer level. One wouldn’t want to write such a big query just to find out this simple information.

The same result can be achieved with a simple syntax and easily understandable logic, by using a CORRELATED SUBQUERY. Correlated sub-query is a performance overhead to the database server and so, you have to use it only if it is required. Avoid using Correlated subquery on large tables, as the inner query is evaluated for each row of the outer query

Following is the query that captures the Nth maximum value:

Select * From Employee E1 Where
(N-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)
(Where N is the level of Salary to be determined)

In the above example, the inner query uses a value of the outer query in its filter condition meaning; the inner query cannot be evaluated before evaluating the outer query. So each row in the outer query is evaluated first and the inner query is run for that row. Let’s look into the background process of this query, by substituting a value for N i.e. 4,(Idea is to find the 4th maximum salary):

Select * From Employee E1 Where
(4-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)

Since the outer query’s value is referred in the inner query, the operation is done row-by-row. Based on the sample data as shown above, the process starts with the following record:

Employee E1
----------------------------------
Eid Name Salary
1 harry 3500
The salary of this record is substituted in the inner query and evaluated as:

Select Count(Distinct(E2.Salary)) From Employee E2
Where E2.Salary > 3500
Above query returns 2 (as there are only 2 salaries greater than 3500). This value is substituted in the outer query and will be evaluated as:

Select * From Employee E1 Where (4-1) = (2)
condition evaluates to FALSE and so, this record is NOT fetched in the result.

Next the SQL Engine processes the 2nd record which is:

Employee E1
----------------------------------
Eid Name Salary
2 jack 2500
Now the inner query is evaluated as:

Select Count(Distinct(E2.Salary)) From Employee E2
Where E2.Salary > 2500
This query returns a value of 3 (as there are 3 salaries greater than 2500). The value is substituted in the outer query and evaluated as:

Select * From Employee E1 Where (4-1) = (3)
condition evaluates to TRUE and so, this record is fetched in the result. This operation continues for all the remaining records. Finally the result shows these 2 records:

Eid Name Salary
2 jack 2500
3 john 2500

Exponential(TM) Identifies Top Online Advertising Trends For 2008

2007-11-18T18:17:00.000+05:30

Exponential(TM) Interactive, Inc., the technology-driven media services company that delivers innovative products and services to meet the demands of advertisers and publishers, today announced online advertising trends for 2008. The parent company of Tribal Fusion, one of the industry's leading online ad networks, Exponential has identified top trends emerging from technology and business innovation, creativity and brand measurement. Trends to watch include: one-to-one marketing, online video advertising, new local advertising platforms, innovation in ad effectiveness measures, marketing opportunities from the semantic web, and virtual worlds.

Read Full Article here

Unix Screen utility

2007-10-30T22:15:00.003+05:30

Screen, this command might not be well known to many people but its one of those programs that you just can’t stop using once you’ve started. Previously I used VNC to connect the remote servers, but now i have stopped using it and use SCREEN on a regular daily basis and it’s really easy to use.
Screen program provides the following functionality:

Remote terminal session management (detaching or sharing terminal sessions)
Unlimited windows (unlike the hardcoded number of Linux virtual consoles)
Scrollback buffer (not limited to video memory like Linux virtual consoles)
Copy/paste between windows
Split terminal (horizontally) into multiple regions
Locking other users out of terminal
Screen is an easy way to allow processes to continue running after the session is terminated, if you lose connection screen will save your spot

Following is the content of my ~/.screenrc file
************************************************************
startup_message off
vbell off
caption always “%{= bb}%{+b w}%n %h %=%t %c”
hardstatus alwayslastline “%-Lw%{= BW}%50>%n%f* %t%{-}%+Lw%<” activity “Activity in %t(%n)” shell -/bin/bash
************************************************************
Screen Commands
(screen) Start screen
(screen -S vishnu) Create a screen session with the name vishnu
(screen -r vishnu) reconnect to the session with the name vishnu
(screen -x ) Connect to an existing screen session
(screen -d ) detaching a screen session
(screen -r) reattaching the screen session
(Ctrl+a c ) New window
(Ctrl+a n ) Next Window
(Ctrl+a p ) Previous Window
(Ctrl+a ” ) Select window from list
(Ctrl+a Ctrl+a) Previous window viewed
(Ctrl+a <0-9> ) Select the numbered window
(Ctrl+a A ) Set window title
(Ctrl+a K ) Kill window
(Ctrl+a d ) Detach screen from terminal
(Ctrl+a x ) Lock Session
(Ctrl+a : ) Goto screen command prompt
(Ctrl+a ? ) Show key binding/command names
(Ctrl+s ) Pause the output on screen
(Ctrl+q ) Resume the output on screen
(Ctrl+a :escape ^Ww) Change key binding to w character
(Ctrl-a * ) List all currently attached displays. (displays)
(Ctrl-a Ctrl\) Kill all windows and terminate screen. (quit)
(Ctrl-a w ) List all windows. (windows)
(Ctrl-a h ) Write contents of the current window to the file hardcopy.n. (hardcopy)
(Ctrl-a H ) Begin/end logging of the current window to the file screenlog.n. (log)
(Ctrl-a ' ) Prompt for window name or number to switch to. (select)

Linux Cut command

2007-09-09T22:19:00.000+05:30

Cut Command
------------

Divide a file into several parts (columns)

syntax:
cut [-b] [-c] [-f] list [-n] [-d delim] [-s] [file]

Examples:
--------

1. Let say you have a file test.txt which has colon(:) seperated data

406378:Sales:Itorre:Jan
031762:Marketing:Nasium:Jim
636496:Research:Ancholie:Mel
396082:Sales:Jucacion:Ed

If you want to print first set of data from each row, you can use cut command as follow:

cut -d":" -f1 test.txt

If you want to print just columns 1 to 6 of each line (the employee serial numbers), use the -c1-6 flag, as in this command

cut -c1-6 test.txt

Linux Tips

2007-09-09T22:12:00.010+05:30

Replace newline with comma (cut -d, -f1 vishnu.csv | tr '\n' ',')
Get some specific string from each line (grep "uid%3D" <fileName> |awk -F"uid%3D" ` {print $2}` | cut -d"%" -f1)
Pull first n characters of each line from a file (cut -c1-n file.txt > newfile.txt)
Count total number of lines in all specific files under a directory (find . -type f -name '*.as' -o -name '*.mxml' -o -name '*.java'| xargs cat |wc -l)
Find number of occurrences of a text in a file (grep text fileName |wc -l)
Display the top most process utilizing most CPU (top -b 1)
Show the working directory of a process ? (pwdx pid )
Display the parent/child tree of a process ? (ptree pid )
Display the no.of active established connections to localhost ? (netstat -a | grep EST)
How to create null file ? (cat /dev/null > filename1)
Display top ten largest files/directories ? (du -sk * | sort -nr | head)
Display disk usage (du -h)
How to save man pages to a file ? (man | col -b > filename )
Display the files in the directory by file size ? (ls -ltr | sort -nr -k 5)
Display the processes, which are running under yourusername ( ps -aef | grep username)
Display the all files recursively with path under current directory ? ( find . -depth -print)
Display the Disk Usage of file sizes under each directory in currentDirectory ? (du -k . | sort -nr)
List the files in current directory sorted by size ? (ls -l | grep ^- | sort -nr)

Soalris: Kill a process which is using a particular port number

2007-09-05T16:30:00.001+05:30

Today i came across a problem in solaris. The problem was that while starting my application server, it was throwing an error "Address already in use".

My app server is a java process and there are many other java process which are running on my zone. But the issue is, how may i know that which java process is using that particular port?

I followed following steps:
1. List all the java process running on my zone ( ps -eaf |grep vagrawal| grep java )
2. Go through each java process and check if it using that particular port ( pfiles $pid|grep 1182 )
(here $pid is the process id of the java process and 1182 is the port number of which i am looking for)

Above method works fine but it is bit a long process, as i have to run step 2 for all java processes, so i ran a folowing command on my console:

for i in `ps -e|awk '{print $1}'`; do echo $i; pfiles $i 2>/dev/null | grep 'port: 1188'; done

Above command/script will list out all the process ID and will tell if any process is using port 1188

Now i have process ID of the process which is occupying my port, and i can kill that by kill -9 pid