Call DB2 SP from Java CallableStatement . CallableStatement in java example

JDBC provides three statement interface which is used to send an SQL statement to the database server.
1. Statement
2. PreparedStatement which extends Statement- You can visit my earlier post for detail on PreparedStatement object and example in DB2
3. CallableStatement which extends PreparedStatement
Vendors of JDBC Driver provide classes that implement the above interfaces. Without a JDBC driver , you cannot create objects based on these interfaces .Database connection is required to to create statement object. This tutorial explains about CallableStatement objects and also how to call a DB2 stored procedure from a java application. Please go through my earlier post on how to create stored procedure in db2 . Stored procedure can be called using the SQL CALL statement in DB2. Now how to call the stored procedures located on the database server from your Java application ? Use CallableStatement interface in java.
CallableStatement object enable you to call and execute stored procedures stored on the database server from your Java application . Three types of JDBC parameters are there . They are IN, OUT, and INOUT
1. IN - parameters used for input . You can set values to IN parameters with the setXXX() methods.
2. OUT - result parameter used for output which returns output value of the stored procedure.
3. INOUT - parameter used for both input and output values parameters
A question mark (?) symbol serves as a placeholder for a parameter. The call to invoke the Stored procedure is written in an JDBC escape syntax that may take the followings forms
1. {call procedure_name[(?, ?, ...)]} - which accepts input parameters but no result parameter
2. {? = call procedure_name[(?, ?, ...)]} which returns a result parameter
3. {call procedure_name} - for no input / output parameters

A CallableStatement can return one or multiple ResultSet objects. Multiple ResultSet objects are handled using operations inherited from Statement. cstmt.getMoreResults(); -used to point to the second or next result set .
Now let us see the steps to create a CallableStatement Object . Before creating it , let us create a stored procedure in DB2 . In our example , I have created two Stored procedures in db2.
Ist Stored Procedure (SP) example : to return all records matching with a given salesman_id and date of sale
input - salesmanid , sales_date
output - result set (salesman_id, salesman, item_name , sales_date, sales_amt)

Stored Procedure for the above problem is given below

   
CREATE PROCEDURE  ItemSalesBy (salesmanid  varchar(5), dateofsale date)     SPECIFIC sp10    DYNAMIC RESULT SETS 1
P1: BEGIN
    DECLARE cursor1 CURSOR WITH RETURN FOR   select salesman_id, (select c.name from salesman c where c.salesman_id=b.salesman_id) SalesMan,  (select a.item_name from item_master a where a.item_code=b.item_code) ItemName, sales_date, sales_amt from salesmantxn b  where b.salesman_id=salesmanid and b.sales_date=dateofsale;
    OPEN cursor1;
END P1
@



We can create and call (execute) the above stored procedure using command line processor (CLP) and through java program.
to create stored procedure using CLP , store above stored procedure in a file . for example salesman.sql . Now run the following commands
db2 connect to test
db2 -td@ -vf salesman.sql
to call the above SP through CLP
db2 call ItemSalesBy('101','2012-02-25') , where 101 is the salesman id , 2012-02-25 is the date of sale
Now let us create and call (execute) the above stored procedure using java program

