exception when calling an oracle stored procedure from java program

[juser11]

Am having problem calling a simple stored proc to insert data from a java program.

The insert statement works fine when called directly with prepare statement. But when I use call it through a strored proc I get an error.

jar file is ojdbc5.jar. - oracle10g. Then i tried another jar file ojdbc14.jar (Both r compatible for oracle 10g). Not able to debug also as I am not able to get the src jar for this

Any idea why.

below is the problem code
-------------------------------------

public void createDVD(String id, String title) throws DAOException { 

if (getDVD(id) != null) throw new DAOException("Id " + id + " is already used"); 

Connection conn = null; 

error = null; 

CallableStatement proc = null; 


try { 

conn = dataSource.getConnection(); 

logger.info("DatabaseDVDManager.createDVD, id=" + id); 

// Prepare a statement to insert a record 

proc = conn.prepareCall("{prc_ins_dvd_info(?,?)}"); 

proc.setString(1,id); 

proc.setString(2,title); 

proc.execute(); 


} 

catch (SQLException e) { 

error = e; 

message = "Create failed"; 

closeConnection(conn); 

} 


closeProc(proc); 

// closePrep(pstmt); 

closeConnection(conn); 

checkOK(); 

}
 
 
below is error stack
-----------------------------
java.lang.NullPointerException
	oracle.jdbc.driver.T4C8Oall.getNumRows(T4C8Oall.java:876)
	oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:971)
	oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)
	oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
	oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3445)
	oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4394)
	org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)
	dk.hansen.DatabaseDVDManager.createDVD(DatabaseDVDManager.java:83)
	dk.hansen.CreateDVDAction.commonExecute(CreateDVDAction.java:34)
	dk.hansen.CommonAction.execute(CommonAction.java:41)
	org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:421)
	org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:226)
	org.apache.struts.action.ActionServlet.process(ActionServlet.java:1164)
	org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:415)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	org.apache.struts.action.RequestProcessor.doForward(RequestProcessor.java:1056)
	org.apache.struts.action.RequestProcessor.processForwardConfig(RequestProcessor.java:388)
	org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:231)
	org.apache.struts.action.ActionServlet.process(ActionServlet.java:1164)
	org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:415)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

[kvprajapati]

Welcome juser11.

Your source code must be surronded by BB code tags. Read more about BB code tags

proc = conn.prepareCall("{call prc_ins_dvd_info(?,?)}"); 
    proc.setInt(1,id); 
    proc.setString(2,title); 
    proc.execute();

[juser11]

below is the code enclosed in bb code tags.
I am using this in a struts framework

public void createDVD(String id, String title) throws DAOException { 

if (getDVD(id) != null) throw new DAOException("Id " + id + " is already used"); 

Connection conn = null; 
error = null; 
CallableStatement proc = null; 


try { 
conn = dataSource.getConnection(); 
logger.info("DatabaseDVDManager.createDVD, id=" + id); 
// Prepare a statement to insert a record 
proc = conn.prepareCall("{prc_ins_dvd_info(?,?)}"); 
proc.setString(1,id); 
proc.setString(2,title); 
proc.execute(); 
} 

catch (SQLException e) { 
error = e; 
message = "Create failed"; 
closeConnection(conn); 
} 

closeProc(proc); 
// closePrep(pstmt); 
closeConnection(conn); 
checkOK(); 
}

[juser11]

the problem is resolved.
The procedure call has to be preceded by a call statement which was missed out. Below is the corrected code

public void createDVD(String id, String title) throws DAOException {
	      if (getDVD(id) != null) throw new DAOException("Id " + id + " is already used");
	      Connection conn = null;
	      error = null;
	      CallableStatement proc = null;
	      
	      try {
	         conn = dataSource.getConnection();
	         logger.info("DatabaseDVDManager.createDVD, id=" + id);
	         // Prepare a statement to insert a record
	         proc = conn.prepareCall("{ call prc_ins_dvd_info(?,?) }");
	         proc.setString(1,id);
	         proc.setString(2,title);
	         proc.executeQuery();
	         
	      } 
	      catch (SQLException e) {
	         error = e;
	         message = "Create failed";
	         closeConnection(conn);
	      }
	       
	      closeProc(proc);
          closeConnection(conn);
	      checkOK();
	                
	                     
	   }

[kvprajapati]

Next time when you post code use BB code tags. You may use # icon (for code tags) at top of editor.