Oracle 9i and jdbc1.4 discrepancy

[palavi]

Hi all,

I have a core java application, I am getting the below exception when ever i run this application. I have a java file named Oracpt.java which takes all variables like username password and url from setttings file which from config file.
becoase of this below exception im not able to go further ..
Please is it because version problem i checked in google all required jar files are there in oracle98/lib/jdbc folder..

Please kindly advice what might be the problem Its not connecting connection variable is null every time. I am struck wiht error since 3 days please help.

Got an exception in Connection Method! 
Io exception: Connection refused(DESCRIPTION=(ERR=1153)(VSNNUM=169869824)(ERROR_STACK=(ERROR=(CODE=1153)(EMFI=4)(ARGS='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sticks-vip.mis.cypress.com)(PORT=1523))(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))null))'))(ERROR=(CODE=303)(EMFI=1))))
java.sql.SQLException: Io exception: Connection refused(DESCRIPTION=(ERR=1153)(VSNNUM=169869824)(ERROR_STACK=(ERROR=(CODE=1153)(EMFI=4)(ARGS='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sticks-vip.mis.cypress.com)(PORT=1523))(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))null))'))(ERROR=(CODE=303)(EMFI=1))))
	at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:114)
	at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:156)
	at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
	at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:210)
	at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java:251)
	at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:224)
	at java.sql.DriverManager.getConnection(DriverManager.java:525)
	at java.sql.DriverManager.getConnection(DriverManager.java:171)
	at parts.OraclePlm.CreateConnection(OraclePlm.java:40)
	at parts.OraclePlm.main(OraclePlm.java:66)
Connection =null
Query  select fam_oid,inventory_item_id from family_part order by fam_oid 
Got an exception in Main class itself! 
null
java.lang.NullPointerException
	at parts.OraclePlm.main(OraclePlm.java:83)

[kvprajapati]

Post your source code.

[javaAddict]

You can also check this:

java.lang.NullPointerException
at parts.OraclePlm.main(OraclePlm.java:83)

At line 83 something is null

[palavi]

Since the connection variable is null .I am getting Null pointer exception. Once the connection is created the null pointer exception disappears.

I have set classpath variable to

.;D:\oracle\ora92\jdbc\lib\classes12.jar;

I am using Ora92 and JDK 1.5 and in this path(D:\oracle\ora92\jdbc\lib) i have these jar files

classes12
classes12_g
classes12dms
classes12dms_g
classes111
classes111_g
ocrs12
ocrs12_g
nls_charset12
nls_charset11
odbc14
odbc14_g

This is my connection method
all variables are coming from patss_config.txt and there is 1 config varaible of Properties class.
Properties.class file is there hash map is used.

But orauser, orapass and oraurl values are specified in pats_cofig.txt file using config variable which is of type properties class.

public Connection CreateConnection()
		{
			Connection conn=null;
			try {
	          
	            Class.forName(Settings.oraDriver);
	            String url = Settings.oraUrl;
	            
	            conn = DriverManager.getConnection(url,Settings.oraUser,Settings.oraPass);
	            System.out.println("Ora Pass:"+Settings.oraPass);
	            System.out.println(conn +" Connection Created");
	            	            
				} catch (Exception e) {
	            System.err.println("Got an exception! in createconnnection method ");
	            System.err.println(e.getMessage());
	            e.printStackTrace();
				}
	       return conn;
		}

now I am getting this exception

Ora USER :user
parts.Settings@82ba41
Ora Pass:pwd
oracle.jdbc.driver.OracleConnection@10dd1f7 Connection Created
Connection =oracle.jdbc.driver.OracleConnection@10dd1f7
Query select oid,item_id from fam_par order by fam_oid 
Got an exception in Main class itself! 
ORA-02019: connection description for remote database not found

java.sql.SQLException: ORA-02019: connection description for remote database not found

	at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:114)
	at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
	at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:542)
	at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1311)
	at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:738)
	at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1313)Ending of the PLM Program

	at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1232)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithBatch(OracleStatement.java:1353)
	at oracle.jdbc.driver.OracleStatement.doExecute(OracleStatement.java:1760)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1805)
	at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:531)
	at parts.OraclePlm.main(OraclePlm.java:80)