To create CallableStatement object , the following statements are used
CallableStatement cstmt = null;
cstmt = conn.prepareCall ("{ call ItemSalesBy(?,?)}"); // Callablestatement object to call the stored procedure
where ItemSalesBy is the SP name and two question mark (?,?) is used to pass input parametters. In our example , salesman_id , sales_date . The following java program drops the existing SP named ItemSalesBy and creates the same SP and executes with the given input parameters. The statement ResultSet rs = cstmt.executeQuery(); returns the result set returned by the SP .

   
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class CSPRS2 {
    static Connection conn;
    public static void main(String[] args) {
    
       try {
              Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
             } catch (ClassNotFoundException e) {
      e.printStackTrace();
      return; }

 try
        {
               conn = DriverManager.getConnection("jdbc:db2:test");
            }
        catch( Exception e )
        {
            e.printStackTrace();
            System.exit(1);
        }
  //  dropSP(); // to drop procedure if already exists 
    createSP();  // to create stored procedure (SP)
       callSP("101", "2012-02-25"); // to execute SP
    }

    private static void dropSP() {
        String str=null;
        Statement stmt = null;
        try
        {
            stmt = conn.createStatement();
           str="drop procedure ItemSalesBy";
            stmt.executeUpdate (str);
        }
        catch (SQLException e1)
        { e1.printStackTrace();}
       }

    private static void createSP() {
        String strSP=null;
        Statement stmt = null;

      strSP="CREATE PROCEDURE  ItemSalesBy (salesmanid  varchar(5), dateofsale date)  SPECIFIC sp10    DYNAMIC RESULT SETS 1 \n" +
             "P1: BEGIN \n" +
             "DECLARE cursor1 CURSOR WITH RETURN FOR   select salesman_id, (select c.name from salesman c  where c.salesman_id=b.salesman_id) SalesMan,  (select a.item_name from item_master a where a.item_code=b.item_code) ItemName, sales_date, sales_amt from salesmantxn b  where  b.salesman_id=salesmanid and b.sales_date=dateofsale; \n" +
             "OPEN cursor1; \n" +
              "END P1 \n";
        try
        {
            stmt = conn.createStatement();
            stmt.executeUpdate (strSP);
            System.out.println("Stored Procedure created successfully\n");
        }
        catch (SQLException e)
        {
            System.out.println("Error in creating SP: " + e.toString());
            System.exit(1);
        } 
    }

    private static void callSP(String sid, String sdate)
    {
        CallableStatement cstmt = null;
        try
        {
            cstmt = conn.prepareCall ("{ call ItemSalesBy(?,?)}");  // CallableStatement object  to call the stored procedure
            cstmt.setString(1,sid);
            cstmt.setDate(2, java.sql.Date.valueOf(sdate));
            ResultSet rs = cstmt.executeQuery();
            System.out.println("SALESMAN_ID SALESMAN              ITEMNAME      SALES_DATE    SALES_AMT\n");
            System.out.println("----------- ---------------- ------------------- ----------  ------------\n");
            if (rs != null) {
 while (rs.next())
 {
  System.out.println(rs.getInt(1) + "             " + rs.getString(2) + "       " + rs.getString(3) + "      " + rs.getString(4) +"       "+rs.getString(5));
 }
                    }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }
}


Output :

2. to calculate wages for a salesman on a particular date based on sales_amt. wages is calculated with the following formula
wages = total_sales_amt * 0.5 /100 + bonus ; and Rs. 100 bonus is added if the sales_amt >10000 and sales_amt<=200000, Rs. 200 bonus if sales_amt>200000 and sales_amt<=300000 , Rs. 300/- bonus if sales_amt>300000

IN Parameter : salesman_id, Sales_date
OUT parameter : wages

Stored Procedure for the above problem is given below

   
CREATE PROCEDURE wagesCalc(IN salesmanid varchar(5)  ,IN SalesDate date , OUT  wages  double)   LANGUAGE SQL
  BEGIN
DECLARE bonus double;
DECLARE sumsales double;
DECLARE wages_temp double;
    DECLARE cursor1 CURSOR FOR SELECT SUM(SALES_AMT),  SUM(SALES_AMT)*0.5/100 FROM SALESMANTXN where SALESMAN_ID=salesmanid  and SALES_DATE=SalesDate;
      SET bonus= 0;
     OPEN cursor1;
     FETCH FROM cursor1 INTO sumsales, wages_temp;
   
IF (sumsales>300000) THEN
 set bonus=300;
ELSEIF (sumsales>200000 and sumsales<=300000) THEN
 set bonus=300;
ELSEIF (sumsales>100000 and sumsales<=200000) THEN
 set bonus=100;
END IF;
     CLOSE cursor1;
SET wages = wages_temp+ bonus;
 END%



to create above SP using CLP , store above stored procedure in a file . eg. wages.sql . Now run the following commands
db2 connect to test
db2 -td% -vf wages.sql
to call the above SP through CLP
db2 call wagesCalc('101', '2012-02-25', ?) , which returns wages for the saleman_id=101 and sales_date='2012-02-25'
Now let us see how to call (execute) the above stored procedure using java program

        private static void callSP(String sid, String sdate)
    {
          Double wages=0.0;
        CallableStatement cstmt = null;
        try
        {
            cstmt = conn.prepareCall ("{ call wagesCalc(?,?,?)}");  // CallableStatement object  to call the stored procedure
            cstmt.setString(1,sid);
            cstmt.setDate(2, java.sql.Date.valueOf(sdate));
            cstmt.registerOutParameter(3, Types.DOUBLE);
          cstmt.execute();
                      wages  = cstmt.getDouble(3);
               System.out.println("Sales Man Id = " + sid  + " Wages= " + wages);
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }


Output of the above program :
D:\as2\JF5>java CSPRS4
Sales Man Id = 101 Wages= 650.0
In the above program , registerOutParameter(3, Types.DOUBLE); Registers the OUT parameter in ordinal position parameterIndex to the JDBC type sqlType
The execute() returns boolean value . if it returns false means , first result is an update count or there is no result ; true means , the first result is a ResultSet object

check parentheses in a string / expression are balanced using Regex / Stack . Find Brackets are matched or not in JAVA Program

One of the interesting  java program given below  that may be asked in core java interview or the same may be given to   students to solve at practical labs

         Write a java program that reads a string (expression) from standard input and find  whether its parentheses "(" , ")" , brackets "[", "]"  and  Braces "{", "}"    are properly balanced or not.  i.e. Every open parentheses"("  , braces "{" and brackets "["  should have closing  parenthesis  ")" , braces "}" and brackets "]" respectively in the right order .   For example, the  program will give output
   true for the following input
              1.   ((29*12)+(54+22))

               2.  {[()()]()}

               3. ((([]{}))((())))

     and false for the following

               1.  [(]).

                2. ((())))

                3.  ((29*12*([5*5+7]*5)) + ((54+22))

Please assume that , '(' , '{' , '[' are open characters and ')' , '}' , ']'  are closing characters . Let us solve the above problem now.
 .
 Method I :   Best way to solve the above problem is using stack data structure .  As we know , stack is a  storage mechanishm that works on  Last -in-First-Out (LIFO) that means  the last item stored on the stack  is the first one to be removed.  For more on Stack , please visit my earlier tutorial Stack a FIFO data structure

How to solve the above problem using stack ?
    1) Read the input string (expression ) from the keyboard
   2) Create a stack object to accept  character only.
    3. Traverse the input string / expression
         a) If the current character is a open (left )  parenthesis '('  or brace '{'  or bracket '['  then push the corresponding closing (right) character (i.e. ')' , '}' , ']')  to the stack. You can push the open chracters.   I have Pushed the closing charaters to the stack instead of left to  reduce the little bit lines of logic.
         b) If the current character is a closing (right) parenthesis ')'  , brace '}' and bracket ']' ,
                         i)  check for the stack is empty , if it is empty , then the parentheses are not balanced  because there is no open characters for the corresponding closing charaters.
                   ii)  if stack is not empty , then check the current character with top character  (get the top character using peek() )  in the stack . If both are equal, then pop the character from the stack and check for the next character.
  4)  Once the traversal of the expression is completed , check for the stack is empty or not . If stack is empty , then parentheses are balanced otherwise not balanced.

The code is as follows .

import java.util.Stack;

import java.util.Scanner;

public class Parentheses1 {

