About Me

My photo
Talk to me... you will know

Thursday, March 15, 2012

Cursors in SQL


A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it. This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.
In SQL procedures, a cursor make it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis. By using the same mechanics, an SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application.


A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed.


To use cursors in SQL procedures, you need to do the following:
Declare a cursor that defines a result set.
Open the cursor to establish the result set.
Fetch the data into local variables as needed from the cursor, one row at a time.
Close the cursor when done
To work with cursors you must use the following SQL statements:
DECLARE CURSOR
OPEN
FETCH
CLOSE


There are two types of cursors in PL/SQL:


Implicit cursors:


These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed. 


Explicit cursors:


They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.


Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed. 
When you execute DML statements like DELETE, INSERT, UPDATE and SELECT statements, implicit statements are created to process these statements.


Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The cursor attributes available are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN. 


For example, When you execute INSERT, UPDATE, or DELETE statements the cursor attributes tell us whether any rows are affected and how many have been affected. 
When a SELECT... INTO statement is executed in a PL/SQL Block, implicit cursor attributes can be used to find out whether any row has been returned by the SELECT statement. PL/SQL returns an error when no data is selected.


The status of the cursor for each of these attributes are defined below :- 



  •  %FOUND :



The return value is TRUE, if the DML statements like INSERT, DELETE and UPDATE affect at least one row and if SELECT ….INTO statement return at least one row.


The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE do not affect row and if SELECT….INTO statement do not return a row. 



example : SQL%FOUND 



  • %NOTFOUND :



The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE at least one row and if SELECT ….INTO statement return at least one row.


The return value is TRUE, if a DML statement like INSERT, DELETE and UPDATE do not affect even one row and if SELECT ….INTO statement does not return a row. 



example: SQL%NOTFOUND 



  • %ROWCOUNT :



Return the number of rows affected by the DML operations INSERT, DELETE, UPDATE, SELECT


example : SQL%ROWCOUNT 




For Example: Consider the PL/SQL Block that uses implicit cursor attributes as shown below:


DECLARE  var_rows number(5);
BEGIN
  UPDATE employee 
  SET salary = salary + 1000;
  IF SQL%NOTFOUND THEN
    dbms_output.put_line('None of the salaries where updated');
  ELSIF SQL%FOUND THEN
    var_rows := SQL%ROWCOUNT;
    dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
  END IF; 
END; 


In the above PL/SQL Block, the salaries of all the employees in the ‘employee’ table are updated. If none of the employee’s salary are updated we get a message 'None of the salaries where updated'. Else we get a message like for example, 'Salaries for 1000 employees are updated' if there are 1000 rows in ‘employee’ table. 



Friday, February 17, 2012

Inheritance in Javascript



Javascript has grown a lot from what i remember of working on it.. I remember the plain javascript i used to work on which now has advanced a lot to include inheritance and other OOP concepts. To understand more i tried an example to understand its reaches...

It's extremely hard to understand for those with classic OOP background but JavaScript has no Classes, just objects, where functions are first class objects but still objects!
Assumed this, we can try in any case to think about a JavaScript Class as a "function which aim is to create instances of function itself".
try with Mootools or some other library
new Class({}) instanceof Class;
 FALSE, since Class returns a function
 and not the created instanceof Class
 Nothing more and nothing less. The fact we would like to use that function to create and initialize instances is just how every Functions work, or if we prefer, a developer convention and a quick way to chain a prototype into an object.
 Since a Class is a Function, and whatever "new Class()" will always be an "instanceof Function", Class.prototype should be exactly the Function.prototype one, so that nobody can ever say again: "Look, I have created a Class".. All that has been made is a function..  But its still elastic




<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Page Start</title>
<script type="text/javascript">
/*Class declared: Person*/
Person = function(id, name, age){
    this.id = id;
    this.name = name;
    this.age = age;
    alert('Accepting Person Object');
}
/*properties of person class*/
Person.prototype = {
    wakeUp: function() {
        alert('I am awake');
    },
    getAge: function() {
        return this.age;
    },
    
    toStringHere : function() {
        return "id : "+this.id+", name : "+this.name+", age : "+this.age;
    }
}


Person.prototype.fly = function(){
alert("finally i can fly");
}
/*An instance to help inherit the properties*/
Inheritance_Manager = {};
/*DOM extension mechanism || Simple extension through object*/
Inheritance_Manager.extend = function(subClass, baseClass) {
    function inheritance() { }
    inheritance.prototype = baseClass.prototype;
    subClass.prototype = new inheritance();
    subClass.prototype.constructor = subClass;
    subClass.baseConstructor = baseClass;
    subClass.superClass = baseClass.prototype;
}
/*Class Declared : Manager */
Manager = function(id, name, age, salary) {
alert('Registering Manager');
    Manager.baseConstructor.call(this, id, name, age);
    this.salary = salary;
    alert("Registration Complete");
}
/*Manager Inherits Person and its handling of constructor*/
Inheritance_Manager.extend(Manager, Person);


