Re: stored procedure tutorial help
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-tp31945511p31979194.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: stored procedure tutorial help
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 =
Embedded - can it be multi-user?
Hi all, I've just started to use Derby and really like it. I've come from a Firebird embedded environment with Java, but the fact that Derby is written in Java is a decisive factor in its favour - its feature set is also impressive. However, there's one thing that's confusing me. First thing I did was go to the getstartderby.pdf docco and go through the tutorial. All worked fine, however I was confused by this bit (End of page 33, start of 34) - Derby's two architectures have caused confusion for some new Derby users, who mistakenly think that embedded is a single-user configuration. This is not true. The embedded driver supports multiple simultaneous connections, performs locking, and provides performance, integrity, and recoverability. Any application that uses the Getting Started with Derby embedded driver can open multiple Derby connections and then provide a means for multiple users to interact with the database on each connection. The Derby Network Server is an example of such an application. - Now, I was using the SQuirreL SQL Client to look at my databases as they were being created, but I couldn't use ij with the database *_and_* SQuirreL at the same time. What I'm wondering is, if there's a single app connecting to the database multiple times, is it up to the app to manage the connections so that only one connection is active at any one time, or how exactly does that work? Say in a context where an App Server is connecting to an embedded Derby database - i.e. no server running - does the App Server have to manage requests to the database in a queue or how, exactly does the system work? TIA for any pointers, tips, clues, references, URL's or anything else useful. Rgs, Paul... -- lineh...@tcd.ie Mob: 00 353 86 864 5772