  public static boolean isBalanced1(String exp)  {

      Stack<Character> stack = new Stack<Character>();

 for (int i=0; i<exp.length(); i++) {

  char c = exp.charAt(i);

  switch (c) {

  case '{' : stack.push('}'); break;

  case '(' : stack.push(')'); break;

  case '[' : stack.push(']'); break;

  case ']' : case ')' : case '}' :

   if (stack.isEmpty())

      return false;

   else   if (stack.peek() == c) {

   stack.pop();

   }

   break;

  default : break;

  }

 }

 return stack.isEmpty();

      }

Some of the other tries to solve the above program.

Method II using Regular expression (Regex)  . You can also try without using regex . I have used very simple  regex .  Steps to solve the above problem  are as follows.

1. Remove all characters like digits , operators, etc  except '(', ')' , '{', '}' , '[' , ']' in the expression.
 2. Replace  every occurrence of  '()' , '{}' , '[]'  in the string  with empty string ("")  (i.e  removal of characters)
 3. Repeat the step 2 ,  until you find that nothing has been replaced .
 4. Now check for the expression  is empty or not. If the string is empty , then  parentheses are perfectly  balanced  , otherwise not.

Code is as follows

public static boolean isBalanced2(String exp)  {

String temp="";

while (exp.trim().length() > 0  )

{

exp=exp.replaceAll("[^\\(\\)\\[\\]\\{\\}]", "") ;

exp=exp.replaceAll("\\(\\)", "");

exp=exp.replaceAll("\\{\\}", "");

exp=exp.replaceAll("\\[\\]", "");

if (exp.trim().equals(temp)) break;

temp=exp;

}

if ( exp.trim().length() ==0) return true;

else {

    System.out.println("Unmatched / Unbalanced characters " + exp);

  return false;

     }

   }

Method III :  Using counter . But this method  is not suitable for all situations .   It just checks , whether the expression has equal number of opening and  closing  parentheses or  brackets or braces and does not check the order. For example, consider the following  expression.
  ([{)}]
 The above expression  has the equal number of opening and closing parentheses / brackets / braces . But it is  not in the right order. The right order may be  ([{}])
 This method may be suitable , if you use either   parentheses or  brackets or  braces in the expression.  eg.   ((()(()()))) . Otherwise  the order should be correct, if you use all .

Steps :
              1. Initialize three counters to 0 , one for parentheses, one for braces , and one for bracket
              2. Traverse the expression by a single  character at a time.
  a) If the current character is '(' , '{' , '[' , then   increment the respective counter by one
  b) If the current character is  ')', '}' , ']' , then decrease one from the respective counter variable.
               3. Check whether all the counters are zero or not. If all counters are having 0 , then the parentheses are balanced  , otherwise not . Please always remember limitation using this method.

 Code is as follows.

public static boolean isBalanced3(String exp)  {

int c1=0, c2=0,c3=0;

 for (int i=0; i<exp.length(); i++) {

  char c = exp.charAt(i);

  switch (c) {

  case '{' : c1++; break;

  case '(' : c2++; break;

  case '[' : c3++; break;

  case '}' : c1--; break;

  case ')' : c2--; break;

  case ']' : c3--; break;

  default : break;

  }

     }

if(c1==0 && c2==0 && c3==0)  return true;

else  return false;

      } 

//Main Method to call the above three methods

public static void main(String[] args) {

Scanner sc=new Scanner(System.in).useDelimiter("\n");;

  

String s = sc.next();

 System.out.println("Balanced checking using stack " + isBalanced1(s));

 System.out.println("Balanced checking using regex " + isBalanced2(s));

 System.out.println("Balanced checking using counter " + isBalanced3(s));

    }

} 

create dynamic HTML in JavaScript | Javascript code to add / remove rows dynamically in a table of HTML

This tutorial covers about how to add or  remove rows  in  a table of a HTML  dynamically  using javascript code .   HTML documets can be easily accessed and manipulated using HTML DOM , which represents an HTML document as a tree-structure.  When a  HTML document is  loaded in a browser window , it  becomes a Document object. Now we can use the Document object  to  access to all HTML elements  (as node objects) in a page, using  any script such as Javacript . Using  Document object ,  we can add or remove  element in the html as we do for  xml,  You can gothrough my earlier post to create xml using DOM .
              Let us see some of the following  HTML DOM properties and methods to access / modify the html documents , before writing the actual  program .

Some DOM properties:

nodeName - returns the name of an element
 nodeValue - returns the value of an element
 parentNode - returns the parent node of an element
 childNodes - returns the child nodes of element
 attributes - returns the attributes nodes of element

innerHTML -  to get or modify the content of  an element

   Suppose  x is a node object (HTML element) , the above properties can be accessed  as  x.innerHTML , x.nodeName, ....

Similarly some DOM methods:

getElementsByTagName(name) - get all elements with a specified tag name
 getElementById(id) - get the element with a specified id
 appendChild(node) - insert a child node to the element
 removeChild(node) - removes a child node from the element

eg. document.getElementById("tableId"), returns the table object.  you can insert , row & columns using the table object.
 to get the content (text)  of an element <p id="test">This is a test</p>  , you can use   innerHTML OR   childNodes and nodeValue properties with the method   getElementById

txt = document.getElementById("test").innerHTML;
 or
  txt=document.getElementById("test").childNodes[0].nodeValue;

Now let us design HTML table  and write javascript code to add or remove rows in a table .   Consider the web page to accept passenger details and child passenger details for booking ticket in a train or bus .

To accept  , passenger details , we need to  insert  rows and cloumns in a table ,  based on the first row  of the table.  The first row is mandatory to accept atleast one passenger details .



Table design with one row and heading



