db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From IkeAbalogu <ikeabal...@hotmail.com>
Subject Re: stored procedure tutorial help
Date Sat, 02 Jul 2011 15:52:27 GMT

Finally got it to run.The problem was:  DYNAMIC RESULT SETS 4.
Took it out and it ran ok.
Cheers




IkeAbalogu wrote:
> 
> A call to the stored procedure returns this exception: 
> ERROR 42X50. No method was found that matched the method call
> storedprocedures.UpdatePreBalanceSP.UpdatePreviousBalanceSP(java.sql.ResultSet[]),
> tried all combinations of object and primitive types and any possible type
> conversion for any  parameters the method call may have. The method might
> exist but it is not public and/or static, or the parameter types are not
> method invocation convertible.
> Please help.
> 
> BTW:Thanks for all your help.I really really appreciate all of them.
> JAVA CODE FOR THE PROCEDURE:
> package storedprocedures;
> 
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.sql.Statement;
> 
> import database.StationDBConnection;
> 
> public class UpdatePreBalanceSP {
> 	//static StationDBConnection con = new StationDBConnection();
> 	
> //	public static void main(String[] args) {
> //		new UpdatePreBalanceSP();
> //		UpdatePreBalanceSP.UpdatePreviousBalanceSP();
> //	}
> 	
> 	public static void UpdatePreviousBalanceSP(){
> 		Connection conn;
> 		int supplyID = 0;
> 		int dipid = 0;
> 		int prevBalance =0;
> 		int balance = 0;
> 		try {
> 			conn = DriverManager.getConnection("jdbc:default:connection");	
> 			  
> 			  String q1 ="select fk1_supplyid, dipid from PRODUCTQUANTMONITOR where
> dipid=" +
> 			  			 "(select max(dipid) from PRODUCTQUANTMONITOR)";
> 			  //q1 returns supplyid and dipid
> 			  PreparedStatement ps1 = conn.prepareStatement(q1);
> 			  ResultSet rs1 = ps1.executeQuery();
> 			  if (rs1.next()){
> 				  supplyID = rs1.getInt("fk1_supplyid");
> 				  dipid = rs1.getInt("dipid");
> 			  }
> 			  rs1.close();
> 			  ps1.close();
> 			  
> 			  String q2 = "select balance from PRODUCTQUANTMONITOR " +
> 			  			  "where fk1_supplyid = ? and dipid < ? " +
> 			  			  "order by dipid desc " +
> 			  			  "FETCH FIRST ROW ONLY";
> 			  
> 			  PreparedStatement ps2 = conn.prepareStatement(q2);
> 			  ps2.setInt(1, supplyID);
> 			  ps2.setInt(2, dipid);			  
> 			  ResultSet rs2 = ps2.executeQuery();
> 			  if (rs2.next()){
> 				  balance = rs2.getInt("balance");
> 			  }
> 			  rs2.close();
> 			  ps2.close();
> 			  
> 			  if(balance == 0){
> 			  String q3 = "SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID
> =?";
> 			  PreparedStatement ps3 = conn.prepareStatement(q3);
> 			  ps3.setInt(1, supplyID);		  
> 			  ResultSet rs3 = ps3.executeQuery();
> 			  if (rs3.next()){
> 				  balance = rs3.getInt("QUANTITY");
> 			  }
> 			  }
> 			  prevBalance = balance;
> 			  String q4 = " UPDATE PRODUCTQUANTMONITOR SET PREVIOUS_BALANCE
> ="+prevBalance +
> 			  			  " WHERE FK1_SUPPLYID ="+supplyID +
> 			  			  " AND DIPID ="+dipid;
> 			  Statement st = conn.createStatement();			  
> 			  st.executeUpdate(q4);
> 			  updateSold(dipid, conn);
> 			  		conn.close();
> 			  		
> 		} catch (SQLException e) {
> 			// TODO Auto-generated catch block
> 			e.printStackTrace();
> 		}finally{
> 			//con.closeConnection();
> 		}
> 		  
> 	}
> 
> 	private static void updateSold(int dipid, Connection conn) {
> 					  
> 		try {
> 			String q = " SELECT PREVIOUS_BALANCE,BALANCE FROM PRODUCTQUANTMONITOR"
> +
> 						" WHERE DIPID ="+dipid;
> 			Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
> ResultSet.CONCUR_UPDATABLE);
> 			Statement st1 = conn.createStatement();
> 			ResultSet rs = st.executeQuery(q);
> 			while(rs.next()){
> 				int sold = rs.getInt("previous_balance")-rs.getInt("balance");
> 				String s =" UPDATE PRODUCTQUANTMONITOR SET QUANTITYSOLD ="+sold +
> 	  			  		  " WHERE DIPID ="+dipid;
> 				st1.executeUpdate(s);
> 			}
> 			
> 			st.close();
> 			st1.close();
> 		} catch (SQLException e) {
> 			// TODO Auto-generated catch block
> 			e.printStackTrace();
> 		}
> 	}
> 
> }
> 
> 
> CALL sqlj.install_jar('c:\previousbalance.jar', 'APP.UPDATEDIPRECORD', 0);
> 
> CALL
> SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath','APP.UPDATEDIPRECORD');
> 
> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.classpath');
> confirms the property has been set
> 
> CREATE PROCEDURE UPDATEDIPRECORD()
>   PARAMETER STYLE JAVA
>   LANGUAGE JAVA
>   DYNAMIC RESULT SETS 4
>   EXTERNAL NAME
> 'storedprocedures.UpdatePreBalanceSP.UpdatePreviousBalanceSP';
> 
> Call UPDATEDIPRECORD();
> 
> A call to the stored procedure returns this exception: 
> ERROR 42X50. No method was found that matched the method call
> storedprocedures.UpdatePreBalanceSP.UpdatePreviousBalanceSP(java.sql.ResultSet[]),
> tried all combinations of object and primitive types and any possible type
> conversion for any  parameters the method call may have. The method might
> exist but it is not public and/or static, or the parameter types are not
> method invocation convertible.
> Please help.
> 
> BTW:Thanks for all your help.I really really appreciate all of them.
> 
> 
> Rick Hillegas-3 wrote:
>> 
>> On 6/28/11 4:45 AM, IkeAbalogu wrote:
>>> I am trying to find websites with tutorial on derby stored procedures.i
>>> find
>>> the reference manual article confusing.
>>> Thanks
>> There is also some material in the Developer's Guide: 
>> http://db.apache.org/derby/docs/10.8/devguide/devguide-single.html#cdevspecial
>> 
>> ...and some material on the Derby wiki: 
>> http://wiki.apache.org/db-derby/DerbySQLroutines
>> 
>> In addition, the Derby scores demo shows how to write functions and 
>> procedures. In your Derby release, look at demo/programs/scores/README
>> 
>> Hope this helps,
>> -Rick
>> 
>> 
> 
> 

-- 
View this message in context: http://old.nabble.com/stored-procedure-tutorial-help-tp31945511p31980071.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message