[palavi]

im using jdbc driver 14.
If i give these orauaer, orapass and oraurl directly in my main code it is working fine. but not throght the setings file.
So i dont think there is problem with the version.

or do i need to do smethign with datasourcename in control panel??

[kvprajapati]

Exception throws in another method. Please post complete code if you want some tips.

[palavi]

hi ,
here is my code.
I am not using settings file directly creating db variables. Please tell me where the problem is but connection refused error is coming

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.io.*;
 public class OraclePm {
	 String oraDriver="oracle.jdbc.driver.OracleDriver";
	 String oraUrl="jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on) (ADDRESS=(PROTOCOL=TCP)(HOST=###### - ###.###.#######.com) (PORT=1523))(ADDRESS=(PROTOCOL=TCP)(HOST=###### - ###.###.#######.###) (PORT=1523))(CONNECT_DATA=(SERVICE_NAME=#####)))";
	String oraUser="####";
	 String oraPass="####";
	 
	 
	 FileOutputStream out;
 	PrintStream  p;
 	
	 OraclePlm()
	 {      System.gc();
	       System.out.println("this is constructor");
		 Settings set = new Settings();
		// System.out.println("Ora USER :"+Settings.oraUser);
		  System.out.println(set);
		 
	 }	
	 
	public Connection CreateConnection()
		{
			Connection conn=null;
			try {
	          
	          Class.forName(Settings.oraDriver);
	            //String url = Settings.oraUrl;
	            
	           // conn = DriverManager.getConnection(url,Settings.oraUser,Settings.oraPass);*/
	        //    System.out.println(conn +" Connection Created");
				 System.out.println("Ora USER :"+oraUser);	 
				 System.out.println("Ora PASS :"+oraPass);	
				//Class.forName("oracle.jdbc.driver.OracleDriver");
				                             
				conn = DriverManager.getConnection(oraUrl,oraUser,oraPass);
				System.out.println(conn +"l Connection Created");
				} catch (Exception e) {
	            System.err.println("Got an exception in Connection Method! ");
	            System.err.println(e.getMessage());
	            e.printStackTrace();
				}
	       return conn;
		}
	 
	public String viewName;
	public String outSeq;
	public String itemName;
   		
    public static void main(String[] args)
    {   
    	OraclePm opm = new OraclePm();    
    	Connection conn;
    	String final_query = "";
    	int return_value=0;
    	
    	//FileOutputStream out;
    	//PrintStream  p;
    	
    	
    	 
    	conn = opm.CreateConnection();
    	System.out.println("Connection ="+ conn);
       	Statement stmt =null;
		ResultSet rs = null;  
		int isItFirstTime=0;
		
		
		int famOid=0,itemId =0;
		String sql = "select fam_oid,inventory_item_id from family_part order by fam_oid ";
		System.out.println("Query  "+sql);
    	try
    	{  
    		   
    		//Settings set = new Settings();
    		//out = new FileOutputStream(Settings.fileLoc + "" +Settings.fileName);
    	//	out = new FileOutputStream(Settings.fileLoc + "" +Settings.fileName);
    		//set.
    		stmt = conn.createStatement();
    		 
    		    		 
    		String sql_all_tab1="delete from tmp_all_tab_columns";
    		stmt.executeUpdate(sql_all_tab1);
    		 
    		
    		sql_all_tab1="insert into tmp_all_tab_columns(table_name,column_name) " +
    			"select table_name,column_name from apps.cyp_xx_all_tab_cols ";

    		
    		System.out.println("sql ll tab");
    		
    		stmt.executeUpdate(sql_all_tab1);

    		 rs = stmt.executeQuery(sql);//
    		 
    		 int famCnt = 0;
    		 int currFamId=0, prevFamId=0;
    		 while(rs.next())
    		 {
    		 	famOid = rs.getInt(1);
    		 	itemId = rs.getInt(2);
    		 	isItFirstTime =0;
    		 	
    		 	System.out.println("FirstTime");
    		 	
    		 	if(famCnt == 0)
    		 	{
    		 		currFamId = famOid;
    		 		prevFamId = currFamId;
    		 		isItFirstTime = 1;
    		 		famCnt=1;
    		 		
    		 	}
    		 	else
    		 	{
    		 		currFamId = famOid;
    		 	}
    		 	System.out.println("FamOID "+ famOid);
    		 	System.out.println("ITEMID "+ itemId);
    		 	
        		 	opm.outSeq = opm.getOutSeq(conn,famOid);//2b,77,26b,1,195,2,3,4,5,6,7,8,9,10,11,12,13,14,15,60,61,62,63,64,65,66,67,68,69,70,71,72,217,73,68,75,76
    		 	System.out.println("View = " + opm.viewName);
    		 	System.out.println("ITEM = " + opm.itemName);
    		 	System.out.println("Sequence = " + opm.outSeq);
    		 	opm.loadTable_tmp_pm_to_pc(conn,opm.viewName,opm.outSeq);
    			final_query = opm.getQuery(conn,opm.outSeq);
    			return_value = opm.printTextFile(conn,final_query,opm.viewName,opm.itemName,currFamId,prevFamId,isItFirstTime);
    			prevFamId = currFamId;
    		}
    		String Famsql="insert into family_part_hist(fam_oid,inventory_item_id,updated_date) select fam_oid,inventory_item_id,sysdate from family_part ";
        	stmt.executeUpdate(Famsql);
        	
    		Famsql = "delete from family_part";
    		stmt.executeUpdate(Famsql);
    		
    	    
    		rs.close();
    		conn.commit();
        	conn.close();

    	}
      catch(Exception e)
      {
      	   System.err.println("Got an exception in Main class itself! ");
            System.err.println(e.getMessage());
            e.printStackTrace();
      }
   
      System.out.println("Ending of the PLM Program");
      
    }
    
    /**This function gets the ItemName based upon the inventory_item_id provided.*/
    
    public String getItemName(Connection conn, int itemId) {
    	
    	Statement stmt =null;
		ResultSet rs = null;   
		String itemName="";
		String sql = "select distinct item_name from apps.cyp_xx_prchnl_mkt where inventory_item_id = " +itemId ;
		try
		{//CY7C170-45LC
			stmt =conn.createStatement();
    		rs = stmt.executeQuery(sql);
    		while(rs.next())
    		{
    			itemName = rs.getString(1);
    			System.out.println("ItemName = "+itemName);
    			
    		}
			rs.close();
		}
		catch(Exception e)
    	{
      	   System.err.println("Got an exception in GetItem class itself! ");
            System.err.println(e.getMessage());
            e.printStackTrace();
      	}
    	
		return itemName;
	}

    /**This function gets the output attributes sequence for the output file, 
     * based on the input as family id.*/ 
    
	public String getOutSeq(Connection conn,int famOid) {
		Statement stmt =null;
		ResultSet rs = null;   
		String outSeq="";
		
		
		String sql = "select plm_attr_seq from PLM_ATTR_SEQ_MAST where plm_fam_id =  " +famOid ;
		try
		{
			stmt =conn.createStatement();
    		rs = stmt.executeQuery(sql);
    		while(rs.next())
    		{
    			outSeq = rs.getString(1);
    			System.out.println("OutSeq = "+outSeq);  
    		}
    		rs.close();
		}
		catch(Exception e)
    	{
      	   System.err.println("Got an exception in GetItem class itself! ");
            System.err.println(e.getMessage());
            e.printStackTrace();
      	}
    	
		return outSeq;
	}

	    
    /** This function prints the data into output file named "parts.txt"*/
	public int printTextFile(Connection conn,String final_query,String view_name,
			String item_name,int currFamId, int prevFamId,int isItFirstTime)
    {
		//FileOutputStream out;
		//PrintStream p =new PrintStream();;
		Settings set = new Settings();
		
		System.out.println("First time ?"+isItFirstTime);
		System.out.println("First time ?"+currFamId);
		System.out.println("First time ?"+prevFamId);
		
    	String sql="";
    	System.out.println(sql);
    	Statement stmt =null;
		ResultSet rs = null,rsHeader = null;   
		String outText = "",outHeader = "";
	
    	try
    	{
    		//FileOutputStream out;
    		String sqlFamName="select family_name from PROCH_FAMILY_MASTER where fam_oid = " + currFamId;
    		System.out.println("Query out side our code "+sqlFamName);
    		stmt=conn.createStatement();
    		rs = stmt.executeQuery(sqlFamName);
    		
    		if(rs.next())
    		{
    		String famName = rs.getString(1);


    		//out = new FileOutputStream(set.fileLoc + '' +set.famName,true);
           
    		out = new FileOutputStream("D:\\"+famName +"_part.txt");
    		p=new PrintStream(out);
    		// this will work checked already
    		System.out.println("File Created "+famName);
    		// out = new FileOutputStream(set.fileLoc,famName +"_part.txt",true);// may not work



    		}

    		
    		// out = new FileOutputStream(set.fileLoc + "" +set.fileName,true);
    		
    		
	    		
    			 stmt = conn.createStatement();
    			 String sqlHeader = "select header_record from PROCH_FAMILY_MASTER where fam_oid = " + currFamId;
    			 if(isItFirstTime ==1 || (currFamId != prevFamId))
    			 {
    			 	rsHeader = stmt.executeQuery(sqlHeader);
    			 	while(rsHeader.next())
    			 		{
    			 			outText = rsHeader.getString(1);
    			 			p.println(outText);
    			 			System.out.println("Generating the Header file");
    					 }	
    			 }
    			 sql =" select " + final_query + " from " + " apps."+view_name + " where item_name='" + item_name + "'";
    			 
    			 System.out.println(sql);
    			 rs = stmt.executeQuery(sql);
    			 while(rs.next())
    			 {
    			 	outText = rs.getString(1);
    			 	//System.out.println("the new data to be added......." + outText);
    			 	p.println(outText);
    			 	System.out.println("Generating the flat file");
    			 }	
    			 	 	
    			 rs.close();	 		            
    			 
    	}
    	 catch(Exception e)
      {
      	   System.err.println("Got an exception! ");
            System.err.println(e.getMessage());
            e.printStackTrace();
            return 0;
      }
      return 1;
    
    }
	
	    public void loadTable_tmp_plm_to_pc(Connection conn,String view_name,String outSeq)
    {
    
        String[] result;
        String new_seq = "";
    
		Statement stmt = null;		
		   //2b,77,26b,1,195,2,3,4,5,6,7,8,9,10,11,12,13,14,15,60,61,62,63,64,65,66,67,68,69,70,71,72,217,73,68,75,76   	
    	 result = outSeq.split("\\,");
    	 
          for (int x=0; x<result.length; x++)
          {    	   
        	  		  
			         if(result[x].charAt(result[x].length()-1)!='b')
			         {
			         	new_seq = new_seq + result[x] + ",";
			         }
        	  		 
		    }
			new_seq = new_seq.substring(0,new_seq.length()-1);
		
		try {
			String sql_upd = "delete from tmp_plm_to_pc";
			
				System.out.println(sql_upd);
			  stmt=conn.createStatement();
			  stmt.executeUpdate(sql_upd);
			          
    	String sql = "insert into tmp_plm_to_pc(plm_att_id,plm_att_name) " +
    			     " select a.plm_att_id,a.plm_att_name from plm_att_mast a, tmp_all_tab_columns b " +
    			     " where plm_att_id in( " + new_seq + ") " +
    			     " and b.column_name=a.PLM_ATT_NAME and b.table_name='" + view_name +"'";
    			System.out.println(sql);		     
        stmt.executeUpdate(sql);
		System.out.println(sql);
   	          }
   	catch(Exception e)
   	{
   	  e.printStackTrace();	
   	}
}
    /** To create the connection with apps*/
    
}

and exception

I have changed some variables in code. and plzz check wheter the oraurl is correct
or the problem with Driver
but some connection is getting created and smetimes not plz guide me..

[kvprajapati]

Its difficult to guess and say about db connection.

Write a seperate and small code to test db connection and then decide what to do or not.