<H1>Passengers Details</H1>


    <TABLE id="PDetailsTable" width="350px" border="1">

     <tr>

       <td>SNO</td>

       <td>Name</td>

       <td>Age</td>

       <td>Gender</td>

       <td>Food Preference</td>

       <td>Berth Preference</td>

       <td>Senior Citizen</td>

       <td>Select to Delete</td>

   </tr>    



        <tr>



            <td> 1 </td>



            <td> <input type="text" name="name" /> </td>

            <td> <input type="text" name="age" /> </td>

            <td>  <select name="Gender">

                     <option value="M">Male</option>

                    <option value="F">Female</option>

                  </select>

           </td>

               

            <td> <select name="Food">

                    <option value="v">Veg</option>

                    <option value="nv">Non-Veg</option>

                </select>

            </td>

              

            <td> <select name="BerthPreference">

                    <option value="l">Lower</option>

                    <option value="m">Middle</option>

                    <option value="u">Upper</option>

                    <option value="sl">Side Lower</option>

                    <option value="su">Side Upper</option>

                </select> </td>

              

            <td><input type="checkbox" name="SCchk" /></td>

          

             <td><input type="checkbox" name="delchk" /></td>



        </tr>    </TABLE> 


Two input  buttons to call   addPassenger function  to insert dynamic row and to call deletePassengerRow  to remove row from the table dynamically.
  


   <input type="button" value="Add More Passengers" onclick="addPassenger('PDetailsTable')" />

    <input type="button" value="Delete Row" onclick="deletePassengerRow('PDetailsTable')" /> 

Two javascript functions  to add and remove dynamic rows and columns  and one more function ( arrangeSno)  to arrange serial numbers when the row is removed from the table are as follows




to arrange serial numbers



 function arrangeSno(tableId)

 {

             var tblObj = document.getElementById(tableId);

             var no_of_rows = tblObj.rows.length;

              for(var i=0; i<no_of_rows-1; i++)

           tblObj.rows[i+1].cells[0].innerHTML = i+1;

 }



To add rows dynamically based on the first row in a table



      function addPassenger(tableId) {


            var tblObj = document.getElementById(tableId);


            var no_of_rows = tblObj.rows.length;
    

// to insert single row

            var row_in_table = tblObj.insertRow(no_of_rows);


// to get the number of colums from the first row


             var colCount = tblObj.rows[0].cells.length;


// to increment sno from the previous row by one

           var rno=eval(tblObj.rows[no_of_rows-1].cells[0].innerHTML) +1;


            for(var i=0; i<colCount; i++) {


// to insert one column

               var column_in_row = row_in_table.insertCell(i);



                column_in_row.innerHTML = tblObj.rows[1].cells[i].innerHTML;

              

                if (i==0)  column_in_row.innerHTML = rno;



// to fill new row with  blank  text box, unchecked checkbox ,  combo selected with index 0 when the row is created , eventhough  the first row of the table having the textbox with filled values , etc...



                switch(column_in_row.childNodes[0].type) {

                    case "text":

                            column_in_row.childNodes[0].value = "";

                            break;

                    case "checkbox":

                            column_in_row.childNodes[0].checked = false;

                            break;

                    case "select-one":

                            column_in_row.childNodes[0].selectedIndex =0;

                            break;

                }

            }

        }





To remove rows dynamically  from the table .



        function deletePassengerRow(tableId) {

                    var tblObj = document.getElementById(tableId);

   
                 var delchkbox=document.bookingForm.delchk;    // where bookingForm is the form name


        var no_of_rows = delchkbox.length;

         for(var i=0; i<no_of_rows;i++)

         {

// heading and atleast one row should be in the table

         if (delchkbox[i].checked && tblObj.rows.length>=3)

         {

             tblObj.deleteRow(i+1);

                 no_of_rows--;

      i--;

          }

                }


          arrangeSno(tableId);        } 


To accept  ,child  passenger details ,  we are creating  elements like textbox , checkbox , dropdown list dynamically. These elements are inserted in the table's columns in a row  as a child element . Table rows and columns are also created dynamically . No row is mandatory . Table heading is displayed , if the table has atleast one row , otherwise , table heading is hidden.

Table design with heading without any initial rows



<H1>Child Passengers Details</H1>

  

    <TABLE id="childPassTable" width="350px" border="1" style="visibility:hidden" >

        <tr>

         <td>SNO</td>

         <td>Name</td>

         <td>Age</td>

         <td>Gender</td>

         <td>Select to delete</td>

    </tr>         </TABLE> 

Two buttons to call  addRowForChild &  deleteRowChild




 <P> <input type ="button" value="Add Child Passengers"  onclick="addRowForChild('childPassTable')">  <input type="button" value="Delete Row" onclick="deleteRowChild('childPassTable')"></P> 

Two javascript functions to add rows / remove rows in a table  and to create textbox , checkbox , combo box dynamically  for accepting child passenger details  .



    function addRowForChild(tableId) {



            var tblObj = document.getElementById(tableId);


            var no_of_rows = tblObj.rows.length;


// to make visible of the header row of the table , if the table has atleast one row
            if(no_of_rows==1)

             tblObj.style.visibility="visible";



// to insert one row of the table

            var row_in_table = tblObj.insertRow(no_of_rows);


//to insert the first column (for SNO)

            var column1 = row_in_table.insertCell(0);



      if (no_of_rows==1) 
         var rno=1;

     else
        var rno=eval(tblObj.rows[no_of_rows-1].cells[0].innerHTML) +1;


            column1.innerHTML = rno;


//to insert the second column (for textbox to accept name)

            var column2 = row_in_table.insertCell(1);

            var element2 = document.createElement("input");

            element2.setAttribute("name", "nameChild");  //to set name of the text box

            element2.type = "text";

            column2.appendChild(element2);


 //to insert the second column (for textbox to accept age)
            var column3 = row_in_table.insertCell(2);

            var element3 = document.createElement("input");

            element3.setAttribute("name", "ageChild");

            element3.type = "text";

            column3.appendChild(element3);


 //to insert the fourth column (for combo box to select gender)

            var column4 = row_in_table.insertCell(3);

            var combo = document.createElement("select");

            combo.setAttribute("name", "genderChild");

            var option = document.createElement("option");

            combo.options[0] = new Option("Male", 1);

             combo.options[1] = new Option("Female", 2);

            column4.appendChild(combo);

// to insert the fifth column (for check box )          

            var column5 = row_in_table.insertCell(4);

           var element5 = document.createElement("input");

            element5.type="checkbox";

            column5.appendChild(element5);

          

        }




       function deleteRowChild(tableId) {


            var tblObj = document.getElementById(tableId);

            var no_of_rows = tblObj.rows.length;


            var i=0;

            while (i<no_of_rows)

            {

                var row_in_table = tblObj.rows[i];

                var chkbox = row_in_table.cells[4].childNodes[0];

                if( chkbox !=null &&  chkbox.checked==true) {

                    tblObj.deleteRow(i);

                    no_of_rows--;

                    i--;

// to hide the table heading , if there is no row

            if(no_of_rows<=1) tblObj.style.visibility="hidden";

                }

                i++;

                }

              

             arrangeSno(tableId);

                   } 





