import java.sql.*;

public class trial22 {
	public static void main(String args[]) {
    String url = "jdbc:odbc:trial";
    Connection con;
		Statement stmt,stmt1;
    ResultSet rs;
    ResultSetMetaData rsm;
    String query = "select * from LLNRD order by [SEQUENCE#]";
    String s1;
    int Sreplacemen=0;
    String Sstatus="";
		try
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    }
    catch(java.lang.ClassNotFoundException e)
    {
      System.err.print("ClassNotFoundException: ");
      System.err.println(e.getMessage());
    }
		try
    {
      con = DriverManager.getConnection(url,"myLogin", "myPassword");
			stmt = con.createStatement();
      stmt1=con.createStatement();
      rs=stmt.executeQuery(query);
      rsm=rs.getMetaData();
      int j=rsm.getColumnCount();
      String[] heading=new String[j];
      String[] coltype=new String[j];
      int[] col=new int[j];
      for(int h=1;h<=j;h++)
      {
      heading[h-1]=rsm.getColumnName(h);
      coltype[h-1]=rsm.getColumnTypeName(h);
      col[h-1]=rsm.getColumnType(h);
      //System.out.println(heading[h-1]+"*"+coltype[h-1]+"*"+col[h-1]);
      }
      String ss="";
      String ss1="";
      String ss2="";
      String column_name="";
      String column_name1="";
      String column_name2="";
      String partreg="";
      s1="insert into NNRD3 values(";
      while(rs.next())
      {
        for(int k=0;k<j;k++)
        {
           if(coltype[k].equals("VARCHAR"))
           {
           column_name=rsm.getColumnName(k+1);
           ss1=rs.getString(column_name);
           if(column_name.equals("STATUS") || column_name.equals("Status"))
           {Sstatus=ss1;ss="'"+ss1+"'";}
           else if(column_name.equals("WellUse") || column_name.equals("WELLUSE"))
           {
           if(ss1.equals("L")) ss1="Q";
           else if(ss1.equals("U")) ss1="P";
             ss="'"+ss1+"'";
           }
           else if(column_name.equals("partreg#") || column_name.equals("PARTREG#"))
           {
           ss1=partreg;
           ss="'"+ss1+"'";
           }
           else if(column_name.equals("Registration#") || column_name.equals("REGISTRATION#"))
            {
            for(int i=0;i<ss1.length();i++)
            {  ss2=""+ss1.charAt(0);
            if(ss1.charAt(2)!='0') {partreg=ss1.substring(2,ss1.length());ss2+=partreg;}
            else if(ss1.charAt(2)=='0')
            {  if(ss1.charAt(3)!='0') {partreg=ss1.substring(3,ss1.length());ss2+=partreg;}
                  else  { if(ss1.charAt(4)!='0') {partreg=ss1.substring(4,ss1.length());ss2+=partreg;}
                     else { if(ss1.charAt(5)!='0') {partreg=ss1.substring(5,ss1.length());ss2+=partreg;}
                        else{ if(ss1.charAt(6)!='0') {partreg=ss1.substring(6,ss1.length());ss2+=partreg;}
                           else{ if(ss1.charAt(7)!='0') {partreg=ss1.substring(7,ss1.length());ss2+=partreg;}
                             else{ if(ss1.charAt(8)!='0') {partreg=ss1.substring(8,ss1.length());ss2+=partreg;}
                                else{partreg=ss1.substring(9,ss1.length());ss2+=partreg;}
                                 } //else if (ss1.charAt(8)==0)
                                } //else if (ss1.charAt(7)==0)
                            } //else if (ss1.charAt(6)==0)
                          }//else if (ss1.charAt(5)==0)
                         }//else if (ss1.charAt(4)==0)
          }//  else if(ss1.charAt(2)==0)
          }//for
           char pp=partreg.charAt(partreg.length()-1);
           char pp1=' ';
           if(partreg.length()>3)
              pp1=partreg.charAt(partreg.length()-2);
           if(pp=='A' || pp=='B' || pp=='C' || pp=='D' ||
              pp=='E' || pp=='F' || pp=='G' || pp=='H' || pp=='I' ||
              pp=='J' || pp=='K' || pp=='L' || pp=='M' || pp=='N' ||
              pp=='O' || pp=='P' || pp=='R' || pp=='S' || pp=='T' ||
              pp=='U' || pp=='W' || pp=='X' || pp=='Y' || pp=='Z' )
              {
                 if(pp1=='A' || pp1=='B' || pp1=='C' || pp1=='D' ||
                 pp1=='E' || pp1=='F' || pp1=='G' || pp1=='H' || pp1=='I' ||
                 pp1=='J' || pp1=='K' || pp1=='L' || pp1=='M' || pp1=='N' ||
                 pp1=='O' || pp1=='P' || pp1=='R' || pp1=='S' || pp1=='T' ||
                 pp1=='U' || pp1=='W' || pp1=='X' || pp1=='Y' || pp1=='Z' )
                 { partreg=partreg.substring(0,(partreg.length()-2));
                   ss2=ss2.substring(0,(ss2.length()-2))+" "+pp+pp1;
                 }
                 else
                 {
                 partreg=partreg.substring(0,(partreg.length()-1));
                 ss2=ss2.substring(0,(ss2.length()-1))+" "+pp;
                 }
              }
           else
              { ss2+=" "; }
           String ss3=ss2;
           for(int ww=ss2.length();ww<=8;ww++)
            {ss3+=" ";}
            ss3+=" ";
            if(Sstatus.equals("I"))
            {ss3+="I";}
            else if(Sstatus.equals("X"))
            {ss3+="X";}
            else
            {ss3+=" ";}
            ss3+="  ";
            if(Sreplacemen > 0)
              { ss3+="R";}
            else
              ss3+=" ";
            ss="'"+ss3+"'";
            }//end of REGISTRATION# case
            else
             ss="'"+ss1+"'";
           }
        else if(coltype[k].equals("DOUBLE"))
           {
           column_name=rsm.getColumnName(k+1);
           double is1=rs.getDouble(column_name);
           ss=""+is1;
           if(column_name.equals("REPLACEMEN") || column_name.equals("Replacemen"))
           {Sreplacemen=(int)(is1+0.1);}
           else
           {Sreplacemen=0;}
           }
       else if(coltype[k].equals("INTEGER") || coltype[k].equals("SMALLINT"))
           {
           column_name=rsm.getColumnName(k+1);
           int is1=rs.getInt(column_name);
           ss=""+is1;
           if(column_name.equals("REPLACEMEN") || column_name.equals("Replacemen"))
           {Sreplacemen=is1;}
           else
           {Sreplacemen=0;}
           }
       else if(coltype[k].equals("DATETIME"))
        {
        column_name=rsm.getColumnName(k+1);
        ss=""+rs.getDate(column_name);
        if(ss.equals("null") || ss.equals(""))
           {ss=null;}
        else
           {ss="'"+ss+"'";}
        }
       else
           {ss=""+rs.getInt(rsm.getColumnName(k+1));}
        if(k!=(j-1))
           s1+=ss+",";
        else
           s1+=ss;
        }
        s1+=")";
        System.out.println(s1);
        stmt1.executeUpdate(s1);
        ss="";
        s1="insert into NNRD3 values(";
       }
    stmt1.close();
    stmt.close();
    con.close();
		}
    catch(SQLException ex)
    {
			System.err.println("SQLException: " + ex.getMessage());
		}
	}
}
