David,
The basic Java program I use to test stored procedures is shown below.
Notes:
SYSTEMNAME = name of system to connect to
USERID/PASSWORD = your userid and password
LIB1:LIB2:LIB3 = library list to use for connection
PROCNAME = name of stored procedure
(?,?,?) following PROCNAME ? each ? represents a parameter
For each input parameter you would use
stmt.setXXXXX(parm#, value);
XXXXX corresponds to parameter type: String, Int, etc.
For each output parameter you would use
stmt.registerOutParameter(parm#, Types.XXXXX);
XXXXX corresponds to parameter type (CHAR, INTEGER);
Let me know if you have any questions. This same process can be used for
stored procedures that return a result set.
If you get stuck on the parameter related stuff, let me know how your
procedure is defined and I can give you the corresponding Java statements.
Have fun!
Richard
===================================================================
import java.sql.*;
public class TestSP {
public static void main(String[] args) {
Connection con = null;
CallableStatement stmt = null;
try {
// Connect to system
Class.forName("com.ibm.as400.access.AS400JDBCDriver").newInstance();
con = DriverManager.getConnection(
"jdbc:as400://SYSTEMNAME;prompt=false;naming=system;libraries=LIB1:LIB2:LIB3
",
"USERID", "PASSWORD");
// Define procedure
String callProc = "CALL PROCNAME(?,?,?)";
stmt = con.prepareCall(callProc);
stmt.setString(1, "parm1"); // Parameter 1 -
input string
stmt.setInt(2, 2); // Parameter 2 -
input integer
stmt.registerOutParameter(3, Types.CHAR); // Parameter 3 -
output string
// Call procedure
stmt.execute();
// Display output parameters
System.out.println("Parameter 3 = " + stmt.getString(3));
// Close connection
con.close();
} catch (Exception e) {
System.out.println("Error: " + e);
System.exit(1);
}
System.exit(0);
};
}
===================================================================
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of David FOXWELL
Sent: Tuesday, December 07, 2010 4:18 AM
To: Midrange Systems Technical Discussion
Subject: RE: Basic stored procedure questions
Richard, I would indeed and I'm sure others would be interested. In my case
the Java would have to be very simple!
-----Message d'origine-----
De : midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de Richard Casey
Envoyé : lundi 6 décembre 2010 22:45
À : 'Midrange Systems Technical Discussion'
Objet : RE: Basic stored procedure questions
David,
I've written small Java programs to test stored procedures
that had output parameters and/or result sets. They work
great. Let me know if you want to try that route and I can
post an example.
Richard Casey
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of David FOXWELL
Sent: Thursday, December 02, 2010 8:41 AM
To: Midrange Systems Technical Discussion
Subject: RE: Basic stored procedure questions
Vern,
I was trying to keep it as simple as possible, I don't see
how it could be simpler as I am no expert with stored
procedures. By call to a service program I meant to a
procedure in that program. I just recompiled to try to
reproduce the problem, and it worked this time. So, I'm
currently calling one procedure with input parameters only
from the sql session and it works fine. Will have to find
another way of exploiting a procedure with output parameters.
midrange.com