Sample code to send / pass /  process the elements created  dynamically  in the page  to server side.            



  String[] names=request.getParameterValues("name");

  

  String[] birthPreference=request.getParameterValues("BerthPreference");



  String[] nameChild=request.getParameterValues("nameChild");

   .....

   .....

   .....

       for (int i = 0; i < names.length; i++)

    

          System.out.println("Name=" + names[i] + " Birth Preferance= " +  birthPreference[i] );

  

   .......   ........

Pdf Writer in C# without any using Paid dll | Pdf writer using Microsoft.Office.Interop.Word in c#

Below code for PDf Writer by using Microsoft.Office.Interop.Word  dll

Pdf Writer in C# without any using Paid dll | Pdf writer using Microsoft.Office.Interop.Word in c#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using Microsoft.Office.Interop.Word;
using System.Diagnostics;
using Microsoft.Office.Interop;
using System.Reflection;
namespace PdfGenerator
{
    class Program
    {
        static void Main(string[] args)
        {
            CreateDocFileInFolder();
            Microsoft.Office.Interop.Word.Application word =
                new Microsoft.Office.Interop.Word.Application();
          
            object oMissing = System.Reflection.Missing.Value;
            // Get list of Word files in specified directory
            DirectoryInfo dirInfo = new DirectoryInfo(@"C:\New folder\New Folder");
            FileInfo[] wordFiles = dirInfo.GetFiles("*.doc"); word.Visible = false;
            word.ScreenUpdating = false;

            foreach (FileInfo wordFile in wordFiles)
            {     // Cast as Object for word Open method   
                Object filename = (Object)wordFile.FullName;
                // Use the dummy value as a placeholder for optional arguments 
                Document doc = word.Documents.Open(ref filename, ref oMissing,
                    ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing,
                    ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing,
                    ref oMissing, ref oMissing, ref oMissing, ref oMissing);
                doc.Activate();
                object outputFileName = wordFile.FullName.Replace(".doc", ".pdf");
                object fileFormat = WdSaveFormat.wdFormatPDF;
                // Save document into PDF Format  
                doc.SaveAs(ref outputFileName,
                    ref fileFormat, ref oMissing, ref oMissing,
                    ref oMissing, ref oMissing, ref oMissing, ref oMissing,
                    ref oMissing, ref oMissing, ref oMissing, ref oMissing,
                    ref oMissing, ref oMissing, ref oMissing, ref oMissing);
                // Close the Word document, but leave the Word application open.
                // doc has to be cast to type _Document so that it will find the  
                // correct Close method.                  
                object saveChanges = WdSaveOptions.wdDoNotSaveChanges;
                ((_Document)doc).Close(ref saveChanges, ref oMissing, ref oMissing);
                doc = null;

            }
           


        }

