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.