Manager.prototype.lead = function(){
   alert('I am a good leader');
}
/*use objects to check how the various functions are accessed*/
var p = new Person(1, 'RAS', 22);
document.write(p.toStringHere()+"<br>");
var pm = new Manager(1, 'Ajo Koshy', 22, '27000');
document.write(pm.toStringHere()+"<br>");
</script>
</head>
</html>

Tuesday, February 07, 2012

Escape Sequencer


To access json strings which have been returned with a double quote often poses a problem on the server side as the characters are not in sync with the values and cannot be processed... So the following problem will help you process the same problem by escaping the character in the json data daved in a file.


//buzz.txt



{"groupOp":"AND","rules":[{"field":"tlc_product_number","op":"eq","data":"2563"},{"field":"tlc_product_desc","op":"eq","data":"1" SPECIAL LABEL (CATERING)"},{"field":"fobsdate","op":"eq","data":"02/01/2012"}]




// EscapeSequencer.java




package com.string.regex;


import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;






public class EscapeSequencer {
public static void main(String args[]){
List<Integer> ind1= new ArrayList<Integer>();
List<Integer> ind2= new ArrayList<Integer>();
String val= "";
try {
val= readFileAsString("C:/buzz");
System.out.println(val);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
int pro=val.indexOf("data\":\"")+6;
String word1 = "data\":\"";
String word2 = "\"}";
String value="";
String filterVal=val.substring(pro,val.indexOf('"', pro));
for(int i=0;i<val.length();i++)
{
if(i+word1.length()<=val.length()){

String compString = val.substring(i, i+word1.length());
if(compString.equalsIgnoreCase(word1))
{


i=i+word1.length();
ind1.add(i);


}



}
//System.out.println(ind1.toString());


}
for(int i=0;i<val.length();i++)
{
if(i+word2.length()<=val.length()){
String compString= val.substring(i,i+word2.length());
if(compString.equalsIgnoreCase(word2))
{
i=i+word2.length();
ind2.add(i);
}
}
//System.out.println(ind2.toString());
}


System.out.println(val.indexOf("\"}"));
for(int j=0;j<ind1.size();j++){
try{
filterVal=val.substring(ind1.get(j)+1,ind2.get(j)-2);
//System.out.println(val.indexOf("\"}"));
}
catch(StringIndexOutOfBoundsException sie)
{
sie.printStackTrace();
}
value=filterVal;
//System.out.println(value+"...");
filterVal=filterVal.replace("\\", "\\\\")
   .replace("\"", "\\\"")
   .replace("\r", "\\r")
   .replace("\n", "\\n");
val=val.replace(value, filterVal);
System.out.println(val);
}


}
private static String readFileAsString(String filePath)
    throws java.io.IOException{
        StringBuffer fileData = new StringBuffer(1000);
        BufferedReader reader = new BufferedReader(
                new FileReader(filePath));
        char[] buf = new char[1024];
        int numRead=0;
        while((numRead=reader.read(buf)) != -1){
            String readData = String.valueOf(buf, 0, numRead);
            fileData.append(readData);
            buf = new char[1024];
        }
        reader.close();
        return fileData.toString();
    }
}

Thursday, January 12, 2012

Appian

I have been working on the appian api and trust me its very different from what i expected... the no of hassles faced are so much that for just shifting from 1 version to other i had to do a POC.. It was worth it.. :)





package com.trial;


import com.ibm.icu.text.DateFormatSymbols;
import com.ibm.icu.text.SimpleDateFormat;
import com.ibm.icu.util.Calendar;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;


/**
 *This program helps to find out the difference in calendar implementations of Appian 5.0 and Appian 6.0
 *@author ajo.koshy
 *@version 1.0
 *The only requirements are 4 jar files :
 *a) appian-asi
 *b) appian-util
 *c) appian-globalization
 *d) icu4j-4.0.1
 * */
public class TrialExec {
/**
* @throws None
* @author ajo.koshy
* */
public static void main(String args[]){

String ans="";
Calendar cal = Calendar.getInstance(Locale.UK);// Use Locale.US for different answer
SimpleDateFormat dtf = new SimpleDateFormat("MM/dd/yy", Locale.UK);// Use Locale.US for different answer
DateFormatSymbols symbols = dtf.getDateFormatSymbols();
String[] shortWeekdays = symbols.getShortWeekdays();
List<String> weekdayInitials = new ArrayList<String>();
int firstDayOfWeek = cal.getFirstDayOfWeek();
cal.set(2012, 0, 1);
int blankSpaces = cal.get(7) - firstDayOfWeek;
int newBlankSpaces = getLeadingBlankSpaces(cal);


for (int i = firstDayOfWeek; i < shortWeekdays.length; i++) {
/*if (shortWeekdays[i].length() > 0) {
 
}*/
weekdayInitials.add(Character.toString(shortWeekdays[i].charAt(0)));
}
 
for (int i = 0; i < firstDayOfWeek; i++) {
if (shortWeekdays[i].length() > 0) {
weekdayInitials.add(Character.toString(shortWeekdays[i].charAt(0)));
}
}



for(int i=0;i<weekdayInitials.size();i++)
{
ans=ans+" "+weekdayInitials.get(i);
}

System.out.println(ans);
System.out.println(cal.getTime());
System.out.println("blankspaces are = "+blankSpaces);
System.out.println("now blankspaces are = "+newBlankSpaces);

}

private static int getLeadingBlankSpaces(Calendar firstDayOfTargetMonthAndYear) {
/**
* @author ajo.koshy
* @param Calendar Object
* */

int firstDayOfWeek = firstDayOfTargetMonthAndYear.getFirstDayOfWeek();
int daysPerWeek = firstDayOfTargetMonthAndYear.getMaximum(7);
int dayOfWeek = firstDayOfTargetMonthAndYear.get(7);
return (daysPerWeek + (dayOfWeek - firstDayOfWeek)) % daysPerWeek;

}
/**
* @return nothing.. but prints values that differ with the locale
* */

}

Wednesday, December 14, 2011

Custom login interceptor


The login in a struts page needs a proper interceptor to handle all possible cases of login... Have tried to make one that works :)


package com.interceptor;


import java.util.List;


import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;


import org.apache.commons.lang.xwork.StringUtils;
import org.apache.struts2.StrutsStatics;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;






import com.opensymphony.xwork2.ActionContext;
import com.opensymphony.xwork2.ActionInvocation;
import com.opensymphony.xwork2.interceptor.AbstractInterceptor;
import com.user.User_Info;


public class LoginInterceptor extends AbstractInterceptor implements StrutsStatics
{


private static final long serialVersionUID = 1L;
HttpSession session;
HttpServletRequest request;
HttpServletResponse response;
String t;

@Override
public String intercept(ActionInvocation actin) throws Exception
{
//System.out.println("call interceptor");
ActionContext ctx=actin.getInvocationContext();
request=(HttpServletRequest)ctx.get(HTTP_REQUEST) ;
response=(HttpServletResponse) ctx.get(HTTP_RESPONSE);
session =  request.getSession (true);
Object user = session.getAttribute ("user");
   if (user == null)
   {
    //System.out.println("inside near user=null");
    String trylog = request.getParameter("user");
       if (! StringUtils.isBlank (trylog) )
       {
        //System.out.println("try");
        String usern=request.getParameter("user").toString();
   
        String pass=request.getParameter("pass").toString();
        SessionFactory sf=new Configuration().configure("hibernate.cfg.xml").buildSessionFactory();
        Session ses= sf.openSession();
        //System.out.println("enter id");
        User_Info u=new User_Info();
        try
        {
        List l = ses.createQuery("select password from com.user.User_Info e where e.user_id="+"'"+usern+"'").list();
        for(Object o:l)
        {
        t=(String) o;
       
        }


        }
        catch (HibernateException e)
        {
        e.printStackTrace();
        }
        if(pass.equals(t.toString()))
        {
        //System.out.println("ho gaya");
        session.setAttribute("user", usern);
        //request.setAttribute("page", "dummy.jsp");//Following line of code is for a concern of a project for using a master page to include other pages by calling the page required to be included from session
        return "success";
        }
        /*else
        {
        System.out.println("cookie");
        Cookie c[]=request.getCookies();
        if(c==null)
{
       
        int counter = 1;
        Cookie cnt = new Cookie("count",new Integer(counter).toString());
        cnt.setMaxAge(65000);
        System.out.println(cnt);
        response.addCookie(cnt);
        }
        else
{
        for(int i=0;i<c.length;i++)
{
        if(c[i].getName().equals("count"))
        {
        int count = Integer.parseInt(c[i].getValue());
         if(count<3)
 {
          count++;
          //System.out.println("now="+count);
          c[i].setValue(new Integer(count).toString());
          response.addCookie(c[i]);
          return "login";
         }
         else
         {
         count++;
         c[i].setValue(new Integer(count).toString());
         response.addCookie(c[i]);
 return "change";
         }
        }
        }
}
       
     
        ses.close();
        System.out.println(u);
        }*/
    return "login";
       
       }


       else
{
           
         Object action = actin.getAction ();
     if (action instanceof com.opensymphony.xwork2.ValidationAware)
 {
    ((com.opensymphony.xwork2.ValidationAware) action).addActionError ("Username / password / code incorrect!");
     }
   
       }
       return "login";
   }
else
    {
    return actin.invoke();
    }
    }
}

Tuesday, November 29, 2011

Distance Calculator using google api distance matrix, and json parsing




Was working on google api for my project to calculate the distance between 2 places.. Google maps has that functionality but i needed a sneak peek into the api for that.. I did a little research for the same and found out that they used json objects in Distance Matrix for the same. So i tried to learn json parsing which is very simple by the way. You will need the json jar file to run this and its quite good at calculating distance between any two places on earth...
Try it out.. Its very simple.


the data from the url for 2 places say noida to delhi will be a json object of the format


You just need to know how to parse through and get thae data. :)

//JSON object :

{
   "destination_addresses" : [ "Bokaro, झारखण्ड, India" ],
   "origin_addresses" : [ "Noida, Delhi, India" ],
   "rows" : [
      {
         "elements" : [
            {
               "distance" : {
                  "text" : "1,201 km",
                  "value" : 1200769
               },
               "duration" : {
                  "text" : "15 hours 19 mins",
                  "value" : 55119
               },
               "status" : "OK"
            }
         ]
      }
   ],
   "status" : "OK"
}


//JSONInterface.java

package com.distanceMatrix;


import java.io.*;
import java.net.URL;
import java.nio.charset.Charset;
import org.json.*;


public class JSONInterface 
{
Integer tem;
Float dist;


private static String readAll(Reader rd) throws IOException 
{
   StringBuilder sb = new StringBuilder();
   int cp;
   while ((cp = rd.read()) != -1) 
   {
     sb.append((char) cp);
   }
   return sb.toString();
}

public static JSONObject readJsonFromUrl(String url) throws IOException, JSONException 
{
   InputStream is = new URL(url).openStream();
   try 
   {
     BufferedReader rd = new BufferedReader(new InputStreamReader(is, Charset.forName("UTF-8")));
     String jsonText = readAll(rd);
     JSONObject json = new JSONObject(jsonText);
     return json;
   }
   finally 
   {
     is.close();
   }
}


public float calcDistance(StringBuffer beg, StringBuffer end) 
{
JSONObject json=null;
try 
{

json = readJsonFromUrl("https://maps.googleapis.com/maps/api/distancematrix/json?origins="+beg+"&destinations="+end+"&mode=driving&sensor=false");
json.get("rows");
JSONArray arr=null;
arr = json.getJSONArray("rows");
tem=(Integer)arr.getJSONObject(0).getJSONArray("elements").getJSONObject(0).getJSONObject("distance").getInt("value");
dist=(float)tem/1000;
}
catch (JSONException e) 
{
e.printStackTrace();

catch (IOException e)
{
    e.printStackTrace();
}
return dist;
}
}




//JSONDiatanceCalculator.java


package com.distanceMatrix;


import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;


public class JSONDiatanceCalculator 
{
static String beg,end;
static StringBuffer start=new StringBuffer(), stop=new StringBuffer();
static float totDistance;
public static void main(String args[])
{
JSONInterface ji=new JSONInterface();
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
try
{
System.out.println("Enter the source place : ");
beg=br.readLine();
System.out.println("Enter the distance place : ");
end=br.readLine();
start.append(beg);
stop.append(end);
for(int i=0; i<start.length(); i++)
{
if(start.charAt(i)==' ')
{
start.deleteCharAt(i);
start.insert(i, "%20");
}
}
for(int i=0;i<stop.length();i++)
{
if(stop.charAt(i)==' ')
{
stop.deleteCharAt(i);
stop.insert(i, "%20");
}
}
totDistance=ji.calcDistance(start,stop);
System.out.println("The distance between "+beg+" and "+end+" is = "+totDistance);
}
catch(IOException e)
{
System.out.println("Improper place value set..");
e.printStackTrace();
}
catch(Exception e)
{
System.out.println("Json type exception");
e.printStackTrace();
}
}
}

There was an error in this gadget