        private static void CreateDocFileInFolder()
        {
          
              

                //Start Word and create a new document.
                Microsoft.Office.Interop.Word._Application oWord;
                Microsoft.Office.Interop.Word._Document oDoc =null;
                try
                {
                    object oMissing = System.Reflection.Missing.Value;
                    object oEndOfDoc = "\\endofdoc"; /* \endofdoc is a predefined bookmark */

                    oWord = new Microsoft.Office.Interop.Word.Application();
                    oWord.Visible = true;
                    oDoc = oWord.Documents.Add(ref oMissing, ref oMissing,
                        ref oMissing, ref oMissing);

                    //Insert a paragraph at the beginning of the document.
                    Microsoft.Office.Interop.Word.Paragraph oPara1;
                    oPara1 = oDoc.Content.Paragraphs.Add(ref oMissing);
                    oPara1.Range.Text = "TUIFly Cabin Crew Application";
                    oPara1.Range.Font.Bold = 1;
                    oPara1.Format.SpaceAfter = 24;    //24 pt spacing after paragraph.
                    oPara1.Range.InsertParagraphAfter();
                    // Insert Image

                    string filename1 = @"C:\BBC\tuiflylogo.jpg";
                    // now add the picture in active document reference
                    oDoc.InlineShapes.AddPicture(filename1, Type.Missing, Type.Missing, Type.Missing);



                    //Insert a paragraph at the end of the document.
                    Microsoft.Office.Interop.Word.Paragraph oPara2;
                    object oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
                    oPara2 = oDoc.Content.Paragraphs.Add(ref oRng);
                    oPara2.Range.Text = "TUIfly Netbook Application ";
                    oPara2.Format.SpaceAfter = 6;
                    oPara2.Range.InsertParagraphAfter();

                    //Insert another paragraph.
                    Microsoft.Office.Interop.Word.Paragraph oPara3;
                    oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
                    oPara3 = oDoc.Content.Paragraphs.Add(ref oRng);
                    oPara3.Range.Text = "Dynamic Table for Crew 3LTR Code and Function:";
                    oPara3.Range.Font.Bold = 0;
                    oPara3.Format.SpaceAfter = 24;
                    oPara3.Range.InsertParagraphAfter();

                    //Insert a 3 x 5 table, fill it with data, and make the first row
                    //bold and italic.
                    Microsoft.Office.Interop.Word.Table oTable;
                    Microsoft.Office.Interop.Word.Range wrdRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
                    oTable = oDoc.Tables.Add(wrdRng, 3, 5, ref oMissing, ref oMissing);
                    oTable.Range.ParagraphFormat.SpaceAfter = 6;
                    int r, c;
                    string strText;
                    for (r = 1; r <= 3; r++)
                        for (c = 1; c <= 5; c++)
                        {
                            strText = "r" + r + "c" + c;
                            oTable.Cell(r, c).Range.Text = strText;
                        }
                    oTable.Rows[1].Range.Font.Bold = 1;
                    oTable.Rows[1].Range.Font.Italic = 1;

                    //Add some text after the table.
                    Microsoft.Office.Interop.Word.Paragraph oPara4;
                    oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
                    oPara4 = oDoc.Content.Paragraphs.Add(ref oRng);
                    oPara4.Range.InsertParagraphBefore();
                    oPara4.Range.Text = "And Table you Want:";
                    oPara4.Format.SpaceAfter = 24;
                    oPara4.Range.InsertParagraphAfter();

                    //Insert a 5 x 2 table, fill it with data, and change the column widths.
                    wrdRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
                    oTable = oDoc.Tables.Add(wrdRng, 5, 2, ref oMissing, ref oMissing);
                    oTable.Range.ParagraphFormat.SpaceAfter = 6;
                    for (r = 1; r <= 5; r++)
                        for (c = 1; c <= 2; c++)
                        {
                            strText = "r" + r + "c" + c;
                            oTable.Cell(r, c).Range.Text = strText;
                        }
                    oTable.Columns[1].Width = oWord.InchesToPoints(2); //Change width of columns 1 & 2
                    oTable.Columns[2].Width = oWord.InchesToPoints(3);

                    //Keep inserting text. When you get to 7 inches from top of the
                    //document, insert a hard page break.
                    object oPos;
                    double dPos = oWord.InchesToPoints(7);
                    oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range.InsertParagraphAfter();
                    do
                    {
                        wrdRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
                        wrdRng.ParagraphFormat.SpaceAfter = 6;
                        wrdRng.InsertAfter("A line of text");
                        wrdRng.InsertParagraphAfter();
                        oPos = wrdRng.get_Information
                                       (Microsoft.Office.Interop.Word.WdInformation.wdVerticalPositionRelativeToPage);
                    }
                    while (dPos >= Convert.ToDouble(oPos));
                    object oCollapseEnd = Microsoft.Office.Interop.Word.WdCollapseDirection.wdCollapseEnd;
                    object oPageBreak = Microsoft.Office.Interop.Word.WdBreakType.wdPageBreak;
                    wrdRng.Collapse(ref oCollapseEnd);
                    wrdRng.InsertBreak(ref oPageBreak);
                    wrdRng.Collapse(ref oCollapseEnd);
                    wrdRng.InsertAfter(":::::::");
                    wrdRng.InsertParagraphAfter();
                    object filename = @"C:\New folder\New Folder\MyDoc.doc";
                 
                    oDoc.SaveAs(ref filename, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing,
                        ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing);

                 
                    wrdRng.InsertAfter("THE END.");
                }

                catch (Exception ex)
                {

                    if (oDoc != null)
                    {
                        oDoc.Close();
                    }
                 

                }
                finally
                {
                    if (oDoc != null)
                    {
                        oDoc.Close();
                    }
                }
         


       

        }
    }
}

Volatile versus Synchronised in multithreading in java | Difference between Volatile and Synchronised

This tutorical covers about the differences between  synchronized and volatile  modifier . To know lot more about   volatile keyword and how it works in java? , please visit my earlier tutorial volatile modifier in java . The following are the differences between synchronized and volatile  modifier which is one of the core java interview question..

1. Synchronization ensures  both   visibilty   and  mutual exclusion  whereas volatile  ensures  only visibilty  ( one thread reads the most up-to-date value written by another thread)

2. The volatile modifier is applied  to a field variable  whereas synchronized is applied to methods and block of codes.

3. When using  synchronized modifier, an unlock on monitor  happens before / synchronizes with all subsequent lock  on that monitor.When using volatile ,  a write to a volatile variable  happens before  with all subsequent reads of that variable by any thread

4. Volatile  does not guarantee the atomicity of composite operations such as incrementing a variable where as synchronized modifier  guarantees the atomicity of composite operations.
  for example

   private static volatile int sno = 0;
    public static int getNextSno() {
     return sno++;
       }

The above method won’t work properly without synchronization. Every invocation may not return unique value because increment operator performs  three operations read, add, store.   first it reads the value then increments  plus one  then it stores  back a new value. When a thread reads the field , another thread may enter and read the same value before the first thread writes the incremented value back . Now both thread returns the same sno. Use synchronized on the method getNextSno(),   to ensure that the increment operation is atomic and remove volatile modifier from sno.

4. A thread acquires or releases a lock on a  monitor to enter or exit  a synchronized method or  block of code  where as a thread does not need to aquire / release a monitor to  read or write a volatile variable.  i.e.  but both have the same memory effects.

5.   final variable can not be a volatile where as final method can be synchronized

6.  volatile variable  can be null where as synchronization can not be done on null object .

7. In some situations , code is simplified  using volatile  than  using synchronized. At the same time,  we have to be careful when using volatile , code using volatile is weaker (easily broken)  than synchronized  as shown in the above example (4th point). 

Java Volatile Keyword Working concept | what use of Volatile keyword in Multithreading

When a shared variable is  accessed by multiple threads , think about volatile keyword  .  When a field  is accessed by multiple threads , one thread’s changes to the field should be  visible to another thread. How it is possible ?.     The following tutorial covers about

  1. What volatile keyword indicates ?

   2. Why volatile keyword is required?

   3. When to use volatile modifier?

   4. How volatile works in java?  .

1. What volatile keyword indicates ?

  The volatile keyword indicates that a field can be  be modified by multiple threads that are executing simultaneously.  If a field is declared as volatile,  then the Java Memory Model  (JMM 133 , from java 5 )   ensures that all threads see a consistent value for the variable.

2. Why volatile keyword is required?

      Some of the following  reasons exist for  why the changes of a field  made by  one thread  is not communicated to another thread immediately  (i.e any thread  does not see the  most up-to-date value of  the field at any time)
               :
 a) Reordering of statements : Compiler optimizations are very important on modern platforms.  Instructions are  reordered by the compiler  to achieve maximum performance .   Due to this , instructions are  frequently not executed in the order it was written. At the same time , system needs to  maintain  as-if-serial semantics for individual threads. This works well in a single-threaded environment. i.e when a single thread executes, the result will appear as if all of the actions performed by that thread occur in the order they appear in the original program. But when multiple thread involves , the statement reordering done for a single thread  may be  out of order for other threads  which causes surprising results. Similarly  Processor and  caches  are allowed to reorder.

b) Thread may keep values in  local registers instead of shared memory whenever possible . So threads may see different values for shared variables.

c) On multiprocessor architectures, each processor may have own local caches to store shared variables  that are out of sync with main memory .

 One  solution to the above problem is Synchronization . Synchronization ensures both
    1. Visibility .  (any thread  sees the  most up-to-date value of  the field at any time)
    2. Mutual exclusion  (prevents methods / block of statements  from being executed concurrently)  . For more on synchronization , you can visit my earlier tutorial , Synchronization in java

 Another solution will be volatile modifier   It ensures the visibilty  of a  field  between threads i.e  any thread that reads the variable will see the last  written value   and it does not perform mutual exclusion  ie. it allows  methods /  block of codes to  be executed simultaneously by two or more threads.

3. When to use volatile modifier

                     
Some of the examples given below will give you clear  idea about when to use volatile

Example 1

class Reorder1 {

static   int value = 0;

static  boolean flag;



static   void one() {

    value = 100;

    flag = true;



       }



static void two() {

    if ( flag == true )

     System.out.println("Value " + value);



       }
In the above code , compiler is free to  reorder of statements as given below  as  statements value = 100; , flag = true; have  no dependency.



static   void one() {

    flag = true;

    value = 100;

 }

When the above code is executed by a single thread , calling  one() & two() methods  will give the output 100. When multiple threads execute the above methods simultaneously , the result may go wrong. When one thread execute the line flag=true , and another thread executes  the line
                       if ( flag == true )
                       System.out.println("Value " + value);
Now the result will be 0 .   One solution is to synchronize the above methods .   It  prevents method one and method two from being executed concurrently and also prevents reordering. Another solution is to declare  the field flag as   volatile   . Volatile read and write operations  can not be reordered with each other or  with respect to nonvolatile variable accesses

Example 2

 public static  PrimaryKeyGenerator getInstance()

   {

            if (instance == null)

       {

        synchronized(PrimaryKeyGenerator.class)

         {

           if (instance == null) 

             {

             instance = new PrimaryKeyGenerator();

               }

           }

        }

         return instance;      } 

The  above code is called double-checked locking idiom which implements singleton pattern suitable for  multithreaded environment and  also supports lazy initialization by  avoiding the overhead of synchronization.  But the above code does not work as expected due to  the reordering of  the  initialization of instance and the write to the instance field by the compiler . Suppose two threads execute the above code simultaneously .  The first thread  writes to instance variable first then  the object is constructed.  In the meantime , the second thread reads the instance field which is not null  before the first thread  finishes the construction of object.  So the second thread returns the incomplete object of  PrimaryKeyGenerator . We can fix  this problem with  Double-Checked Locking by declaring the instance field  volatile,  which prevents  reordering.

Example 3

class  WorkerOwnerThread

{

 // field is accessed by multiple threads.

 private static boolean  stopSignal;



  private static  void  doWork()

    {

        while (!stopSignal)

        {

           Thread t = Thread.currentThread(); 

           System.out.println(t.getName()+ ": I will work until i get STOP signal from my Owner...");

        }

         System.out.println("I got Stop signal . I stop my work");

    }



   private static   void stopWork()

    {

       stopSignal = true;

       Thread t = Thread.currentThread(); 

       System.out.println("Stop signal from " + t.getName()  );

    }

 

 

public static void main(String[] args) throws InterruptedException {



Thread workerThread = new Thread(new Runnable() {

      public void run() {

             doWork();    }

       });

workerThread.setName("Worker");

workerThread.start();



//Main thread

Thread.sleep(100);

stopWork();

}

}

The above code is another example for  when to use volatile modifier.  Two threads  Worker thread and Main thread are involved.  Worker thread starts and running continousely until it gets stop signal from the main thread.  This situation is a common use case for volatile.

  Under the new memory model (from JLS7), when one thread  writes to a volatile variable V, and thread B reads from V, any variable values that were visible to A at the time that V was written are guaranteed now to be visible to B. Here stopSignal is volatile variable. The changes made by the main thread to the stopSignal variable is communicated to Worker thread immediately.

 In the above code , if we synchronize  both methods doWork() and stopWork() instead of using volatile variable , if the worker thread starts first , then it  never stops . As synchronization performs mutual exclusion , when  the worker thread starts first  , the main thread never gets chance to update  stopSignal = true.  So declaring the stopSignal field as volatile is the  good solution.

4. How volatile works in java?

                                             1. The reads and writes of volatile fields  is made directly to main memory, instead of  to registers or the local processor cache.  This ensures that all the threads see the the most  recent written  value of  the field at all times        
               2. Fields that are declared volatile are not subject to compiler optimizations. ie. the reads and writes of volatile variables  can  not be reordered  with each other or  with respect to nonvolatile variable accesses.  A write to a volatile field happens-before every subsequent read of that same volatile.

Some of the key points about volatile keyword 

1. The volatile modifier is applied  to a field that is accessed by multiple threads without using the lock

2. Writes and reads of long and double variables declared as volatile are always atomic

2. The volatile keyword can not be applied to local variable

3.  final variable can not be a volatile.

4.  volatile modifier performs no mutual exclusion

5. Object variable declared as  volatile can be null .

6.  Reads and writes operations of volatile variables can not be reordered with each other  respect to each other or with respect to nonvolatile variable accesses

7. A write to a volatile field happens before every subsequent read of that same volatile

    Note : volatile is not the alternative to synchronization .

You can visit my another post for Difference between volatile and synchronized keyword 

How to create XML in java using DOM parser | XML with Java

This tutorial covers about how to create a XML file using DOM Parser in Java and also covers that how to indent an XML using java . XML is made up of Elements . You can visit my earlier tutorial for more on XML and How to traverse / parse an XML . DOM allows a developer to create objects like root , child elements , comments and text nodes, etc . Document interface have the factory methods to create these objects. Elements are represented by Element interface , can have other elements , text, attributes . Document and Element interface inherit Node interface which is the primary datatype for the DOM.

Some of the methods of Document & Element interface to create XML are as follows .

1. createElement(String tagName) : creates an element using the tagname in the document.

2. createComment(String data) : to create comment node of the given string

3. createTextNode(String data) : to create a Text node of the given string.

4. setAttribute(java.lang.String name, java.lang.String value) : method of Element that adds a new attribute to the element.

5. appendChild(Node newChild) : method of Element inherited from Node which is used to add the child element node

The following is the sample e-mail in XML format .

  <?xml version="1.0" encoding="utf-8" standalone="no"?><email>   <!--Sample XML format of Email -->   <subject>Message from TechLabs</subject>   <from>j2eeforum@rediffmail.com</from>   <fromdisplay>TechLabs</fromdisplay>   <receipients>      <receipient role="to">         <email-id>vkj@xyz.com</email-id>         <display>VKJ</display>      </receipient>   </receipients>   <bodytext>Hope this URL will help you to learn about  how to create XML using DOM.</a></bodytext>   <signature>Administrator</signature></email>

 Steps to create an XML using DOM . There are no fixed steps to create XML. I have used the following steps to create the above Email XML.

1. Create a Document to put DOM tree

2. Create the root element of the document . Add attribute and comments of root element if required

3. Create child element of the root element, and add attribute and comments of child

4. Add text node to the child elements

5. Create a Transformer to transform a source tree from the memory into a result which may be DOMResult , SAXResult or StreamResult. StreamResult is constructed from a file , string writer , outputstream , etc..

To indent of an XML , the following statements are used using the transformer object.

transformer.setOutputProperty(OutputKeys.INDENT, "yes");transformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "3");   

 Java 5 transformer may not give the expected result with the indenting request due to bugs in Java 5.

You will get the clear idea when you create the above Email XML with the following code.

import java.io.*;
import org.w3c.dom.*;
import javax.xml.parsers.*;
import javax.xml.transform.*;
import javax.xml.transform.dom.*;
import javax.xml.transform.stream.*;

public class CreateDomXml {
public static void main (String args[]) {
createDomXml();
}

public static void createDomXml() {
try {
//to create document to put DOM tree.
DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
DocumentBuilder db = dbf.newDocumentBuilder();
Document doc = db.newDocument();

//to create root element email
Element root = doc.createElement("email");
doc.appendChild(root);

//to create comment
Comment comment = doc.createComment("Sample XML format of Email ");
root.appendChild(comment);

Element subject = doc.createElement("subject");
root.appendChild(subject);

Text subText = doc.createTextNode("Message from TechLabs");
subject.appendChild(subText);

//to create child element from and text node of that element
Element from = doc.createElement("from");
root.appendChild(from);
Text fromText = doc.createTextNode("j2eeforum@rediffmail.com");
from.appendChild(fromText);

Element fromdisplay = doc.createElement("fromdisplay");
root.appendChild(fromdisplay);
Text fromdisplayText = doc.createTextNode("TechLabs");
fromdisplay.appendChild(fromdisplayText);

Element receipients = doc.createElement("receipients");
root.appendChild(receipients);

Element receipient = doc.createElement("receipient");
receipients.appendChild(receipient);
receipient.setAttribute("role", "to"); // to create attribute with value

Element email_id = doc.createElement("email-id");
receipient.appendChild(email_id);
Text emailidText= doc.createTextNode("vkj@xyz.com");
email_id.appendChild(emailidText);

Element disp = doc.createElement("display");
receipient.appendChild(disp);
Text dispText= doc.createTextNode("VKJ");
disp.appendChild(dispText);

Element bodytext = doc.createElement("bodytext");
root.appendChild(bodytext);
Text bodytextText = doc.createTextNode("Hope this URL will help you to learn about how to create XML using DOM.");
bodytext.appendChild(bodytextText);

Element sign= doc.createElement("signature");
root.appendChild(sign);
Text signText = doc.createTextNode("Administrator");
sign.appendChild(signText);

//to create transformer
TransformerFactory tf = TransformerFactory.newInstance();
Transformer transformer = tf.newTransformer();
transformer.setOutputProperty(OutputKeys.ENCODING, "utf-8");

//to include the xml declaration line <?xml version="1.0" encoding="utf-8" standalone="no"?>
transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "no");

//for indent of XML
transformer.setOutputProperty(OutputKeys.INDENT, "yes");
transformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "3");

// to write into a file
// String fileName= "D://as2/jf6/email1.xml";
// StreamResult result = new StreamResult(new File(fileName));

DOMSource source = new DOMSource(doc);

//to write as a string
StringWriter sw = new StringWriter();
StreamResult result = new StreamResult(sw);
transformer.transform(source, result);
//System.out.println("XML file saved " + fileName);

String xmlString = sw.toString();
System.out.println("Email XML \n\n " + xmlString);

} catch (Exception e) {
System.out.println(e);
}
}
}